细说analyze table

导读

本文详细介绍了ANALYZE TABLE的作用及更深入的原理,执行代价估算公式等

ANALYZE TABLE 作用

  • ANALYZE TABLE 会统计索引分布信息,并将结果持久化存储;
  • 对于 MyISAM 表,相当于执行了一次 myisamchk –analyze;
  • 支持 InnoDB、NDB、MyISAM 等存储引擎,但不支持 视图(view);
  • ANALYZE TABLE也可以用在表分区上;
  • 对InnoDB、MyISAM表执行 ANALYZE TABLE 时,会加上读锁(read lock);
  • 执行 ANALYZE TABLE 会记录binlog。(这是合理的,因为索引分析这个操作,在MASTER端执行完后,SLAVE端也是需要的)

索引分布分析都干了啥

若自从上次索引分析后没有数据更新的话,执行 ANALYZE TABLE 并不会再分析一次。
optimizer 会根据索引分析结果来判断表 JOIN 的驱动顺序,以及选用哪个索引。

关于 innodb_stats_persistent 选项

我们可以通过设定该选项,决定索引分析结果是否要持久化存储到磁盘中。

不持久化存储的话,可能需要频繁更新统计信息,并由此引发执行计划反复变化。

这个设置在每个表创建(或后期 ALTER 修改)时都可以自行指定 STATS_PERSISTENT 选项,也可以设置全局选项 innodb_stats_persistent(这个选项设置为 1 时,则表统计信息将持久化存储)。

关于 innodb_stats_persistent_sample_pages 选项

该选项决定了每次统计索引及其他信息时要采集多少个data page,默认值是 20。

增加这个值,可以提高统计信息的精确度,同样也能提高执行计划的准确性,不过也相应增加了在InnoDB表上分析的I/O开销。

备注

  • 增加 innodb_stats_persistent_sample_pages 的值可能导致 ANALYZE TABLE 的耗时增加。可以参考下方公式估算执行 ANALYZE TABLE 的代价。
  • 只有在 innodb_stats_persistent 选项启用后,innodb_stats_persistent_sample_pages 也才能跟着生效,否则的话,只有选项 innodb_stats_transient_sample_pages 才能生效。
  • 选项 innodb_stats_transient_sample_pages 设定的是 动态 统计信息采集的data page数量,默认值是 8。
  • 选项 innodb_stats_persistent_sample_pages 是全局作用的,但如果某个表想单独定义采集的page数目,可以在DDL时自行设定:
CREATE TABLE ... STATS_SAMPLE_PAGES = 30;

ALTER TABLE ... STATS_SAMPLE_PAGES = 30;

ANALYZE TABLE 代价估算

关于执行ANALYZE TABLE 的代价计算公式:

影响代价因素:
– innodb_stats_persistent_sample_pages定义值大小;
– 表中索引数多少;
– 表中分区数多少。

代价粗略估算公式:innodb_stats_persistent_sample_pages * 索引数 * 分区数

而更严谨的计算公式见下:

O(n_sample
  * (n_cols_in_uniq_i
     + n_cols_in_non_uniq_i
     + n_cols_in_pk * (1 + n_non_uniq_i))
  * n_part)

各项指标解释:

  • n_sample,采集的data page数量;
  • n_cols_in_uniq_i,所有唯一索引(不含主键索引)中的列总数;
  • n_cols_in_non_uniq_i,所有普通索引中的列总数;
  • n_cols_in_pk,主键索引中的列总数(若未显式定义主键,则相当于只有一列的ROWID);
  • n_non_uniq_i,非唯一索引数量;
  • n_part,表分区数量。

以下表为例:

CREATE TABLE t (
  a INT,
  b INT,
  c INT,
  d INT,
  e INT,
  f INT,
  g INT,
  h INT,
  PRIMARY KEY (a, b),
  UNIQUE KEY i1uniq (c, d),
  KEY i2nonuniq (e, f),
  KEY i3nonuniq (g, h)
);

我们执行下面的SQL来查询这个表的索引信息:

  SELECT index_name, stat_name, stat_description
  FROM mysql.innodb_index_stats
  WHERE
  database_name='test' AND
  table_name='t' AND
  stat_name like 'n_diff_pfx%';

  +------------+--------------+------------------+
  | index_name | stat_name    | stat_description |
  +------------+--------------+------------------+
  | PRIMARY    | n_diff_pfx01 | a                |
  | PRIMARY    | n_diff_pfx02 | a,b              |
  | i1uniq     | n_diff_pfx01 | c                |
  | i1uniq     | n_diff_pfx02 | c,d              |
  | i2nonuniq  | n_diff_pfx01 | e                |
  | i2nonuniq  | n_diff_pfx02 | e,f              |
  | i2nonuniq  | n_diff_pfx03 | e,f,a            |
  | i2nonuniq  | n_diff_pfx04 | e,f,a,b          |
  | i3nonuniq  | n_diff_pfx01 | g                |
  | i3nonuniq  | n_diff_pfx02 | g,h              |
  | i3nonuniq  | n_diff_pfx03 | g,h,a            |
  | i3nonuniq  | n_diff_pfx04 | g,h,a,b          |
  +------------+--------------+------------------+

上面这个结果看起来有点奇怪是不是,其实没错,先科普几点知识:

  • 所有的普通索引,实际物理存储时,都要包含主键列的,也就是所谓的 index extensions 特性;
  • 统计索引信息时,是根据最左原则,要统计各种组合的。比如(a,b) 索引,要统计(a), (a,b), (a,b,pk) 三种信息,而不是只统计(a,b)这个信息;
  • 不过,在 mysql.innodb_index_stats 中存储统计信息时,是不统计唯一索引后面存储主键列信息的,非唯一普通索引后存储主键列信息则会被统计进去;

因此,上面 mysql.innodb_index_stats 中存储的统计结果是正确的。

我们再回来看下索引统计的代价公式,像下面这样计算:

- n_sample,采集的data page数量,值为 20(默认值);
- n_cols_in_uniq_i,所有唯一索引(不含主键索引)中的列总数,值为 2;
- n_cols_in_non_uniq_i,所有普通索引中的列总数,值为 4;
- n_cols_in_pk,主键索引中的列总数(若未显式定义主键,则相当于只有一列的ROWID),值为 2;
- n_non_uniq_i,非唯一索引数量,值为 2;
- n_part,表分区数量,值为 1(没有表分区,值为1,而不是0)。

那么最终需要扫描的data page数结果就是:

20 * (2 + 4 + 2 * (1 + 2)) * 1 = 240

实际需要读取的字节数则是:

240 * 16384 = 3932160 (即 3.84M)

当然了,要读取的data page,有可能已经在buffer pool中了,因此并不全是物理读。

从中,我们也可以看到,这个代价和表的数据量并无直接关系。
不过,当表数量越大时,聚集索引的 B+ 树也越大,搜索代价肯定也越大。

参考

全文完。


扫码加入叶老师的「MySQL核心优化课」,开启MySQL的修行之旅吧。

活久见,为什么SHOW TABLE STATUS总是不更新

踩坑了吗?

1. 问题描述

前几天,QQ群里在讨论一个关于MySQL表统计信息迟迟不更新的问题。
这个问题我复现了,下面是详细过程:

# 创建一个空表
[root@yejr.run]>create table ttxx like t1;

# 第一次执行 show table status,看到 Rows = 0,没问题
[root@yejr.run] [test]>show table status like 'ttxx'\G
*************************** 1. row ***************************
           Name: ttxx
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: 1
    Create_time: 2020-06-04 16:17:54
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

# 写入将近80万条数据
[root@yejr.run]>insert into ttxx select id,name,c1 from t1;
Query OK, 799994 rows affected (8.25 sec)
Records: 799994  Duplicates: 0  Warnings: 0

# 再次执行 show table status,发现 Rows 值还是 0,并且过了几秒钟后多执行几次,结果依然如此
[root@yejr.run] [test]>show table status like 'ttxx'\G
*************************** 1. row ***************************
           Name: ttxx
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: 1
    Create_time: 2020-06-04 16:17:54
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:

简言之,就是执行 SHOW TABLE STATUS无法及时查看到该表的最新统计信息

但与此同时,直接查看 mysql.innodb_table_statsmysql.innodb_index_stats 两个表,却又可以看到该表的统计信息已经更新了:

[root@yejr.run] [test]>select * from mysql.innodb_table_stats where database_name ='test' and table_name ='ttxx';
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name | last_update         | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| test          | ttxx       | 2020-06-04 16:18:24 | 795064 |                 2788 |                        0 |
+---------------+------------+---------------------+--------+----------------------+--------------------------+

[root@yejr.run] [test]>select * from mysql.innodb_index_stats where database_name ='test' and table_name ='ttxx';
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| test          | ttxx       | PRIMARY    | 2020-06-04 16:18:24 | n_diff_pfx01 |     795064 |          20 | aid                               |
| test          | ttxx       | PRIMARY    | 2020-06-04 16:18:24 | n_leaf_pages |       2764 |        NULL | Number of leaf pages in the index |
| test          | ttxx       | PRIMARY    | 2020-06-04 16:18:24 | size         |       2788 |        NULL | Number of pages in the index      |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+

尝试跑一个SQL观察执行计划,看起来也是正确的:

[root@yejr.run] [test]>desc select count(*) from ttxx;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | ttxx  | NULL       | index | NULL          | PRIMARY | 4       | NULL | 795064 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+

此时再执行 SHOW TABLE STATUS 依然无法看到信息更新。

到底肿么回事呢?

2. 问题探究

作为老司机(踩坑大户),首先想到的就是检查官方手册。
MySQL官方手册的描述中,有这么一段内容:

• Rows

The number of rows. Some storage engines, such as MyISAM, store the exact count. For other storage engines, such as InnoDB, this value is an approximation, and may vary from the actual value by as much as 40% to 50%. In such cases, use SELECT COUNT(*) to obtain an accurate count.

The Rows value is NULL for INFORMATION_SCHEMA tables.

For InnoDB tables, the row count is only a rough estimate used in SQL optimization. (This is also true if the InnoDB table is partitioned.)

简言之,就是说MyISAM表的Rows是精确值,但InnoDB表则只是大概值,甚至有可能只是真实值的40% ~ 50% 之间。

另外,这个信息是从 INFORMATION_SCHEMA.TABLES (下面简称IFS.TABLES)里获取的:

Table information is also available from the INFORMATION_SCHEMA TABLES table. See Section 25.36, “The INFORMATION_SCHEMA TABLES Table”.

那我们再看看文档中关于 IFS.TABLES 的描述吧:

25.36 The INFORMATION_SCHEMA TABLES Table

The TABLES table provides information about tables in databases.

Columns in TABLES that represent table statistics hold cached values. The information_schema_stats_expiry system variable defines the period of time before cached table statistics expire. The default is 86400 seconds (24 hours). If there are no cached statistics or statistics have expired, statistics are retrieved from storage engines when querying table statistics columns. To update cached values at any time for a given table, use ANALYZE TABLE. To always retrieve the latest statistics directly from storage engines, set information_schema_stats_expiry to 0. For more information, see Section 8.2.3, “Optimizing INFORMATION_SCHEMA Queries”.

看到这里,真相基本上呼之欲出了。
IFS.TABLES表中看到的数据是有cache的,默认cache时长是 86400秒(即1天),修改参数 information_schema_stats_expiry 即可调整时长。也就是说,除非cache过期了,或者手动执行 ANALYZE TABLE 更新统计信息,否则不会主动更新。
这个参数(功能)是MySQL 8.0后新增的,所以这个问题在8.0之前的版本不存在。
参数 information_schema_stats_expiry 还影响其 IFS.STATISTICS 表。
此外,该参数还可以在session级动态修改。
我们尝试修改session级配置:

[root@yejr.run]>set session information_schema_stats_expiry = 0;

# 修改完后就可以看到Rows数据变了
[root@yejr.run]>show table status like 'ttxx'\G
*************************** 1. row ***************************
           Name: ttxx
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 795064
 Avg_row_length: 57
...

[root@yejr.run]>set session information_schema_stats_expiry = 86400;
# 把session配置改回默认值,尴尬的发现Rows值又恢复成0了
[root@yejr.run] [test]>show table status like 'ttxx'\G
*************************** 1. row ***************************
           Name: ttxx
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
...

看来,如果应用程序中有需要读取 table status 概要信息的时候,最好还是先手动执行 ANALYZE TABLE 或者修改参数值,也可以用下面这样的SQL:

select /* set_var(information_schema_stats_expiry = 1) */ * from information_schema.tables where table_schema='test' and table_name = 'ttxx'\G

这是MySQL 8.0后新增的HINT语法。

另外,文档中还有一段注释:

If the innodb_read_only system variable is enabled, ANALYZE TABLE may fail because it cannot update statistics tables in the data dictionary, which use InnoDB. For ANALYZE TABLE operations that update the key distribution, failure may occur even if the operation updates the table itself (for example, if it is a MyISAM table). To obtain the updated distribution statistics, set information_schema_stats_expiry=0.

意思是,当启用参数 innodb_read_only 后再执行 ANALYZE TABLE 就会失败,哪怕要更新统计信息的表是MyISAM引擎,因为所有InnoDB表都被设置为只读,更新统计信息后无法回写到对应的InnoDB字典表里了。

3. 总结

遇到诡异问题时,总是习惯性地先去查阅官方手册,通常都是可以得到答案的,耐心点,再耐心点。

延伸阅读

enjoy MySQL 8.0 :)

全文完。


扫码加入叶老师的「MySQL核心优化课」,开启MySQL的修行之旅吧。

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

导读

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

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

延伸阅读

全文完。


扫码加入叶老师的「MySQL核心优化课」,开启MySQL的修行之旅吧。