mysql统计信息相关

查询优化器使用统计信息为sql选择执行计划Mysql没有直方图信息，也无法手工删除统计信息

一、如何查询统计信息

1、使用show table或information_schema.tables

mysql> show table status like 'subject';

+---------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+-------------+------------+-----------+----------+----------------+---------+

| Name    | Engine | Version |Row_format | Rows  | Avg_row_length |Data_length | Max_data_length | Index_length | Data_free  | Auto_increment | Create_time         | Update_time | Check_time | Collation| Checksum | Create_options | Comment |

+---------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+-------------+------------+-----------+----------+----------------+---------+

| subject | InnoDB |      10 | Compact    | 10505 |            350 |     3686400 |               0 |      1212416 | 1198522368 |           NULL | 2013-07-23 08:40:24 |NULL        | NULL       | gbk_bin   |    NULL |                |         |

+---------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+-------------+------------+-----------+----------+----------------+---------+

1 row in set (0.42 sec)

    部分列含义：

Row_format：行格式。对于MyISAM引擎，这可能是Dynamic，Fixed或Compressed。动态行的行长度可变，例如Varchar或Blob类型字段。固定行是指行长度不变，例如Char和Integer类型字段。

Max_data_length：表可以容纳的最大数据量。

Index_length：索引占用磁盘的空间大小 。

Auto_increment：下一个Auto_increment的值。

Update_time：表的最近更新时间。

Check_time： 使用 check table 或myisamchk工具检查表的最近时间

2、Show indexfrom table或查看information_schema.statistics表

 

二、如何收集统计信息

Analyze table收集表和索引统计信息，适用于MyISAM和InnoDB；

对于innodb表，还可以使用以下选项来收集

1、表第一次打开的时候

2、表修改的行超过1/16或者20亿条

 ./row/row0mysql.c:row_update_statistics_if_needed

3、执行show index/table或者查询information_schema.tables/statistics表时

在访问以下表时，innodb表的统计信息可自动收集

information_schema.TABLES

information_schema.STATISTICS

information_schema.PARTITIONS

information_schema.KEY_COLUMN_USAGE

information_schema.TABLE_CONSTRAINTS

information_schema.REFERENTIAL_CONSTRAINTS

information_schema.table_constraints

 

innodb_stats_on_metadata参数用来控制此行为，设置为false时不更新统计信息。

Innodb_stats_sample_pages每次收集统计信息时采样的页数，默认为8。

     每个表维护一个stat_modified_counter，每次DML更新1行就加1，直到满足阈值则自动收集统计信息，并把此值清0；

函数dict_update_statistics用于更新统计信息，但若有多个线程同时检测到阈值，会导致多次调用，浪费了系统资源；可以直接修改代码，让dict_update_statistics对stat_modified_counter加锁，避免并发执行

5.6之前的版本，统计信息是保存在内存中，第一次open的时候做分析；5.6提供选项innodb_stats_persistent，默认on，将analyze table产生的统计信息保存于磁盘，直至下次analyze table为止，此举避免了统计信息动态更新，保证了执行计划的稳定，对于大表也节省了收集统计信息的所需资源;除非当前sql执行计划不佳，否则不应经常analyze table收集统计信息。

Innodb_stats_method和myisam_stats_method

计算统计信息时，拥有相同key prefix的行算作一个value group（类似oracle索引中的num_distinct，其值越多意味着索引选择性越好），average group size是非常重要的指标，即平均一个索引值返回的表行数，主要有两个用途：

1、估算每次ref access要读取多少行

2 、估算一个partial join要产生多少行 (…) join tab on tab.key =expr

       由此可知，average group size越高则索引选择性越低，表基数即value group数量计算公式为N/S(N:表行数 S:average group size)，可通过show index查看除了主键，索引不可避免的会遇到Null(对于<=>操作符，NULL和Non-null被同等对待，而Null = Null则会返回false)，mysql将NULL视作无穷小；收集统计信息时，为了灵活的处理Null，InnoDB/MyISAM各引入一个参数。

Innodb_stats_method/myisam_stats_method，分别三个候选值：nulls_equal/nulls_unequal/nulls_ignored(其中innod_stats_method只有全局变

量)

Nulls_equal：所有Null都相等，即算作一个value group；若Null过多则会导致average group size偏大。

Nulls_unequal：所有Null互不相同，每个算作一个value group；如果non-null group size过大且null数量过多，此设置会拉低整体的average group size，可能导致滥用索引

Nulls_ignored：忽略Null

       对于已经收集的统计信息，无法分辨其采用了那种方式；对于非InnoDB/MyISAM表，只有一种收集方式，即nulls_equal；

       手工收集统计信息需要调用analyze table，但若表自上次analye至今没有任何改动，即便调用此命令实际也不会收集统计信息，需先让统计信息过期(插入一行再删除即可)

Mysql也可自动收集，诸如bulk insert/delete以及某些alter table语句均会触发。