手机版
你好,游客 登录 注册 搜索
背景:
阅读新闻

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

[日期:2018-08-25] 来源:Linux社区  作者:wy123 [字体: ]

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

linux
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款