Reprinted from ： Geek time
MySQL Server side structure model
Now I'm going to give you MySQL Basic architecture diagram , You can see clearly from it SQL Statements in MySQL The execution process in each function module of .
In general ,MySQL Can be divided into Server Layer and storage engine layer .
Server Layers include connectors 、 The query cache 、 analyzer 、 Optimizer 、 Actuators etc. , cover MySQL Most of the core service functions of , And all the built-in functions （ Such as date 、 Time 、 Mathematics and cryptographic functions ）, All cross-storage engine functionality is implemented in this layer , Like stored procedures 、 trigger 、 View etc. .
The storage engine layer is responsible for data storage and extraction . Its architecture pattern is plug-in , Support InnoDB、MyISAM、Memory Wait for multiple storage engines . Now the most commonly used storage engine is InnoDB, It is from MySQL 5.5.5 Version began to be the default storage engine .
in other words , You execute create table When building the watch , If the engine type is not specified , The default is InnoDB. however , You can also choose another engine by specifying the type of storage engine , For example create table Use in statement engine=memory, To specify the use of the memory engine to create tables . Different storage engines have different access to table data , The supported functionality also varies , In a later article , We'll talk about the choice of engines .
It's not hard to see from the picture , Different storage engines share one Server layer , From the connector to the actuator . You can first have an impression of the name of each component , I'll combine that with the one mentioned at the beginning SQL sentence , Take you through the whole execution process , Look at the function of each component in turn .
First step , You will connect to this database first , It's the connector that receives you . The connector is responsible for establishing a connection with the client 、 Access permissions 、 Maintaining and managing connections . The connection command is usually written like this ：
mysql -h$ip -P$port -u$user -p
After the command is lost , You need to enter the password in the interactive dialogue . Although the password can also be directly followed by -p It's on the command line , But this may cause your password to leak . If you're connected to a production server , It is strongly recommended that you do not do this .
Connect... In the command mysql It's a client tool , Used to establish a connection with the server . In the completion of the classic TCP After shaking hands , The connector is about to start authenticating your identity , This is the user name and password you entered .
- If the user name or password is wrong , You'll get one "Access denied for user" Error of , Then the client program ends execution .
- If the user name and password are authenticated , The connector will go to the permission table to find out the permissions you have . after , The permission judgment logic in this connection , Will depend on the permissions read at this time .
That means , After a user successfully establishes a connection , Even if you use the administrator account to modify the permissions of this user , It does not affect the permissions of existing connections . After the modification is completed , Only new connections will use the new permission settings .
When the connection is complete , If you don't follow up , This connection is idle , You can show processlist See it in the command . The picture in the text is show processlist Result , Among them Command The column is shown as “Sleep” This line , It means that there is an idle connection in the system .
If the client does not move for a long time , The connector will automatically disconnect it . This time is determined by the parameter wait_timeout The control of the , The default value is 8 Hours .
If after the connection is broken , The client sends the request again , You'll get an error alert ： Lost connection to MySQL server during query. At this time, if you want to continue , You have to reconnect , Then the request is executed .
In the database , Long connection refers to the successful connection , If the client continues to have requests , Always use the same connection . Short join refers to the disconnection after a few queries are executed , Create another query the next time . The process of establishing a connection is usually more complicated , So I suggest that you try to minimize the action of establishing connection in use , That is, try to use long connections .
But after using all the long connections , You might notice , Sometimes MySQL Memory usage is rising very fast , This is because MySQL Memory temporarily used during execution is managed in connection objects . These resources will be released when the connection is broken . So if long connections accumulate , May cause too much memory , Killed by the system （OOM）, From a phenomenological point of view MySQL Abnormal restart .
How to solve this problem ？ You can consider the following two options .
Regularly disconnect long connections . Use it for a while , Or it can be judged in the program that a large memory consuming query has been executed , disconnect , Then query and reconnect .
If you're using a MySQL 5.7 Or later , You can do this after each large operation , Through execution mysql_reset_connection To reinitialize the connection resources . This process does not require reconnection and re-authorization , But it restores the connection to the state it was in when it was created .
After the connection is established , You can do it select Statement . The execution logic will come to the second step ： The query cache .
MySQL After getting a query request , I will go to query cache first , Have you executed this statement before . Previously executed statements and their results may be key-value On the form of , Is directly cached in memory .key Is the statement of the query ,value Is the result of a query .
If your query can be found directly in this cache key, So this value Will be returned directly to the client . If the statement is not in the query cache , I'm going to continue the execution phase . After execution , The execution result will be stored in the query cache . You can see , If the query hits the cache ,MySQL There is no need to perform the following complex operations , I can just return the result , This will be very efficient .
But for the most part I would advise you not to use the query cache , Why? ？ Because query caching often does more harm than good .
Query cache failures are very frequent , As long as there is an update to a table , All query caches on this table will be cleared . So it's likely that you're struggling to save the results , It's not in use yet , It's all cleared by an update . For databases that are under pressure to update , The hit rate for the query cache will be very low . Unless your business is a static table , It takes a long time to update . such as , A system configuration table , Then the query on this table is suitable for the query cache .
Fortunately MySQL This is also provided “ According to the need to use ” The way . You can set the parameters query_cache_type Set to DEMAND, So for the default SQL Statements do not use the query cache . For the statements that you decide to use the query cache , It can be used SQL_CACHE Explicitly specify , Like the following statement ：
mysql> select SQL_CACHE * from T where ID=10;
It should be noted that ,MySQL 8.0 Version of the query cache directly removed the entire block , in other words 8.0 It's starting to disappear completely .
If the query cache is not hit , It's about time to actually execute the statement . First ,MySQL Need to know what you're going to do , So you need to SQL Statement parsing .
The analyzer will do “ Lexical analysis ”. What you enter is a string with multiple Spaces SQL sentence ,MySQL You need to identify the strings in it , What is the .
MySQL From what you typed "select" This keyword recognizes , This is a query statement . It also takes strings “T” Identify a “ Table name T”, Put the string “ID” Identify a “ Column ID”.
After these identifications , Just do it “ Syntax analysis ”. According to the result of lexical analysis , The parser will follow the grammar rules , Judge the one you typed SQL Does the statement satisfy MySQL grammar .
If your statement is wrong , Will receive “You have an error in your SQL syntax” Error warning , Take the following statement select Less initial letters “s”.
mysql> elect * from t where ID=1; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'elect * from t where ID=1' at line 1
A general syntax error will indicate the first place where the error occurred , So what you want to focus on is immediately “use near” The content of .
Through the analyser ,MySQL You know what you're gonna do . Before we start executing , It's also handled by the optimizer .
The optimizer is when there are multiple indexes in a table , Decide which index to use ; Or there are multiple table associations in a statement （join） When , Determine the join order of the tables . Let's say you execute the following statement , This statement executes two tables join：
mysql> select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;
- You can start from the table t1 The inside out c=10 The record of ID value , According to ID Values are associated to tables t2, To determine t2 Inside d Is the value of 20.
- You can also start from the table t2 The inside out d=20 The record of ID value , According to ID The value associated with the t1, To determine t1 Inside c Is the value of 10.
The logical result of the two execution methods is the same , But the efficiency of the execution will be different , The role of the optimizer is to decide which scheme to use .
After the optimizer phase is complete , The execution of this statement is determined (explain see ), Then we go to the executor phase . If you have any questions , For example, how does the optimizer choose indexes , Is it possible to choose wrong etc , No problem , I'll expand the description of the optimizer separately in a later article .
MySQL I know what you are going to do through the analyzer , You know what to do with the optimizer , So it goes into the actuator stage , Start statement execution .
At the beginning of execution , Let's first judge what you do to this watch T Do you have permission to execute the query , without , An error with no permissions is returned , As shown below ( In engineering implementation , If you hit the query cache , When the query cache returns results , Do authority verification . Queries will also be called before the optimizer precheck Verify permissions ).
mysql> select * from T where ID=10; ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'
If you have authority , Open the table and continue . When I open my watch , The actuator is defined according to the engine of the table , Use the interface provided by the engine .
For example, the table in our example T in ,ID Field has no index , So the execution process of the actuator is like this ：
- call InnoDB The engine interface takes the first row of this table , Judge ID Value is 10, If not, skip , If it is, the row will exist in the result set ;
- Call the engine interface “ The next line ”, Repeat the same logic of judgment , Until you get to the last row of the table .
- The executor returns the record set composed of all the rows that meet the conditions in the traversal process to the client as a result set .
thus , This statement is executed .
For tables with indexes , The execution logic is similar . The first time I call this “ Let's take the first row that satisfies our condition ” This interface , And then we loop “ That satisfies the next row ” This interface , These interfaces are all defined in the engine .
You will see a... In the slow query log of the database rows_examined Field of , Indicates how many rows were scanned during the execution of the statement . This value is accumulated each time the actuator calls the engine to get a row of data .
In some cases , The executor is called once , Inside the engine, it scans multiple lines , So the engine scans the number of rows followed rows_examined It's not exactly the same . We will talk about the internal mechanism of storage engine in a special article later , There will be detailed instructions .