InnoDB 统计信息

一、InnoDB 统计信息的两种情况

• 持久化统计信息：重启MySQL时，统计信息不丢失
• 非持久化统计信息：重启MySQL时，统计信息丢失

两种情况下，保存的方式，收集的条件，采样率都不一样，接下来介绍持久化跟非持久化的不同。

 

二、持久化统计信息

2.1 持久化统计信息保存在哪？

 

• 当  innodb_stats_persistent = ON，默认开启
• 创建表的时候指定 STATS_PERSISTENT=1

 

MySQL收集的统计信息会保存在以下两种表

• mysql.innodb_table_stats
• mysql.innodb_index_stats

 

2.2 何时收集持久化统计信息？

• 当 innodb_stats_auto_recalc = ON，默认开启
• 创建表时指定 STATS_AUTO_RECALC=1。
• 表数据变化超过10%时，触发自动收集。
• 但是可能会有延迟几秒，并不会马上触发收集。

 

2.3 采样率

• 统计信息收集是采样分析，默认是20个 page，由 innodb_stats_persistent_sample_pages 参数控制。
• 如果统计信息相差太多，可以增加这个参数，不过 ANAYLZE TABLE 收集的时间会增加。
• 也可以在创建表的时候指定 STATS_SAMPLE_PAGES。
• 如果感觉自动收集的统计信息不准时，甚至可以直接修改  mysql.innodb_table_stats 和 mysql.innodb_index_stats。修改完 flush table tbl_name 就可以了。

 

三、非持久化统计信息

3.1 非持久化统计信息保存在哪？

保存在内存中，重启就失效了

 

3.2 何时收集非持久化统计信息

• ANALYZE TABLE
• innodb_stats_on_metadata = 1(该参数只有 innodb_stats_persistent =OFF 才生效)。启用 innodb_stats_on_metadata 可能会降低性能
  • 执行 SHOW TABLE STATUS， SHOW INDEX
  • 查询 INFORMATION_SCHEMA.TABLES 或 INFORMATION_SCHEMA.STATISTICS 表
• mysql客户端使用了 --auto-rehash，该 auto-rehash 选项会导致所有InnoDB表被打开，并且打开表操作会导致重新计算统计信息。建议禁用该操作 auto-rehash，使用参数 --disable-auto-rehash 禁用。
• 第一次打开表
• InnoDB 检测自上次更新统计信息以来表已修改了1/16。

 

3.3 采样率

 

默认的采样 page 是 8，由参数 innodb_stats_transient_sample_pages 控制 

 

四、手动收集统计信息

手动收集统计信息使用

analyze table tbl_name。

如果发现执行计划内的统计信息有问题，可以手动收集，如果还是手动收集的不准，可以调整下参数采样参数。

innodb_stats_persistent = ON。调整 innodb_stats_persistent_sample_pages 参数

innodb_stats_persistent = OFF。调整 innodb_stats_transient_sample_pages  参数