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_statsmysql.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 如果您有其他问题,欢迎您联系火山引擎技术支持服务

0
0
0
0
相关产品
评论
未登录
看完啦,登录分享一下感受吧~
暂无评论