MySQL中的统计数据

基于索引统计数据的成本计算

有时候使用索引执行查询时会有许多单点区间，例如使用in语句就很容易产生非常多的单点区间，比如下边这个查询（下边查询语句中的…表示还有很多参数）：

 select * from t_emp where hire_date in ('1890-11-10','1990-12-10'...'1991-11-12');

很显然，这个查询可能使用到的索引就是idx_hire_date，由于这个索引并不是唯一的二级索引，所以并不能确定一个单点区间对应的二级索引记录的条数有多少，需要我们去计算。就是先获取索引对应的B+树的区间最左记录和区间最右记录，然后再计算这两条记录之间有多少记录（记录条数少的时候可以做到精确计算，多的时候只能估算）。MySQL把这种通过直接访问索引对应的B+树来计算某个范围区间对应的索引记录条数的方式称之为index dive。

只有少数几个单点区间的话，使用index dive的方式去计算这些单点区间对应的记录数也不是什么问题，如果in语句里20000个参数怎么办？这就意味着MySQL的查询优化器为了计算这些单点区间对应的索引记录条数，要进行20000次index dive操作，这性能损耗就很大，搞不好计算这些单点区间对应的索引记录条数的成本比直接全表扫描的成本都大了。MySQL考虑到了这种情况，所以提供了一个系统变量eq_range_index_dive_limit，我们看一下在MySQL5.7.35中这个系统变量的默认值：

mysql> show variables like '%dive%';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| eq_range_index_dive_limit | 200   |
+---------------------------+-------+
1 row in set (0.02 sec)

也就是说如果我们的in语句中的参数个数小于200个的话，将使用index dive的方式计算各个单点区间对应的记录条数，如果大于或等于200个的话，可就不能使用index dive了，要使用所谓的索引统计数据来进行估算，也叫index statistics。

怎么个估算法？像会为每个表维护一份统计数据一样，MySQL也会为表中的每一个索引维护一份统计数据，比如我们查看一下t_emp的各个索引的统计数据可以这么写：

mysql> show index from t_emp;
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t_emp |          0 | PRIMARY        |            1 | emp_no      | A         |      299645 |     NULL | NULL   |      | BTREE      |         |               |
| t_emp |          1 | idx_hire_date  |            1 | hire_date   | A         |        5590 |     NULL | NULL   |      | BTREE      |         |               |
| t_emp |          1 | idx_birth_date |            1 | birth_date  | A         |        4770 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

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

前边说道，当in语句中的参数个数大于或等于系统变量eq_range_index_dive_limit的值的话，就不会使用index dive的方式计算各个单点区间对应的索引记录条数，而是使用索引统计数据，这里所指的索引统计数据指的是这两个值：

• 使用show table status展示出的Rows值，也就是一个表中有多少条记录。

• 使用show index语句展示出的Cardinality属性。

mysql> show table status like 't_emp';
+-------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| Name  | Engine | Version | Row_format | Rows   | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation         | Checksum | Create_options | Comment |
+-------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| t_emp | InnoDB |      10 | Dynamic    | 299645 |             50 |    15220736 |               0 |      9469952 |   2097152 |           NULL | 2021-08-19 07:19:44 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |         |
+-------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
1 row in set (0.00 sec)

结合Rows和Cardinality，我们可以针对索引列，计算出平均一个值重复多少次。

一个值的重复次数≈ Rows ÷ Cardinality

以t_emp表的idx_hire_date索引为例，它的Rows值是299645，它对应的Cardinality 值是5590，所以我们可以计算hire_date列平均单个值的重复次数就是：299645÷5590≈53（条）

此时再看上边那条查询语句：

 select * from t_emp where hire_date in ('1890-11-10','1990-12-10'...'1991-11-12');

假设in语句中有20000个参数的话，就直接使用统计数据来估算这些参数。每个单点区间大约对应53条记录，所以总共需要回表的记录数就是：20000 x 53= 1060000

使用统计数据来计算单点区间对应的索引记录条数比index dive的方式简单，但是它的致命弱点就是：不精确！。使用统计数据算出来的查询成本与实际所需的成本可能相差非常大。

大家需要注意一下，在MySQL 5.7.3以及之前的版本中，eq_range_index_dive_limit 的默认值为10，之后的版本默认值为200。所以如果大家采用的是5.7.3以及之前的版本的话，很容易采用索引统计数据而不是index dive的方式来计算查询成本。当你的查询中使用到了in查询，但是却实际没有用到索引，就应该考虑一下是不是由于eq_range_index_dive_limit值太小导致的。

InnoDB中的统计数据

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

统计数据存储方式

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

• 永久性的统计数据，这种统计数据存储在磁盘上，也就是服务器重启之后这
  些统计数据还在。

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

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

mysql> show variables like 'innodb_stats_persistent';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_stats_persistent | ON    |
+-------------------------+-------+
1 row in set (0.00 sec)

不过最近的MySQL版本都基本不用基于内存的非永久性统计数据了，所以
我们也就不深入研究。

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%';
+---------------------------+
| Tables_in_mysql (innodb%) |
+---------------------------+
| innodb_index_stats        |
| innodb_table_stats        |
+---------------------------+
2 rows in set (0.00 sec)

可以看到，这两个表都位于mysql系统数据库下边，其中：

• innodb_table_stats存储了关于表的统计数据，每一条记录对应着一个表的统
  计数据。

• innodb_index_stats存储了关于索引的统计数据，每一条记录对应着一个索
  引的一个统计项的统计数据。

innodb_table_stats

mysql> SELECT * FROM mysql.innodb_table_stats where table_name='t_emp';
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name | last_update         | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| employees     | t_emp      | 2021-08-19 07:19:44 | 299645 |                  929 |                      578 |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
1 row in set (0.00 sec)

直接看一下这个innodb_table_stats表中的各个列都是干嘛的：

n_rows统计项的收集

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

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

mysql> show variables like 'innodb_stats_persistent_sample_pages';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| innodb_stats_persistent_sample_pages | 20    |
+--------------------------------------+-------+
1 row in set (0.01 sec)

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

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

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

clustered_index_size和sum_of_other_index_sizes统计项的收集牵涉到很具
体的InnoDB表空间的知识和存储页面数据的细节，我们就不深入讲解了。

innodb_index_stats

mysql> select * from mysql.innodb_index_stats where table_name='t_emp';
+---------------+------------+----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name     | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+---------------+------------+----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| employees     | t_emp      | PRIMARY        | 2021-08-19 07:19:40 | n_diff_pfx01 |     299645 |          20 | emp_no                            |
| employees     | t_emp      | PRIMARY        | 2021-08-19 07:19:40 | n_leaf_pages |        886 |        NULL | Number of leaf pages in the index |
| employees     | t_emp      | PRIMARY        | 2021-08-19 07:19:40 | size         |        929 |        NULL | Number of pages in the index      |
| employees     | t_emp      | idx_birth_date | 2021-08-19 07:19:44 | n_diff_pfx01 |       4770 |          20 | birth_date                        |
| employees     | t_emp      | idx_birth_date | 2021-08-19 07:19:44 | n_diff_pfx02 |     300069 |          20 | birth_date,emp_no                 |
| employees     | t_emp      | idx_birth_date | 2021-08-19 07:19:44 | n_leaf_pages |        231 |        NULL | Number of leaf pages in the index |
| employees     | t_emp      | idx_birth_date | 2021-08-19 07:19:44 | size         |        289 |        NULL | Number of pages in the index      |
| employees     | t_emp      | idx_hire_date  | 2021-08-19 07:19:40 | n_diff_pfx01 |       5590 |          20 | hire_date                         |
| employees     | t_emp      | idx_hire_date  | 2021-08-19 07:19:40 | n_diff_pfx02 |     300069 |          20 | hire_date,emp_no                  |
| employees     | t_emp      | idx_hire_date  | 2021-08-19 07:19:40 | n_leaf_pages |        231 |        NULL | Number of leaf pages in the index |
| employees     | t_emp      | idx_hire_date  | 2021-08-19 07:19:40 | size         |        289 |        NULL | Number of pages in the index      |
+---------------+------------+----------------+---------------------+--------------+------------+-------------+-----------------------------------+
11 rows in set (0.00 sec)

直接看一下这个innodb_index_stats表中的各个列都是干嘛的：

innodb_index_stats表的每条记录代表着一个索引的一个统计项。我们来具体看一下一个索引都有哪些统计项：

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

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

• n_diff_pfxNN：表示对应的索引列不重复的值有多少。

n_diff_pfxNN其中的NN长得有点
儿怪呀，啥意思呢？
其实NN可以被替换为01、02、03… 这样的数字。比如对于联合索引u_idx_day_status(insert_time,order_status,expire_time)来说：

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

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

• n_diff_pfx03 表示的是统计insert_time、order_status、expire_time这三个列组
  合起来不重复的值有多少。

• n_diff_pfx04 表示的是统计insert_time、order_status、expire_time、id这四个列
  组合起来不重复的值有多少。

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

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

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

定期更新统计数据

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

开启innodb_stats_auto_recalc

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

innodb_stats_auto_recalc默认为on，表示开启。

mysql> show variables like 'innodb_stats_auto_recalc';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_stats_auto_recalc | ON    |
+--------------------------+-------+
1 row in set (0.02 sec)

再一次强调，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语句来重新计算统计数据，比如我们可以这样更新关于
t_emp表的统计数据：

mysql> select * from mysql.innodb_table_stats where table_name='t_emp';
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name | last_update         | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| employees     | t_emp      | 2021-08-19 07:19:44 | 299645 |                  929 |                      578 |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
1 row in set (0.00 sec)

mysql> analyze table employees.t_emp;
+-----------------+---------+----------+----------+
| Table           | Op      | Msg_type | Msg_text |
+-----------------+---------+----------+----------+
| employees.t_emp | analyze | status   | OK       |
+-----------------+---------+----------+----------+
1 row in set (0.05 sec)

mysql> select * from mysql.innodb_table_stats where table_name='t_emp';
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name | last_update         | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| employees     | t_emp      | 2021-08-30 08:48:24 | 299468 |                  929 |                      578 |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
1 row in set (0.00 sec)

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

手动更新innodb_table_stats和innodb_index_stats表

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

1. 更新innodb_table_stats 表。

2. 让MySQL 查询优化器重新加载我们更改过的数据。

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

FLUSH TABLE t_emp;