查看原文
其他

为什么要关注索引统计误差

叶金荣 老叶茶馆 2024-07-08

导读

由一个不可思议的索引统计信息误差案例引发的监控需求。

事情的起因是,我的朋友小明同学有一天突然发现有个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 更新索引统计信息,一般就能解决问题了

如何监控

  1. 每个非唯一辅助索引都会包含主键列,正常情况下,包含主键列的那行统计信息和主键索引的统计信息相差不会太大。

  2. 唯一索引比较特殊,因为在 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 |
+------------+-----+---------+--------------+------------+-----------------------------------+

以上面为例,希望得到的结果是

  1. 唯一索引nu的统计信息和主键索引统计信息一样,没问题。

  2. 辅助索引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库里“查看索引统计偏差”。

延伸阅读


END







点这里这里有好课,为职场助攻

点文末“阅读原文”有惊喜




技术文章,都会在有赞平台专栏免费首发
扫码关注“知数堂Plus”小程序,最快速度获取


原创不易,转发、点“在看”、打赏都是对我最好的支持


继续滑动看下一个
向上滑动看下一个

您可能也对以下帖子感兴趣

文章有问题?点此查看未经处理的缓存