姜承尧 mysql 8_show index from 及analyze table 详解

内容参考：官方文档、老叶茶馆、姜承尧的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 |     |   |        | BTREE        |           |                 |

| t1      |            1 | idx_name     |              1 | name          | A           |             5 |     |   |        | BTREE        |           |                 |

| t1      |            1 | idx_name_age |              1 | name          | A           |             5 |     |   |        | BTREE        |           |                 |

| t1      |            1 | idx_name_age |              2 | age           | A           |             5 |     |   | YES    | BTREE        |           |                 |

| t1      |            1 | idx_name_sub |              1 | name          | A           |             5 |          5 |   |        | 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)。

那么根据公式最终需要扫描的data page数结果就是：

20 * (2 + 4 + 2 * (1 + 2)) * 1 = 240  (单位是pages)

实际需要读取的字节数则是：240 * 16*1024 = 3932160 (即 3.84M)

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

从中，我们也可以看到，这个代价和表的数据量并无直接关系。

不过，当表数量越大时，聚集索引的 B+ 树也越大，搜索代价肯定也越大。