Shulou(Shulou.com)06/02 Report--

This article will explain in detail how to analyze the MySQL architecture, the content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have some understanding of the relevant knowledge after reading this article.

To understand how mysql works, you must first have some understanding of the architecture of mysql.

Recently, I have been hit hard by some things, and I feel that no matter how hard I may try, my career will be like this. So I suddenly lost my original passion for specializing in technology and blogging.

But in any case, the road is chosen by yourself, and you have to finish it even if you cry. Besides, there seems to be no other way to go at the moment.

Mysql architecture

Mysql architecture

From the figure, you can see that MySQL consists of the following parts:

Connection pool component

Manage services and tool components

SQL interface component

Query Analyzer component

Optimizer component

Buffer (Cache) component

Plug-in storage engine

Physical file

Description:

One of the most important features that distinguishes MySql database from other databases is its plug-in table storage engine.

It should be noted that:

The storage engine is based on tables, not databases.

Based on this feature, most of the business databases in the project need to support transaction attributes, so the InnoDB storage engine is used. But for tables with read-only characteristics, we can consider using the MyISAM storage engine, which does not break the transactional nature of the database.

detailed description

The top layer is the connection component. The following server consists of connection pooling, management tools and services, SQL interface, parser, optimizer, cache, storage engine, and file system.

Connection pooling: since each establishment takes a lot of time, the function of connection pooling is to cache these connections. Next time, you can directly use the established connections to improve server performance.

Management tools and services: system management and control tools, such as backup and restore, Mysql replication, clustering, etc.

SQL interface: accepts the user's SQL command and returns the results that the user needs to query. For example, select from calls SQL Interface.

Query parser: when SQL commands are passed to the parser, they are validated and parsed by the parser (permissions, syntax structures)

Main functions:

A. Decompose the SQL statement into a data structure and pass this structure to the next step, based on which the delivery and processing of the SQL statement is based

b. If an error is encountered in the decomposition composition, then the sql statement is unreasonable.

Query optimizer: query optimizer, the SQL statement uses the query optimizer to optimize the query before the query. He uses the "select-project-join" strategy to query.

It can be understood by an example: select uid,name from user where gender = 1

This select query is selected according to the where statement first, rather than querying all the tables first and then gender filtering.

This select query first projects attributes based on uid and name, rather than taking out all the attributes and then filtering them.

Join these two query conditions together to generate the final query result.

In addition, the query optimizer can also control a sql statement to query which index to go, and automatically adjust the order of where conditions in the sql statement to meet the rules of the leftmost matching principle of the composite index.

Cache: query cache (removed after MySQL version 8.0). If the query cache has a hit query result, the query statement can directly fetch data from the query cache.

If the cold end of the data is overflowed by the LRU algorithm, it will be refreshed to the data page of the disk in time in the future, which is called the dirty page.

This caching mechanism consists of a series of small caches. Such as table cache, record cache, key cache, permission cache, etc.

Caching is not recommended for MySQL queries, because query cache invalidation can be very frequent in real business scenarios, and if you update a table, all query caches on that table will be emptied.

Therefore, it is generally not recommended to use query caching in most cases.

The cache feature has been deleted after MySQL version 8.0, and officials also believe that this feature is relatively rare in actual application scenarios, so it is simply deleted.

Plug-in storage engine: to put it bluntly, the storage engine is a way to manage operational data (storing data, how to update, query data, etc.). Because data is stored in a relational database in the form of a table, the storage engine can also be called a table type (that is, the type that stores and manipulates the table)

Physical files:

Physical files include: log files, data files, configuration files, pid files, socket files, etc.

The execution flow of a query statement

With all that said, how exactly is a sql statement executed? In fact, our sql can be divided into two types, one is query, the other is update (add, update, delete). Let's first analyze the query statement as follows:

Select * from tb_student A where A.ageThe 18' and A.nameplate 'Zhang San'

Combined with the above instructions, let's analyze the execution flow of this statement:

1. Establish a connection

2. Call the sql API

3. Authentication: first check whether the statement has permission. If not, error message is returned directly.

4. Cache judgment: if you have permission, the cache will be queried before the MySQL8.0 version, and this sql statement will be used to query whether there is a result in memory for key. If there is a direct cache, if not, perform the next step.

5. Parse the sql statement:

Through the parser for lexical analysis, extract the key elements of the sql statement, for example, extract the above statement is the query select, extract the table to be queried is called tb_student, you need to query all the columns, the query condition is the id='1' of the table. Then determine whether the sql statement has syntax errors, such as whether the keywords are correct, etc., and if there is no problem with the check, proceed to the next step.

6. Query optimization to sql sql statements

Next, the optimizer determines the execution plan, and the above sql statement can be executed in two ways:

a. First inquire the student whose name is "Zhang San" in the student table, and then determine whether the age is 18.

b. First find out the 18-year-old students, and then inquire about the students whose name is "Zhang San".

Then the optimizer chooses the one with the best execution efficiency according to its own optimization algorithm (sometimes, according to the optimizer, it is not necessarily the best). After confirming the execution plan, we are ready to start the execution.

7. Execute the query and return the result

Physical file description

1. Log file

Error log error log troubleshooting / var/log/mysqld.log [enabled by default]

Bin log binary log backup incremental backup DDL DML DCL

Relay log Relay Log replication receives replication master

Slow log slow query log tuning query time exceeds the specified value

Default location of log files:

Error Log

Log-error=/var/log/mysqld.log

Binary Log

Log-bin=/var/log/mysql-bin/bin.log

After server-id=2 version 5.7.x

Slow Query Log

Slow_query_log=1 | 0

Slow_query_log_file=/var/log/mysql-slow/slow.log

Long_query_time=3

2. Configuration file

/ etc/my.cnf- > / etc/mysql/my.cnf- > / usr/etc/my.cnf- > ~ / .my.cnf

In case of conflict, the last read shall prevail.

Initialization parameters for mysqld service startup are included in the [mysqld] group.

The [client] group contains parameters that can be read by client tool programs, as well as other specific parameter groups for each client software, etc.

3. Data file

1. .frm file

Regardless of the storage engine, each table has a .frm file named after the table name, and the meta information related to the table is stored in this file, including the definition of the table structure, and so on.

2. .MYD file

The myisam storage engine is dedicated to storing data from myisam tables (data). Each myisam table has a .MYD file that echoes it and is also stored in the directory of the database to which it belongs.

3. .MYI file

It is also dedicated to the myisam storage engine to store the index-related information of the myisam table. For the myisam storage engine, the content that can be cached (cache) is mainly from .MYI files.

Each myisam table corresponds to a .MYI file in the same location as .frm and .MYD.

4. .ibd files and ibdata files

Data files (including indexes) that store innoDB. InnoDB storage engine has two tablespace modes: exclusive tablespace and shared tablespace.

Exclusive tablespace: use .ibd files to store data, and each table has an .ibd file, which is stored in the same location as myisam data.

Shared tablespaces: using .ibdata files, all tables share one (or more, self-configured) ibdata files.

Ibdata1: system tablespace (data file) undo segment

A group of ib_logfile0 redlog files

Ib_logfile1

Db.opt file this file is available in every self-built library, recording the default character set and verification rules used by the library.

4. Pid file

Pid file is a process file of mysqld application in Unix/Linux environment. Like many other Unix/Linux server programs, it stores its own process id.

5. Socket file

Socket files are also available in Unix/Linux environment. In Unix/Linux environment, users can connect to mysql directly using Unix Socket instead of TCP/IP network.

Database, database instance, database server

Database (database):

A collection of data files on the operating system or storage. In mysql database, database files can be files ending with * .frm, .MYD, .MYI, * .ibd, etc., and different storage engines have different file types.

More inclined to the storage aspect of the file.

Database instance (instance)

It consists of background processes or threads and a shared memory area. Shared memory can be shared by running background threads.

It is important to note that it is the database instance that really operates the database.

More inclined to the application operation.

Relationship between database and database instance:

Usually, there is an one-to-one correspondence between database instance and database, that is, one database instance corresponds to one database.

However, in a clustered environment, there are multiple database instances that share a database.

For example, the tables in a database can be distributed among multiple database instances through a sub-table strategy.

The database instance refers to the database application, and the database refers to the database created on the database instance.

For example, if a 3306 port mysql application is deployed on the server, it is a database instance.

If you continue to deploy a 3309 mysql application, you are deploying two instances of msql on one service.

On the mysql instance on port 3306, create a database named order, which creates a database.

Database server (database server)

Deploy the server where the database instance is installed.

Summary

1. Mysql architecture, including connection pool components, management services and tool components

SQL interface component, query analyzer component, optimizer component, Cache component, plug-in storage engine

Physical files.

2. Analyze the execution process of a query statement: establish a connection-"permission check -" query cache-"analyzer -" optimizer-"permission check -" executor-"engine.

3. Distinguish between database, database instance and database server.

[happy moment]

On how to analyze the MySQL architecture to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.