MySQL统计信息
条评论前言
MySQL在生成执行计划时,需要根据索引的统计信息进行估算,计算出最低代价的索引.MySQL支持有限的索引统计信息,因存储引擎不同而不同统计信息收集的方式也不同.
查询统计信息.
针对常用的Innodb和Myisam存储引擎,可以通过如下两种方式:
1.通过show table或information_schema.tables获取.
MySQL [rhau]> show table status like ‘cdo’\G
*** 1. row ***
Name: cdo
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 512
Avg_row_length: 160
Data_length: 81920
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2014-06-23 10:56:37
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
以上部分列的含义如下:
- Name:表名
- Engine:存储引擎类型
- Version:表的.frm文件的版本号
- Row_format:行存储格式,Myisam表为 Fixed, Dynamic, Compressed, Redundant, Compact。InnoDB表的格式被报告为Redundant或Compact.
- Rows:行的数目。对MyISAM存储引擎而言,存储精确的数目,对Innodb存储引擎而言,存储的是估计值.
- Avg_row_length:行的平均长度.
- Data_length:数据文件的长度.
- Max_data_lengt:数据文件的最大长度。如果给定了数据指针的大小,这是可以被存储在表中的数据的字节总数.
- Index_length:索引文件长度.
- Data_free:被重新整理但没有使用的字节数目.
- Auto_increment:下一个AUTO_INCREMENT值.
- Update_time:表的最近更新时间.
- Check_time: 使用 check table 或myisamchk工具检查表的最近时间.
2.show index或者查询information_schema.tables/statistics获取统计信息.
收集策略
针对Myisam和Innodb表可以通过analyze table来收集统计信息.Innodb表除了以上方法外,在下面的这几种情况下也会触发:
- 表第一次打开的时候
- 表修改的行超过1/16或者20亿条 `./row/row0mysql.c:row_update_statistics_if_needed.
- 执行show index/table或者查询information_schema.tables/statistics表时
- MYSQL5.6提供选项innodb_stats_persistent,默认on,将analyze table产生的统计信息保存于磁盘,直至下次analyze table为止.
Innodb统计信息
InnoDB层统计信息除了设置MySQL Server层统计信息外,还在本层维护了自身的统计信息,根据此统计信息,计算全表扫描/索引扫描代价.主要的信息如下:
stat_n_rows
- 表记录数量;I/U/D操作时,实时修改
- 用于设置MySQL Server层的records信息
stat_clustered_index_size
- 聚簇索引页面总数量
- 计算MySQL Server层,data_file_length信息
- 计算全表扫描IO代价
stat_sum_of_other_index_size
stat_modified_counter
- I/U/D,此值++
主要的收集算法主要包括如下几点:
- 统计索引中叶页面数量
- 随机定位索引中的8个叶页面
- 统计页面中,前缀索引列组合的Distinct数量
- 根据以上信息,计算表的数据量和MySQL Server层的rec_per_key信息以及Join Optimizer等重要的统计信息.
总结
5.6之前的版本,统计信息是保存在内存中,第一次open的时候做分析;5.6提供选项innodb_stats_persistent,默认on,将analyze table产生的统计信息保存于磁盘,直至下次analyze table为止,此举避免了统计信息动态更新,保证了执行计划的稳定,对于大表也节省了收集统计信息的所需资源.了解MySQL统计信息对MySQL执行计划可以有跟深入的理解,了解执行计划,对MySQL优化有很大的帮助.
- 本文链接:http://www.darcy.org.cn/2014/06/23/mysqltong-ji-xin-xi/
- 版权声明:作者拥有版权,转发请注明出处来源