mysql查看表的索引统计信息_MySQL InnoDB统计信息的收集与作用

最新推荐文章于 2022-09-19 14:35:45 发布

韩军安

最新推荐文章于 2022-09-19 14:35:45 发布

阅读量2.5k

点赞数

文章标签： mysql查看表的索引统计信息

本文链接：https://blog.csdn.net/weixin_34005394/article/details/113146628

版权

我们知道索引可以加快查询速度，那么是不是只要是查询语句需要，就建上索引？答案是否定的。因为索引虽然加快了查询速度，但索引也是有代价的：索引文件本身要消耗存储空间，同时索引会加重插入、删除和修改记录时的负担，另外，MySQL 在运行时也要消耗资源维护索引，因此索引并不是越多越好。一般两种情况下不建议建索引。

第一种情况是表记录比较少，例如一两千条甚至只有几百条记录的表，没必要建索引，让查询做全表扫描就好了。至于多少条记录才算多，这个个人有个人的看法，我个人的经验是以 2000 作为分界线，记录数不超过 2000 可以考虑不建索引，超过 2000 条可以酌情考虑索引。

另一种不建议建索引的情况是索引的选择性较低。所谓索引的选择性(Selectivity)，是指不重复的索引值(也叫基数，Cardinality)与表记录数(#T)的比值：Index Selectivity = Cardinality / #T

显然选择性的取值范围为 (0, 1)，选择性越高的索引价值越大，这是由 B+Tree 的性质决定的。

一、什么是 Cardinality(基数)？

索引列中不重复的值的数量叫做 Cardinality(基数)，比如性别列，该列只有男女之分，所以这一列基数是 2。主键列的基数等于表的总行数，基数的高低影响列的数据分布。比如下面查询 student 表中性别为 ‘M’ 的数据：

select * from student where sex='M'

select*fromstudentwheresex='M'

按性别进行查询时，可取值的范围一般只有 ‘M’，’F’。因此上述 SQL 语句得到的结果可能是该表 50% 的数据，这时添加 B+tree 索引是完全没有必要的，MySQL 也不会选择走索引，而是使用全表扫描。相反，如果某个字段的取值范围很广，几乎没有重复，则此时使用 B+tree 索引是最合适的。例如，对于姓名字段，基本上在一个应用中不允许重名的出现。

现在得到一个结论：如果某个列基数很低，该列数据分布就会非常不均衡，由于该列数据分布不均匀，会导致 SQL 查询可能走索引，也可能走全表扫描。在做 SQL 优化的时候，如果怀疑列数据分布不均匀，我们可以使用 select distinct(column) from table 来查看列的数据分布。也可以通过 SHOW INDEX 结果中的 Cardinality 列来观察，或者通过 information_schema.STATISTICS 表查看。

mysql> select * from information_schema.STATISTICS limit 1;

TABLE_CATALOG: def

TABLE_SCHEMA: member

TABLE_NAME: MemberLabel

NON_UNIQUE: 1

INDEX_SCHEMA: member

INDEX_NAME: idx_memberId

SEQ_IN_INDEX: 1

COLUMN_NAME: memberId

COLLATION: A

CARDINALITY: 9875162

SUB_PART: NULL

PACKED: NULL

NULLABLE:

INDEX_TYPE: BTREE

COMMENT:

INDEX_COMMENT:

mysql>select*frominformation_schema.STATISTICSlimit1;

TABLE_CATALOG:def

TABLE_SCHEMA:member

TABLE_NAME:MemberLabel

NON_UNIQUE:1

INDEX_SCHEMA:member

INDEX_NAME:idx_memberId

SEQ_IN_INDEX:1

COLUMN_NAME:memberId

COLLATION:A

CARDINALITY:9875162

SUB_PART:NULL

PACKED:NULL

NULLABLE:

INDEX_TYPE:BTREE

COMMENT:

INDEX_COMMENT:

这里的 Cardinality 就表示索引中不重复记录的数量。同时需要注意的是，Cardinality 是一个预估值，而不是一个准确值，是通过采样得到的，具体是如何采样的，下面再说。在实际应用中，Cardinality/n_rows_in_table 应尽可能地接近 1。如果非常小，那么用户需要考虑是否还有必要创建这个索引。故在访问高选择性属性的字段并从表中取出很少一部分数据时，对这个字段添加 B+tree 索引是非常有必要的。使用执行计划(explain)可以用来查看这个 SQL 语句需要扫描的行数。

上面说的是单列索引 Cardinality，如果是复合索引呢？其实有些不一样，比如复合索引(a,b)，当你 SHOW INDEX 时可以看到多个索引条目。如果是单列索引，那么 SEQ_IN_INDEX 值就是 1；如果是复合索引，那么 SEQ_IN_INDEX 值会递增。它们都各自有自己的 Cardinality，只是计算方式不同而已。简单来说就是，第一列的 Cardinality 代表的是当前列中不重复记录数量的预估值(就是 count(distinct a) )，第二列的 Cardinality 代表的是第一列加第二列合并后不重复记录数量的预估值(就是 count(distinct a, b) )，以此类推。

索引选择性

索引的选择性是指索引列唯一值的数目与表中记录数的比例，如果一个表中有 2000 条记录，表索引列有 1980 个不同的值，那么这个索引的选择性就是 “1980/2000=0.99”。一个索引的选择性越接近于 1，这个索引的效率就越高。如果是使用基于 Cost 的最优化，优化器不应该使用选择性不好的索引。如果是使用基于 Rule 的最优化，优化器在确定执行路径时不会考虑索引的选择性，当然除非是唯一性索引(唯一性索引属于高选择性)，并且不得不手工优化查询以避免使用非选择性的索引。

在进行 SQL 优化的时候，单独看列的基数是没有意义的，基数必须对比总行数才有实际意义，正是因为这个原因，我们才引出了选择性的概念。

什么样的列必须建立索引呢？

有人说基数高的列，有人说 where 条件中的列。这些答案并不完美，基数高究竟是多高？没有和总行数对比，始终不知道有多高。比如某个列的基数有几万行，但是总行数有几十亿行，那么这个列的基数还高吗？这就是要引出选择性的根本原因。

当一个列选择性大于 20% 时，说明该列的数据分布就比较均衡了。当然，如果这 20% 中有 95% 都是某两个值，那么就要注意了，基于该列的查询值刚好也是这两个值，那么就可能不会命中索引了。

总结一下就是，当一个列出现在 where 条件中，该列没有创建索引并且选择性大于 20%，那么该列就可以创建索引了，从而提升 SQL 查询性能。当然了，如果表只有几百条数据，就不用创建索引。

二、两种不同的统计数据存储方式

InnoDB 存储引擎提供了两种存储统计数据的方式：持久化(PERSISTENT)与非持久化统计数据(TRANSIENT)

持久化(PERSISTENT)统计数据，存储在 mysql.innodb_index_stats 和 mysql.innodb_table_stats 表中。这种统计数据存储在磁盘上，也就是服务器重启之后这些统计数据还在。

非持久化(TRANSIENT)统计数据，存储在 information_schema.statistics 和 information_schema.tables 中。这种统计数据存储在内存中，当服务器关闭时这些这些统计数据就都被清除掉了，等到服务器重启之后，在某些适当的场景下才会重新收集这些统计数据。

前者是 InnoDB 表后者是 Memory 表，它们受 innodb_stats_persistent 参数的控制。从 MySQL 5.6.6 开始，默认情况下，innodb_stats_persistent 为 ON，也就是统计数据默认被存储到磁盘中，基本是主流。

InnoDB 默认是以表为单位来收集和存储统计数据的，也就是说我们可以把某些表的统计数据(以及该表的索引统计数据)存储在磁盘上，把另一些表的统计数据存储在内存中。怎么做到的呢？我们可以在创建和修改表的时候通过指定 STATS_PERSISTENT 属性来指明该表的统计数据存储方式：

CREATE TABLE table (...) Engine=InnoDB, STATS_PERSISTENT = (1|0);

ALTER TABLE table Engine=InnoDB, STATS_PERSISTENT = (1|0);

CREATETABLEtable(...)Engine=InnoDB,STATS_PERSISTENT=(1|0);

ALTERTABLEtableEngine=InnoDB,STATS_PERSISTENT=(1|0);

当 STATS_PERSISTENT=1 时，表明我们想把该表的统计数据永久的存储到磁盘上，当 STATS_PERSISTENT=0 时，表明我们想把该表的统计数据临时的存储到内存中。如果我们在创建表时未指定 STATS_PERSISTENT 属性，那默认采用系统变量 innodb_stats_persistent 的值作为该属性的值。

随着我们不断的对表进行增删改操作，表中的数据也一直在变化，innodb_table_stats 和 innodb_index_stats 表里的统计数据是不是也应该跟着变一变了？当然要变了，不变的话 MySQL 查询优化器计算的成本可就差老远了。

三、持久化(PERSISTENT)统计数据

当我们选择把某个表以及该表索引的统计数据存放到磁盘上时，在 MySQL 中提供了两个表记录统计信息的相关内容，分别是 innodb_table_stats 与 innodb_index_stats，下面就这两个表进行介绍。

innodb_table_stats

存储了关于表的统计数据，每一条记录对应着一个表的统计数据。看一下表中的各个列都是干嘛的：

字段名

描述

database_name

数据库名

table_name

表名

last_update

本条记录最后更新时间

n_rows

表中记录的条数

clustered_index_size

表的聚簇索引占用的页面数量

sum_of_other_index_sizes

表的其他索引占用的页面数量

注意这个表的主键是(database_name, table_name)，也就是 innodb_table_stats 表的每条记录代表着一个表的统计信息。

n_rows 统计项的收集

InnoDB 统计一个表中有多少行(n_rows)记录的套路是这样的：按照一定算法(并不是纯粹随机的)选取几个叶子节点页面，计算每个页面中主键值记录数量，然后计算平均一个页面中主键值的记录数量乘以全部叶子节点的数量就算是该表的 n_rows 值。

可以看出来这个 n_rows 值精确与否取决于统计时采样的页面数量，前面介绍了 innodb_stats_persistent_sample_pages 参数就是控制采样页面数量的，该值设置的越大，统计出的 n_rows 值越精确，但是统计耗时也就最久；该值设置的越小，统计出的 n_rows 值越不精确，但是统计耗时特别少。所以在实际使用是需要我们去权衡利弊，该系统变量的默认值是 20。

我们前边说过，不过 InnoDB 默认是以表为单位来收集和存储统计数据的，我们也可以单独设置某个表的采样页面的数量，设置方式就是在创建或修改表的时候通过指定 STATS_SAMPLE_PAGES 属性来指明该表的统计数据存储方式：

CREATE TABLE table (...) Engine=InnoDB, STATS_SAMPLE_PAGES = 具体的采样页面数量;

ALTER TABLE table Engine=InnoDB, STATS_SAMPLE_PAGES = 具体的采样页面数量;

CREATETABLEtable(...)Engine=InnoDB,STATS_SAMPLE_PAGES=具体的采样页面数量;

ALTERTABLEtableEngine=InnoDB,STATS_SAMPLE_PAGES=具体的采样页面数量;

如果我们在创建表的语句中并没有指定 STATS_SAMPLE_PAGES 属性的话，将默认使用系统变量 innodb_stats_persistent_sample_pages 的值作为该属性的值。

clustered_index_size 和 sum_of_other_index_sizes 统计项的收集

统计这两个数据需要大量用到 InnoDB 表空间的知识。这两个统计项的收集过程如下：

从数据字典里找到表的各个索引对应的根页面位置，系统表 SYS_INDEXES 里存储了各个索引对应的根页面信息。

从根页面的 Page Header 里找到叶子节点段和非叶子节点段对应的 Segment Header。在每个索引的根页面的 Page Header 部分都有两个字段：

PAGE_BTR_SEG_LEAF：表示 B+树叶子段的 Segment Header 信息。

PAGE_BTR_SEG_TOP：表示 B+树非叶子段的 Segment Header 信息。

从叶子节点段和非叶子节点段的 Segment Header 中找到这两个段对应的 INODE Entry 结构。

这个是 Segment Header 结构：

从对应的 INODE Entry 结构中可以找到该段对应所有零散的页面地址以及 FREE、NOT_FULL、FULL 链表的基节点。

这个是 INODE Entry 结构：

直接统计零散的页面有多少个，然后从那三个链表的 List Length 字段中读出该段占用的区的大小，每个区占用 64 个页，所以就可以统计出整个段占用的页面。

这个是链表基节点的示意图：

分别计算聚簇索引的叶子结点段和非叶子节点段占用的页面数，它们的和就是 clustered_index_size 的值，按照同样的套路把其余索引占用的页面数都算出来，加起来之后就是 sum_of_other_index_sizes 的值。

这里需要大家注意一个问题，我们说一个段的数据在非常多时(超过 32 个页面)，会以区为单位来申请空间，这里头的问题是以区为单位申请空间中有一些页可能并没有使用，但是在统计 clustered_index_size 和 sum_of_other_index_sizes 时都把它们算进去了，所以说聚簇索引和其他的索引占用的页面数可能比这两个值要小一些。

innodb_index_stats

这个表里面输出的内容相对会比较复杂一些。先看一下这个表中的各个列都是干嘛的：

字段名

描述

database_name

数据库名

table_name

表名

index_name

索引名

last_update

本条记录最后更新时间

stat_name

统计项的名称

stat_value

对应的统计项的值

sample_size

为生成统计数据而采样的页面数量

stat_description

对应的统计项的描述

注意这个表的主键是(database_name,table_name,index_name,stat_name)，其中 stat_name 的是指统计项的名称，也就是说 innodb_index_stats 表的每条记录代表着一个索引的一个统计项。可能这会大家有些懵逼这个统计项到底指什么，别着急，我们直接看一下关于single_table表的索引统计数据都有些什么：

mysql> SELECT * FROM mysql.innodb_index_stats WHERE table_name = 'single_table';

+---------------+--------------+--------------+---------------------+--------------+------------+-------------+-----------------------------------+

+---------------+--------------+--------------+---------------------+--------------+------------+-------------+-----------------------------------+

| xiaohaizi | single_table | PRIMARY | 2018-12-14 14:24:46 | n_diff_pfx01 | 9693 | 20 | id |

| xiaohaizi | single_table | idx_key1 | 2018-12-14 14:24:46 | n_diff_pfx01 | 968 | 28 | key1 |

| xiaohaizi | single_table | idx_key2 | 2018-12-14 14:24:46 | n_diff_pfx01 | 10000 | 16 | key2 |

| xiaohaizi | single_table | idx_key3 | 2018-12-14 14:24:46 | n_diff_pfx01 | 799 | 31 | key3 |

+---------------+--------------+--------------+---------------------+--------------+------------+-------------+-----------------------------------+

20 rows in set (0.03 sec)

mysql>SELECT*FROMmysql.innodb_index_statsWHEREtable_name='single_table';

+---------------+--------------+--------------+---------------------+--------------+------------+-------------+-----------------------------------+

+---------------+--------------+--------------+---------------------+--------------+------------+-------------+-----------------------------------+

|xiaohaizi|single_table|PRIMARY|2018-12-1414:24:46|n_diff_pfx01|9693|20|id|

|xiaohaizi|single_table|idx_key1|2018-12-1414:24:46|n_diff_pfx01|968|28|key1|

|xiaohaizi|single_table|idx_key2|2018-12-1414:24:46|n_diff_pfx01|10000|16|key2|

|xiaohaizi|single_table|idx_key3|2018-12-1414:24:46|n_diff_pfx01|799|31|key3|

+---------------+--------------+--------------+---------------------+--------------+------------+-------------+-----------------------------------+

20rowsinset(0.03sec)

这个结果有点儿多，正确查看这个结果的方式是这样的：

先查看 index_name 列，这个列说明该记录是哪个索引的统计信息，从结果中我们可以看出来，PRIMARY 索引(也就是主键)占了 3 条记录，idx_key_part 索引占了 6 条记录。

针对 index_name 列相同的记录，stat_name 表示针对该索引的统计项名称，stat_value 展示的是该索引在该统计项上的值，stat_description 指的是来描述该统计项的含义的。我们来具体看一下一个索引都有哪些统计项：

size：表示该索引共占用多少页面。

n_leaf_pages：表示该索引的叶子节点占用多少页面。

n_diff_pfxNN：表示对应的索引列不重复的值有多少。其中的 NN 长得有点儿怪呀，啥意思呢？其实 NN 可以被替换为 01、02、03… 这样的数字。比如对于 idx_key_part 来说：

n_diff_pfx01 表示的是统计 key_part1 这单一一个列不重复的值有多少。

n_diff_pfx02 表示的是统计 key_part1、key_part2 这两个列组合起来不重复的值有多少。

n_diff_pfx03 表示的是统计 key_part1、key_part2、key_part3 这三个列组合起来不重复的值有多少。

n_diff_pfx04 表示的是统计 key_part1、key_part2、key_part3、id 这四个列组合起来不重复的值有多少。

在计算某些索引列中包含多少不重复值时，需要对一些叶子节点页面进行采样，sample_size 列就表明了采样的页面数量是多少。对于有多个列的联合索引来说，采样的页面数量是：innodb_stats_persistent_sample_pages × 索引列的个数。当需要采样的页面数量大于该索引的叶子节点数量的话，就直接采用全表扫描来统计索引列的不重复值数量了。所以大家可以在查询结果中看到不同索引对应的 size 列的值可能是不同的。

Note

这里需要注意的是，对于普通的二级索引，并不能保证它的索引列值是唯一的，比如对于 idx_key1 来说，key1 列就可能有很多值重复的记录。此时只有在索引列上加上主键值才可以区分两条索引列值都一样的二级索引记录。对于主键和唯一二级索引则没有这个问题，它们本身就可以保证索引列值的不重复，所以也不需要再统计一遍在索引列后加上主键值的不重复值有多少。比如上边的 idx_key1 有 n_diff_pfx01、n_diff_pfx02 两个统计项，而 idx_key2 却只有 n_diff_pfx01 一个统计项。

通过这个表我们可以查看索引选择性如何，并且可以看到组合索引中每一列选择性如何，还可以计算索引的大小：

select stat_value pages,

index_name,

(stat_value * @@innodb_page_size) / 1024 /1024 as size

from

mysql.innodb_index_stats

where

table_name = 't1' and

database_name = 'sbtest' and

stat_description = 'Number of pages in the index' and

stat_name = 'size'

group by

index_name;

selectstat_valuepages,

index_name,

(stat_value*@@innodb_page_size)/1024/1024assize

from

mysql.innodb_index_stats

where

table_name='t1'and

database_name='sbtest'and

stat_description='Number of pages in the index'and

stat_name='size'

groupby

index_name;

查询结果如下：

+-------+------------+------------+

| pages | index_name | size |

+-------+------------+------------+

| 1 | PRIMARY | 0.01562500 |

| 1 | i1 | 0.01562500 |

| 1 | i2uniq | 0.01562500 |

+-------+------------+------------+

3 rows in set (0.00 sec)

+-------+------------+------------+

|pages|index_name|size|

+-------+------------+------------+

|1|PRIMARY|0.01562500|

|1|i1|0.01562500|

|1|i2uniq|0.01562500|

+-------+------------+------------+

3rowsinset(0.00sec)

数据库中的一些系统表对 DBA 是非常重要的，可以帮助我们排查问题、性能分析、去更好的了解一些机制。

innodb_table_stats 与 innodb_index_stats 两张表我们可以了解统计信息、计算索引的大小、索引的选择性如何，也可以做到监控中。

通过 MySQL 5.7 中添加了 sys schema 也就是让大家不用通过去查看代码的方式去排查各种问题、故障处理等，可见对系统表的学习在日后会更重要。

定期更新统计数据

对于持久化(persistent)统计数据的更新策略：

系统变量 innodb_stats_auto_recalc 决定着服务器是否自动重新计算统计数据，它的默认值是 ON，也就是该功能默认是开启的。每个表都维护了一个变量，该变量记录着对该表进行增删改的记录条数，如果发生变动的记录数量超过了表大小的 10%，并且自动重新计算统计数据的功能是打开的，那么服务器会重新进行一次统计数据的计算，并且更新 innodb_table_stats 和 innodb_index_stats 表。不过自动重新计算统计数据的过程是异步发生的，也就是即使表中变动的记录数超过了 10%，自动重新计算统计数据也不会立即发生，可能会延迟几秒才会进行计算。

对于非持久化(transient)统计数据的更新策略：

InnoDB 检测到自上次更新统计信息以来表的 1/16 已被修改，通过 transient 方式更新统计信息。

一些说明，非持久化统计数据是直接更新的或者说是同步更新的，但是持久化统计数据应该是异步更新的，典型的生产者消费者模型。MySQL 中有一个专门的后台线程来负责收集统计数据如下：

mysql> select name,thread_id from performance_schema.threads where NAME like '%dict_stats_thread%' \G

*************************** 1. row ***************************

name: thread/innodb/dict_stats_thread

thread_id: 25

mysql>selectname,thread_idfromperformance_schema.threadswhereNAMElike'%dict_stats_thread%'\G

***************************1.row***************************

name:thread/innodb/dict_stats_thread

thread_id:25

实际上优化器统计数据基本逻辑都在 row/row0mysql.cc:row_update_statistics_if_needed 函数中，代码如下(不是整个逻辑链)：

/*********************************************************************//**

Updates the table modification counter and calculates new estimates

for table and index statistics if necessary. */

UNIV_INLINE

void

row_update_statistics_if_needed(

/*============================*/

dict_table_t*table)/*!< in: table */

{

ib_uint64_tcounter;

ib_uint64_tn_rows;

if (!table->stat_initialized) {

DBUG_EXECUTE_IF(

"test_upd_stats_if_needed_not_inited",

fprintf(stderr, "test_upd_stats_if_needed_not_inited"

" was executed\n");

);

return;

}

counter = table->stat_modified_counter++;

n_rows = dict_table_get_n_rows(table);

# 持久化逻辑(PERSISTENT)

# 表数据更改超过1/10

if (dict_stats_is_persistent_enabled(table)) {

if (counter > n_rows / 10 /* 10% */

&& dict_stats_auto_recalc_is_enabled(table)) {

dict_stats_recalc_pool_add(table);

table->stat_modified_counter = 0;

}

return;

}

# 非持久化逻辑(TRANSIENT)

# 表数据更改超过1/16

/* Calculate new statistics if 1 / 16 of table has been modified

since the last time a statistics batch was run.

We calculate statistics at most every 16th round, since we may have

a counter table which is very small and updated very often. */

if (counter > 16 + n_rows / 16 /* 6.25% */) {

ut_ad(!mutex_own(&dict_sys->mutex));

/* this will reset table->stat_modified_counter to 0 */

dict_stats_update(table, DICT_STATS_RECALC_TRANSIENT);

}

/*********************************************************************//**

Updatesthetablemodificationcounterandcalculatesnewestimates

fortableandindexstatisticsifnecessary.*/

UNIV_INLINE

void

row_update_statistics_if_needed(

/*============================*/

dict_table_t*table)/*!

{

ib_uint64_tcounter;

ib_uint64_tn_rows;

if(!table->stat_initialized){

DBUG_EXECUTE_IF(

"test_upd_stats_if_needed_not_inited",

fprintf(stderr,"test_upd_stats_if_needed_not_inited"

" was executed\n");

);

return;

}

counter=table->stat_modified_counter++;

n_rows=dict_table_get_n_rows(table);

# 持久化逻辑(PERSISTENT)

# 表数据更改超过1/10

if(dict_stats_is_persistent_enabled(table)){

if(counter>n_rows/10/*10%*/

&&dict_stats_auto_recalc_is_enabled(table)){

dict_stats_recalc_pool_add(table);

table->stat_modified_counter=0;

}

return;

}

# 非持久化逻辑(TRANSIENT)

# 表数据更改超过1/16

/*Calculatenewstatisticsif1/16oftablehasbeenmodified

sincethelasttimeastatisticsbatchwasrun.

Wecalculatestatisticsatmostevery16thround,sincewemayhave

acountertablewhichisverysmallandupdatedveryoften.*/

if(counter>16+n_rows/16/*6.25%*/){

ut_ad(!mutex_own(&dict_sys->mutex));

/*thiswillresettable->stat_modified_counterto0*/

dict_stats_update(table,DICT_STATS_RECALC_TRANSIENT);

}

除了自动更新统计信息之外，也可以手动调用 ANALYZE TABLE 语句来更新统计信息。需要注意的是，ANALYZE TABLE 语句会立即重新计算统计数据，也就是这个过程是同步的，在表中索引多或者采样页面特别多时这个过程可能会特别慢，请不要没事儿就运行一下 ANALYZE TABLE 语句，最好在业务不是很繁忙的时候再运行。

手动更新 innodb_table_stats 和 innodb_index_stats 表

其实 innodb_table_stats 和 innodb_index_stats 表就相当于一个普通的表一样，我们能对它们做增删改查操作。这也就意味着我们可以手动更新某个表或者索引的统计数据。比如说我们想把 single_table 表关于行数的统计数据更改一下可以这么做：

UPDATE innodb_table_stats

SET n_rows = 1

WHERE table_name = 'single_table';

UPDATEinnodb_table_stats

SETn_rows=1

WHEREtable_name='single_table';

更新完 innodb_table_stats 只是单纯的修改了一个表的数据，需要让 MySQL 查询优化器重新加载我们更改过的数据，运行下边的命令就可以了：

FLUSH TABLE single_table;

FLUSHTABLEsingle_table;

之后我们使用 SHOW TABLE STATUS 语句查看表的统计数据时就看到 Rows 行变为了 1。

四、非持久化(TRANSIENT)统计数据

当我们把系统变量 innodb_stats_persistent 的值设置为 OFF 时，之后创建的表的统计数据默认就都是非永久性的了，或者我们直接在创建表或修改表时设置 STATS_PERSISTENT 属性的值为 0，那么该表的统计数据就是非永久性的了。

innodb_stats_on_metadata

MySQL 5.6.6 默认启用了持久化优化器统计信息后，默认设置 innodb_stats_on_metadata 为 OFF。参数的意义是当通过命令 SHOW TABLE STATUS、SHOW INDEX 及访问 INFORMATION_SCHEMA 架构下的表 TABLES 和 STATISTICS 时，是否需要重新计算统计信息？默认值：OFF，InnoDB 1.2 版本提供。当 innodb_stats_on_metadata 设置为 ON 的时候，就需要重新计算；但仅在统计信息配置为非持久化的时候生效，也就是说在 innodb_stats_persistent 配置为 OFF 时，innodb_stats_on_metadata 的设置才生效。

innodb_stats_sample_pages

在 InnoDB 1.2 版本之前，用来设置统计时每次采样的数量，默认值：8，在 InnoDB 1.2 版本时被 innodb_stats_transient_sample_pages 参数取代。

innodb_stats_transient_sample_pages

该参数用来取代之前版本的参数 innodb_stats_sample_pages，表示每次采样页的数量。默认值：8，InnoDB 1.2 版本提供。

以非持久化(transient)为例，接着考虑 InnoDB 存储引擎内部是怎样来进行 Cardinality 信息的统计和更新操作的？同样是通过采样的方法，默认 InnoDB 存储引擎对 8 个叶子节点(Leaf Page)进行采样，采样的大概过程如下：

取得 B+tree 索引中叶子节点的数量，记为 A。

随机取得 B+tree 索引中 8 个叶子节点，统计每个页不同记录的个数，即为 P1，P2，…，P8。

根据采样信息给出 Cardinality 的预估值：Cardinality = (P1+P2+…+P8) * A/8。

通过上述的说明可以发现，在 InnoDB 存储引擎中，Cardinality 值是通过对 8 个叶子节点预估而得到的，不是一个精确的值。再者，每次对 Cardinality值的统计，都是通过随机取 8 个叶子节点得到的，这同时又暗示了另一个 Cardinality 对象，即每次得到的 Cardinality 值可能是不同的。如：SHOW INDEX FROM OrderDetails 语句会触发 MySQL 数据库对于 Cardinality 值的统计，所以可能会出现此表没有任何数据变化，但是你多次执行 SHOW INDEX FROM OrderDetails 看到的 Cardinality 值不同。因为每次执行都会触发 MySQL 数据对于 Cardinality 值的统计，随机选取 8 个叶子节点进行分析。所以如果遇到这个并不是 InnoDB 存储因为的 Bug，只是随机采样而导致的。

当然，有一种情况使得用户每次观察到的索引 Cardinality 值都是一样的，那就是表足够小，表的叶子节点数小于或者等于 8 个。这时即使随机采样，也总是会采取到这些页，因此每次得到的 Cardinality 值是相同的。

当执行 SQL 语句 ANALYZE TABLE、SHOW TABLE STATUS、SHOW INDEX 以及访问 INFORMATION_SCHEMA 架构下的表 TABLES和STATISTICS 时会导致 InnoDB 存储引擎去重新计算索引的 Cardinality 值。若表中的数据量非常大，并且表中存在多个辅助索引时，执行上述这些操作可能会非常慢。虽然用户可能并不希望去更新 Cardinality 值。当我们 show index from table 时，实际上就是访问 information_schema.statistics 表。

五、innodb_stats_method 参数的使用

我们知道索引列不重复的值的数量(Cardinality)这个统计数据对于 MySQL 查询优化器十分重要，因为通过它可以计算出在索引列中平均一个值重复多少行，它的应用场景主要有两个：

1. 单表查询中单点区间太多，比方说这样：

SELECT * FROM tbl_name WHERE key IN ('xx1', 'xx2', ..., 'xxn');

SELECT*FROMtbl_nameWHEREkeyIN('xx1','xx2',...,'xxn');

当 IN 里的参数数量过多时，采用 index dive 的方式直接访问 B+tree 索引去同步统计每个单点区间对应的记录的数量就太耗费性能了，所以直接依赖统计数据中的平均一个值重复多少行来计算单点区间对应的记录数量。

2. 连接查询时，如果有涉及两个表的等值匹配连接条件，该连接条件对应的被驱动表中的列又拥有索引时，则可以使用 ref 访问方法来对被驱动表进行查询，比方说这样：

SELECT * FROM t1 JOIN t2 ON t1.column = t2.key WHERE ...;

SELECT*FROMt1JOINt2ONt1.column=t2.keyWHERE...;

在真正执行对 t2 表的查询前，t1.comumn 的值是不确定的，所以我们也不能通过 index dive 的方式直接访问 B+tree 索引去同步统计每个单点区间对应的记录的数量，所以也只能依赖统计数据中的平均一个值重复多少行来计算单点区间对应的记录数量。

在统计索引列不重复的值的数量时，有一个比较烦的问题就是索引列中出现 NULL 值怎么办，比方说某个索引列的内容是这样：

+------+

| col |

+------+

| 1 |

| 2 |

| NULL |

+------+

|col|

+------+

|1|

|2|

|NULL|

+------+

此时计算这个 col 列中不重复的值的数量就有下边的分歧：

1. 有的人认为 NULL 值代表一个未确定的值，所以 MySQL 开发者才认为任何和 NULL 值做比较的表达式的值都为 NULL，就是这样：

mysql> SELECT 1 = NULL;

+----------+

| 1 = NULL |

+----------+

| NULL |

+----------+

1 row in set (0.00 sec)

mysql> SELECT 1 != NULL;

+-----------+

| 1 != NULL |

+-----------+

| NULL |

+-----------+

1 row in set (0.00 sec)

mysql> SELECT NULL = NULL;

+-------------+

| NULL = NULL |

+-------------+

| NULL |

+-------------+

1 row in set (0.00 sec)

mysql> SELECT NULL != NULL;

+--------------+

| NULL != NULL |

+--------------+

| NULL |

+--------------+

1 row in set (0.00 sec)

mysql>SELECT1=NULL;

+----------+

|1=NULL|

+----------+

|NULL|

+----------+

1rowinset(0.00sec)

mysql>SELECT1!=NULL;

+-----------+

|1!=NULL|

+-----------+

|NULL|

+-----------+

1rowinset(0.00sec)

mysql>SELECTNULL=NULL;

+-------------+

|NULL=NULL|

+-------------+

|NULL|

+-------------+

1rowinset(0.00sec)

mysql>SELECTNULL!=NULL;

+--------------+

|NULL!=NULL|

+--------------+

|NULL|

+--------------+

1rowinset(0.00sec)

所以每一个 NULL 值都是独一无二的，也就是说统计索引列不重复的值的数量时，应该把 NULL 值当作一个独立的值，所以 col 列的不重复的值的数量就是：4(分别是 1、2、NULL、NULL 这四个值)。

2. 有的人认为其实 NULL 值在业务上就是代表没有，所有的 NULL 值代表的意义是一样的，所以 col 列不重复的值的数量就是：3(分别是 1、2、NULL 这三个值)。

3. 有的人认为这 NULL 完全没有意义嘛，所以在统计索引列不重复的值的数量时压根儿不能把它们算进来，所以 col 列不重复的值的数量就是：2(分别是 1、2 这两个值)。

基于此，MySQL 提供了一个名为 innodb_stats_method 的系统变量，相当于在计算某个索引列不重复值的数量时如何对待 NULL 值这个锅甩给了用户自己选择了。这个系统变量有三个候选值：

nulls_equal：认为所有 NULL 值都是相等的。这个值也是 innodb_stats_method 的默认值。如果某个索引列中 NULL 值特别多的话，这种统计方式会让优化器认为某个列中平均一个值重复次数特别多，所以倾向于不使用索引进行访问。

nulls_unequal：认为所有 NULL 值都是不相等的。如果某个索引列中 NULL 值特别多的话，这种统计方式会让优化器认为某个列中平均一个值重复次数特别少，所以倾向于使用索引进行访问。

nulls_ignored：直接把 NULL 值忽略掉。

例如，某索引记录为 (NULL、NULL、1、2、2、3、3、3)，若值为 nulls_equal，该索引的 Cardinality 为 4；若值为 nulls_unequal，则该索引的 Cardinality 为 5；若值为 nulls_ignored，则该索引的 Cardinality 为 3。默认值是相对比较友好的，但对于用户的我们来说，最好不在索引列中存放 NULL 值才是正解，避免优化器选择了不是最优的执行计划。

NOTE

在代码中比较两条记录是否相同的函数是 cmp_rec_rec_with_match。从代码调用来看，在计算基于磁盘的统计数据时，cmp_rec_rec_with_match 函数的 nulls_unequal 参数是硬编码为 FALSE 的。也就是说，在计算基于内存的统计数据时，改变系统变量 innodb_stats_method 的值是起作用的，但是在计算基于磁盘的统计数据时，改变该系统变量的值是无效的。你可以试试看，目前还不知道这么做的原因。

六、总结

InnoDB 以表为单位来收集统计数据，这些统计数据可以是基于磁盘的永久性统计数据，也可以是基于内存的非永久性统计数据。

innodb_stats_persistent

控制着使用永久性统计数据还是非永久性统计数据。

innodb_stats_auto_recalc

默认为 ON，是否自动触发更新统计信息，开启与否只会影响 persistent 类型的统计，也就是仅影响持久化存储统计信息的表，而阈值是变化的数据超过表行数的 10%。也就是说，当一个表索引统计信息是持久化存储的，并且表中数据变化了超过 10%，如果 innodb_stats_auto_recalc 为 ON，就会自动更新统计信息，否则不更。

innodb_stats_persistent_sample_pages

若参数 innodb_stats_persistent 设置为 ON，该参数就是用来设置持久化更新统计信息时候索引页的取样页数。默认值 20，InnoDB 1.2 版本提供。如果设置的过高，那么在更新统计信息的时候，会增加 ANALYZE TABLE 的执行时间。

innodb_stats_include_delete_marked

MySQL 5.7.16 中引入。默认情况下，InnoDB 在计算统计信息时读取未提交的数据，如果未提交的事务从表中删除行，则 InnoDB 排除在计算行估计和索引统计信息时删除标记的记录，这可能会导致对正在该表上操作的其他事务使用非最佳执行计划。为了避免这种情况，innodb_stats_include_delete_marked 可以在计算持久化优化器统计信息时确保包括删除标记的记录。当 innodb_stats_include_delete_marked 启用时，ANALYZE TABLE 重新计算统计数据时，会考虑删除标记的记录。innodb_stats_include_delete_marked 是影响所有 InnoDB 表的全局设置，仅适用于持久性优化器统计信息。

innodb_stats_method

决定着在统计某个索引列不重复值的数量时如何对待 NULL 值。

我们可以针对某个具体的表，在创建和修改表时通过指定 STATS_PERSISTENT、STATS_AUTO_RECALC、STATS_SAMPLE_PAGES 的值来控制相关统计数据属性。

七、索引选择性实践

为了讨论索引策略，需要一个数据量不算小的数据库作为示例。本文选用MySQL官方文档中提供的示例数据库之一：employees。这个数据库关系复杂度适中，且数据量较大。下图是这个数据库的E-R关系图(引用自 MySQL 官方手册)：

MySQL 官方文档中关于此数据库的页面为https://dev.mysql.com/doc/employee/en。里面详细介绍了此数据库，并提供了下载地址和导入方法，如果有兴趣导入此数据库到自己的 MySQL 可以参考文中内容。

显然选择性的取值范围为 (0, 1]，其公式 Index Selectivity = Cardinality / #T，选择性越高的索引价值越大，这是由 B+Tree 的性质决定的。例如，如测试数据库中用到的 employees.titles 表，如果 title 字段经常被单独查询，是否需要建索引，我们看一下它的选择性：

mysql> SELECT count(DISTINCT(title))/count(*) AS Selectivity FROM employees.titles;

+-------------+

| Selectivity |

+-------------+

| 0.0000 |

+-------------+

1 row in set (0.29 sec)

mysql>SELECTcount(DISTINCT(title))/count(*)ASSelectivityFROMemployees.titles;

+-------------+

|Selectivity|

+-------------+

|0.0000|

+-------------+

1rowinset(0.29sec)

title 的选择性不足 0.0001(精确值为 0.00001579)，所以实在没有什么必要为其单独建索引。

有一种与索引选择性有关的索引优化策略叫做前缀索引，就是用列的前缀代替整个列作为索引 key，当前缀长度合适时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引 key 变短而减少了索引文件的大小和维护开销。下面以 employees.employees 表为例介绍前缀索引的选择和使用。

从上图可以看到 employees 表只有一个索引，那么如果我们想按名字搜索一个人，就只能全表扫描了：

mysql> EXPLAIN SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido';

+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

1 row in set (0.00 sec)

mysql>EXPLAINSELECT*FROMemployees.employeesWHEREfirst_name='Eric'ANDlast_name='Anido';

+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

1rowinset(0.00sec)

如果频繁按名字搜索员工，这样显然效率很低，因此我们可以考虑建索引。有两种选择，建或，看下两个索引的选择性：

mysql> SELECT count(DISTINCT(first_name))/count(*) AS Selectivity FROM employees.employees;

+-------------+

| Selectivity |

+-------------+

| 0.0042 |

+-------------+

1 row in set (0.30 sec)

mysql> SELECT count(DISTINCT(concat(first_name, last_name)))/count(*) AS Selectivity FROM employees.employees;

+-------------+

| Selectivity |

+-------------+

| 0.9313 |

+-------------+

1 row in set (0.87 sec)

mysql>SELECTcount(DISTINCT(first_name))/count(*)ASSelectivityFROMemployees.employees;

+-------------+

|Selectivity|

+-------------+

|0.0042|

+-------------+

1rowinset(0.30sec)

mysql>SELECTcount(DISTINCT(concat(first_name,last_name)))/count(*)ASSelectivityFROMemployees.employees;

+-------------+

|Selectivity|

+-------------+

|0.9313|

+-------------+

1rowinset(0.87sec)

显然选择性太低，选择性很好，但是 first_name 和 last_name 加起来长度为 30，有没有兼顾长度和选择性的办法(对于 VARCHAR 索引最长可以有768字节)？可以考虑用 first_name 和 last_name 的前几个字符建立索引，例如，看看其选择性：

mysql> SELECT count(DISTINCT(concat(first_name, left(last_name, 3))))/count(*) AS Selectivity FROM employees.employees;

+-------------+

| Selectivity |

+-------------+

| 0.7879 |

+-------------+

1 row in set (0.69 sec)

mysql>SELECTcount(DISTINCT(concat(first_name,left(last_name,3))))/count(*)ASSelectivityFROMemployees.employees;

+-------------+

|Selectivity|

+-------------+

|0.7879|

+-------------+

1rowinset(0.69sec)

选择性还不错，但离 0.9313 还是有点距离，那么把 last_name 前缀加到 4：

mysql> SELECT count(DISTINCT(concat(first_name, left(last_name, 4))))/count(*) AS Selectivity FROM employees.employees;

+-------------+

| Selectivity |

+-------------+

| 0.9007 |

+-------------+

1 row in set (0.75 sec)

mysql>SELECTcount(DISTINCT(concat(first_name,left(last_name,4))))/count(*)ASSelectivityFROMemployees.employees;

+-------------+

|Selectivity|

+-------------+

|0.9007|

+-------------+

1rowinset(0.75sec)

这时选择性已经很理想了，而这个索引的长度只有 18，比短了接近一半，这时我们把这个前缀索引建上。

先开启 profile 再执行一遍按名字查询，比较分析一下与建索引前的结果：

mysql> set profiling=1;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SELECT SQL_NO_CACHE * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido';

+--------+------------+------------+-----------+--------+------------+

+--------+------------+------------+-----------+--------+------------+

| 18454 | 1955-02-28 | Eric | Anido | M | 1988-07-18 |

+--------+------------+------------+-----------+--------+------------+

1 row in set (0.17 sec)

mysql> ALTER TABLE employees.employees ADD INDEX `first_name_last_name4` (first_name, last_name(4));

Query OK, 0 rows affected (1.23 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> SELECT SQL_NO_CACHE * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido';

+--------+------------+------------+-----------+--------+------------+

+--------+------------+------------+-----------+--------+------------+

| 18454 | 1955-02-28 | Eric | Anido | M | 1988-07-18 |

+--------+------------+------------+-----------+--------+------------+

1 row in set (0.00 sec)

mysql> set profiling=0;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW PROFILES;

+----------+------------+----------------------------------------------------------------------------------------------+

| Query_ID | Duration | Query |

+----------+------------+----------------------------------------------------------------------------------------------+

| 1 | 0.17215550 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' |

| 2 | 1.20597425 | ALTER TABLE employees.employees ADD INDEX `first_name_last_name4` (first_name, last_name(4)) |

| 3 | 0.00076825 | SELECT SQL_NO_CACHE * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' |

+----------+------------+----------------------------------------------------------------------------------------------+

7 rows in set, 1 warning (0.00 sec)

mysql>setprofiling=1;

QueryOK,0rowsaffected,1warning(0.00sec)

mysql>SELECTSQL_NO_CACHE*FROMemployees.employeesWHEREfirst_name='Eric'ANDlast_name='Anido';

+--------+------------+------------+-----------+--------+------------+

+--------+------------+------------+-----------+--------+------------+

|18454|1955-02-28|Eric|Anido|M|1988-07-18|

+--------+------------+------------+-----------+--------+------------+

1rowinset(0.17sec)

mysql>ALTERTABLEemployees.employeesADDINDEX`first_name_last_name4`(first_name,last_name(4));

QueryOK,0rowsaffected(1.23sec)

Records:0Duplicates:0Warnings:0

mysql>SELECTSQL_NO_CACHE*FROMemployees.employeesWHEREfirst_name='Eric'ANDlast_name='Anido';

+--------+------------+------------+-----------+--------+------------+

+--------+------------+------------+-----------+--------+------------+

|18454|1955-02-28|Eric|Anido|M|1988-07-18|

+--------+------------+------------+-----------+--------+------------+

1rowinset(0.00sec)

mysql>setprofiling=0;

QueryOK,0rowsaffected,1warning(0.00sec)

mysql>SHOWPROFILES;

+----------+------------+----------------------------------------------------------------------------------------------+

|Query_ID|Duration|Query|

+----------+------------+----------------------------------------------------------------------------------------------+

|1|0.17215550|SELECT*FROMemployees.employeesWHEREfirst_name='Eric'ANDlast_name='Anido'|

|2|1.20597425|ALTERTABLEemployees.employeesADDINDEX`first_name_last_name4`(first_name,last_name(4))|

|3|0.00076825|SELECTSQL_NO_CACHE*FROMemployees.employeesWHEREfirst_name='Eric'ANDlast_name='Anido'|

+----------+------------+----------------------------------------------------------------------------------------------+

7rowsinset,1warning(0.00sec)

性能的提升是显著的，查询速度提高了 200 多倍。

前缀索引兼顾索引大小和查询速度，但是其缺点是不能用于 ORDER BY 和 GROUP BY 操作，也不能用于 Covering index(即当索引本身包含查询所需全部数据时，不再访问数据文件本身)。

如果您觉得本站对你有帮助，那么可以支付宝扫码捐助以帮助本站更好地发展，在此谢过。

韩军安

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
mysql查看表的索引统计信息_MySQL InnoDB统计信息的收集与作用

我们知道索引可以加快查询速度，那么是不是只要是查询语句需要，就建上索引？答案是否定的。因为索引虽然加快了查询速度，但索引也是有代价的：索引文件本身要消耗存储空间，同时索引会加重插入、删除和修改记录时的负担，另外，MySQL 在运行时也要消耗资源维护索引，因此索引并不是越多越好。一般两种情况下不建议建索引。第一种情况是表记录比较少，例如一两千条甚至只有几百条记录的表，没必要建索引，让查询做全表扫描就...
复制链接

扫一扫

mysql查看表的索引统计信息_MySQL InnoDB统计信息的收集与作用

“相关推荐”对你有帮助么？