MySQL统计信息 | Darcy Blog

前言

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优化有很大的帮助.