Shulou(Shulou.com)05/31 Report--

This article is about the process analysis of executing query statements in mysql. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

For example, when we query the information of an id=2 from the student table

Select * from student where id=2

Before explaining the execution process of this statement, let's take a look at the infrastructure of mysql.

The figure comes from the mysql practice of geek time, which depicts the logical architecture of MySQL.

The server layer includes connectors, query caching, parsers, optimizers, and executors that cover most of MySQL's core service functions, as well as all built-in functions across storage engines, such as stored procedures, triggers, views, and so on.

The storage engine layer is responsible for data storage and extraction. Its architecture mode is plug-in, supporting multiple storage engines such as InnoDB, MyISAM, Memory and so on. Usually, we usually use innoDB engine.

Connector

Before we can use the database, we need to connect to the database. The connection statement is

Mysql-h $ip-u $username-p $password

And our connector handles this process. The main function of the connector is to establish a connection with the client, obtain permissions, maintain and manage the connection. If the user's permissions change during the use of the connector, it will not take effect immediately, because the user permissions are read during the connection, and the permissions can only be updated by reconnecting.

The communication protocol between the connector and the client is tcp protocol. After connecting, you can use show processlist; to see the number of connections performed.

At the same time, if the connection time is more than 8 hours, the state of sleep will be disconnected automatically. This is the default setting of mysql. If you keep opening it, then this process can be called a long connection.

Correspondingly, there is a short connection, which refers to the disconnection after the execution of one or more.

When using persistent connections, it takes up a lot of memory resources, so you can use mysql_reset_connection statements to reinitialize resources after mysql5.7.

Query cache

After connecting, the database is connected, and the statement can be executed at this time.

When executing statements, mysql first queries the cache. If such statements have been executed before, mysql will store the previously executed statements and results in the form of key-value (of course, there is a certain amount of storage and effectiveness time). If there is a cache, the result of the cache is returned directly.

The workflow of caching is

The server receives the SQL and looks up the cache table for key with SQL and some other conditions

If the cache is found, the cache is returned directly.

If no cache is found, the SQL query is executed, including the original SQL parsing, optimization, and so on.

After executing the SQL query results, cache the SQL query results in the cache table

Of course, if the table is modified, all caches in the table will no longer be valid, and the query cache will be cleared. Therefore, it is not appropriate to cache statements in a table that has been repeatedly modified, because the cache will be effective at any time, so the hit ratio of the query cache will be much lower, which is not very cost-effective.

When the table is writing data, the cache (hit cache, cache write, etc.) of the table will be invalidated. In Innodb, if a transaction modifies the table, the cache of the table will be invalidated before the transaction commits, and the related queries of the table cannot be cached before the transaction commits.

In general, if it is a static table or a table with few changes, it can be cached, so the hit rate is very high.

Let's talk about the timing of cache use. It is a difficult topic to measure whether turning on cache will improve the performance of the system.

According to cache hit ratio, cache hit ratio = cache hit times (Qcache_hits) / query times (Com_select)

Through cache write rate, write rate = cache writes (Qcache_inserts) / queries (Qcache_inserts)

Judging by the hit-write rate, the ratio = Qcache_hits / Qcache_inserts, which is called the index that reflects the performance improvement in high-performance MySQL. Generally speaking, 3:1 is considered to be effective for the query cache, and it is best to reach 10:1.

Analyzer

When querying cache effectiveness or when there is no cache, MySQL's server uses a parser to parse the statement, which is also called a parser.

The MySQL analyzer consists of two parts. The first part is used for lexical analysis to scan character streams to identify individual words according to word formation rules. MySQL uses Flex to generate lexical scanners. MySQL keywords and function keywords are defined in sql/lex.h and stored in two arrays. The function of the second part is that the grammar analysis forms the word sequence into grammar phrases on the basis of lexical analysis, and finally generates a grammar tree, which is submitted to the optimizer parser to use Bison, and the grammar rules are defined in sql/sql_yacc.yy. Then the syntax tree is generated according to the theory of relational algebra.

The above explanation parser is too official and complex, in fact, the parser is mainly used for "lexical analysis" and then know what the database statement is going to do and what it means.

At this time, the parser will report an error if there is a problem with the statement, such as ERROR 1064 (42000): You have an error in your SQL syntax

Optimizer

When the parser knows what the statement is doing after parsing, the next step is to optimize the statement with an optimizer whose task is to find the best way to execute a SQL query. Most query optimizers, including MySQL's query optimizer, always more or less search for the best of all possible query evaluation scenarios.

The main purpose of the optimizer is to choose the best execution scheme, which is to reduce the overhead and improve the execution efficiency.

MySQL's optimizer is a very complex component that uses a lot of optimization strategies to generate an optimal execution plan:

Redefine the association order of tables (when multiple tables are associated with queries, they do not necessarily follow the order specified in SQL, but there are some tricks to specify the association order)

Optimize the MIN () and MAX () functions (find the minimum value of a column. If the column has an index, you only need to find the leftmost end of the B+Tree index. Otherwise, you can find the maximum value. For details, see below)

Terminate the query early (for example, when using Limit, the query will be terminated as soon as a satisfied number of result sets are found)

Optimized sorting (in the old version of MySQL will use two transfer sort, that is, first read the row pointer and the fields that need to be sorted in memory, and then read the data rows according to the sort results, while the new version uses a single transfer sort, that is, read all the data rows at once, and then sort according to the given column. For Istroke O-intensive applications, it will be much more efficient)

With the continuous development of MySQL, the optimization strategies used by optimizers are also constantly evolving. Here are only a few optimization strategies that are not frequently used and easy to understand.

Actuator

After the parser knows what the statement is going to do and the optimizer knows what to do, it comes to the stage of execution, which is left to the executor.

When executing, the executor first determines whether the user has permission to execute the table, and if not, it returns an error prompt such as denied.

If you have permission, the table will be opened to continue execution. When you open a table, the executor uses the interface of the engine defined by the table.

Finally, the data is returned to the client by executing the statement.

Summary

After MySQL gets the sql statement, the approximate flow is as follows:

0. The connector is responsible for communicating with the client

1. Query cache: first query the cache to see if there is a kmurv cache

two。 Parser: responsible for parsing and forwarding sql

3. Preprocessor: validate the parsed sql tree

4. Optimizer: get an execution plan

5. Query execution engine: executor executes statements to get data result sets

6. Put the data back to the caller.

Thank you for reading! This is the end of this article on "process Analysis of executing query sentences in mysql". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.