This article has participated in the “Digitalstar Project” and won a creative gift package to challenge the creative incentive money.
After writing a lot of queries, I have never looked at how the SELECT statement is implemented from the start of the query to the return of data.
I only pay attention to the data returned from the query, and very little attention to how the data is returned.
For example, mysql server address, or user password error, such as written SQL statement syntax error, how can mysql identify.
The presumptuous knowledge is to find data directly from a table and return what??
Until recently, our company needed to build a metadata management system. I suddenly thought of this question. If I need to write the function of select query, how will I write it?
The best way to do this is to take a look at the mysql website and move the implementation directly into the metadata management module.
Do any requirements first have their own independent thinking, and then to verify, so their first to think about mysql this query execution process needs to do what?
- Verify that the mysql address and port, user name and password are correct.
- If yes, take the query sent to us by the client and verify that the query is correct.
- If the query statement is correct, check whether all tables in the query exist, then check whether the query fields are correct, and then check whether the query conditions are correct. The fields must be combined with the queried table to determine their correctness.
- If the table, field, and condition of the query are correct, then we need to verify that the SELECT statement is correct.
The main execution steps I want to these, can link to the database server, query statement format is no problem, query table and field is no problem, so it is to execute the query.
Go and see what the authorities are doing…
When we execute a normal SELECT statement, we normally connect to our mysql database with a connection pool.
The connection pool configuration includes the IP address, port number (3306 by default), user name, and password.
When the connection information is correct, the mysql statement is executed to query the records that meet the SQL statement. When the mysql statement is executed, the execution flow is as follows:
- In this process, the connector performs the function of verifying mysql link information that we analyzed ourselves above. The lexical analyzer serves the purpose of verifying the query format and correctness of the statement we analyzed above. The executor is to execute the query, so we do our own analysis without the lexical optimizer and without considering caching.
The connector
The connector verifies that the address and port of the mysql server are correct, that the user name and password are correct, and that the current user has query permission.
If all this is true then you can establish a connection to the mysql server, then how to manage the connection after the connection is established is a problem.
Whether to release the connection when the current user finishes querying the current statement, or establish a long connection to maintain the status of the connection query.
What mysql is doing is setting up a connection with the server. After the TCP handshake is complete, a long connection is maintained. If you query a single query from the connected service and nothing else is done, the connection is idle and can be viewed with the show processList command.
Of course, we can’t keep the idle connection running for a long time, so we can set a parameter value to configure the idle connection disconnection time.
Just like when we set the redis cache expiration time, you keep using the current cache and the cache expiration time will keep refreshing, but when you stop using the current cache and the cache expiration time will expire, the connection will be disconnected when we are idle and at our configured maximum time.
Mysql’s official configuration parameter is wait_timeout. The default is 8 hours.
At this point, the connection to mysql is complete and can be used for some time before the connection is disconnected. Disconnect how to disconnect disconnect need to re-use must be re-establish the connection ah.
Check the cache
My first impression of a cache is a noSQL cache like Redis.
Mysql has a cache for each query statement. If you query the same statement every time and the query result is the same, then I will take the result from the mysql cache. I do not need to perform the operation of the database query.
Mysql creates a key-value map structure if query caching is enabled.
Key is the query statement, value is the query result, so when the query statement comes, we first look for the result in the cache of the key-value, if we find the result, we directly return.
How can I guarantee that the result of my query is the same as the result of the database query?
SQL > alter table update; SQL > alter table update; SQL > alter table update; SQL > alter table update; SQL > alter table update; The result of the query from the cache is the same as the result of the database query.
Mysql is better for static tables that do not need to be updated than for static tables that do not need to be updated.
At present, if we need cache, it is more realized by other technologies, such as Redis cache.
After mysql8.0, mysql’s query cache was deprecated. The cache knowledge comes next to the parser.
analyzer
The analyzer is easy to understand, is to analyze the SQL query you write is correct.
Will be analyzed from the perspective of grammar and morphology, I think I first analyze whether the keyword is correct, whether there is select, from and other keywords.
I will divide the query statement into several sections, from SELECT to form is a paragraph, form to WHERE is a paragraph, after where is a paragraph, analyze whether there is a problem in the whole statement. Mysql certainly has its own analysis logic, which is not delved into here.
If the analysis finds that there is a problem with the WRITTEN SQL statement, an error message will be displayed, and the location of the error message will be displayed.
When the parser is done, you can write a query that is basically fine and run without error.
The optimizer
In the process of my above analysis, I did not think of the need for the optimizer, I think the SQL statement is correct then is to execute the query results, mysql before the query added the optimizer.
As the name implies, the optimizer is to optimize the SQL statement we write, for example, if there is an index will determine which index to go, if there is a table association will determine which table to associate first and then associate which table, etc. Is that we write SQL through the optimizer’s execution logic, optimization again, but the execution result will not change, the efficiency of the optimized query (time) will be improved.
If we write SQL is not optimized then I think mysql does not need the optimizer step, but we can not do wow…. Can’t do it wow…
Optimizer is the icing on the cake. Now let’s look at the actuators.
actuator
The executor is to execute the SQL statement we wrote, which is the last step in the query process.
Mysql will judge whether the user has permission to query the table in this step, but I wonder whether the judge whether the user has permission to query the table can refer to the analyzer step, you do not have permission to query, but also go to the optimizer executive why.
The executor returns the result set to the client after execution. This is the end of the whole query process.
To this whole SQL query statement from the query to return data have a general understanding, so if you need to optimize the efficiency of mysql execution, not only from the executor step analysis will also consider in the whole execution process where can be optimized…
Well bye bye I wish you a happy National Day……..