You need to retrieve some data that meets the requirements from the database , It's easy for us to write Select A B C FROM T WHERE ID = XX In this way SQL, So when we send such a request to the database , What the database does ？

Today we use MYSQL For example , Reveal it MySQL Database query process , And let you know some parts in the database .

1、MYSQL framework

MySQL Mainly can be divided into Server Layer and storage engine layer .

Server layer   Including connectors 、 The query cache 、 analyzer 、 Optimizer 、 Actuators etc. , All cross-storage engine functionality is implemented in this layer , Like stored procedures 、 trigger 、 View 、 Functions, etc , There is also a general logging module binlog Log module ;

Storage engine layer   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（ Support transactions ）, It is from MySQL5.5.5 Version starts to be the default storage engine .

2、 The connector

Connector is mainly responsible for user login database , Authentication of users , Including checking account password , Permission and other operations .

If the user's password is wrong , You'll get one "Access denied for user" Error of , Then the client program ends execution .

If the user account password has passed , The connector will query all permissions of the user in the permission table , Later, the logical judgment of permission in this connection will depend on the permission data read at this time , in other words , In the future, as long as the connection is kept open , The immediate administrator modified the user's permissions , The user is also unaffected .

3、 The query cache （ Query cache）

After the client establishes a connection with the server ,MySQL When the query statement is executed, the cache is queried first , Check this one SQL Was it executed before . Previously executed statements and their results will be written as 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 you don't hit , You need to perform subsequent operations , The result will also be cached after completion , Convenient for the next call .

If you see this, you'll see a bright future , Will there be an impulse to make good use of this function .

In fact, query caching is not recommended here , 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 was emptied by an update before it was used . For databases that are under pressure to update , The hit rate for the query cache will be very low . Unless it's a table that won't be updated for a long time , For example, the system configuration table , But this kind of system configuration we put in the configuration platform is not good ？

stay MYSQL8.0 Query caching has been removed in , The official also thinks that this function is less in the actual application scenarios , So just delete .

4、 analyzer

Mysql Miss query cache , So it goes into the analyzer , The analyzer is mainly used to analyze SQL What are the sentences for . The analyzer is mainly divided into the following two steps ：

• Lexical analysis  ： One SQL Statement has multiple strings , First, we need to extract keywords , such as select, A table to query , Propose field name , Put forward query conditions and so on .

• Syntax analysis ： According to the result of lexical analysis , Grammar analysis is mainly to judge what you input SQL Is the statement correct , Compliance MYSQL grammar , If your statement is wrong , Will receive “You have an error in your SQL syntax” Error warning .

The lexical analysis program decomposes the whole query statement into various kinds of marks , Syntax analysis is based on the defined system language “ All kinds of signs ” Turn to right MySQL Meaningful combination . Finally, the system generates a syntax tree (AST), The syntax tree is the data structure that the optimizer depends on .

5、 Optimizer

Through the analyser ,MySQL You know what you're gonna do . Before we start executing , It's also handled by the optimizer .

Why an optimizer is needed ？

• The optimizer contains many Complex optimization techniques , These optimization techniques are often more than the best programmers can master . The automatic optimization of the system is equivalent to making everyone have these optimization technologies .

• The optimizer can get many... From the data dictionary Statistics , For example, the number of rows in a table 、 The distribution of each column in the table, etc . The optimizer optimizer can consider hundreds of different execution plans , Programmers can only consider a limited number of possibilities ;

• You can choose from this information Effective implementation plan , And it's hard for user programs to get that information ;

In short, the optimizer modifies the shape of the parse tree , Turn the parsing tree into a query tree , Determine the implementation plan .

6、 actuator

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 , First check whether the user has the right to execute the query , without , An error with no permissions is returned . If you have authority , Will call the engine's interface , Returns the result of the interface execution .

7、 analysis of sentences

Let's take the following real SQL Query statement for analysis MYSQL The execution of the query

select id,name,sex,phoone from user t where t.age='26' and t.account='javadaily'

• First, the client needs to connect to the database , If the account password is wrong, the error message will be returned directly , If it's right, go to the next step .

• stay MYSQL8.0 I'll go to the query cache first , With this SQL Sentence as key Query in memory for results , If yes, judge whether you have permission first , If you have permission, return to the client , Otherwise, the report will be wrong ; If there is no hit from the query cache, go to the next step

• Lexical analysis by Analyzer , extract sql Key elements of the statement , For example, extracting the above statement is a query select, Extract the table name to be queried as user, The columns to query are id,name,sex,phoone, The query condition is age=26  and account=javadailly. And then judge this sql Is there any grammatical error in the statement , For example, whether the keywords are correct, etc , If the check is OK, go to the next step .

• above SQL There are two implementation options , The optimizer chooses the most efficient one according to its own optimization algorithm a programme （ Inaccurate statistics may cause the optimizer to choose the wrong execution scheme ）, After the optimization plan is determined, it will be implemented .
  a. First query  account=javadaily  Users of , And then determine  age  Is it equal to 26 b. Find out first  age=26  Users of , The query again  account=javadaily  Users of

• Verify authority , If you have query permission, call the database engine interface to return the execution result ; Otherwise, the report will be wrong .