网站制作 网站建设 杭州,周口学做网站,网站关键词排名软件推荐,天津建站商城表的统计信息错误导致优化器选择错误的执行计划。
一个客户的性能优化案例: 没有修改数据库实例的任何配置参数以及业务代码没有变更的情况下#xff0c;一条 sql 出现大幅性能下降。
我们来看看出问题的sql 以及他的执行计划:
mysql explain
- SELECT count(con.…表的统计信息错误导致优化器选择错误的执行计划。
一个客户的性能优化案例: 没有修改数据库实例的任何配置参数以及业务代码没有变更的情况下一条 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*1642318548 但是实际的行数是 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,...
) ENGINEInnoDB ....
mysql select * from mysql.innodb_table_stats where database_namecutom and table_nameorders;
----------------------------------------------------------------------------------------------------------
| 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_namecutom and table_nameorders and index_namedpcz_FK;
mysql select * from mysql.innodb_index_stats where database_namecustom and table_nameorders and index_namedpcz_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/195166708 并且执行计划预估的扫描的行数应该是 (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 叶页中找到的不同键值的平均数。
采样算法代码的详细信息可以在链接中找到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 128stat_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_namecustom and table_nameorders and index_namedpcz_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_namecustom and table_nameorders and index_namedpcz_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 engineinnodb;
Query OK, 0 rows affected (11 min 16.37 sec)
mysql select * from mysql.innodb_index_stats where database_namecustom and table_nameorders and index_namedpcz_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 conjoin orders o on con.order_id o.idJOIN p_c_z AS pcz ON o.d_p_c_z_id pcz.idleft 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 达到同样的效果。
原文链接
本文为阿里云原创内容未经允许不得转载。
