MySQL实行SQL会经由SQL剖析和查询优化的历程,剖析器将SQL分解成数据结构并通报到后续步骤,查询优化器发明实行SQL查询的最好计划、生成实行计划。查询优化器决议SQL怎样实行,依赖于数据库的统计信息,下面我们引见MySQL 5.7中innodb统计信息的相干内容。
MySQL统计信息的存储分为两种,非耐久化和耐久化统计信息。
一、非耐久化统计信息
非耐久化统计信息存储在内存里,假如数据库重启,统计信息将丧失。有两种体式格局能够设置为非耐久化统计信息:
1 全局变量, INNODB_STATS_PERSISTENT=OFF |
2 CREATE/ALTER表的参数, STATS_PERSISTENT=0 |
非耐久化统计信息在以下状况会被自动更新:
1 实行ANALYZE TABLE |
2 innodb_stats_on_metadata=ON状况下,执SHOW TABLE STATUS, SHOW INDEX, 查询 INFORMATION_SCHEMA下的TABLES, STATISTICS |
3 启用--auto-rehash功用状况下,运用mysql client登录 |
4 表第一次被翻开 |
5 距上一次更新统计信息,表1/16的数据被修正 |
非耐久化统计信息的瑕玷不言而喻,数据库重启后假如大量表最先更新统计信息,会对实例形成很大影响,所以现在都邑运用耐久化统计信息。
二、耐久化统计信息
5.6.6最先,MySQL默许运用了耐久化统计信息,即INNODB_STATS_PERSISTENT=ON,耐久化统计信息保存在表mysql.innodb_table_stats和mysql.innodb_index_stats。
耐久化统计信息在以下状况会被自动更新:
1 INNODB_STATS_AUTO_RECALC=ON 状况下,表中10%的数据被修正 |
2 增添新的索引 |
innodb_table_stats是表的统计信息,innodb_index_stats是索引的统计信息,各字段寄义以下:
innodb_table_stats |
|
database_name |
数据库名 |
table_name |
表名 |
last_update |
统计信息末了一次更新时候 |
n_rows |
表的行数 |
clustered_index_size |
群集索引的页的数目 |
sum_of_other_index_sizes |
其他索引的页的数目 |
innodb_index_stats |
|
database_name |
数据库名 |
table_name |
表名 |
index_name |
索引名 |
last_update |
统计信息末了一次更新时候 |
stat_name |
统计信息名 |
stat_value |
统计信息的值 |
sample_size |
采样大小 |
stat_description |
范例申明 |
为更好的明白innodb_index_stats,建一张测试表做申明:
CREATE TABLE t1 ( a INT, b INT, c INT, d INT, e INT, f INT, PRIMARY KEY (a, b), KEY i1 (c, d), UNIQUE KEY i2uniq (e, f) ) ENGINE=INNODB;
写入数据以下:
检察t1表的统计信息,需重要关注stat_name和stat_value字段
tat_name=size时:stat_value示意索引的页的数目
stat_name=n_leaf_pages时:stat_value示意叶子节点的数目
stat_name=n_diff_pfxNN时:stat_value示意索引字段上唯一值的数目,此处做一下细致申明:
1、n_diff_pfx01示意索引第一列distinct以后的数目,如PRIMARY的a列,只要一个值1,所以index_name='PRIMARY' and stat_name='n_diff_pfx01'时,stat_value=1。
2、n_diff_pfx02示意索引前两列distinct以后的数目,如i2uniq的e,f列,有4个值,所以index_name='i2uniq' and stat_name='n_diff_pfx02'时,stat_value=4。
3、关于非唯一索引,会在原有列以后加上主键索引,如index_name='i1' and stat_name='n_diff_pfx03',在原索引列c,d后加了主键列a,(c,d,a)的distinct效果为2。
了解了stat_name和stat_value的细致寄义,就能够辅佐我们排查SQL实行时为何没有运用适宜的索引,比方某个索引n_diff_pfxNN的stat_value远小于现实值,查询优化器以为该索引挑选度较差,就有能够致使运用毛病的索引。
三、统计信息不准确的处置惩罚
我们检察实行计划,发明未运用准确的索引,假如是innodb_index_stats中统计信息差异较大引发,可经由历程以下体式格局处置惩罚:
1、手动更新统计信息,注重实行历程当中会加读锁:
ANALYZETABLE TABLE_NAME;
2、假如更新后统计信息仍不准确,可斟酌增添表采样的数据页,两种体式格局能够修正:
a) 全局变量INNODB_STATS_PERSISTENT_SAMPLE_PAGES,默许为20;
b) 单个表能够指定该表的采样:
ALTER TABLE TABLE_NAME STATS_SAMPLE_PAGES=40;
经测试,此处STATS_SAMPLE_PAGES的最大值是65535,超越会报错。
现在MySQL并没有供应直方图的功用,某些状况下(如数据散布不均)仅仅更新统计信息不一定能获得准确的实行计划,只能经由历程index hint的体式格局指定索引。新版本8.0会增添直方图功用,让我们期待MySQL愈来愈壮大的功用吧!
相干引荐:
实例剖析:统计信息管理、Spring注解开辟和EasyUI
网络SQL Server统计信息_PHP教程
以上就是MySQL统计信息的细致概述的细致内容,更多请关注ki4网别的相干文章!