- 当前位置：首页 >应用开发 >MySQL 统计信息不准导致的性能问题
MySQL 统计信息不准导致的性能问题
发布时间：2025-11-05 11:07:58 来源：码上建站 作者：域名
表的计信统计信息错误导致优化器选择错误的执行计划。
一个客户的息不性性能优化案例: 没有修改数据库实例的任何配置参数以及业务代码没有变更的情况下，一条 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 的响应时间在预期之内，但是计信执行计划一对应的响应时间反而更慢。源码下载
进一步来看表 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 是WordPress模板如何收集表的统计信息
我们可以通过显式的方式或者系统自动采集表的统计信息 。
通过开启参数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 达到同样的效果。
- 超燃教程（学会超燃剪辑，为你的动漫视频增添无限激情）
- 金立FL00（全面升级的金立FL00，你值得拥有）
- 揭秘有网啦WiFi（一种新型的无线网络接入方式与）
- 探索骨传导耳机的功能与优势（全面了解骨传导耳机的工作原理、音质表现和使用场景）
- 轻松拖的好处及技巧（帮你提高效率，减少压力的拖延解决方案）
- 探析JBLE40BT的音质表现（深入剖析JBLE40BT耳机的声音特点和品质）
- 小米四手机的性能如何？（解析小米四手机的各项功能和表现，为你带来更好的使用体验）
- 威索尼克GR07耳机（深度解析GR07耳机的卓越音质和舒适度）
- 在windows中我们可以将喜欢的网址放在桌面并双击打开，那么在ubuntu上可不可以呢，当然可以下面我们就一起来看一下。1、首先大家要写一个脚本用来打开一个网址，只有两行代码，很简单的。（我写的是打开JAVA的在线API帮助）。2、脚本写好以后我们运行脚本发现是可以正常运行的，我们先赋予脚本可运行的权限，chmod a+x 脚本名称，设置后发现脚本的各项权限如图所示。3、输入./JAVA_Api.sh 可以打开网页，这一步完成后接下来就简单了，和创建程序的快捷方式几乎一样。 4、我们来看一下JAVA_Api.desktop的详细设置，文件的配置几乎一样，就是脚本文件多了一个.sh的后缀。一定要注意脚本文件赋予它可运行的权限。5、添加完成后 在applications文件夹多了一个JAVA_Api.desktop的文件这就是快捷方式，双击图标就能打开。6、假如你想把它加到侧边栏，拖动就可以加入，好了在桌面添加网址的快捷方式就这样完成了。注意事项：编辑文件时权限的使用，要给脚本可运行的权限。相关推荐：ubuntu如何截图 ubuntu中截屏的三种方法Ubuntu 15.10正式版发布 官方ISO镜像下载
- 金立V580评测（金立V580的配置、性能和使用体验分析）
- 小米手机2017年的表现如何？（从性能到用户体验，一切尽在掌握）
- 蟒蛇按摩（探索蟒蛇按摩的健康益处及适用范围）
- 全面升级的新款iPad2017（性能卓越，功能升级，打造更高效的移动办公环境）
- 智齿难拔的原因及处理方法（探索智齿难拔的原因以及有效的处理方法）
- 铁三角A990Z手机评测（领先一步的性能和设计，铁三角A990Z让你体验全新视听盛宴）
- 苹果6版本10.2.1的功能和优势（探索苹果6版本10.2.1的卓越表现）
- 探索以200的显卡的性能和性价比（一起了解200的显卡在游戏和多媒体处理方面的表现）
- 以200的显卡怎么样？（性能与价值兼得，挑战你的游戏需求）
- 如何利用电脑设计出引人注目的冰粉广告？（通过教程学习，轻松掌握设计冰粉广告的技巧与方法）
- 波导仿苹果手机，如何实现近乎完美的模仿？（探索波导手机在外观、功能和性能等方面的模仿成果及局限性）
随便看看
IT技术网源码库IT资讯网服务器租用香港云服务器企商汇亿华云源码下载益华科技益强科技编程之道全栈开发亿华云计算亿华互联亿华智造极客码头益强IT技术网益强编程堂码上建站汇智坊技术快报思维库多维IT资讯益华科技亿华科技益强资讯优选极客编程IT资讯网亿华灵动益华科技益强智未来益强智囊团益强科技科技前瞻智能时代益华IT技术论坛云智核亿华云益强数据堂益强编程舍云站无忧码力社
- Copyright © 2025 Powered by MySQL 统计信息不准导致的性能问题,码上建站 滇ICP备2023006006号-47sitemap