Shulou(Shulou.com)06/01 Report--

This article mainly talks about "how the execution of a SQL statement is like". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "what happens when a SQL statement is executed".

I. MySQL architecture

-connection pool component

1. Responsible for communicating with the client in half-duplex mode, which means that at a fixed time, the client can only request to the server or the server can send data to the client, but not at the same time.

2. Verify whether the user name and password are correct (verify in the user table of the database MySQL). Notify Access denied for user 'root'@'localhost' (using password:YES) if an error is returned; if it is correct, it will go to the permission table of MySQL to query the permissions of the current user.

-caching component

Also known as query cache, the stored data is stored in the form of key-value pairs. If caching is enabled, when a query SQL statement comes in, it will first determine whether the current SQL statement key-value pair is included in the cache. If it exists, the corresponding results will be returned directly, and if it does not exist, a series of subsequent operations will be performed. If it's not open, just skip it.

Show variables like 'have_query_cache'; # View cache configuration: show variables like' query_cache_type'; # check whether show variables like 'cache is enabled # View cache occupancy size show status like' Qcache%'; # View cache status information

Cache invalidation scenario:

The query statement is inconsistent. The two query SQL must be exactly the same.

When the query statement contains some uncertain values, it will not be cached. For example, now (), current_date (), curdate (), curtime (), rand (), uuid (), etc.

Do not use any table queries. Such as select'A'

When querying tables in a mysql, information_schema, or performance_schema database, the query cache is not removed

A query executed within the body of a stored function, trigger, or event

If the table changes, all cached queries that use the table become invalid and deleted from the cache, including queries that use MERGE to map to tables that have changed tables. A table can be changed by many types of statements, such as insert, update, delete, truncate table, alter table, drop table, drop database.

From the above invalidation scenario, we can see that the cache is easy to fail, so if the number of queries is not much greater than the number of modifications, using the cache not only does not improve the query efficiency, but also reduces the efficiency (one copy needs to be saved to the cache after each read, and the cache is easy to be cleared). So caching is turned off by default in MySQL5.6 and removed directly at 8.0. Of course, if the scene needs to be used, it can be used.

Enable:

In the configuration file (under linux is the cnf file of the installation directory, and windows is the ini file under the installation directory), add the configuration: query_cache_type = 1

# specify the same for SQL_NO_CACHE,SQL_CACHE. SELECT SQL_NO_CACHE * FROM student WHERE age > 20th-Analyzer

Analyze the SQL sent from the client, which will include the process of preprocessing and parsing, and the extraction and analysis of keywords, and form a parsing tree. Specific parsing words include, but are not limited to, select/update/delete/or/in/where/group by/having/count/limit. If grammatical errors are analyzed, they will be thrown directly to

Client exception: ERROR:You have an error in your SQL syntax.

Select * from user where userId = 1234

In the analyzer, the keywords select from where are extracted and matched by the semantic ruler. MySQL will automatically judge keywords and non-keywords, and identify the user's matching fields and custom statements. Some verification is also done at this stage: for example, to verify whether the user table exists in the current database, and if the userId field does not exist in the user table, it will also report an error: unknown column in field list.

-Optimizer

Entering the optimizer indicates that the SQL statement conforms to the standard semantic rules and can be executed. The optimizer will choose the best choice according to the execution plan, match the appropriate index, and choose the best scheme. For example, a typical example goes like this:

Table T, create a joint index on columns A, B, and C-- (AMagneBrect C). When querying, when the SQL query condition is: select xx where indexing and matching x and indexing. Many people will think that the index will not be used, but in fact, although the index must conform to the leftmost principle before it can be used, in essence, the optimizer will automatically optimize the SQL to: where index x and index x and index, this optimization will be in order to match the index at the bottom, at the same time, it is automatically preprocessed according to the execution plan, and MySQL accounting calculates the best time for each execution method. Finally, it is determined that an executed SQL is handed over to the final executor.

The optimizer will judge whether to use an index according to the number of rows scanned, whether to use temporary tables, whether to sort, etc., in which the number of rows scanned can be estimated by statistical information, and the statistics can be regarded as the number of unique indexes, which can be estimated by partial sampling. Specifically, select N data pages, count the different values of the data on these pages, and get an average. Then multiply by the number of pages in this index, and you get. However, because the index data changes, the statistics of the index also change. The statistics are recalculated when the number of rows of data changed exceeds 1 stroke M.

You can choose whether to persist statistics:: through innodb_stats_persistent, when set to on, it means that statistics will be persisted. At this point, the default N is 20 and M is 10. When set to off, the statistics are stored only in memory. At this point, the default N is 8 and M is 16.

How to optimize without using the optimal index:

1. Although the statistics are updated automatically, there is no guarantee that the statistics are up-to-date, which may cause the optimizer to choose a different index and cause the execution to be slower, so you can recalculate the statistics of the index through the analyze table table name.

2. Add a force index (index name) statement after the table name to force the use of indexes (not recommended)

3. Modify the SQL so that the optimizer can choose the optimal index.

4. Create an optimal index or delete the index misused by the optimizer

-Actuator

The executor invokes the corresponding storage engine to execute SQL, the mainstream being MyISAM and Innodb.

Second, the execution process of write operation

Third, the execution process of read operation

Index push-down (Index Condition Pushdown) was introduced after MySQL 5.6.Therefore, there will be a process of Index Filter and Table Filter in the query operation, and the query flow chart can be summarized as follows:

IV. SQL execution order

At this point, I believe you have a deeper understanding of "how a SQL statement is executed". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.