表的统计信息错误导致优化器选择错误的执行计划
一个客户的性能优化案例: 没有修改数据库实例的任何配置参数以及业务代码没有变更的情况下，一条 sql 出现大幅性能下降。
我们来看看出问题的sql 以及他的执行计划:
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 |
+----+-------------+-------+------------+--------+-------------------+----------+---------+----------------------------+------+----------+----------------------------------------------+
执行计划显示 rows examined = (19410%)1642(14.23%)=1347 查看执行计划我们就发现 where 条件 con.date = current_date() 。这个条件看起来更适合作为索引过滤数据。但是 为什么 MySQL 优化器不选择该索引呢？接下来使用 force index 强制执行计划使用 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 |
+----+-------------+-------+------------+--------+-----------------+----------+---------+---------------------------------------+--------+----------+---------------------------------+
问题来了 rows examined = 110446*(1*10%)=11045 rows根据计算评估， 第一个执行计划的 1347 大概是 110446 的十分之一 ，至少从表面上看来这个是MySQL 优化器选择第一个执行计划的原因。
但是对比实际的查询结果的响应时间，肯定粗问题了。因为执行计划二 的sql 的响应时间在预期之内，但是执行计划一对应的响应时间反而更慢。
进一步来看表 orders 的创建语句以及执行计划1，我们发现 表pcz的确有194行。然后查看 索引 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 的区分度(不一样的值的总数)为 195 。在信息统计表里面 dpcz_FK的 stat_value 值是 19498 ，显然这个值是不准确的并且比实际值大的多，100倍 。索引的 state_value 值应该等于这个字段的在表里面的区分度。
如果使用正确的 索引 dpcz_FK 的值 stat_value 195 去重新评估执行计划的成本，我们将得到执行计划1 的结果 32508150/195=166708 ，并且执行计划预估的扫描的行数应该是 (194*10%)*166708*(1*4.23%)=136804。因为该值是10倍于执行计划2 的值 11045 。MySQL 在没有使用force index的情况下就能走到正确的执行计划 。
这个sql的问题解决了，但是为什么 MySQL 的统计信息会计算错误，我们如何修复它呢？
回答这个问题之前，我们先了解一下 MySQL 是如何收集统计信息以及哪些参数控制 这个动作。
InnoDB 是如何收集表的统计信息
我们可以通过显式的方式或者系统自动采集表的统计信息 。
通过开启参数innodb_stats_auto_recalc =on (默认也是打开的) 以便在表的数据发生重大变化以后来自动收集表的统计信息。比如当表中的10% 的行发生变化 ，InnoDB 将重新计算统计信息。或者我们可以使用ANALYZE TABLE显式地重新计算统计信息。
InnoDB 使用随机采样技术的方法采集统计信息-- 随机抽取索引页，估计索引的基数。参数innodb_stats_persistent_sample_pages 控制采样页面的数量。
根据代码和描述，随机抽样并不是完全随机的。采样页面实际上是根据采样算法选择的。最终，不同键值的总数，即索引的 stat_value 将通过以下公式计算
N * R * N_DIFF_AVG_LEAF。其中
N : 叶页数
R : level LA上不同key值的个数与level LA上记录总数的比值
`N_DIFF_AVG_LEAF`：在所有 A 叶页中找到的不同键值的平均数。
基于上面的介绍，我们知道当一个表的索引发生分裂时，无论是叶子页数（N），还是 层LA 上不同键值的个数占 层LA 总记录数的比值（R ) 变得越来越不准确，因此 stat_value 的计算可能不正确。一旦发生这种情况，除非更改参数innodb_stats_persistent_sample_pages或重建索引，否则显式重新计算（手动运行 ANALYZE TABLE）将无法生成正确的 stat_value。
解决方法
我们怎么修正表的统计信息 ，并且阻止这类情况进一步发生。
经过前面的分析和讨论，我们知道 有两个因素影响数据库收集表的统计信息 ，
innodb_stats_persistent_sample_pages: A索引的组织方式
为了能够让 InnoDB 得到正确的 统计信息，我们需要 调整innodb_stats_persistent_sample_pages 或者重建索引 。
1、通过命令 analyze table 不重建的方式，保持
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 不重建，但将innodb_stats_persistent_sample_pages 从 128 增加到 512，使 stat_value 到192非常接近实际基数 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、重建表，保持innodb_stats_persistent_sample_pages 为128，同样得到了正确的 stat_value 187，接近真实基数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_sample_pages 来控制系统采集表统计信息的准确性。
我们还可以选择通过在对索引进行碎片整理的同时重建/重建表来强制重新计算表统计信息，这有助于提高表统计信息的准确性。重构表，我们可以直接用 alter table xx; 修改表或者使用 pt-online-schema-change 达到同样的效果。
本文内容不用于商业目的，如涉及知识产权问题，请权利人联系51Testing小编(021-64471599-8017)，我们将立即处理
