为什么要关注索引统计误差
导读
由一个不可思议的索引统计信息误差案例引发的监控需求。
事情的起因是，我的朋友小明同学有一天突然发现有个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库里“查看索引统计偏差”。
本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2019-07-17，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录
相关产品与服务
云数据库 MySQL
腾讯云数据库 MySQL（TencentDB for MySQL）为用户提供安全可靠，性能卓越、易于维护的企业级云数据库服务。其具备6大企业级特性，包括企业级定制内核、企业级高可用、企业级高可靠、企业级安全、企业级扩展以及企业级智能运维。通过使用腾讯云数据库 MySQL，可实现分钟级别的数据库部署、弹性扩展以及全自动化的运维管理，不仅经济实惠，而且稳定可靠，易于运维。