内容参考:官方文档、老叶茶馆、姜承尧的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)。
那么根据公式最终需要扫描的data page数结果就是:
20 * (2 + 4 + 2 * (1 + 2)) * 1 = 240 (单位是pages)
实际需要读取的字节数则是:240 * 16*1024 = 3932160 (即 3.84M)
当然了,要读取的data page,有可能已经在buffer pool中了,因此并不全是物理读。
从中,我们也可以看到,这个代价和表的数据量并无直接关系。
不过,当表数量越大时,聚集索引的 B+ 树也越大,搜索代价肯定也越大。