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)
某个索引的统计信息更新时间参考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为更新统计信息的采样页数
6. innodb_stats_sample_pages
已弃用
. 已用innodb_stats_transient_sample_pages 替代。
======重新收集统计信息=======
1.分析和存储表的关键字分布
analyze table table_name;
analyze 用于收集优化器的统计信息、和tuning相关;对 myisam、BOB、innodb起作用。
注意:(经过实践操作 在MySQL5.5版本下执行sql:analyze table table_name 会马上返回一个ok状态,但是后端
仍然会出现锁表等情况)。
2.检查表(视图),检查一个或者多个表是否有错误
check table table_name;
对myisam(关键字统计数据被更新)和innodb都有作用,对于myisam来说,需要check和repair(由于myisam表
可能
损坏)。
3.定期优化表(对空间碎片进行整理合并,消除由于删除或者更新造成的空间浪费)
optimize table table_name;
对myisam,bob和innodb表起作用,但是该操作会引起锁表。
optimize 可以回收空间,整理碎片,提高IO 之处 innodb、myisam、archive;如若是replication环境,可以加
no_wri
te_to_binlog。
optimize local table table_name;
====小结:====
在MySQL实际生产环境中,对于一些经常性DML操作的表,是需要定期在业务低峰期执行optimize来收缩表空间,对于优化慢sql和压缩磁盘都有较大的好处。
analyze 和optimize都是会锁表的,(当然是因为业务生产中基本选择了innodb的前提下)注意在业务高峰期切忌操作。