Hello everyone, I am not Chen Mou~

Today we will share how to quickly locate slow query SQL and optimization

There are generally 3 directions of thinking:

First give the steps, followed by instructions, there are 3 steps

First open the slow query log, the parameter slow_query_log decide whether to turn it on, enter the following command under MySQL command line:

In the default environment, slow query logging is turned off, so it is turned on here.

As long as your actual SQL execution time exceeds this threshold, it will be recorded in the slow query log. This threshold is 10s by default, online services generally recommend setting the long_query_time to 1s, if the MySQL requirements of a business are relatively high QPS, you can set the slow query to 0.1s.

Timely optimization of slow queries found or reminders for development rewriting. The general test environment recommends that the threshold value set long_query_time be smaller than that of the production environment, for example, if the production environment is 1s, then the test environment is recommended to be configured to 0.5s. It is convenient to discover some efficient SQL in time in the test environment.

Even some important business test environment long_query_time can be set to 0 so that all statements are logged. After the functional test before going online, analyze the output of each type of statement in the slow query log, focusing on the Rows_examined (the number of rows read from the storage engine during statement execution), and optimize in advance.

As a result, we will find that the default path of slow logs is the MySQL data directory, and we can take a look at the MySQL data directory

Don’t worry about why it’s not MySQL 8.0, it has nothing to do with versions.

Come, serve directly, dry definition I can’t read myself

Let’s look at the variables first, and I’ve boxed out the points that need attention

Query for related variables with quer

Here set the slow query threshold to 1s

You can see that it has been modified

However, restarting the mysql client settings and statistics slow query log number will be cleared, that is, all configuration modifications will be restored

After the command modifies the configuration, close the MySQL service on the command line net stop mysql, then net start mysql to open the MySQL service, and then execute show global variables like ‘%quer%’; You will find that the configuration has been restored.

The configuration file can only be changed permanently, otherwise the database will be restored by restarting it

Data table structure, lazy did not write comment

The data here is 200W bars. Pay attention to the table structure, remember which fields have indexes, and then analyze the table.

This 3.36s is not the actual execution time, the actual execution time has to go to the slow query log to see the Query_time parameters

You can see Query_time: 6.337729s, more than 1s, so it will be recorded, a select statement query for so long, simply unbearable.

The other parameters in the figure are explained below:

If you feel that the slow query log that comes with the system is not convenient to view, you can use pt-query-digest or mysqldumpslow and other tools to analyze the slow query log.

Note: Some slow queries are executing, the result has caused the database load to be too high, and because the slow query has not been executed, the slow query log does not see any statements, at this time you can use the show processlist command to view the slow query that is executing. The show processlist shows which threads are running, and if you have PROCESS permissions, you can see all the threads. Otherwise, only the current session thread is visible.

From the table structure in the previous section, you can know that account is the field with a unique index added. explainAnalyze the execution plan.

We need to focus on the select_type, type, possible_keys, key, Extra these columns, we will explain one by one, see the select_type column, here is the SIMPLE simple query, other values are listed below for you.

The type column, in this case, is the index, which represents a full index scan

The table represents the SQL query performance from the best to the worst, if the type type is all, it means that the sql statement needs to be optimized.

Note: If type = NULL, it means that a MySQL can get the result without accessing the table or index, such as explain select sum(1+2);

possible_keys represents the index columns that may be used, and key represents the index columns that are actually used, subject to the actual index columns used, which are selected after the query optimizer optimizes, and then we can also force the use of our own index columns to query according to the actual situation.

Extra column, here is the Using index

It is important to note that the presence of Using filesort and Using temporary in Extra means that MySQL cannot use indexes at all, and efficiency will be seriously affected and should be optimized as much as possible.

The appearance of Using filesort indicates that MySQL uses an external index to sort the results, instead of reading the relevant content in index order from the table, the B+ tree is maintained with an index, and the data is already ordered, which means that the index is not used at all, but the data is sorted after reading, possibly in memory or on disk. Others refer to sorting operations in MySQL that do not take advantage of indexes as “file sorting”.

Using temporary indicates that MySQL uses temporary tables when sorting query results, commonly found in order by order and grouped query group by

Going back to the previous topic, we saw that account is a field with a unique index added. explain after analyzing the execution plan

Look directly at the descending order of account

Looking at the slow query log, it is found that after using the index, the speed of querying 200W data is 2s faster

Next, let’s analyze the sql execution plan of the query name

Then index the name field

After adding the index, continue to look at the sql execution plan of the query name

Comparing the execution plan of the previous name without indexing, it can be found that after the index is added, the type changes from an ALL full table scan to an index index scan. order by does not use filesort, but uses index, where the B+ tree has sequenced the values of the index fields of this nonclustered index, rather than waiting until the time of the query to sort them.

Then we continue to execute the query, at which point the name has been indexed.

It turns out that before the name is added to the index, the descending query name costs 6.337729s, and after adding the index, the descending query name costs 3.479827s, because the result set of the B+ tree is already orderly.

Query the number of pieces of data, here count(id), analyze the sql execution plan

The index actually used here is the account-only index.

Analyze it: which index to actually use is determined by the query optimizer, the leaf node of the B+ tree is the linked table structure, and the traversal linked list can count the number, but this table, there is a primary key index, a unique index, an ordinary index, the optimizer chose the account this unique index, which will certainly not use the primary key index, because the primary key index is a clustered index, each leaf contains a specific row record (many columns of data are inside), rather than a clustered index, each leaf contains only a pointer to the next primary key index , it is clear that the leaf nodes contain as little data as possible, and the query optimizer will not select the primary key index

Of course, it is also possible to force the use of a primary key index and then parse the sql execution plan

Let’s take a look at the approximate execution time of the optimizer using a unique index by default of 676ms

Enforce the approximate execution time of the primary key index 779ms

We can use force index to specify the index, and then analyze the execution plan to see which index is better, because the query optimizer does not necessarily choose the index to be 100% accurate, and the specific situation can be analyzed according to the actual scenario to determine whether to use the index selected by the query optimizer.

WeChat 8.0 will let go of friends to ten thousand, small partners can add my size, first-come, first-served, and then full is really gone