专栏首页「3306 Pai」社区为什么要关注索引统计误差

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

导读

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

事情的起因是,我的朋友小明同学有一天突然发现有个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库里“查看索引统计偏差”。

本文分享自微信公众号 - 3306pai(pai3306),作者:叶金荣

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-07-17

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 开源分布式中间件 DBLE 快速入门指南

    DBLE 是企业级开源分布式中间件,江湖人送外号 “MyCat Plus”;以其简单稳定,持续维护,良好的社区环境和广大的群众基础得到了社区的大力支持;

    田帅萌
  • MySQL分库分表中间件-RadonDB性能测试

    了解一个产品,从性能测试下手是最好的方法,这里就是针对金融级MySQL解决方案RadonDB中的核心组件Radon进行一次性能测试。

    田帅萌
  • 好文推荐|MySQL 8.0 常见问题——群组复制篇

    群组复制(MySQL Group Replication)是 InnoDB Cluster 的一部分。

    田帅萌
  • Python学习记录day2

    Python是一门基于虚拟机的语言,pyc 是由py文件经过编译后二进制文件,py文件变成pyc文件后,加载的速度有所提高,而且pyc是一种跨平台的字节码,是由...

    py3study
  • 私有云中Kubernetes Clu

    Kubernetes Master HA架构图 ? 配置与说明 所有组件可以通过kubelet static pod的方式启动和管理,由kubelet st...

    Walton
  • keras读取训练好的模型参数并把参数赋值给其它模型详解

    本博文中的代码,实现的是加载训练好的模型model_halcon_resenet.h5,并把该模型的参数赋值给两个不同的新的model。

    砸漏
  • MYSQL在众多表中查询表名和字段名

    SELECT table_name, column_name from information_schema.columns WHERE column_name...

    大江小浪
  • python 子类中扩张超类属性

    class Person: def init(self, name): self.name = name

    用户5760343
  • python变量,输入、输出、判断、循环

    声明变量 name = 'ajune' 变量赋值 name = 'ajune' name1 = name

    py3study
  • shell脚本的参数传递一例

    在shell脚本中,我们也可以像其他编程语言一样处理传递的option以及对应的值,比如: 既支持 --name=<NAME>, 也支持 --name <N...

    qsjs

扫码关注云+社区

领取腾讯云代金券