This is the sixth day of my participation in the November Gwen Challenge. See details: The Last Gwen Challenge 2021.
- If the article is helpful to you, welcome to pay attention to, like, collect (one key three even), have any questions welcome private letter, see will reply in time!
One, foreword
- Hello, everyone, I am Xiao Cheng, “From 0 to 1- Comprehensive and profound Understanding of MySQL series” has come to the fourth chapter, this chapter mainly from the beginning of a SQL execution, from shallow to deep parsing OF SQL statements from the client to the server complete execution process, and finally achieve the purpose of “know what is, know why”.
- Recently, I often received feedback from some partners. In order to facilitate communication, I created a communication group. If you are interested, you can add it.
- “From 0 to 1- comprehensive and profound understanding of MySQL series” series of articles will continue to update, interested partners can follow me, together with cheer, progress together! , if it helps, don’t forget a key triple oh,ღ(´ ᴗ · ‘) than heart!
How is a SQL query executed
A few days ago on the Internet for an interview, interview questions about the database is not back less, but the interviewer’s question how much you let me touch some guard, he did not ask more common syntax and the basis of the optimization problem, but let me explain a SQL from the client to the server implementation process, it makes me very embarrassed, because at ordinary times on the surface of the application layer Things, not to really understand something deeper, so unconventional problems, it is easy to be circle, hope everybody to my guide, learn knowledge to achieve “learning, know the why” as far as possible, so that even if the interviewer transformation problem point of view, we can better deal with answer, finished DaoLao, positive start!
Most common normal work, we is from the client sends a corresponding data table SQL to the database server operation, actually abstract up is: the client (that is, our business code) sent a text, SQL server received a text and then parse SQL processing, eventually return to a text (results).
What does the server actually do to the client to parse the steps before it returns the result? Here we first through a picture of the image of the analysis of the process, and then the specific analysis of the process.
2.1 Connection processing module:
The module mainly manage the client’s connection, the client can via TCP/IP and named pipes, Shared memory, a socket connection with the server, the server receives a connection, will generate a special thread to handle the client’s request, when you’re done with the client’s request, the thread will not be destroyed, but in the thread pool, thereby reducing the frequency The consumption of creating and deleting threads greatly saves system resources and improves efficiency.
Each time a client initiates a connection request, it carries authentication information such as the user name and password. If the server fails to pass the authentication, the connection is rejected. At the same time, if many clients request connections at the same time, the maximum number of connections can be limited to avoid server program crash and improve efficiency.
2.2. Analyze and optimize modules
After the connection is established between the client and the server, the server has a special thread to process the request from the client. In this case, the server receives a text message from the client and needs to convert it into identifiable information. The procedure is as follows:
2.2.1. Query cache
Just think, you use mobile phone to see a movie tonight at the weekend learning experience, suddenly call to a boss, let you to deal with an emergency things, you have to interrupt the movies to deal with emergencies, processing after you definitely want to continue to watch from the last position of the play rather than watch from the ground up, the effect of cache is here.
To improve the response efficiency, the Mysql server program generates a cache based on the information requested by the client. If the requested information matches the cache, it is returned directly without further interaction with the underlying layer.
However, the Mysql server program is not as intelligent as humans. If two requests for different text such as extra Spaces, upper case, and functions that return different values each time, it will not hit the cache because it cannot determine whether these extra things affect the final result of the SQL execution.
The cache detection program in Mysql will monitor every table involved in the cache. If the data or structure of the table changes, such as the execution of insert, ALTER and other commands, then it will invalidate and delete the cache corresponding to the table. Because maintaining the cache is expensive, especially if there are many tables, the query caching process was removed in Mysql8.0.
2.2.2 Syntax analysis
If the request does not hit the cache, it enters the step of parsing, because the server program receives the text message sent by the client, the Mysql server program needs to parse out the specific meaning of the request from the text, such as what fields to query, which tables to query, etc
2.2.3 Query optimization
After the parsing step, the server program already knows the information requested by the client, such as the requested table, data, etc., but the server program will not immediately execute based on this information. It will parse out the statement for some optimization, such as: sub-connection to associated query, internal and external connection query, to achieve the maximum optimization efficiency, the result of optimization is to generate an execution plan, is usually we use the Explain keyword to see a result.
3. Storage engine module
After two steps of connection processing and parsing optimization, there is still no actual data processing. In Mysql, the operation of data storage and extraction is extracted into a module called storage engine.
On logic, we see is a table of data is a line of form, but the actual physical level, how the table data storage, how to read the data of the table, it is need to responsible for the operation of the storage engine, provide different storage engines in Mysql, different storage engines storage data structure may not be the same, the algorithm can also be different.
4, extension
We often see the concepts of MySQL Server layer and storage engine module in some teaching videos or professional articles. They are defined as follows:
For the convenience of management, connection processing/management, query caching, syntax parsing, query optimization and other functions that do not involve real data access are divided into Mysql Server layer functions.
The functions related to real data access are divided into the functions of storage engine modules. The Mysql Server layer accesses the storage engine responding to the query through the API provided by each storage engine. After the Mysql generates the execution plan through query optimization, it can call the API provided by the storage engine to obtain the corresponding data and return it to the client.
3, MySQL flow often meet questions
3.1 Execution sequence of database statements
(I): Order of execution
From -> on -> join -> WHERE -> group by -> having -> count -> select -> distinct -> order by -> limit
(II) Explanation of execution Steps:
(1) from: indicates the source of data
(2). On: indicates the associated table of data. After the execution, a temporary table T1 is generated and provided for the next operation
(3) join: add the data of the join table to the temporary table T1 after the execution of ON. For example, left JOIN will add the remaining data of coordinates to the temporary table T1. If there are more than three joins, repeat on… Step between join.
(4) WHERE: According to the conditions carried, the qualified data is screened from the temporary table and temporary table T2 is generated.
(5) Groub by: According to the conditions carried, group the corresponding data of temporary table T2, and form temporary table T3. If the statement contains group BY, the fields following it must appear in select or appear in aggregate function, otherwise SQL syntax error will be reported.
(6), Having: Filter the data of temporary table T3 after grouping, get temporary table T4.
(7) Aggregation functions such as count: perform aggregation function operations on specified fields of temporary table to form temporary table T5.
(8) select: Select the data to be returned from the temporary table to form temporary table T6.
(9). Distinct: The temporary table T6 is de-filtered to form temporary table T7.
(10), order by: create temporary table t8
(11) limit: Filter the number of returned data items
To learn more about the problems of the execution process, check out the previous article on the execution process: Do you really know how to use Group by?
Fourth, series of articles
1, from 0 to 1- Comprehensive understanding of MySQL series – The most detailed MySQL installation process (Windows Edition)
2, “From 0 to 1- Comprehensive understanding of MySQL series” – the most detailed MySQL installation process (Linux environment)
How to connect to the database when you forget your MySQL login password
Five, the summary
As we know from the above,MySQL Server is divided into MySQL Server layer and storage engine layer. The MySQL Server layer is mainly responsible for client connection, syntax parsing, syntax optimization and other operations, while the storage engine layer is responsible for the actual data access operations.
A complete parsing of an SQL statement goes through the following steps: Client and server request processing – query caching – Syntax parsing – Query optimization – Storage engine access to data – Return processing results
Next time an interviewer asks you this question, throw this article in his face. Finally, if you feel that the article is helpful to you, don’t forget one key three even oh, your support is my motivation to create more high-quality articles, there are any questions can be private letter to me, see will give you a timely reply!