MySQL 为每个表维护了一系列的统计信息，MySQL 底层的查询成本分析和查询优化都会用到这些数据。下面我们就来深入了解一下这些数据到底代表什么和 MySQL 是如何收集到这些数据的。

如何查看 MySQL 统计数据

表信息

MySQL 给我们提供了 SHOW TABLE STATUS 语句来查看表的统计信息，如果要看指定的某个表的统计信息，在该语句后加对应的 LIKE 语句就好了，比方说我们要查看 order_exp表的统计信息可以这么写：

SHOW TABLE STATUS LIKE ‘order_exp’\G

image.png

下面我们来解释一下比较常用的字段含义：

  • Name：表名
  • Engine：使用的存储引擎类型
  • Version：版本

  • Row_format：行格式，对于MyISAM引擎，这可能是Dynamic、Fixed 或 Compressed动态行的行长度可变，例如 Varchar 或 Blob 类型字段。固定行是指行长度不变，例如Char 和 Integer 类型字段。

  • Row：项表示表中的记录条数。对于使用 MyISAM存 储引擎的表来说，该值是准确的，对于使用 InnoDB 存储引擎的表来说，该值是一个估计值。从查询结果我们也可以看出来，由于我们的 order_exp 表是使用 InnoDB 存储引擎的，所以虽然实际上表中有 10567 条记录，但是 SHOW TABLE STATUS 显示的 Rows 值只有 10354 条记录。

  • Data_length：表示表占用的存储空间字节数。使用 MyISAM 存储引擎的表来说，该值就是数据文件的大小，对于使用 InnoDB 存储引擎的表来说，该值就相当于聚簇索引占用的存储空间大小，也就是说可以这样计算该值的大小：Data_length = 聚簇索引的页面数量 x 每个页面的大小。我们的 order_exp 使用默认16KB的页面大小，而上边查询结果显示 Data_length 的值是 1589248。

  • Avg_row_length：表示每行记录的平均大小值。

  • Max_data_length：是只针对 MyISAM 存储引擎，表示该表最大可以容纳多少字节

索引信息

像会为每个表维护一份统计数据一样，MySQL 也会为表中的每一个索引维护一份统计数
据，查看某个表中索引的统计数据可以使用SHOW INDEX FROM 表名的语法，比如我们查看一下 order_exp 的各个索引的统计数据可以这么写：

show index from order_exp

image.png

下面我们来逐一解释上面每个字段的意思：

  • Table：索引所在表名称
  • Non_unique：索引的列是否唯一，聚簇索引和唯一二级索引该列值为0，普通二级索引该列值为1
  • Key_name：索引的名称
  • Seq_in_index：索引列在索引中的位置，从1开始计数。比如对于联合索引 index（a，b，c）来说，a, b, ‘c’ 对应的位置分别是1、2、3。
  • Column_name：索引列的名称。
  • Collation：索引列中的值是按照何种排序方式存放的，值为A时代表升序存放，为 NULL 时代表降序存放。
  • Cardinality：索引列中不重复值的数量。后边我们会重点看这个属性的。
  • Sub_part：对于存储字符串或者字节串的列来说，有时候我们只想对这些串的前 n 个字符或字节建立索引，这个属性表示的就是那个 n 值。如果对完整的列建立索引的话， 该属性的值就是NULL。
  • Packed：索引列如何被压缩，NULL值表示未被压缩。这个属性我们暂时不了解，可以先忽略掉。
  • Null：该索引列是否允许存储NULL值。
  • Index_type ：使用索引的类型，我们最常见的就是BTREE，其实也就是B+树索引。
  • Comment：索引列注释信息。

  • Index_comment：索引注释信息。


    接下来我们详细说一下 Cardinality 属性，Cardinality 直译过来就是基数的意思，表示索引列中不重复值的个数。比如对于一个一万行记录的表来说，某个索引列的 Cardinality 属性是10000，那意味着该列中没有重复的值，如果 Cardinality 属性是1的话，就意味着该列的值全部是重复的。不过需要注意的是，对于 InnoDB 存储引擎来说，使用 SHOW INDEX 语句展示出来的某个索引列的 Cardinality 属性是一个估计值，并不是精确的。

统计数据的存储

我们前边学习查询成本的时候经常用到一些统计数据，比如通过 SHOW TABLE STATUS 可以看到关于表的统计数据，通过 SHOW INDEX 可以看到关于索引的统计数据，那么这些统计数据是怎么来的呢？它们是以什么方式收集的呢？

InnoDB 提供了两种存储统计数据的方式：

  • 永久性的统计数据，这种统计数据存储在磁盘上，也就是服务器重启之后这些统计数据还在。
  • 非永久性的统计数据，这种统计数据存储在内存中，当服务器关闭时这些这些统计数据就都被清除掉了，等到服务器重启之后，在某些适当的场景下才会重新收集这些统计数据。

MySQL 给我们提供了系统变量 innodb_stats_persistent 来控制到底采用哪种方式去存储统计数据。在 MySQL 5.6.6 之前，innodb_stats_persistent 的值默认是 OFF，也就是说InnoDB 的统计数据默认是存储到内存的，之后的版本中 innodb_stats_persistent 的值默
认是 ON，也就是统计数据默认被存储到磁盘中。

SHOW VARIABLES LIKE ‘innodb_stats_persistent’

image.png

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

CREATE TABLE 表名 (…) Engine=InnoDB, STATS_PERSISTENT = (1|0) ALTER TABLE 表名 Engine=InnoDB, STATS_PERSISTENT = (1|0)

  • 当 STATS_PERSISTENT=1 时，表明我们把该表的统计数据永久的存储到磁盘上
  • 当STATS_PERSISTENT=0 时，表明我们把该表的统计数据临时的存储到内存中

如果我们在创建表时未指定 STATS_PERSISTENT 属性，那默认采用系统变量
innodb_stats_persistent 的值作为该属性的值。

最近的 MySQL 版本都基本不用基于内存的非永久性统计数据了，所以重点来看基于磁盘的统计数据。当我们选择把某个表以及该表索引的统计数据存放到磁盘上时，实际上是把这些统计数据存储到了两个表里，通过一下命令可以看到：

SHOW TABLES FROM mysql LIKE ‘innodb%’

image.png
可以看到，这两个表都位于 mysql 系统数据库下边，其中：
innodb_table_stats 存储了关于表的统计数据，每一条记录对应着一个表的统计数据。
innodb_index_stats 存储了关于索引的统计数据，每一条记录对应着一个索引的一个统
计项的统计数据。

innodb_table_stats

我们通过以下命令可以看到 innodb_table_stats 该表的信息

desc mysql.innodb_table_stats

image.png

  • database_name：数据库名
  • table_name：表名
  • last_update：本条记录最后更新时间
  • n_rows：表中记录的条数
  • clustered_index_size：表的聚簇索引占用的页面数量
  • sum_of_other_index_sizes：表的其他索引占用的页面数量

下满我们直接看一下这个表里的内容：

SELECT * FROM mysql.innodb_table_stats

image.png
该表有几个重要的统计信息如下：
n_rows 的值是10350，表明 order_exp 表中大约有 10350 条记录，注意这个数据是估计值。clustered_index_size 的值是97，表明 order_exp 表的聚簇索引占用 97 个页面，这个值是也是一个估计值。sum_of_other_index_sizes 的值是81，表明 order_exp 表的其他索引一共占用81个页面，这个值是也是一个估计值。

n_rows统计项的收集

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

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

innodb_index_stats

我们通过以下命令可以看到 innodb_index_stats 该表的信息

desc mysql.innodb_index_stats

image.png

  • database_name：数据库名
  • table_name：表名
  • index_name：索引名
  • last_update：本条记录最后更新时间
  • stat_name：统计项的名称
  • stat_value：对应的统计项的值
  • sample_size：为生成统计数据而采样的页面数量
  • stat_description：对应的统计项的描述

innodb_index_stats 表的每条记录代表着一个索引的一个统计项。可能这会大家有些懵逼这个统计项到底指什么，别着急，我们直接看一下关于 order_exp 表的索引统计数据都有些什么：

SELECT * FROM mysql.innodb_index_stats WHERE table_name = ‘order_exp’

image.png

先查看 index_name 列，这个列说明该记录是哪个索引的统计信息，从结果中我们可以看出来，PRIMARY 索引（也就是主键）占了3条记录，idx_expire_time索引占了6条记录。
针对index_name列相同的记录，stat_name表示针对该索引的统计项名称，stat_value展
示的是该索引在该统计项上的值，stat_description指的是来描述该统计项的含义的。

  • n_leaf_pages：表示该索引的叶子节点占用多少页面。
  • size：表示该索引共占用多少页面。
  • n_diff_pfxXX：表示对应的索引列不重复的值有多少。

其中的 n_diff_pfxXX 中的 XX 长得有点儿怪呀，啥意思呢？其实 XX 可以被替换为 01、02、03… 这样的数字。比如对于 index(a、b、c) 来说：

  • n_diff_pfx01 表示的是统计 a 这一个列不重复的值有多少。
  • n_diff_pfx02 表示的是统计 a、b 这两个列组合起来不重复的值有多少。
  • n_diff_pfx03 表示的是统计 a、b、c 这三个列组合起来不重复的值有多少。

对于主键和唯一二级索引则没有这个问题，它们本身就可以保证索引列值的不重复，所以也不需要再统计一遍在索引列后加上主键值的不重复值有多少。对于普通的二级索引，并不能保证它的索引列值是唯一的，此时只有在索引列上加上主键值才可以区分两条索引列值都一样的二级索引记录。

在计算某些索引列中包含多少不重复值时，需要对一些叶子节点页面进行采样，sample_size 列就表明了采样的页面数量是多少。对于有多个列的联合索引来说，采样的页面数量是：

sample_size = innodb_stats_persistent_sample_pages × 索引列的个数

其中 innodb_stats_persistent_sample_pages 可以通过以下命令获取：

show variables like ‘innodb_stats_persistent_sample_pages ‘

image.png
当需要采样的页面数量大于该索引的叶子节点数量的话，就直接采用全表扫描来统计索
引列的不重复值数量了。所以大家可以在查询结果中看到不同索引对应的size列的值可
能是不同的。

定时更新统计数据

随着我们不断的对表进行增删改操作，表中的数据也一直在变化，innodb_table_stats 和innodb_index_stats 表里的统计数据也在变化。MySQL提供了如下两种更新统计数据的
方式：

开启 innodb_stats_auto_recalc

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

再一次强调，InnoDB 是以表为单位来收集和存储统计数据的，我们也可以单独为某个表设置是否自动重新计算统计数的属性，设置方式就是在创建或修改表的时候通过指定STATS_AUTO_RECALC 属性来指明该表的统计数据存储方式：

CREATE TABLE 表名 (…) Engine=InnoDB, STATS_AUTO_RECALC = (1|0) ALTER TABLE 表名 Engine=InnoDB, STATS_AUTO_RECALC = (1|0)

  • 当 STATS_AUTO_RECALC=1 时，表明我们想让该表自动重新计算统计数据，
  • 当 STATS_AUTO_RECALC=0 时，表明不想让该表自动重新计算统计数据。

如果我们在创建表时未指定STATS_AUTO_RECALC属性，那默认采用系统变量innodb_stats_auto_recalc 的值作为该属性的值。

手动调用 ANALYZE TABLE 语句更新

如果 innodb_stats_auto_recalc 系统变量的值为 OFF 的话，我们也可以手动调用ANALYZE TABLE 语句来重新计算统计数据，比如我们可以这样更新关于 order_exp 表的统计数据：

ANALYZE TABLE order_exp

image.png
ANALYZE TABLE 语句会立即重新计算统计数据，也就是这个过程是同步的，在表中索引多或者采样页面特别多时这个过程可能会特别慢最好在业务不是很繁忙的时候再运行。

手动更新 innodb_table_stats 和 innodb_index_stats 表

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

步骤一：更新innodb_table_stats表。
步骤二：让MySQL查询优化器重新加载我们更改过的数据。

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

FLUSH TABLE order_exp