Learn more about MySQL, starting with an overview of the MySQL logical architecture.

MySQL logical architecture

MySQL logical architecture can be divided into three layers:

  • Clients: The top-level services are not unique to MySQL; most web-based client/server tools or services have a similar architecture. Such as connection handling, authentication, security, and so on.
  • Server layer: Most of MySQL’s core service functions are in this layer, including query parsing, analysis, optimization, caching, and all of the built-in functions (for example, date, time, math, and encryption functions). All cross-storage engine functions are implemented in this layer: stored procedures, triggers, views, and so on.
  • Storage engine layer: The third layer contains the storage engine. The storage engine is responsible for storing and extracting data from MySQL. The Server layer communicates with the storage engine through apis. These interfaces mask the differences between storage engines and make these differences transparent to the upper-layer query process.

It is worth mentioning that query caching was eliminated in MySQL8.0, presumably because query caching has serious scalability issues and can easily become a serious bottleneck cache, and better performance can be achieved by moving the cache to the client.

Through the execution of a query statement, to understand some key components:

mysql> select * from T where ID=10;Copy the code

1. Connector

First, you need to connect to the database.

When the client (application) connects to the MySQL server, the server must authenticate it. Authentication is based on the user name, original host information, and password.

Connection command:

mysql -h$ip -P$port -u$user -p
Copy the code

In addition to basic authentication, the connector does some threading.

Each client connection will have a thread in the server process, and queries for this connection will only be executed in this single thread, which can only be run in rotation within a CPU core or CPU. The server is responsible for caching threads, so there is no need to create or destroy threads for each new connection.

2. Query cache

For SELECT statements, the server checks the Query Cache before parsing the Query. If a Query is found in the Query Cache, the server does not have to perform the entire process of Query parsing, optimization, and execution. Instead, the server directly returns the result set in the Query Cache.

But query caching is not recommended. Why? Because query caching often does more harm than good.

The query cache invalidates so frequently that whenever a table is updated, all the query cache on that table is cleared. For databases under pressure to update, the hit ratio of the query cache can be very low. Unless your business has a static table, it only updates once in a while. For example, a system configuration table, the query on that table is suitable for query caching.

Fortunately, MySQL also provides this “on demand” approach. You can set the query_cache_type parameter to DEMAND so that query caching is not used for default SQL statements. For statements that determine to use the query cache, SQL_CACHE can be explicitly specified as follows:

mysql> select SQL_CACHE * from T where ID=10;Copy the code

As mentioned above, MySQL8.0 completely scrapped query caching.

parsers

If the cache does not hit, MySQL will parse the query. In a nutshell, parsing takes SQL that we can understand and parses it into a language that MySQ can understand.

The parser first does “lexical parsing.” The input is an SQL statement consisting of multiple strings and Spaces. MySQL needs to identify what the strings are and what they represent.

MySQL recognizes this from the input “select” keyword, which is a query statement. It also recognizes the string “T” as “table name T” and the string “ID” as “column ID.”

Once you’ve done this, you need to do “parsing.” Based on the result of lexical parsing, the syntax parser determines whether the entered SQL statement meets the MySQL syntax based on the syntax rules.

4. Optimizer

After going through the parser, MySQL knows what we’re doing.

Instead of executing it directly, I’m going to optimize it at the level of the optimizer, which is a very complex piece of work that will help me optimize the SQL statement in the way it thinks is best and generate a plan of execution.

For example, if there are multiple indexes in a table, determine which index to use. Or determine the order in which tables are joined when a statement has multiple joins. For example, if you execute a statement like the following, this statement performs a join on two tables:

mysql> select * from t1 join t2 using(ID)  where t1.c=10 and t2.d=20;
Copy the code
  • It can first fetch the ID value of the record c=10 from table T1, then associate it with table T2 according to the ID value, and then judge whether the value of D in T2 is equal to 20.
  • 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 logical result of the two execution methods is the same, but the efficiency of the execution will be different, and the role of the optimizer is to decide which one to use.

After the optimizer phase is complete, the execution plan of the statement is determined, and then the executor phase is entered. If you have any questions about how the optimizer chose the index, or whether it might have chosen the index incorrectly, I’ll explain the optimizer separately in a later article.

5. Actuator

MySQL knows what you need to do through the parser, knows how to do it through the optimizer, and then enters the executor phase, which calls the storage engine’s interface to execute SQL based on a series of execution plans.

SQL > select * from table T; select * from table T; select * from table T; select * from table T; The query also calls Precheck to verify permissions before the optimizer).

mysql> select * from T where ID=10;

ERROR 1142 (42000) :SELECT command denied to user 'b'@'localhost' for table 'T'
Copy the code

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.

For example, in table T of our example, the ID field has no index, then the execution flow of the executor is like this:

  1. Call InnoDB engine interface to fetch the first row of the table, check whether the ID value is 10, if not skip, if yes, save the row in the result set;
  2. Call the engine interface to fetch “next row” and repeat the same logic until the last row of the table is fetched.
  3. 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.

The same logic is performed for indexed tables. 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.




Reference:

[1] : High performance MySQL

[2] : geek time MySQL Practice 45 Lecture

[3] : MySQL Technology Insider InnoDB Storage Engine

[4] : bridge Support for Query Cache

[5] : Headline 2: explain the execution process of an SQL in detail