Performance problems caused by inaccurate MySQL statistics

Incorrect statistics for the table caused the optimizer to choose the wrong execution plan.
A customer's performance optimization case: without modifying any configuration parameters of the database instance and without changing the business code, a sql has a significant performance drop.
Let's take a look at the sql of the problem and its execution plan:

The execution plan shows rows examined = (19410%)1642(14.23%)=1347 Looking at the execution plan, we find the where condition con.date = current_date() . This condition seems more suitable as an index to filter data. But why didn't the MySQL optimizer choose that index? Next use force index to force the plan to use the index on the con.date field. The execution plan is as follows:

The problem is rows examined = 110446*(1*10%)=11045 rows According to the calculation evaluation, 1347 of the first execution plan is about one tenth of 110446, at least on the surface, this is the first choice of MySQL optimizer. A reason to execute the plan.
But comparing the response time of the actual query results, it must be a rough problem. Because the response time of sql in execution plan 2 is within expectations, but the response time corresponding to execution plan 1 is slower.

Looking further at the creation statement of the table orders and execution plan 1, we find that the table pcz does have 194 rows. Then look at the index orders.dpcz_FK, the table orders returns 1642 rows, because the foreign key constraints the definition of orders_ibfk_10, which means that the number of records in the table orders should be 194*1642=318548, but the actual number of rows is 32508150, a hundred times more than the execution plan The estimated value is 318548.

So far, we can conclude that the statistics of orders.dpcz_FK are inaccurate, so we use the following statement to confirm its actual data volume:
mysql> select * from mysql.innodb_index_stats where database_name='cutom' and table_name='orders' and index_name='dpcz_FK';
mysql> select * from mysql.innodb_index_stats where database_name='custom' and table_name='orders' and index_name='dpcz_FK';

Bingo! From the above, the discrimination degree (the total number of different values) of the table orders field d_p_c_z_id is 195. In the information statistics table, the stat_value value of dpcz_FK is 19498, obviously this value is inaccurate and much larger than the actual value, 100 times. The state_value value of the index should be equal to the distinguishing degree of this field in the table.

If we use the correct index dpcz_FK value stat_value 195 to re-evaluate the cost of the execution plan, we will get the result of execution plan 1 32508150/195=166708, and the estimated number of rows scanned by the execution plan should be (194*10%) *166708*(1*4.23%)=136804. Because the value is 10 times the value 11045 of execution plan 2. MySQL can get to the correct execution plan without using a force index.

This sql problem is solved, but why is MySQL's stats miscalculated and how can we fix it?

Before answering this question, let's take a look at how MySQL collects statistics and what parameters control this action.

How InnoDB collects table statistics

We can collect table statistics explicitly or automatically by the system.

By turning on the parameter innodb_stats_auto_recalc =on (which is also turned on by default) to automatically collect table statistics after a significant change in the table's data. For example, when 10% of the rows in the table change, InnoDB will recalculate the statistics. Or we can explicitly recalculate statistics using ANALYZE TABLE.

InnoDB uses random sampling techniques to collect statistics -- randomly sample index pages to estimate the cardinality of the index.

According to the code and description, random sampling is not completely random. The sampling page is actually selected based on the sampling algorithm. Ultimately, the total number of distinct key values, i.e. the index's stat_value will be calculated by the following formula
N*R*N_DIFF_AVG_LEAF. in
N : number of leaf pages
R : the ratio of the number of different key values ​​on level LA to the total number of records on level LA
`N_DIFF_AVG_LEAF`: Average number of distinct key values ​​found across all A leaf pages.

Based on the above introduction, we know that when the index of a table is split, whether it is the number of leaf pages (N), or the ratio of the number of different key values ​​on the layer LA to the total number of records in the layer LA (R ) becomes more and more The less accurate, so the calculation of stat_value may not be correct. Once this happens, an explicit recalculation (manually running ANALYZE TABLE) will not produce the correct stat_value unless the parameter innodb_stats_persistent_sample_pages is changed or the indexes are rebuilt.

Solution

How do we fix table statistics and prevent this from happening further.
After the previous analysis and discussion, we know that there are two factors that affect the statistics of the database collection table,
innodb_stats_persistent_sample_pages: A

How the index is organized

In order for InnoDB to get the correct statistics, we need to tune innodb_stats_persistent_sample_pages or rebuild the index.
1 Through the command analyze table without rebuilding, keep innodb_stats_persistent_sample_pages = 128, stat_value is slightly changed to 19582, close to the original incorrect 19498, still closed. The number of leaf pages in the index changed slightly from 55653 to 55891, and the number of pages in the index also changed slightly from 63864 to 64248
mysql> show variables = 'innodb_stats_persistent_sample_pages;

2 ANALYZE TABLE does not rebuild, but increases innodb_stats_persistent_sample_pages from 128 to 512, making stat_value to 192 very close to the actual base 195. The number of leaf pages in the index has changed significantly, from 55653 to 44188. The number of pages in the index has also changed dramatically from 63864 to 50304.
mysql> show variables like '%persistent_sample%';

mysql> select * from mysql.innodb_index_stats where database_name='custom' and table_name='orders' and index_name='dpcz_FK';

mysql> alter table orders engine=innodb;
Query OK, 0 rows affected (11 min 16.37 sec)
mysql> select * from mysql.innodb_index_stats where database_name='custom' and table_name='orders' and index_name='dpcz_FK';

After correcting table statistics, the MySQL optimizer also chooses the correct execution plan:
mysql> explain
SELECT count(con.id) ,
MAX(DAYNAME(con.date)) ,
now() ,
pcz.type,
pcz.c_c
FROM con AS con
join orders o on con.order_id = o.id
JOIN p_c_z AS pcz ON o.d_p_c_z_id = pcz.id
left join c c on con.c_id = c.id

in conclusion

The MySQL optimizer relies on the accuracy of table statistics to choose the optimal execution plan. We can control the accuracy of the system collection table statistics by changing the parameter innodb_stats_persistent_sample_pages.
We can also choose to force a recalculation of table statistics by rebuilding/rebuilding the table while the index is being defragmented, which helps improve the accuracy of the table statistics. To reconstruct the table, we can directly use alter table xx; to modify the table or use pt-online-schema-change to achieve the same effect.