- 当前位置：首页 >人工智能 >MySQL 统计信息不准导致的性能问题
MySQL 统计信息不准导致的性能问题
发布时间：2025-11-05 14:12:32 来源：技术快报 作者：数据库
表的计信统计信息错误导致优化器选择错误的执行计划。
一个客户的息不性性能优化案例: 没有修改数据库实例的任何配置参数以及业务代码没有变更的情况下，一条 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 |+----+-------------+-------+------------+--------+-------------------+----------+---------+----------------------------+------+----------+----------------------------------------------+1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.执行计划显示 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 |+----+-------------+-------+------------+--------+-----------------+----------+---------+---------------------------------------+--------+----------+---------------------------------+1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.问题来了 rows examined = 110446*(1*10%)=11045 rows根据计算评估， 第一个执行计划的问题 1347 大概是 110446 的十分之一 ，至少从表面上看来这个是计信MySQL 优化器选择第一个执行计划的原因。
但是息不性对比实际的查询结果的响应时间，肯定粗问题了。准导因为执行计划二 的问题sql 的响应时间在预期之内，但是计信执行计划一对应的响应时间反而更慢。IT技术网
进一步来看表 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 |+---------------+------------+---------------------+----------+----------------------+--------------------------+1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.分析至此，我们可以断定 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 |+----------------------------------------------+1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.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 控制采样页面的数量。参考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上不同key值的个数与level LA上记录总数的比值`N_DIFF_AVG_LEAF`：在所有 A 叶页中找到的不同键值的平均数。1.2.3.采样算法代码的详细信息可以在链接中找到：https://github.com/mysql/mysql-server/blob/6846e6b2f72931991cc9fd589dc9946ea2ab58c9/storage/innobase/dict/dict0stats.cc
基于上面的介绍，我们知道当一个表的索引发生分裂时，无论是叶子页数（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 |+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.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 |+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.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 |+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.在更正表统计数据后，MySQL 优化器也会选择正确的执行计划：
复制mysql> explainSELECT count(con.id) ,
MAX(DAYNAME(con.date)) ,
now() ,
pcz.type,
pcz.c_cFROM 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.idWHERE 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)1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22. 结论MySQL优化器依赖于表的统计信息的准确性来选择最优的执行计划。我们可以通过更改参数innodb_stats_persistent_sample_pages 来控制系统采集表统计信息的准确性。
我们还可以选择通过在对索引进行碎片整理的同时重建/重建表来强制重新计算表统计信息，这有助于提高表统计信息的准确性。重构表，我们可以直接用 alter table xx; 修改表或者使用 pt-online-schema-change 达到同样的效果。
- 上一篇：大多数的 Linux Distro 也将 IPv6 (Internet Protocol Version 6) 预设地开启，但有时实际上不需要 IPv6 支援，如要关闭 IPv6 可用以下方法: Debian / Ubuntu 1. 开启 /etc/modprobe.d/aliases 2. 里面有一行是 “alias net-pf-10 ipv6″ 3. 将以上一行用以下两行取代: alias net-pf-10 off alias ipv6 off Red Hat 1. 开启 /etc/modprobe.conf 2. 加入这一行: alias net-pf-10 off 更改以上档案后重新启动系统，这时 IPv6 便会关闭。我在 Ubuntu 上关闭了 IPv6 后，发觉 DNS 的效能改善了，那就是一般上网时要 resolve ip 时回应得更快。
- 下一篇：手机回收站删除照片恢复妙招（一键找回已删除的手机照片，让珍贵回忆不再消失）
- 戴尔超极本（戴尔超极本在市场中的地位和用户评价）
- 高通骁龙630游戏性能全面解析（以高通骁龙630为基础，畅享流畅游戏体验）
- 如何在Windows7中设置开机启动项为主题（快速定制你的Windows7开机界面）
- 老毛桃教程ISO（探索老毛桃教程ISO，感受大自然的神奇之处）
- 因为把Ubuntu从13.04升级到13.10后，鼠标出现了问题，一打开网页就不停的闪，而且好多东西都不一样了，又不好用，所以选择重装系统，重装的时候偏偏又重新分了区，然后装完了，一重启，悲剧了，进入修复grub rescue模式下了，一时间就不知道怎么办好了，好在还有个平板可以上网，马上上网搜。很快就搜到不少，点开第一个，“用U盘启动进入Windows系统重写mbr”？那我的Ubuntu不就没了！！！直接PASS！！！“直接在grub rescue”下修复，嗯，这个好。马上按给的方法试，我去，完全不一样好不好！完全没有变化！！再接着搜，嗯，这篇好像靠谱多了，还写了出现这种问题对应的情况，不错，有重新分区造成的，这个看起来是了。马上照着做。1、输入set查看当前的设置（原来这个是查看当前设置的，先前搜到的都不说清楚）2、输入set root=(hd0,msdos11)，难怪刚才我的没有变化，选择前搜到的根本没有set，直接就root=(hd0,msdos11)了。（msdos11是我Ubuntu安装的位置，每个人不同的，可以用ls (hd0,x)/root/grub 查看安装到哪个分区了，运行命令后出现很多文件的就是了）3、输入insmod /grub/normal.mod，我靠，没有这个文件，什么情况！！！难道我的不是Ubuntu！！这不科学！！！冷静，要冷静！检查Ubuntu安装在哪个盘时还有几个文件夹，会不会是文件的路径不一样，嗯，查看一个其它文件路径看看，输入ls (hd0,msdos11)/root/grub/i386-pc，一个回国，出来一大串的文件名，大部分是.mod和.img的，应该就是这个了，把命令换成insmod (hd0,msdos11)/root/grub/i386-pc/normal.mod，成功！！！！4、输入normal，回车的，熟悉的启动菜单出来了。不过还没有完。5，进行Ubuntu，打开终端，输入sudo update-grub，更新一下引导文件。6、输入sudo grub-install /dev/sda，等待几秒，我靠，失败，还没有原因！！算了，重启试试。等待中。。。我去，又是grub rescue！！！重新来过，再次进入Ubuntu，把网页再往下拉，还有解决的方法，不过刚才因为是要用live cd启动就没管，再试这个方法好了！（一般情况到这里应该就修复完成了，不过我的情况不一样，我是在重装时又重新分了区，而且还重新指定了引导文件存在的位置导致的，需要再用到下面的方法才行。）7、输入sudo mount /dev/sda11 /mnt，把安装的盘挂载到/mnt上8、输入sudo grub-install --boot-directory=/mnt/boot/dev/sda，我靠，出来这一串东西是什么，我没输入错误啊，怎么不是运行成功！再试！还是这样！！！！重复试了几次后，算了，重启吧，奇迹般的启动菜单出来了！！原来已经执行成功了吗？！！好了！这就是过程了，我的小心脏啊！！！（这是没有把/boot单独分出来的方法，把/boot单独分出来的话就把命令中的“/boot”去掉就好了）
- 如何正确调节CPU以提升电脑性能？（掌握CPU调节技巧，轻松提升电脑速度和稳定性）
- 探究联发科MTK6580的性能及特点（MTK6580处理器在智能手机领域的优势和应用）
- 将显示器转变为电视机的方法及优势（以显示器作为电视机使用的关键步骤和注意事项）
- 探索戴尔E5530的性能和功能（一款强大的商务笔记本电脑解析）
- 大疆精灵4画质的优劣势及用户体验剖析（大疆精灵4的画质表现如何？用户反馈如何？）
- 简便快捷的笔记本电脑U盘重装系统方法（轻松解决笔记本电脑系统问题，提升操作效率）
- 探寻最顶级台式电脑的华丽配置（领略未来科技的无限魅力）
- 小米4A43寸（颠覆传统电视，带来全新视觉体验）
- 电脑无法连接WiFi的常见问题与解决方法（轻松解决电脑无法连接WiFi的困扰，享受高速上网体验）
- 以讯为4412开发板的性能与应用（一款强大的开发板助力创新项目）
- 华为畅享6（探索华为畅享6的手写功能，释放创作潜力）
- 如何使用电脑文件夹加密文件（保护个人隐私的有效方法）
- 如何查询苹果手机激活时间？（简单步骤帮您查询苹果手机激活时间的方法）
- 苹果6P手机如何？好不好？（全面评测，从外观到性能，一一解析苹果6P的优势和不足）
- 飞利浦9551（体验舒适顺滑的剃须过程，享受持久细腻的剃须效果）
随便看看
源码下载企商汇IT资讯网香港云服务器益华科技服务器租用源码库IT技术网亿华云亿华灵动亿华科技亿华云计算IT资讯网码上建站极客码头益华科技极客编程全栈开发益强前沿资讯益强资讯优选思维库益强智囊团技术快报创站工坊运维纵横多维IT资讯亿华智造益强编程堂云站无忧科技前瞻益强编程舍编程之道智能时代益华IT技术论坛云智核亿华智慧云亿华互联益强科技码力社益华科技益强IT技术网益强科技亿华云
- Copyright © 2025 Powered by MySQL 统计信息不准导致的性能问题,技术快报 滇ICP备2023006006号-46sitemap