MySQL统计信息简介

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:

MySQL执行SQL会经过SQL解析和查询优化的过程,解析器将SQL分解成数据结构并传递到后续步骤,查询优化器发现执行SQL查询的最佳方案、生成执行计划。查询优化器决定SQL如何执行,依赖于数据库的统计信息,下面我们介绍MySQL 5.7中innodb统计信息的相关内容。

MySQL统计信息的存储分为两种,非持久化和持久化统计信息。

一、非持久化统计信息

非持久化统计信息存储在内存里,如果数据库重启,统计信息将丢失。有两种方式可以设置为非持久化统计信息:

879c376f144aa0c9589941dabd9535bf36f432ff

非持久化统计信息在以下情况会被自动更新:

beb473dc16eefb60dc91a4ae9d1c39a78dadc689

非持久化统计信息的缺点显而易见,数据库重启后如果大量表开始更新统计信息,会对实例造成很大影响,所以目前都会使用持久化统计信息。

二、持久化统计信息

5.6.6开始,MySQL默认使用了持久化统计信息,即INNODB_STATS_PERSISTENT=ON,持久化统计信息保存在表mysql.innodb_table_statsmysql.innodb_index_stats

持久化统计信息在以下情况会被自动更新:

4f13e2017b9cd4e6c7906a01159117b2dc3ebec7

innodb_table_stats是表的统计信息,innodb_index_stats是索引的统计信息,各字段含义如下:

acfbef5d8024047cd6465a78851bda5c67043000
为更好的理解 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;

写入数据如下:

401f913d8be4bbb21bedf37ae3822d1f47cc15c2

查看t1表的统计信息,需主要关注stat_namestat_value字段

e0efc871a8bd40e1ee14f8ec31de5ba3306539fd

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越来越强大的功能吧!


原文发布时间为:2018-01-23

本文作者:王小龙

本文来自云栖社区合作伙伴“老叶茶馆”,了解相关信息可以关注“老叶茶馆”微信公众号

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
4月前
|
关系型数据库 MySQL
MySQL查看连接数和进程信息
这篇文章介绍了如何在MySQL中查看连接数和进程信息,包括当前打开的连接数量、历史成功建立连接的次数、连接错误次数、连接超时设置,以及如何查看和终止正在执行的连接进程。
800 10
|
15天前
|
自然语言处理 Java 关系型数据库
Java mysql根据很长的富文本如何自动获取简介
通过使用Jsoup解析富文本并提取纯文本,然后根据需要生成简介,可以有效地处理和展示长文本内容。该方法简单高效,适用于各种应用场景。希望本文对您在Java中处理富文本并生成简介的需求提供实用的指导和帮助。
52 14
|
16天前
|
自然语言处理 Java 关系型数据库
Java mysql根据很长的富文本如何自动获取简介
通过使用Jsoup解析富文本并提取纯文本,然后根据需要生成简介,可以有效地处理和展示长文本内容。该方法简单高效,适用于各种应用场景。希望本文对您在Java中处理富文本并生成简介的需求提供实用的指导和帮助。
32 9
|
1月前
|
存储 Oracle 关系型数据库
索引在手,查询无忧:MySQL索引简介
MySQL 是一款广泛使用的关系型数据库管理系统,在2024年5月的DB-Engines排名中得分1084,仅次于Oracle。本文介绍MySQL索引的工作原理和类型,包括B+Tree、Hash、Full-text索引,以及主键、唯一、普通索引等,帮助开发者优化查询性能。索引类似于图书馆的分类系统,能快速定位数据行,极大提高检索效率。
60 8
|
3月前
|
SQL 分布式计算 关系型数据库
Hadoop-21 Sqoop 数据迁移工具 简介与环境配置 云服务器 ETL工具 MySQL与Hive数据互相迁移 导入导出
Hadoop-21 Sqoop 数据迁移工具 简介与环境配置 云服务器 ETL工具 MySQL与Hive数据互相迁移 导入导出
125 3
|
3月前
|
Oracle 关系型数据库 MySQL
Mysql(1)—简介及Windows环境下载安装
MySQL 是一个流行的关系型数据库管理系统(RDBMS),基于 SQL 进行操作。它由瑞典 MySQL AB 公司开发,后被 Sun Microsystems 收购,现为 Oracle 产品。MySQL 是最广泛使用的开源数据库之一,适用于 Web 应用程序、数据仓库和企业应用。
78 2
|
3月前
|
存储 SQL 关系型数据库
MySQL 存储过程错误信息不打印在控制台
MySQL 存储过程错误信息不打印在控制台
95 1
|
3月前
|
存储 关系型数据库 MySQL
MySQL 如何存储地理信息
MySQL 如何存储地理信息
315 1
|
3月前
|
SQL 存储 关系型数据库
mysql 数据库空间统计sql
mysql 数据库空间统计sql
57 0
|
5月前
|
存储 关系型数据库 MySQL
[mysql]MGR简介与部署
[mysql]MGR简介与部署
AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等