导读
由一个不可思议的索引统计信息误差案例引发的监控需求。
事情的起因是，我的朋友小明同学有一天突然发现有个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库里“查看索引统计偏差”。
