MySQL索引统计信息更新相关的参数

MySQL统计信息相关的参数：

1. innodb_stats_on_metadata（是否自动更新统计信息），MySQL 5.7中默认为关闭状态

仅在统计信息配置为非持久化的时候生效。

也就是说在innodb_stats_persistent 配置为OFF的时候，非持久化存储统计信息的手，innodb_stats_on_metadata的设置才生效。

当innodb_stats_on_metadata设置为ON的时候，

InnoDB在执show table status 或者访问INFORMATION_SCHEMA.TABLES

或者INFORMATION_SCHEMA.STATISTICS 系统表的时候，

更新费持久化统计信息（类似于ANALYZE TABLE），innodb_stats_on_metadata不管打开还是关闭，都不影响持久化存储统计信息的索引

某个索引的统计信息更新时间参考mysql.innodb_index_stats这个系统表

某个索引的统计信息更新时间参考mysql.innodb_index_stats这个系统表

select *

from mysql.innodb_index_stats

where table_name = 'teststatistics';

2. innodb_stats_auto_recalc

是否自动触发更新统计信息，仅影响持久化存储的统计信息的表，阈值是变化的数据超过表行数的10%。

也就是说，一个表索引统计信息是持久化存储的，并且表中数据变化了超过10%，

如果innodb_stats_auto_recalc为ON，就会自动更新统计信息，否则不会自动更新

3. innodb_stats_persistent（非持久化统计信息开关），MySQL 5.7中默认为打开，持久化存储统计信息

该选项设置为ON时候，统计信息会持久化存储到磁盘中，而不是存在在内存中，

相反，如果是非持久化存储的（存在内存中），相应的统计信息会随着服务器的关闭而丢失。

4. innodb_stats_persistent_sample_pages （持久化更新统计信息时候索引页的取样页数）

默认是20个page，如果设置的过高，那么在更新统计信息的时候，会增加ANALYZE TABLE的执行时间。

5. innodb_stats_transient_sample_pages（临时性更新统计信息时候索引页的取样页数）

默认值是8，innodb_stats_persistent设置为disable的情况下，也即非持久化明确关闭的时候，innodb_stats_transient_sample_pages才生效，

也就是非持久化存储过索引统计信息的时候，innodb_stats_transient_sample_pages为更新统计信息的采样页数

这个值是否生效，要依赖于innodb_stats_on_metadata，而innodb_stats_on_metadata又依赖于innodb_stats_persistent

总而言之：如果配置为持久化存储统计信息，非持久化相关的配置选项就不起作用

6. innodb_stats_sample_pages

已弃用 . 已用innodb_stats_transient_sample_pages 替代。

为啥要用innodb_stats_transient_sample_pages替代？

个人猜测是一开始参数命名不规范，既然是临时行统计信息，却没有做到见名知意，与innodb_stats_persistent_sample_pages区分开来，

或许是一开始 MySQL 中只有临时行统计信息，没有持久化统计信息。

统计信息更新测试1：打开innodb_stats_auto_recalc的情况下，统计信息会在触发其更新阈值后自动更新

查询统计信息更新时间

收集统计信息之后再次查询，innodb_index_stats表，统计信息更新

通过改变表的变化行数，使得统计信息自动收集

统计信息更新测试2：关闭innodb_stats_auto_recalc的情况下，统计信息会在触发其更新阈值后自动更新

关闭自动收集统计信息选项innodb_stats_auto_recalc

往表中插入超过原表10%的数据（已经达到触发统计信息更新阈值的情况），统计信息依旧没有更新

此时（关闭innodb_stats_auto_recalc的情况下），只有通过手动收集才能完成统计信息的更新

MySQL可以在表上指定一个统计信息取样的page个数，并且可以修改表上的统计取样page个数

-- 创建表的时候指定一个统计取样page数据

create table testSamplePages

(

id int,

name varchar(50)

)ENGINE=InnoDB,

STATS_PERSISTENT=1,

STATS_AUTO_RECALC=1,

STATS_SAMPLE_PAGES=25;

--修改表的统计取样page数据

ALTER TABLE testSamplePages

STATS_SAMPLE_PAGES 60

Linux公社的RSS地址： https://www.linuxidc.com/rssFeed.aspx

本文永久更新链接地址： https://www.linuxidc.com/Linux/2018-08/153704.htm