Ask questions:

As a new background engineer, I only realized the functions of the project at first, such as adding, deleting, modifying and searching, and establishing the basic query index. Until an interviewer asked me a question, how exactly does a SQL query execute in mysql database? I was abused. I was happy. Thank him. So I started to learn mysql in depth. This article is approved by

How does an SQL query execute in mysql database?

To explain the mysql infrastructure in detail.

Interpretation of the

mysql> select * from Student where ID=1;
Copy the code

The simple query above is simple, but I don’t think many developers know how to execute it inside MYSQL.

The basic architecture of Mysql

The basic architecture of Mysql.

The Server layer includes connectors, query caches, analyzers, optimizers, actuators, etc. These cover most of the core services of MySQL and all the built-in functions (such as date, time, math and encryption functions, etc.). Functions across storage engines are implemented in this layer, such as stored procedures, triggers, views, etc.

The storage engine layer is responsible for data storage and extraction, and provides data read and write interfaces. Its architecture mode is plug-in, supporting InnoDB, MyISAM, Memory and other storage engines. The most commonly used storage engine under development is InnoDB, which has been the default storage engine since MySQL5.5.5, but developers can select other storage engines by specifying the type of storage engine.

Even if the storage engines are different, they share a Server layer. The following describes the execution process of the Server layer and its functions.

The connector

Run the query statement to start the query is the first step to connect to the database, this time waiting for you is the connector. Connectors establish connections to clients, obtain permissions, and maintain and manage connections.

In normal development mode, the client and server need to establish a connection. After the two complete the classic TCP handshake, the Server layer connector begins to authenticate your identity, which in this case is the username and password used by server-side code.

Connector description:

  • Connection: If the user name or password is incorrect, the server will receive the error “Access denied for user”, and the client will report that the login cannot be used.
  • When connecting: If the user name and password are authenticated, the connector will check the permission table for you. , through the connection query to the permissions for all kinds of logic, and read permissions will depend on the connection (to note here that is to say, a database user after successful connection is established, even if you use administrator account to modify the user permission to do the, also won’t affect the already existing connections. After the permission is changed, the new permission can be used only when a new connection is established.
  • After connection: If no operation has been performed on the database, the connection will remain idle. Show ProcessList shows all connections, where the Command column indicates that a connection is idle if the Command column “Sleep” is in the connection state
mysql>show processlist
Copy the code

  • Disconnection related: The connector automatically disconnects the client if it is inactive for too long. This time is controlled by the wait_timeout parameter, and the default is 8 hours. If the client sends a request again after the connection has been disconnected, it will receive an error message: Lost Connection to MySQL Server during Query. At this point, if you want to continue, you need to reconnect and then execute the request. In the database, a persistent connection means that the same connection is used all the time if the client continues to receive requests after a successful connection. A short connection is one that is disconnected after a few queries are executed and then re-established the next time.

  • The process of establishing a connection is usually complicated, so I recommend that you minimize the action of establishing a connection by using long connections as much as possible.

Better connection mode Long connection problems and solutions:

After using all long connections, you may find that MySQL memory usage increases very quickly in some cases, because the memory temporarily used by MySQL during execution is managed in connection objects. These resources are released when the connection is disconnected. If long connections are accumulated, the system may use too much memory and kill it forcibly (OOM). In this case, MySQL restarts abnormally. How to solve this problem? There are two options you can consider.

  1. Disconnect long connections periodically. Use for a period of time, or in the program to determine the execution of a large memory – consuming query, disconnect, then query and reconnect.

  2. If you are using MySQL 5.7 or later, you can re-initialize the connection resource by executing mysql_reset_connection after each large operation. This process does not require reconnection and re-authentication of permissions, but restores the connection to where it was when it was created

The query cache

After the first connection is established, you can execute the query statement. Step 2: Query cache. Mysql determines the query statement and goes to the query cache to see if it has been executed before. If this query statement has been executed before, the query result may be directly cached in the memory in key-value mode. Key is the query statement and value is the queried value. In this case, the query cache returns the value directly to the client. If a query statement is executed by step in the cache, it is normally executed. After obtaining new query results, the statement is stored in the cache.

Description:

  • Query caching is not recommended in most cases. Query caching often does more harm than good.

    The query cache invalidates so frequently that whenever a table is updated, the entire query cache for that table is cleared. So it’s very likely that an update will wipe out your results before you even use them, especially for update-pressured databases where the hit rate of the query cache is low. However, it is not impossible to use, if a static table (system configuration table) is updated over a long period of time, this situation is more suitable to use the query cache.

  • How to set Mysql not to use query cache

    Set the Mysql parameter query_cache_type to DEMAND so that none of the default SQL statements use query caching

  • How can I specify a query statement that uses the query cache to determine the statement that uses the query cache with SQL_CACHE

    mysql> select SQL_CACHE * from Student where ID=1;
Copy the code

Note:

Mysql 8.0 removes query cache pairs from the database.

analyzer

If no cached data is found in the query cache, actual query execution begins. What does Mysql need to do up to this query? Therefore, SQL statements need to be parsed.

Analysis process:

  • Lexical analysis

    The parser first performs lexical analysis. The query contains multiple strings and Spaces, and Mysql needs to identify what the strings represent.

    mysql> select * from Student where ID=1;
Copy the code

    Analyzing the query, the “SELECT” keyword identifies it as a query. Select * from Student where ID = ID; select * from Student where ID = ID

  • Syntax analysis

    After lexical analysis, statement analysis determines whether the entered SQL statement meets the MySql syntax based on the syntax rules. You have an error in your SQL syntax. For example,

    mysql> elect * from Student where ID=1;
Copy the code
    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

    When reading the error prompt, pay attention to the text immediately following “use near”

The optimizer

After the analyzer executes, it reaches the optimizer.

The optimizer does these optimizations:

  • When there are multiple indexes in the table, the optimizer decides which index to use in the query statement

  • When a query has multiple table joins, the order in which the tables are joined is determined.

    Examples are as follows:

    mysql> select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;
Copy the code
    1. In this example, the ID value of the record c=10 can be first extracted from table T1, and then associated with table T2 according to the ID value, and then determine whether the value of D in T2 is equal to 20.
    2. It can also take the ID value of the record d=20 from table T2, and then associate it with T1 according to the ID value, and then judge whether the value of C in T1 is equal to 10.

    The results of the two associated query schemes will be the same, but the execution efficiency will be different, and the optimizer will decide which one to use.

actuator

MySQL knows what you need to do from the parser, and knows how to do it from the optimizer (what’s the execution plan?). , and then it enters the executor phase and begins to execute the statement.

Select * from Student where Student = Student where Student = Student where Student = Student; select * from Student where Student = Student where Student = Student; The query also calls Precheck to verify permissions before the optimizer).

mysql> select * from Student where ID=10;
Copy the code
ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'Student'

If you have permission, open the table and continue. When a table is opened, the executor uses the interface provided by the table engine according to its definition.

The storage engine

Go to the storage engine and execute the data read and write interface provided by the storage engine. In this query, the process of reading and writing data by the executor is considered in two cases:

  • Select * from Student where ID is not indexed;

    Call InnoDB engine interface to fetch the first row of the table, check whether the ID value is 1, if not skip, if it is stored in the result set;

    Call the engine interface to fetch “next row” and repeat the same logic until the last row of the table is fetched.

    The executor returns the recordset of all rows that meet the criteria in the above traversal as a result set to the client.

    At this point, the statement is complete.

  • Student (ID); Student (ID);

    Tables with indexes perform similar logic. The first call is the “fetch the first row that meets the condition” interface, and then the “next row that meets the condition” interface is iterated, all of which are already defined in the engine.

conclusion

So far, a query statement has provided an overview of the basic flow of executing the mysql architecture. In this process, there will be a lot of details and areas to learn more about, such as joins, indexes, logging systems, etc., and then we will continue to learn and document some of the things MySql digs into.

Push the article:

How to write elegant SQL native statements? The two articles together will help you thoroughly understand how SQL statements are executed in the schema and how to write good SQL.

Find this article helpful? Please share it with more people

My official account.jpg

Welcome everyone to pay attention to my public number — programmer growth refers to north. Please search by yourself on wechat — “Programmer growth refers to north”