查询优化器使用统计信息为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语句均会触发。