时间:2021-05-23
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。
持久化统计信息在以下情况会被自动更新:
1INNODB_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越来越强大的功能吧!
声明:本页内容来源网络,仅供用户参考;我单位不保证亦不表示资料全面及准确无误,也不保证亦不表示这些资料为最新信息,如因任何原因,本网内容或者用户因倚赖本网内容造成任何损失或损害,我单位将不会负任何法律责任。如涉及版权问题,请提交至online#300.cn邮箱联系删除。
一、概述MySQL版本:5.6.21下载地址:https://dev.mysql.com/downloads/mysql/客户端工具:NavicatforMyS
统计信息数据一、统计信息数据功能介绍:统计信息数据为对网站信息数量进行统计。二、统计信息数据:(一)、登录后台,单击“栏目”菜单,选择&
本文实例讲述了java实现的统计字符算法。分享给大家供大家参考,具体如下:统计字符:概述:给定字符串,将它们进行分类,分别的去统计它们的个数及其字符分类的有:字
一、概述文章主要介绍因为启动mysql服务报错引发的问题:"ERROR!TheserverquitwithoutupdatingPIDfile(/var/lib
mysql获取groupby内部可以获取到某字段的记录分组统计总数,而无法统计出分组的记录数。mysql的SQL_CALC_FOUND_ROWS使用获取查询的行