Recently, I encountered slow SQL again. After a brief look, it is also because MySQL optimizer has the problem of inaccurate estimation of query plan. The SQL is as follows:

select * from t_pay_record
WHERE
((
    user_id = 'user_id1' 
    AND is_del = 0 
)) 
ORDER BY
    id DESC 
    LIMIT 20

The SQL took 20 minutes to execute before there was a result. But let's change it_ ID, the execution is very fast. From the perspective of online business performance, the performance of most users is normal. We use another user whose data distribution is similar to this user to check, which is still relatively fast.

Let's EXPLAIN the original SQL first. The result is:

+----+-------------+--------------+------------+-------+-----------------------------------------------------------------------------------------+---------+---------+------+-------+----------+-------------+
| id | select_type | table        | partitions | type  | possible_keys                                                                           | key     | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+--------------+------------+-------+-----------------------------------------------------------------------------------------+---------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | t_pay_record | NULL       | index | idx_user_id,idx_user_status_pay,idx_user_id_trade_code_status_amount_create_time_is_del | PRIMARY | 8       | NULL | 22593 |     0.01 | Using where |
+----+-------------+--------------+------------+-------+-----------------------------------------------------------------------------------------+---------+---------+------+-------+----------+-------------+

Then we change some users with similar distribution but normal response time. The EXPLAIN results are:

+----+-------------+--------------+------------+-------+-----------------------------------------------------------------------------------------+---------------------------------------------------------+---------+------+-------+----------+-------------+
| id | select_type | table        | partitions | type  | possible_keys                                                                           | key                                                     | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+--------------+------------+-------+-----------------------------------------------------------------------------------------+---------------------------------------------------------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | t_pay_record | NULL       | index | idx_user_id,idx_user_status_pay,idx_user_id_trade_code_status_amount_create_time_is_del | idx_user_id_trade_code_status_amount_create_time_is_del | 195     | NULL | 107561|     10.00| Using where |
+----+-------------+--------------+------------+-------+-----------------------------------------------------------------------------------------+---------------------------------------------------------+---------+------+-------+----------+-------------+

Some are:

+----+-------------+--------------+------------+-------+-----------------------------------------------------------------------------------------+-------------+---------+------+-------+----------+-------------+
| id | select_type | table        | partitions | type  | possible_keys                                                                           | key         | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+--------------+------------+-------+-----------------------------------------------------------------------------------------+-------------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | t_pay_record | NULL       | index | idx_user_id,idx_user_status_pay,idx_user_id_trade_code_status_amount_create_time_is_del | idx_user_id | 195     | NULL |  87514|     10.00| Using where |
+----+-------------+--------------+------------+-------+-----------------------------------------------------------------------------------------+-------------+---------+------+-------+----------+-------------+

In fact, according to this performance, it can be inferred that it is the wrong index. Why use the wrong index? This is due to various reasons. This article will analyze the various reasons for this SQL and give the final solution.

Analysis of MySQL slow SQL

In the previous article, I mentioned that SQL tuning is generally performed through the following three tools:

1. EXPLAIN: This is a relatively simple analysis and will not really execute SQL. The analysis may not be accurate and detailed enough. But some key problems can be found.
2. PROFILING: execute sampling through SQL enabled by set profiling = 1. You can analyze which stages SQL execution is divided into and how time-consuming each stage is. The SQL needs to be executed and executed successfully, and the analyzed stages are not detailed enough. Generally, it can only be optimized by whether there are some stages and how to avoid the occurrence of these stages (for example, avoiding the occurrence of memory sorting, etc.).
3. OPTIMIZER TRACE: show each step of the optimizer in detail. SQL needs to be executed and executed successfully. The MySQL optimizer considers too many factors, too many iterations, and the configuration is quite complex. The default configuration is OK in most cases, but there will be problems in some special cases, requiring human intervention.

Here again, in different MySQL versions, the results of EXPLAIN and OPTIMIZER TRACE may be different, which is caused by the insufficient design of MySQL itself. EXPLAIN is closer to the final execution result. OPTIMIZER TRACE is equivalent to burying point collection in each step. It is inevitable that there will be omissions when MySQL is developing iteratively

For the above SQL, we can actually EXPLAIN that the reason is the wrong index. However, we can't intuitively see why the wrong index will go. We need to further locate it through OPTIMIZER TRACE. But before further positioning, I want to talk about the data configuration of MySQL's InnoDB query optimizer.

MySQL InnoDB query optimizer data configuration (MySQL InnoDB Optimizer Statistics)

Official website document address: https://dev.mysql.com/doc/ref...

In order to optimize users' SQL queries, MySQL will perform SQL parsing, rewriting and query plan optimization for all SQL queries. For InnoDB engine, when making query plan, analyze:

1. What is the consumption of full table scanning
2. Which indexes can I use? We will consider the where condition and order condition, and find the index with these conditions through the conditions inside
3. How much is the query consumption per index
4. Select the query plan with the least consumption and execute it

The consumption of each index query needs to query the optimizer data through InnoDB. This data is obtained by collecting table and index data, and is not collected in full, but by sampling. Related to the following configurations:

1. innodb_ stats_ The persistent global variable controls whether the global default data is persistent. The default is ON, that is, persistent. Generally, we can't accept saving in memory. In case of database restart, the table will be re analyzed, so as to slow down the startup time. The configuration that controls a single table is STATS_PERSISTENT (used in CREATE TABLE and ALTER TABLE).
2. innodb_stats_auto_recalc global variable is automatically updated by default. The default is ON, that is, after more than 10% of the rows in the table are updated, trigger the background asynchronous update to collect data,. The configuration that controls a single table is STATS_AUTO_RECALC (used in CREATE TABLE and ALTER TABLE).
3. innodb_ stats_ persistent_ sample_ The pages global variable controls the number of collection pages by default, which is 20 That is, 20 pages of data of the table and each index in the table are collected randomly for each update to estimate the query consumption of each index and the scanning consumption of the whole table. Stats is used to control the configuration of a single table_ SAMPLE_ Pages (used in CREATE TABLE and ALTER TABLE).

Location of SQL causes with the slowest execution time

Through the results of the previous EXPLAIN, we know that the index used for the final query is the PRIMARY key index. In this way, the whole SQL execution process is to traverse each piece of data in the table in reverse order through the PRIMARY key until 20 pieces are filtered. Through the time-consuming execution, we know that it takes a lot of data to complete 20 entries, which is extremely inefficient. Why?

Through the SQL statement, we know that in the second step mentioned above, the index considered includes the user in the where condition_ id，is_ Del related indexes (we know these indexes through EXPLAIN: idx_user_id,idx_user_status_pay,idx_user_id_trade_code_status_amount_create_time_is_del), and the ID index in the order by condition, that is, the primary key index. Suppose the page data collected in this random collection is like this:

The blue in the figure represents the sampled pages. Each index in the same table will sample 20 pages by default. Assuming that the result of this collection is as shown in the figure, the collection of other indexes is relatively balanced. Judging from other indexes, users have to scan tens of thousands of rows of results. However, the last page of the primary key collection is just the end of the user's records. Since there is limit 20 at the end of the statement, if there are exactly 20 records at the end (and all meet the where condition), it will be considered that it is OK to find 20 records backwards according to the primary key. This will cause the optimizer to think that the primary key scan consumes the least. But this is not the case, because this is sampling. There may be a lot of records that are not the user, especially for large tables.

If we remove the limit, EXPLAIN will find that the index is right, because if we don't limit the limit, the primary key index must be scanned once, and the consumption can't be higher than that of user_ The ID related index is low.

Why SQL with normal execution time_ Different IDS also lead to different indexes. Analyze the reasons for leaving different indexes

Similarly, since the optimizer data of all indexes is sampled randomly, with the continuous expansion of the table and the index, there may be more complex indexes, which will aggravate the difference of index consumption by using different parameters (here is using different user_id s).

This also leads to a new problem that you may also encounter. I added a composite index on the basis of the original index (for example, there was only idx_user_id, and later idx_user_status_pay). Then the original index is only based on user_ ID to check the SQL of the data. Some may use
idx_user_id, some may use idx_user_status_pay, using idx_user_status_pay is more likely than using idx_user_id, slow. Therefore, adding a new composite index may slow down other business SQL that is not the SQL to be optimized by the composite index, so it needs to be added carefully

What problems will this design bring when the amount of data is increasing and the table becomes more and more complex

1. The number of rows to be updated in real time does not exceed a certain proportion. And the statistical data is not full statistics, but sampling statistics. Therefore, when there is a large amount of data in the table, this statistical data is difficult to be very accurate.
2. Because the statistical data is not accurate enough, if the table design is also complex, there are many stored data types and many fields, and the most important thing is that there are various composite indexes, and the indexes are becoming more and more complex, which aggravates the inaccuracy of the statistical data.
3. By the way: MySQL tables can't have a large amount of data, so they need to be split horizontally. At the same time, there can't be too many fields, so they need to be split vertically. And the index can not be added casually. There are also the two reasons mentioned above. This will aggravate the inaccuracy of statistical data and lead to the wrong index.
4. Manually analyzing a Table will place a read lock on the Table and block updates and transactions on the Table. Therefore, it cannot be used on this online business key Table. Consider regularly analyzing the business critical Table at low peak times
5. Rely on the automatic data refresh mechanism of the Table itself, The parameter is difficult to adjust (mainly the parameter STATS_SAMPLE_PAGES. We generally do not change STATS_PERSISTENT, and we will not accept saving in memory. In this way, in case of database restart, the Table will be re analyzed, which will slow down the startup time, and STATS_AUTO_RECALC will not be closed, which will lead to more and more inaccurate analysis by the optimizer), It's hard to predict what is the most appropriate value to adjust to. Moreover, the growth of business and the tilt of data caused by user behavior are also difficult to predict. Modify stats of a Table through Alter Table_ SAMPLE_ When using pages, it will have the same effect as analyzing the Table. It will put a read lock on the Table and block the updates and transactions on the Table. Therefore, it cannot be used on this online business key Table. So it's best to estimate the magnitude of the large Table at the beginning, but this is very difficult.

Conclusions and recommendations

To sum up, I suggest that for online tables with large data volume, it is best to control the data volume of each table by database and table in advance, but the business growth and product demand are constantly iterative and complex. It is difficult to guarantee that there will be no large tables with complex indexes. In this case, we need to increase stats appropriately_ SAMPLE_ On the premise of pages, for the key query SQL triggered by some users, use force index to guide it to follow the correct index, so that some user id queries will not follow the wrong index due to the inaccurate data collected by MySQL optimizer table.

WeChat search "my programming meow" attention to the official account, plus WeChat, daily brush, easy to upgrade technology, and acquire various offer:

I will send out some good news videos of official communities in various frameworks and add personal translation subtitles to the following addresses (including the official account above).

• Know: https://www.zhihu.com/people/...
• Station B: https://space.bilibili.com/31...