修正MySQL表统计信息以获得更好的执行计划
2022-04-18 22:32 abce 阅读(482) 评论(1) 编辑 收藏 举报案例学习：
表的统计信息错误导致优化器不能选择正确的执行计划
一个客户说，在没有代码和配置变更的情况下，一个查询发生了灾难性的性能下降。为简介起见，对本文中的数据进行了编辑和修改，以免信息泄露。该案例也获得客户的允许。
以下是执行计划和执行结果：
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 | +----+-------------+-------+------------+--------+-------------------+----------+---------+----------------------------+------+----------+----------------------------------------------+
深入研究一下查询，可以看到，条件con.date=current_date()。这个条件似乎是个能很好地过滤结果，但是MySQL优化器为什么跳过了使用索引呢？我们再来看看执行计划，通过强制使用在con.date列上的索引。执行计划输出结果是：
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的十倍。
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的统计信息是不准的。可以通过以下的语句进行查询：
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叶子页上，不同键值的平均值
理解了上面的算法之后，当表的索引有了碎片之后，叶子页的数量，和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：
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。
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。
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优化器就会使用正确的执行计划：
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工具来完成。
