show index from 及analyze table 详解

保持热爱奔赴山海

修改于 2019-09-17 10:38:37

1.1K0

修改于 2019-09-17 10:38:37

文章被收录于专栏：数据库相关

内容参考：官方文档、老叶茶馆、姜承尧的InnoDB存储引擎2

https://mp.weixin.qq.com/s/1MsyxhtG6Zk3Q9gIV2QVbA

https://dev.mysql.com/doc/refman/5.7/en/show-index.html

https://dev.mysql.com/doc/refman/5.7/en/innodb-analyze-table-complexity.html

https://dev.mysql.com/doc/refman/5.7/en/innodb-statistics-estimation.html

show index from tb1 \G 或者使用select * from information_schema.STATISTICS \G 都可以列出表上面存在哪些索引及其它相关信息。

创建一个测试表：

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(20) CHARACTER SET latin1 NOT NULL DEFAULT '',
  `age` int(11) DEFAULT NULL, 
  `sex` enum('F','M') CHARACTER SET latin1 DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`),
  KEY `idx_name_age` (`name`,`age`),
  KEY `idx_name_sub` (`name`(5))
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 ;
INSERT INTO t1 (name,age,sex) VALUES('zhang san',24,'F');
INSERT INTO t1 (name,age,sex) VALUES('li si',26,'F');
INSERT INTO t1 (name,age,sex) VALUES('wang wu',20,'M');
INSERT INTO t1 (name,age,sex) VALUES('zhao liu',34,'M');
INSERT INTO t1 (name,age,sex) VALUES('John',64,'F');
show index from t1 ;

+---------+--------------+--------------+----------------+---------------+-------------+---------------+------------+----------+--------+--------------+-----------+-----------------+
| Table   |   Non_unique | Key_name     |   Seq_in_index | Column_name   | Collation   |   Cardinality |   Sub_part |   Packed | Null   | Index_type   | Comment   | Index_comment   |
|---------+--------------+--------------+----------------+---------------+-------------+---------------+------------+----------+--------+--------------+-----------+-----------------|
| t1      |            0 | PRIMARY      |              1 | id            | A           |             5 |     <null> |   <null> |        | BTREE        |           |                 |
| t1      |            1 | idx_name     |              1 | name          | A           |             5 |     <null> |   <null> |        | BTREE        |           |                 |
| t1      |            1 | idx_name_age |              1 | name          | A           |             5 |     <null> |   <null> |        | BTREE        |           |                 |
| t1      |            1 | idx_name_age |              2 | age           | A           |             5 |     <null> |   <null> | YES    | BTREE        |           |                 |
| t1      |            1 | idx_name_sub |              1 | name          | A           |             5 |          5 |   <null> |        | BTREE        |           |                 |
+---------+--------------+--------------+----------------+---------------+-------------+---------------+------------+----------+--------+--------------+-----------+-----------------

详解：

1.Table

表的名称。

2.Non_unique

如果索引不能包括重复词，则为0。如果可以，则为1。

3.Key_name

索引的名称。

4.Seq_in_index

索引中的列序列号，从1开始。

5.Column_name

列名称。

6.Collation

列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分类）。[InnoDB都是A，Heap表都是NULL]

7.Cardinality

非常关键的一个参数。表示的是索引中唯一值的数目的估计值。

Cardinality/n_rows_in_table的值应尽可能接近1，如果非常小，那么用户需要考虑是否可以删除该索引。

8.Sub_part

如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。

9.Packed

指示关键字如何被压缩。如果没有被压缩，则为NULL。

10.Null

索引的列中含有NULL。含有NULL则为YES。如果没有，则这里显示为空。

11.Index_type

索引的类型（BTREE, FULLTEXT, HASH, RTREE）。这里是InnoDB存储引擎，所以显示的都是BTREE

12.Comment

多种评注。

上面这一堆的状态值中，最重要的就是Cardinality。

Cardinality 值：

表的cardinality（可以翻译为“散列程度”），优化器会根据这个值来判断是否使用这个索引。但是这个值不是实时更新的，因为实时的话代价太大了，因此这个值不是太准确，只是个估值。

如果cardinality大大少于数据的实际散列程度，那么索引就基本失效了。

一般经验是，在访问表中很少一部分时使用B+树索引才会有意义。对应性别、地区、这些字段，它们可取值的范围很小，称为低选择性。如：

SELECT* FROM students WHERE sex='M';

这种查询的话，在大量样本的情况下一般能返回50% 的数据，这时添加B+树索引是完全没有必要的。

相反，如果某个字段的取值范围很广，几乎没有重复，即属于高选择性，则此时建议使用B+树索引。

例如，对应姓名字段，基本上在一个应用中不允许重名的出现。

在实际应用中，cardinality/n_rows_in_table应尽可能地接近1。如果非常小，那么用户需要考虑是否还有必要创建这个索引了。

故在访问高选择性属性的字段并从表中去除很少一部分数据时，对这个字段添加B+树索引是非常有必要的。

Cardinality 统计值的更新：

MySQL对Cardinality的统计是放在存储引擎层进行的。

在生产环境，索引的更新操作可能会非常的频繁。如果每次索引在发生操作时就对其进行Cardinality统计的话，会给数据库造成很大的负担。

假如一张表数据非常大，对其进行一次Cardinality统计可能要花费很长的时间，这对于生产环境而言，是不可接受的。

因此，数据库对于Cardinality的统计都是通过采样(Sample)的方法来完成的。

在InnoDB存储引擎中，Cardinality统计信息的更新发生在两个操作中：INSERT、UPDATE。但是我们不可能每次变动就去更新Cardinality，这样消耗太大了。

因此，InnoDB存储引擎内部对更新Cardinality信息的策略为：

1、表中1/16的数据已发生过变化。

2、 stat_modified_counter > 20 0000 0000 【20亿行】

第一种策略是自从上次统计Cardinality信息后，表中1/16的数据已经发生过变化，这时需要更新Cardinality信息。

第二种情况考虑的是，如果对表中某一行数据频繁地更新操作，这时表中的数据实际上并没有增加，实际发生变化的还是这一样数据，则第一种更新策略就无法适用这种情况。故在InnoDB存储引擎内部有一个计数器stat_modified_counter，用来表示发生变化的次数，当stat_modified_counter大于20 0000 0000时，则同样需要更新Cardinality信息。

InnoDB存储引擎内部是如何进行Cardinality信息的统计和更新操作的？

方法如下：

1、取得B+树所以中叶子节点的数量，记为A。

2、InnoDB存储引擎随机对8个叶子节点(leaf page)进行采样，统计每个页不同记录的个数，记为P1，P2 ... P8。

3、估算出的Cardinality值为：(P1+P2...+P8) * A/8

因为是随机取8个叶子节点做的统计，因此每次算出的Cardinality可能都不一样。如下我从脱敏的数据库摘录的例子：

        > use Mobile;
        > show index from wx_table\G
        *************************** 1. row ***************************
                Table: wx_table
           Non_unique: 0
             Key_name: PRIMARY
         Seq_in_index: 1
          Column_name: Id
            Collation: A
          Cardinality: 39129       #第一次计算的Cardinality结果。表示的是存储引擎估算表中有多少个不同的值
             Sub_part: NULL
               Packed: NULL
                 Null: 
           Index_type: BTREE
              Comment: 
        Index_comment: 
        1 row in set (0.00 sec)
        > analyze table wx_table;  #手工执行下，以便更新 Cardinality 值，不然要等到InnoDB的刷新要等好久。
        +--------------------+---------+----------+----------+
        | Table              | Op      | Msg_type | Msg_text |
        +--------------------+---------+----------+----------+
        |    Mobile.wx_table | analyze | status   | OK       |
        +--------------------+---------+----------+----------+
        1 row in set (0.01 sec)
        > show index from wx_table\G
        *************************** 1. row ***************************
                Table: wx_table
           Non_unique: 0
             Key_name: PRIMARY
         Seq_in_index: 1
          Column_name: Id
            Collation: A
          Cardinality: 41417    第二次计算的Cardinality结果
             Sub_part: NULL
               Packed: NULL
                 Null: 
           Index_type: BTREE
              Comment: 
        Index_comment: 
        1 row in set (0.00 sec)

如果我们再次执行 analyze table wx_table; 然后执行show index from wx_table\G 可以发现Cardinality的值又变了。

另外，如果用户不管怎么刷新，看到的Cardinality值都是不变的。一般这种情况发生在表足够小，表的叶子节点数小于或等于8。这样无论怎样随机采样，都会全部采样，算出的Cardinality总是一样的。

与之相关的参数有：

             >show VARIABLES like 'innodb_stats%';
            +--------------------------------------+-------------+
            | Variable_name                        | Value       |
            |--------------------------------------+-------------|
            | innodb_stats_auto_recalc             | ON          |
            | innodb_stats_method                  | nulls_equal |
            | innodb_stats_on_metadata             | OFF         |
            | innodb_stats_persistent              | ON          |
            | innodb_stats_persistent_sample_pages | 20          |
            | innodb_stats_sample_pages            | 8           |
            | innodb_stats_transient_sample_pages  | 8           |
            +--------------------------------------+-------------+

innodb_stats_sample_pages

设置采样的页的数量，默认是8

MySQL5.6.3开始已经废弃，改为使用innodb_stats_transient_sample_pages

innodb_stats_method

判断如何对待索引中出现的NULL值记录。

默认是nulls_equal，表示将NULL值记录视为相等的记录。

其有效值还有nulls_unequal和nulls_ignored。 nulls_unequal表示将NULL值记录视为不同的记录，nulls_ignored表示忽略NULL值记录。

举例子：

假如采样的8个页中索引记录值为 NULL、NULL、1、2、2、3、3、3。

如果设置innodb_stats_method=nulls_equal，则Cardinality为4; 【采样值：NULL、1、2、3】

如果设置innodb_stats_method=nulls_unequal，则Cardinality为5; 【采样值：NULL、NULL、1、2、3】

如果设置innodb_stats_method=nulls_ignored，则Cardinality为3; 【采样值：1、2、3】

注意：

当我们执行analyze table xxx; show table status; show index from tb_name; 以及访问information_schema架构下的表tables和statistics时会导致InnoDB存储引擎去重新计算索引的Cardinality值。

若表中存在多个辅助索引时，执行上述这些操作可能会非常慢，虽然用户可能并不希望去更新Cardinality值。

在innodb1.2后，对这些参数又做了下扩展及修改：

innodb_stats_persistent 默认ON

控制是否使用永久化的统计数据（也就是是否将analyze table计算出的Cardinality值存放到磁盘上)。

设置为ON的好处是可以减少重新计算每个索引的Cardinality值，例如当MySQL数据库重启时。

此外，用户也可通过命令create table和alter table的选项STATS_PERSISTENT来对每张表进行控制。

例如：

关闭某张表的采样统计：

CREATE TABLE `t2` (a int(11) NOT NULL AUTO_INCREMENT,b int(11) DEFAULT NULL, PRIMARY KEY (a), KEY idx_b (b)) ENGINE=InnoDB STATS_PERSISTENT=0;

手动设置采样的page数量：

CREATE TABLE `t3` (a int(11) NOT NULL AUTO_INCREMENT,b int(11) DEFAULT NULL, PRIMARY KEY (a), KEY idx_b (b)) ENGINE=InnoDB STATS_SAMPLE_PAGES=30;

永久化的统计数据存储在 mysql.innodb_index_stats 和 mysql.innodb_table_stats 中，官方文档说统计数据并非实时的，也就是收集统计数据会滞后几分钟，如果想要及时的更新统计可以执行analyze table。

innodb_stats_persistent_sample_pages 默认是20

只有在 innodb_stats_persistent=ON时候，innodb_stats_persistent_sample_pages 参数的设置才会生效。 【为了获得更准确的执行计划，我们可以设置my.cnf里面这个值稍微大些，如设置为32或者64】

官方文档：

innodb_stats_persistent_sample_pages only applies when innodb_stats_persistent is enabled for a table; when innodb_stats_persistent is disabled, innodb_stats_transient_sample_pages applies instead.

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_stats_on_metadata

该参数表示analyze table更新Cardinality值时每次需要采样的页的数量。默认是20个页。

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

innodb_stats_on_metadata 默认为OFF 【不建议设置为ON】

注意：这个参数只有在 innodb_stats_persistent=OFF 时候才会起作用。

【官方文档：https://dev.mysql.com/doc/refman/5.7/en/innodb-statistics-estimation.html

innodb_stats_on_metadata only applies when optimizer statistics are configured to be non-persistent (when innodb_stats_persistent is disabled).】

如果这个参数是ON的话，使用show table status、show index from tb_name、及访问information_schema架构下的表tables和statistics时，会立即导致存储引擎重新计算索引的Cardinality值，这个造成的影响在日常业务时间是不能接受。

innodb_stats_transient_sample_pages

表示每次采样页的数量。默认是8。该参数用来取代老版本里面之前版本的参数 innodb_stats_sample_pages

只有在某张表 STATS_PERSISTENT=0 时候， innodb_stats_transient_sample_pages 参数的设置才会生效。

官方文档：

innodb_stats_transient_sample_pages only applies when innodb_stats_persistent is disabled for a table; when innodb_stats_persistent is enabled, innodb_stats_persistent_sample_pages applies instead.

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_stats_on_metadata

innodb_stats_auto_recalc

用于决定是否在表上存在大量更新时（超过10%的记录更新）重新计算统计信息。

默认打开.

如果关闭该选项，就需要在每次创建索引或者更改列之后，运行一次ANALYZE TABLE命令来更新统计信息，否则可能选择错误的执行计划。

同样的，也可以在CREATE TABLE/ALTER TABLE命令中指定STATS_AUTO_RECALC值。

analyze table 说明：

老叶博客：https://mp.weixin.qq.com/s/1MsyxhtG6Zk3Q9gIV2QVbA

官网：https://dev.mysql.com/doc/refman/5.7/en/innodb-analyze-table-complexity.html

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 代价估算：

影响代价因素：

innodb_stats_persistent_sample_pages定义值大小；

表中索引数多少；

表中分区数多少。

analye table的代价 = 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数量

is the number of pages sampled (defined by innodb_stats_persistent_sample_pages)

n_cols_in_uniq_i

所有唯一索引（不含主键索引）中的列总数

is total number of all columns in all unique indexes (not counting the primary key columns)

n_cols_in_non_uniq_i

所有普通索引中的列总数

is the total number of all columns in all non-unique indexes

n_cols_in_pk

主键索引中的列总数（若未显式定义主键，则相当于只有一列的ROWID）

is the number of columns in the primary key (if a primary key is not defined, InnoDB creates a single column primary key internally)

n_non_uniq_i

非唯一索引数量

is the number of non-unique indexes in the table

n_part

表分区数量

is the number of partitions. If no partitions are defined, the table is considered to be a single partition.

mysql官网的实例：

                USE test;
                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)
                );
                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（c和d）

- n_cols_in_non_uniq_i，所有普通索引中的列总数，值为 4 （e、f、g、h）

- n_cols_in_pk，主键索引中的列总数（若未显式定义主键，则相当于只有一列的ROWID），值为 2 (a、b)

- n_non_uniq_i，非唯一索引数量，值为 2 （注意，这里指的是索引的数量，而不是不是列的数量。因此这里是i2nonuniq、i3nonuniq）

- n_part，表分区数量，值为 1（没有表分区，值为1，而不是0）。