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
来源: http://www.linuxidc.com/Linux/2018-08/153704.htm