为什么要关注索引统计误差
导读
由一个不可思议的索引统计信息误差案例引发的监控需求。
事情的起因是,我的朋友小明同学有一天突然发现有个SQL的执行计划出问题了。经过一番排查,居然发现是该表的辅助索引统计信息存在严重偏差。
我们知道,InnoDB表里每个辅助索引都会同时存储聚集索引列值,这就是所谓的 Index Extensions特性。那么,在统计索引信息时,包含聚集索引列的统计值就应该和聚集索引列的值几乎一样的才对,比如:(建议横屏观看)
[root@yejr.me]>select * from mysql.innodb_index_stats;
+------------+------------+------------+-------------+------------------+
| table_name | index_name | stat_value | sample_size | stat_description |
+------------+------------+------------+-------------+------------------+
...
| zst | PRIMARY | 40002 | 20 | id |
...
| zst | k1 | 40376 | 20 | uid,id |
...
+------------+------------+------------+-------------+------------------+
可以看到k1索引的 (uid, id) 统计值(stat_value列)和主键索引是几乎差不多的。
这次小明遇到的问题,也是我这么多年来头一次遇到过,而且这还是在国内某知名公有云数据库上发生的,简直有点不太可思议。提交工单后,工程师给的答复也表示以前没遇到过,暂时不确定是什么原因引起的。
既然这种问题不能避免,那就自己主动加个监控吧,于是就有了本文。
解决方案
找出索引统计信息中,辅助索引统计信息和主键索引相差太大的情况,也就是辅助索引的基数和主键索引相差太大的现象,发出告警,并且手动执行 ANALYZE TABLE t
更新索引统计信息,一般就能解决问题了。
如何监控
每个非唯一辅助索引都会包含主键列,正常情况下,包含主键列的那行统计信息和主键索引的统计信息相差不会太大。
唯一索引比较特殊,因为在 mysql.innodb_index_stats 表中,唯一索引列统计信息不会再包含主键列,但其基准值和主键列的基准值也不能相差太大。
假设有个表t3的索引统计数据如下(建议横屏观看)
[root@yejr.me] [mysql]>select database_name as db,
table_name as tbl, index_name as idx, stat_name,
stat_value, stat_description
from innodb_index_stats where
database_name = 'zhishutang' and table_name = 't3';
+------------+-----+---------+--------------+------------+-----------------------------------+
| db | tbl | idx | stat_name | stat_value | stat_description |
+------------+-----+---------+--------------+------------+-----------------------------------+
| zhishutang | t3 | PRIMARY | n_diff_pfx01 | 1900 | id |
| zhishutang | t3 | PRIMARY | n_leaf_pages | 1 | Number of leaf pages in the index |
| zhishutang | t3 | PRIMARY | size | 1 | Number of pages in the index |
| zhishutang | t3 | name | n_diff_pfx01 | 1 | name |
| zhishutang | t3 | name | n_diff_pfx02 | 19 | name,id |
| zhishutang | t3 | name | n_leaf_pages | 1 | Number of leaf pages in the index |
| zhishutang | t3 | name | size | 1 | Number of pages in the index |
| zhishutang | t3 | nu | n_diff_pfx01 | 1900 | nu |
| zhishutang | t3 | nu | n_leaf_pages | 1 | Number of leaf pages in the index |
| zhishutang | t3 | nu | size | 1 | Number of pages in the index |
+------------+-----+---------+--------------+------------+-----------------------------------+
以上面为例,希望得到的结果是
唯一索引nu的统计信息和主键索引统计信息一样,没问题。
辅助索引name的第二条(含主键列的那条)统计信息 (name, id) 和主键索引统计信息相差太远,属于异常,要能被发现。
实现该目的的SQL方法如下:(建议横屏观看)
set @statdb = 'yejr';
select
a.database_name ,
a.table_name ,
a.index_name ,
a.stat_value SK,
b.stat_value PK,
round((a.stat_value/b.stat_value)*100,2) stat_pct
from
(
select
b.database_name ,
b.table_name ,
b.index_name ,
b.stat_value
from
(
select database_name ,
table_name ,
index_name ,
max(stat_name) stat_name
from innodb_index_stats
where database_name = @statdb
and stat_name not in ( 'size' ,'n_leaf_pages' )
group by
database_name ,
table_name ,
index_name
) a join innodb_index_stats b on a.database_name=b.database_name
and a.table_name=b.table_name
and a.index_name=b.index_name
and a.stat_name=b.stat_name
and b.index_name !='PRIMARY'
) a left join
(
select
b.database_name ,
b.table_name ,
b.index_name ,
b.stat_value
from
(
select database_name ,
table_name ,
index_name ,
max(stat_name) stat_name
from innodb_index_stats
where database_name = @statdb
and stat_name not in ( 'size' ,'n_leaf_pages' )
group by
database_name ,
table_name ,
index_name
) a join innodb_index_stats b
on a.database_name=b.database_name
and a.table_name=b.table_name
and a.index_name=b.index_name
and a.stat_name=b.stat_name
and b.index_name ='PRIMARY'
) b
on a.database_name=b.database_name
and a.table_name=b.table_name
where b.stat_value is not null
and a.stat_value >0
order by stat_pct;
+---------------+-------------------+--------------+--------+--------+----------+
| database_name | table_name | index_name | SK | PK | stat_pct |
+---------------+-------------------+--------------+--------+--------+----------+
| zhishutang | t_json_vs_vchar | c1vc | 37326 | 39825 | 93.73 |
| zhishutang | t_json_vs_vchar | c2vc | 37371 | 39825 | 93.84 |
| zhishutang | t1 | name | 299815 | 299842 | 99.99 |
| zhishutang | t4 | c2 | 2 | 2 | 100.00 |
+---------------+-------------------+--------------+--------+--------+----------+
上面的SQL逻辑过于复杂,我是搞不定的,也是请知数堂SQL优化班郑松华老师帮忙给写的。
这个SQL脚本,我也已放在知数堂github库里“查看索引统计偏差”。
延伸阅读
查看索引统计偏差,http://t.cn/AiWN2dO8
MySQL Reference Manual,8.3.10 Use of Index Extensions,http://t.cn/AiWNLKiS
END
点文末“阅读原文”有惊喜
原创不易,转发、点“在看”、打赏都是对我最好的支持