mysql统计信息_概述MySQL统计信息

深碍√TFBOYSˉ_ 2023-01-11 03:54 167阅读 0赞

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;

写入数据如下：

![20180208094403.png][]

查看t1表的统计信息，需主要关注stat\_name和stat\_value字段

![20180208094411.png][]

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

本文标题: 概述MySQL统计信息

本文地址: http://www.cppcns.com/shujuku/mysql/220130.html

[20180208094403.png]: /images/20221119/2ee71a1433dc4135b4043696ec2aaa70.png
[20180208094411.png]: /images/20221119/846bf8c03f5f43118a442cfc4ee61b6a.png

mysql统计信息_概述MySQL统计信息

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 MySQL 5.5 统计信息收集

相关 MySQL 统计信息收集相关参数

相关 mysql统计信息_概述MySQL统计信息

相关 mysql 收集表的统计信息

相关 mysql 收集表的统计信息

相关 MySQL优化系列7-MySQL的统计信息

相关 Oracle统计信息

相关统计信息

相关 MySQL中的统计信息相关参数介绍

相关 python 统计MySQL表信息