专栏首页MySQLBeginnerMySQL统计信息简介

MySQL统计信息简介

作者:王小龙@网易乐得DBA 原文地址: http://mp.weixin.qq.com/s/698g5lm9CWqbU0B_p0nLMw

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_statsmysql.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_namestat_value字段

stat_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之后的数量,如i2uniqe,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_namestat_value的具体含义,就可以协助我们排查SQL执行时为什么没有使用合适的索引,例如某个索引n_diff_pfxNNstat_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越来越强大的功能吧!

本文分享自微信公众号 - MySQLBeginner(MySQLBeginner),作者:王小龙

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2018-01-23

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MySQL 统计信息简介

    MySQL执行SQL会经过SQL解析和查询优化的过程,解析器将SQL分解成数据结构并传递到后续步骤,查询优化器发现执行SQL查询的最佳方案、生成执行计划。查询优...

    wubx
  • 学习MySQL 5.7的Sys库使用(一)

    MySQL 5.7 引入 sys库 用于帮助 DBA 分析一些问题。 sys 库里面就是一些存储过程,视图,函数等。

    wubx
  • 优雅地使用pt-archiver进行数据归档

    最近由于业务需求,需要将公有云RDS(业务库)的大表数据归档至私有云MySQL(历史库),以缩减公有云RDS的体积和成本。

    wubx
  • MySQL 统计信息简介

    MySQL执行SQL会经过SQL解析和查询优化的过程,解析器将SQL分解成数据结构并传递到后续步骤,查询优化器发现执行SQL查询的最佳方案、生成执行计划。查询优...

    wubx
  • 预测:云计算及数据中心未来发展的5个趋势

    如今,大数据时代已经降临,云端的作用日益凸显。人们几乎可以摆脱传统的硬件储存方式,只要有能上网,人们便可轻易从云数据中心进行储存、调取各种所需资料,如图片、文档...

    静一
  • jquery动态生成input,并且限制生成个数

    写一个小的demo,具体是点击添加按钮,会自动生成input输入框,并且根据要求限制生成的input输入框的个数。

    王小婷
  • Canvas高级

    天天_哥
  • AAAI 2020 | 北大&上交大:姿态辅助下的多相机协作实现主动目标追踪

    2020 年 2 月 7 日-2 月 12 日,AAAI 2020 将于美国纽约举办。不久之前,大会官方公布了今年的论文收录信息:收到 8800 篇提交论文,评...

    机器之心
  • 模型压缩95%,MIT韩松等人提出新型Lite Transformer

    虽然推出还不到 3 年,Transformer 已成为自然语言处理(NLP)领域里不可或缺的一环。然而这样流行的算法却需要极高的算力才能实现足够的性能,这对于受...

    机器之心
  • 最后一公里,你需要一套具备质量思维的发布平台!

    传统上,软件的最终发布是个充满压力的过程,需要大量的手工配置、操作和团队配合。为了发布的可靠性,开发人员需要准备详尽的部署文档,然后再把相关信息同步给运维人员执...

    DevOps时代

扫码关注云+社区

领取腾讯云代金券