MySQL 统计信息相关参数解析

MySQL 中的优化器是基于成本的优化器(CBO)，所谓基于成本，代表着运行目标 SQL 语句需要消耗的 CPU，扫描行数，IO 等，这些信息是基于统计信息来获得的。如果统计信息不准确，优化器很可能选择那些不够优化，甚至是错误的执行计划。 对于统计信息的搜集方式有两种：

1. 非持久化统计信息
2. 持久化统计信息

所谓的非持久化统计信息，指的是统计信息没有持久化到磁盘上，如果数据库重启，统计信息将全部丢失。 设置非持久化统计信息有两种方式：

1. 设置全局变量 INNODB_STATS_PERSISTENT=OFF
2. 使用 create/alter table 属性 STATS_PERSISTENT=0，例如：

mysql> alter table test0407 STATS_PERSISTENT=0;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table test0407;
+----------+------------------------------
| Table    | Create Table                    |
+----------+-----------------------------
| test0407 | CREATE TABLE `test0407` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 |
+----------+---------------------------+
1 row in set (0.04 sec)

非持久化统计信息搜集规则

有如下几种情况，非持久化统计信息会自动进收集

1. 表第一次被打开
2. 表中有 1/16 数据被修改
3. 执行 analyze table
4. innodb_stats_on_metadata = ON 时，执行 show table status，show index，或者是查询 information schema 下统计信息相关的表时会自动进行收集
5. 启用--auto-rehash(默认)功能情况下，使用mysql client登录

非持久化统计信息相关参数

1. innodb_stats_on_metadata：当统计信息配置为非持久化时生效
2. innodb_stats_transient_sample_pages：统计信息的搜集是基于采样的，此参数控制了采样数，数值越大，统计信息越准确，当然开销也会更大。
3. INNODB_STATS_PERSISTENT=OFF：OFF 即为使用非持久化统计信息。

相反的，持久化统计信息指的是统计信息会持久化到磁盘上，不会随着实例重启而丢失。 使用持久化统计信息需要打开参数 INNODB_STATS_PERSISTENT=ON，持久化信息表为 mysql.innodb_table_stats 和mysql.innodb_index_stats

持久化统计信息搜集规则

1. innodb_stats_auto_recalc 参数开启的情况下(默认)，当表中数据更改超过 10% 时会自动统计
2. 当添加新的索引时自动更新统计信息

持久化统计信息相关参数

1. innodb_stats_persistent：为 ON 时使用持久化统计信息
2. innodb_stats_auto_recalc：控制是否自动搜集统计信息，当打开之后，当表中数据变化超过 10% 自动收集统计信息
3. innodb_stats_persistent_sample_pages：和 参数 innodb_stats_transient_sample_pages 一样，控制搜集统计信息采样的 page 个数。
4. innodb_stats_include_delete_mark：在更新持久化统计信息时，是否包含未提交事务中已经被删除的行。此参数为 5.6.35 新增。

持久化统计信息表

在 mysql schema 中有两张持久化信息的统计表，分别为：

1. innodb_table_stats：表的统计信息
2. innodb_index_stats：索引的统计信息

1. innodb_table_stats

列名	解释
database_name	数据库名
table_name	表名
last_update	最新一次更新统计信息的时间
n_rows	表的行数
clustered_index_size	聚簇索引页的数量
sum_of_other_index_sizes	其他索引的页的数量

2. innodb_index_stats

列名	解释
database_name	数据库名
table_name	表名
index_name	索引名
last_update	最新一次更新统计信息的时间
stat_name	统计信息名
stat_value	统计信息的值
sample_size	采样大小
stat_description	说明

1. 手动更新统计信息

您可以执行 analyze table table_name 来手动更新统计信息。需要注意的是，请选择业务上合适的维护时间进行此操作。

2. 增加采样页面数

我们前面提到过参数 innodb_stats_persistent_sample_pages，此参数默认为 20，可以适当调大一些来解决统计信息不准确的问题。 同时，此参数可以在表级别进行设置，例如：

mysql> alter table test0407 STATS_SAMPLE_PAGES=100;;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> show create table test0407;
+----------+------------------------
| Table    | Create Table                            
+----------+------------------------
| test0407 | CREATE TABLE `test0407` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 STATS_SAMPLE_PAGES=100 |
+----------+---------------------------------------------------+
1 row in set (0.04 sec)

[1] https://dev.mysql.com/doc/refman/5.7/en/innodb-persistent-stats.html [2] https://dev.mysql.com/doc/refman/5.7/en/innodb-statistics-estimation.html 如果您有其他问题，欢迎您联系火山引擎技术支持服务