修正MySQL表统计信息以获得更好的执行计划

案例学习：

表的统计信息错误导致优化器不能选择正确的执行计划

一个客户说，在没有代码和配置变更的情况下，一个查询发生了灾难性的性能下降。为简介起见，对本文中的数据进行了编辑和修改，以免信息泄露。该案例也获得客户的允许。

以下是执行计划和执行结果：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

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.o_id = o.id ->        JOIN pcz AS pcz ON o.d_p_c_z_id = pcz.id ->        left join c c on con.c_id = c.id -> WHERE con.date = current_date() and pcz.type = "T_D" -> GROUP BY con.date, pcz.c_c,  pcz.type; +----+-------------+-------+------------+--------+-------------------+----------+---------+----------------------------+------+----------+----------------------------------------------+ | id | select_type | table | partitions | type   | possible_keys     | key      | key_len | ref                        | rows | filtered | Extra                                        | +----+-------------+-------+------------+--------+-------------------+----------+---------+----------------------------+------+----------+----------------------------------------------+ |  1 | SIMPLE      | pcz   | NULL       | ALL    | PRIMARY           | NULL     | NULL    | NULL                       |  194 |    10.00 | Using where; Using temporary; Using filesort | |  1 | SIMPLE      | o     | NULL       | ref    | PRIMARY,dpcz_FK   | dpcz_FK  | 9       | custom.pcz.id              | 1642 |   100.00 | Using index                                  | |  1 | SIMPLE      | con   | NULL       | ref    | FK_order,IDX_date | FK_order | 8       | custom.o.id                |    1 |     4.23 | Using where                                  | |  1 | SIMPLE      | c     | NULL       | eq_ref | PRIMARY           | PRIMARY  | 8       | custom.con.c_id            |    1 |   100.00 | Using index                                  | +----+-------------+-------+------------+--------+-------------------+----------+---------+----------------------------+------+----------+----------------------------------------------+

评估出的行数= (194x10%)x1642x(1x4.23%)=1347

 

深入研究一下查询，可以看到，条件con.date=current_date()。这个条件似乎是个能很好地过滤结果，但是MySQL优化器为什么跳过了使用索引呢？我们再来看看执行计划，通过强制使用在con.date列上的索引。执行计划输出结果是：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

mysql> explain -> SELECT count(con.id)          , ->        MAX(DAYNAME(con.date)) , ->        now()                  , ->        pcz.type, ->        pcz.c_c -> FROM   con AS con USE INDEX(IDX_date) ->        join orders o on con.o_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 -> WHERE con.date = current_date() and pcz.type = "T_D" -> GROUP BY con.date, pcz.c_c,  pcz.type; +----+-------------+-------+------------+--------+-----------------+----------+---------+---------------------------------------+--------+----------+---------------------------------+ | id | select_type | table | partitions | type   | possible_keys   | key      | key_len | ref                                   | rows   | filtered | Extra                           | +----+-------------+-------+------------+--------+-----------------+----------+---------+---------------------------------------+--------+----------+---------------------------------+ |  1 | SIMPLE      | con   | NULL       | ref    | IDX_date        | IDX_date | 3       | const                                 | 110446 |   100.00 | Using temporary; Using filesort | |  1 | SIMPLE      | c     | NULL       | eq_ref | PRIMARY         | PRIMARY  | 8       | custom.con.c_id                       |      1 |   100.00 | Using index                     | |  1 | SIMPLE      | o     | NULL       | eq_ref | PRIMARY,dpcz_FK | PRIMARY  | 8       | custom.con.o_id                       |      1 |   100.00 | Using where                     | |  1 | SIMPLE      | pcz   | NULL       | eq_ref | PRIMARY         | PRIMARY  | 8       | custom.o.d_p_c_z_id                   |      1 |    10.00 | Using where                     | +----+-------------+-------+------------+--------+-----------------+----------+---------+---------------------------------------+--------+----------+---------------------------------+

强制使用索引后，估算出的记录数= 110446(110%)=11045 。

根据预估，1347是11045的十分之一，MySQL自然会选择第一个执行计划。

但是，真正运行时间与期望的响应时间相比，明显有些不对。第二个执行计划在预计的时间内返回了结果，第一个执行计划时间超过了预估时间。

进一步深入地检查表的结构和执行计划一，我们发现pcz表实际只有194行记录。但是，再看orders表通过索引orders.dpcz_FK，表orders会返回1642行记录，因为外键约束orders_ibfk_10(如下面所示)：这意味着表orders中的记录数应该是194*1642=318548，但是实际的记录数是32508150，是估算记录数318548的十倍。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

CREATE TABLE `orders` (   `id` bigint(20) NOT NULL AUTO_INCREMENT,   ...   `d_p_c_z_id` bigint(20) DEFAULT NULL,   ...,   PRIMARY KEY (`id`),   ...   KEY `dpcz_FK` (`d_p_c_z_id`),   ...   CONSTRAINT `orders_ibfk_10` FOREIGN KEY (`d_p_c_z_id`) REFERENCES `p_c_z` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,   ... ) ENGINE=InnoDB .... mysql> select * from mysql.innodb_table_stats  where database_name='cutom' and table_name='orders'; +---------------+------------+---------------------+----------+----------------------+--------------------------+ | database_name | table_name | last_update         | n_rows   | clustered_index_size | sum_of_other_index_sizes | +---------------+------------+---------------------+----------+----------------------+--------------------------+ | custom        | orders     | 2022-03-03 21:58:18 | 32508150 |               349120 |                   697618 | +---------------+------------+---------------------+----------+----------------------+--------------------------+

因此，我们怀疑表上关于orders.dpcz_FK的统计信息是不准的。可以通过以下的语句进行查询：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

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'; +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ | database_name | table_name | index_name | last_update         | stat_name    | stat_value | sample_size | stat_description                  | +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ | custom        | orders     | dpcz_FK    | 2022-02-28 12:35:30 | n_diff_pfx01 |      19498 |          50 | d_p_c_z_id                        | | custom        | orders     | dpcz_FK    | 2022-02-28 12:35:30 | n_diff_pfx02 |   32283087 |         128 | d_p_c_z_id,id                     | | custom        | orders     | dpcz_FK    | 2022-02-28 12:35:30 | n_leaf_pages |      55653 |        NULL | Number of leaf pages in the index | | custom        | orders     | dpcz_FK    | 2022-02-28 12:35:30 | size         |      63864 |        NULL | Number of pages in the index      | +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ mysql> select count(distinct d_p_c_z_id) from orders; +----------------------------------------------+ | count(distinct d_p_c_z_id)                   | +----------------------------------------------+ |                                          195 | +----------------------------------------------+

bingo！在表orders上，列d_p_c_z_id的cardinality是195。在表mysql.innodb_index_stats中，索引dpcz_FK的stat_value是19498，这个值是错误的，与实际的值相差很大。索引的stat_value被认为是列的cardinality。

使用正确的stat_value=195,从执行计划一的第二行可以得到实际的行数是32508150/195=166708，然后要被检查的行数是(194x10%)x166708x(1x4.23%)=136804。这个值比11045的十倍还大，11045是执行计划二预估的值，现在MySQL会选择执行计划二，而不需要强制走索引。

 

但是，为什么MySQL会统计出错误的统计信息？如何去修正呢？

为了回答这个问题，我们首先需要知道MySQL是如何估算统计信息，哪些参数会有影响。然后就可以很容易地找到解决路径。

 

InnoDB是如何估算表的统计信息的？

表的统计信息可以自动收集或显式收集。人们通常会开启(默认就是开启)innodb_stats_auto_recalc，在表中的数据变更一定比例后，自动重新收集持久性统计信息。当表中的记录超过10%被修改后，InnoDB会重新收集统计信息。我们也可以使用analyze table来显式地重新收集表的统计信息。

InnoDB使用采样技术(被熟知技术random dive)，采样索引的随机的页，来估算索引的cardinality。innodb_stats_persistent_sample_pages控制了采样的页数。可以参考：https://dev.mysql.com/doc/refman/5.7/en/innodb-persistent-stats.html

随机采样不是完全随机。采样的页是根据采样算法算出来的。最后，全部不同的键值，即索引的stat_value会根据公式：N * R * N_DIFF_AVG_LEAF

其中：

N：表示叶子页的数量

R：Level LA上不同的值的个数/Levl LA上所有的记录数。从root页开始，向下一层(level)遍历它的全部child page，直到遇到这样的一个leve：此level中至少包含A*10条不同的key，把此level标记为LA。

N_DIFF_AVG_LEAF:在所有的A叶子页上，不同键值的平均值

详细的算法：https://github.com/mysql/mysql-server/blob/6846e6b2f72931991cc9fd589dc9946ea2ab58c9/storage/innobase/dict/dict0stats.cc:

理解了上面的算法之后，当表的索引有了碎片之后，叶子页的数量，和level LA的不同键值与LA上的所有记录的比例边的越来越不准确，因此估算stat_value的值就会不准确。一旦发生碎片，除非参数inodb_stats_persistent_sample_pages被修改或者重构、显式地重新计算(手动执行analyze table)，否则就无法生成准确的stat_value值。

解决方案：如何纠正表的统计信息，避免统计信息再次错误

根据采样算法，只有两个因素会影响统计信息的估算：参数innodb_stats_persistent_sample_pages;索引是如何组织的

要想innodb获得正确的统计信息，要么增加innodb_stats_persistent_sample_pages;或者重构索引。重构索引的首要方式是重构表，例如执行一个alter操作。

让我们来看看下面的三个例子：

1.analyze table，不rebuild表，保持innodb_stats_persistent_sample_pages=128。将stat_value更新成了19582 ，接近原来不准确的值19498。索引中叶子页的数量从55653变成了55891，索引中的页的数量也从63864变成了64248：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

mysql> show variables = 'innodb_stats_persistent_sample_pages'; +--------------------------------------+-------+ | Variable_name                        | Value | +--------------------------------------+-------+ | innodb_stats_persistent_sample_pages | 128   | +--------------------------------------+-------+ mysql> analyze table orders; +---------------+---------+----------+----------+ | Table         | Op      | Msg_type | Msg_text | +---------------+---------+----------+----------+ | custom.orders | analyze | status   | OK       | +---------------+---------+----------+----------+ mysql> select * from mysql.innodb_index_stats  where database_name='custom' and table_name='orders' and index_name='dpcz_FK'; +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ | database_name | table_name | index_name | last_update         | stat_name    | stat_value | sample_size | stat_description                  | +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ | custom        | orders     | dpcz_FK    | 2022-03-03 21:58:18 | n_diff_pfx01 |      19582 |          50 | d_p_c_z_id                        | | custom        | orders     | dpcz_FK    | 2022-03-03 21:58:18 | n_diff_pfx02 |   32425512 |         128 | d_p_c_z_id,id                     | | custom        | orders     | dpcz_FK    | 2022-03-03 21:58:18 | n_leaf_pages |      55891 |        NULL | Number of leaf pages in the index | | custom        | orders     | dpcz_FK    | 2022-03-03 21:58:18 | size         |      64248 |        NULL | Number of pages in the index      | +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+

 

2.analyze table，不rebuild表，但是将innodb_stats_persistent_sample_pages从128增加到512。得到的stat_value的值为192，非常接近真正的cardinality，即195。索引中叶子页的数量有的很大的改变，从55653变成了44188。索引中页的数量也有很大的改边，从63864变成了50304。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

mysql> show variables like '%persistent_sample%'; +--------------------------------------+-------+ | Variable_name                        | Value | +--------------------------------------+-------+ | innodb_stats_persistent_sample_pages | 512   | +--------------------------------------+-------+ mysql> analyze table orders; +---------------+---------+----------+----------+ | Table         | Op      | Msg_type | Msg_text | +---------------+---------+----------+----------+ | custom.orders | analyze | status   | OK       | +---------------+---------+----------+----------+ mysql> select * from mysql.innodb_index_stats  where database_name='custom' and table_name='orders' and index_name='dpcz_FK'; +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ | database_name | table_name | index_name | last_update         | stat_name    | stat_value | sample_size | stat_description                  | +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ | custom        | orders     | dpcz_FK    | 2022-03-09 06:54:29 | n_diff_pfx01 |        192 |         179 | d_p_c_z_id                        | | custom        | orders     | dpcz_FK    | 2022-03-09 06:54:29 | n_diff_pfx02 |   31751321 |         512 | d_p_c_z_id,id                     | | custom        | orders     | dpcz_FK    | 2022-03-09 06:54:29 | n_leaf_pages |      44188 |        NULL | Number of leaf pages in the index | | custom        | orders     | dpcz_FK    | 2022-03-09 06:54:29 | size         |      50304 |        NULL | Number of pages in the index      | +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+

 

3.rebuild表，保持innodb_stats_persistent_sample_pages=128,得到的stat_value值是187，接近准确的cardinality 195。索引中叶子页的数量改变很大，从55653变成了43733，索引中页的数量也从63864变成了50111。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

mysql> show variables = 'innodb_stats_persistent_sample_pages'; +--------------------------------------+-------+ | Variable_name                        | Value | +--------------------------------------+-------+ | innodb_stats_persistent_sample_pages | 128   | +--------------------------------------+-------+ 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'; +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ | database_name | table_name | index_name | last_update         | stat_name    | stat_value | sample_size | stat_description                  | +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ | custom        | orders     | dpcz_FK    | 2022-03-07 18:44:43 | n_diff_pfx01 |        187 |         128 | d_p_c_z_id                        | | custom        | orders     | dpcz_FK    | 2022-03-07 18:44:43 | n_diff_pfx02 |   31531493 |         128 | d_p_c_z_id,id                     | | custom        | orders     | dpcz_FK    | 2022-03-07 18:44:43 | n_leaf_pages |      43733 |        NULL | Number of leaf pages in the index | | custom        | orders     | dpcz_FK    | 2022-03-07 18:44:43 | size         |      50111 |        NULL | Number of pages in the index      | +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+

表的统计信息正确之后，mysql优化器就会使用正确的执行计划：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

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 WHERE con.date = current_date() and pcz.type = "T_D" GROUP BY con.date, pcz.c_c,  pcz.type; +----+-------------+-------+------------+--------+-------------------+----------+---------+---------------------------------------+------+----------+---------------------------------+ | id | select_type | table | partitions | type   | possible_keys     | key      | key_len | ref                                   | rows | filtered | Extra                           | +----+-------------+-------+------------+--------+-------------------+----------+---------+---------------------------------------+------+----------+---------------------------------+ |  1 | SIMPLE      | con   | NULL       | ref    | FK_order,IDX_date | IDX_date | 3       | const                                 | 3074 |   100.00 | Using temporary; Using filesort | |  1 | SIMPLE      | c     | NULL       | eq_ref | PRIMARY           | PRIMARY  | 8       | custom.con.c_id                       |    1 |   100.00 | Using index                     | |  1 | SIMPLE      | o     | NULL       | eq_ref | PRIMARY,dpcz_FK   | PRIMARY  | 8       | custom.con.order_id                   |    1 |   100.00 | Using where                     | |  1 | SIMPLE      | pcz   | NULL       | eq_ref | PRIMARY           | PRIMARY  | 8       | custom.o.d_p_c_z_id                   |    1 |    10.00 | Using where                     | +----+-------------+-------+------------+--------+-------------------+----------+---------+---------------------------------------+------+----------+---------------------------------+ 4 rows in set, 1 warning (0.01 sec)

 

结论

mysql优化器依赖表的统计信息的准确性来选择优化的执行计划。我们可以控制表的统计信息的准确性，通过修改innodb_stats_persistent_pages。

我们也可以强制重新统计表的统计信息，通过重构表，从而重构索引，这样可以增强表的统计信息的准确性。

重构表我们可以使用alter table或者pt工具来完成。