MySQL中的统计数据

╰+哭是因爲堅強的太久メ 2022-09-10 08:21 157阅读 0赞

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

有时候使用索引执行查询时会有许多单点区间，例如使用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中这个系统变量的默认值：

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

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

<table> 
 <thead> 
  <tr> 
   <th>属性名</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Table</td> 
   <td>索引所属表的名称。</td> 
  </tr> 
  <tr> 
   <td>Non_unique</td> 
   <td>索引列的值是否是唯一的，聚簇索引和唯一二级索引的该列值为0，普通二级索引该列值为1。</td> 
  </tr> 
  <tr> 
   <td>Key_name</td> 
   <td>索引的名称。</td> 
  </tr> 
  <tr> 
   <td>Seq_in_index</td> 
   <td>索引列在索引中的位置，从1开始计数。对普通索引来说，永远都是1，对联合索引才会大于1。比如对于联合索引u_idx_day_status(insert_time,order_status,expire_time)，来说，insert_time、order_status、expire_time对应的位置分别是1、2、3。</td> 
  </tr> 
  <tr> 
   <td>Column_name</td> 
   <td>索引列的名称。</td> 
  </tr> 
  <tr> 
   <td>Collation</td> 
   <td>索引列中的值是按照何种排序方式存放的，值为A时代表升序存放，为NULL 时代表降序存放。</td> 
  </tr> 
  <tr> 
   <td>Cardinality</td> 
   <td>索引列中不重复值的数量。</td> 
  </tr> 
  <tr> 
   <td>Sub_part</td> 
   <td>的前n个字符或字节建立索引，这个属性表示的就是那个n值。如果对完整的列建立索引的话，该属性的值就是NULL。</td> 
  </tr> 
  <tr> 
   <td>Packed</td> 
   <td>索引列如何被压缩，NULL 值表示未被压缩。</td> 
  </tr> 
  <tr> 
   <td>Null</td> 
   <td>该索引列是否允许存储NULL值。</td> 
  </tr> 
  <tr> 
   <td>Index_type</td> 
   <td>使用索引的类型，我们最常见的就是BTREE，其实也就是B+树索引。</td> 
  </tr> 
  <tr> 
   <td>Comment</td> 
   <td>索引列注释信息。</td> 
  </tr> 
  <tr> 
   <td>Index_comment</td> 
   <td>索引注释信息。</td> 
  </tr> 
 </tbody> 
</table>

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属性。

结合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版本都基本不用基于内存的非永久性统计数据了，所以  
我们也就不深入研究。

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系统数据库下边，其中：

*  innodb\_table\_stats存储了关于表的统计数据，每一条记录对应着一个表的统  
    计数据。
 *  innodb\_index\_stats存储了关于索引的统计数据，每一条记录对应着一个索  
    引的一个统计项的统计数据。

### innodb\_table\_stats ###

直接看一下这个innodb\_table\_stats表中的各个列都是干嘛的：

<table> 
 <thead> 
  <tr> 
   <th>列名</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>database_name</td> 
   <td>数据库名</td> 
  </tr> 
  <tr> 
   <td>table_name</td> 
   <td>表名</td> 
  </tr> 
  <tr> 
   <td>last_update</td> 
   <td>本条记录最后更新时间</td> 
  </tr> 
  <tr> 
   <td>n_rows</td> 
   <td>表中记录的条数</td> 
  </tr> 
  <tr> 
   <td>clustered_index_size</td> 
   <td>表的聚簇索引占用的页面数量</td> 
  </tr> 
  <tr> 
   <td>sum_of_other_index_sizes</td> 
   <td>表的其他索引占用的页面数量</td> 
  </tr> 
 </tbody> 
</table>

### 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 ###

直接看一下这个innodb\_index\_stats表中的各个列都是干嘛的：

<table> 
 <thead> 
  <tr> 
   <th>列名</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>database_name</td> 
   <td>数据库名</td> 
  </tr> 
  <tr> 
   <td>table_name</td> 
   <td>表名</td> 
  </tr> 
  <tr> 
   <td>index_name</td> 
   <td>索引名</td> 
  </tr> 
  <tr> 
   <td>last_update</td> 
   <td>本条记录最后更新时间</td> 
  </tr> 
  <tr> 
   <td>stat_name</td> 
   <td>统计项的名称</td> 
  </tr> 
  <tr> 
   <td>stat_value</td> 
   <td>对应的统计项的值</td> 
  </tr> 
  <tr> 
   <td>sample_size</td> 
   <td>为生成统计数据而采样的页面数量</td> 
  </tr> 
  <tr> 
   <td>stat_description</td> 
   <td>对应的统计项的描述</td> 
  </tr> 
 </tbody> 
</table>

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，表示开启。

再一次强调，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表的统计数据：

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;