社区首页 >专栏 >为什么要关注索引统计误差

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

田帅萌

发布于 2019-07-19 09:35:30

49200

代码可运行

文章被收录于专栏：「3306 Pai」社区「3306 Pai」社区

运行总次数：0

代码可运行

导读

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

事情的起因是，我的朋友小明同学有一天突然发现有个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 |
+---------------+-------------------+--------------+--------+--------+----------+