阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

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

245次阅读
没有评论

共计 2213 个字符,预计需要花费 6 分钟才能阅读完成。

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 的情况下,统计信息会在触发其更新阈值后自动更新

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

 

查询统计信息更新时间

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

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

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

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

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

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

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

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

 

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

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

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

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

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

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计2213字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中