当前位置：首页 > news >正文

【MySQL篇】持久化和非持久化统计信息的深度剖析(含analyze命令和mysqlcheck工具两种收集方式)

news 来源：原创 2025/9/16 22:42:58

💫《博主介绍》：✨又是一天没白过，我是奈斯，从事IT领域✨

💫《擅长领域》：✌️擅长阿里云AnalyticDB for MySQL(分布式数据仓库)、Oracle、MySQL、Linux、prometheus监控；并对SQLserver、NoSQL(MongoDB)有了解✌️

💖💖💖大佬们都喜欢静静的看文章，并且也会默默的点赞收藏加关注💖💖💖

哈喽各位小伙伴，好久不见，甚是想念！今天这篇文章，我们回归 MySQL 的世界。作为最流行的开源数据库，MySQL 凭借其社区版不错的性能，成为了众多公司的首选数据库，稳稳的位居TOP 2的位置。

博主作为一名官方文档手册爱好者，从最初阅读 Oracle 官方文档手册，到后来研究阿里云的 AnalyticDB for MySQL（分布式数据仓库）官方文档手册，再到如今深入 MySQL 官方文档手册，每一款数据库产品都有其独特的使用场景。在阅读到 MySQL 统计信息这一知识点时，博主觉得这对于想深入研究数据库优化原理的小伙伴来说非常重要。因此，结合官方文档和大模型DeepSeek-V3 的见解（DeepSeek还很强大的，给个大大的点赞 😃 ），博主撰写了这篇关于 MySQL 统计信息的博客。

众所周知，优化器是 SQL 执行过程中的核心组件，它通过分析统计信息，为每条 SQL 语句选择最高效的执行路径。而这些统计信息对于优化器的决策具有决定性的影响。因此，了解和掌握统计信息对于数据库的性能调优至关重要。接下来，我们将深入探讨统计信息的相关知识，帮助大家更好地理解并优化自己的数据库性能。

特别说明💥：本篇文章部分知识点均来源于 MySQL 公开可查的官方文档手册和大模型DeepSeek-V3 的见解，并结合了我个人的理解和案例演示。如有冲突，请联系，会立即处理。转载请标明出处😄

官方文档对统计信息的详细介绍（MySQL8.0）：

https://dev.mysql.com/doc/refman/8.0/en/innodb-performance-optimizer-statistics.html

1.1 持久化统计信息（既innodb_stats_persistent=ON，默认on，生产必须持久化）

（1）相关参数

（2）配置每张表的统计信息参数

案例一：create表时配置表的持久化统计信息

（3）查看统计信息

（4）手动收集统计信息

4.1 analyze方式收集，oracle也支持（analyze是单表收集统计信息）

案例一：计算ANALYZE TABLE复杂性（消耗的读取）

4.2 mysqlcheck工具方式收集（mysqlcheck工具是全表全库收集统计信息）

（5）8.0版本直方图的最新变化

（6）解决统计信息差别较大的问题（执行计划受统计信息影响，统计信息不准会导致执行计划不准）

案例一：通过设置STATS_SAMPLE_PAGES或者设置innodb_stats_persistent_sample_pages解决统计信息不准问题

1.2 非持久化统计信息（既innodb_stats_persistent=OFF，默认on，不推荐使用仅了解）

（1）相关参数

（2）设置非持久化统计信息的两种方式

一、全局变量（影响所有表）

二、表级参数（只影响设置的表）

那让我们开始今天统计信息的介绍。

MySQL 统计信息是指数据库通过采样和统计分析得出的表、索引的相关数据，例如表的记录数、聚集索引的页数、字段的基数（Cardinality）等。在生成执行计划时，MySQL 优化器会依赖这些统计信息进行估算，从而选择出代价最低（或开销最小）的执行计划。需要注意的是，MySQL 支持的索引统计信息相对有限，且不同存储引擎收集统计信息的方式也有所不同。

有趣的是，MySQL 官方文档对统计信息的概念介绍非常简略（官方文档链接已附上，感兴趣的小伙伴可以自行查阅）。不过，对于接触过其他数据库（如 Oracle）的小伙伴来说，理解这个概念应该并不困难。

相较于其他数据库，MySQL 的统计信息无法手动删除，并且在 MySQL 8.0 之前的版本中，是没有直方图（Histogram）这一功能的。优化器（Optimizer）会根据统计信息为每个 SQL 语句选择最优的执行计划，而这些执行计划的质量直接受到统计信息准确性的影响（执行计划受统计信息影响）。

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

Oracle和MySQL统计信息的区别：

Oracle

收集方式：

统计信息在特定时间手动收集，不是自动收集。

使用DBMS_STATS包手动收集全实例的统计信息。

动态采样：

当对象没有统计信息时，通过动态采样技术选择执行计划。

默认动态采样级别为2，采样对象的64个数据块进行分析。

MySQL

收集方式：

默认启用持久化统计信息（innodb_stats_persistent=ON），统计信息自动收集。

当表数据变化超过10%时，自动触发统计信息重新计算（innodb_stats_auto_recalc=ON）。

统计范围：

默认以表为单位收集和存储统计数据。

1.1 持久化统计信息（既innodb_stats_persistent=ON，默认on，生产必须持久化）

持久化统计信息在数据库重启统计信息不丢失，统计信息会被持久化到物理表中，会给出最优的执行计划，稳定和精确，对于大表也节省了收集统计信息的所需资源。5.6.6开始默认使用了持久化统计信息。MySQL 统计信息流程图图形化表示：

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

1.innodb_stats_auto_recalc 为 ON：

当这个参数设置为 ON 时，InnoDB 会自动重新计算表的统计信息。触发条件是表中大约 10% 的行发生了更改（插入、更新、删除等操作）。
这种自动更新机制是为了确保统计信息不会过于陈旧，从而影响查询优化器的决策。

2.DDL 语句：

当执行 CREATE TABLE、CREATE INDEX、ALTER TABLE、TRUNCATE TABLE 等涉及数据修改的 DDL 语句时，MySQL 会自动更新相关表的统计信息。
这是因为这些操作通常会显著改变表的结构或数据分布，因此需要重新计算统计信息以反映最新的状态。

3.手动更新统计信息：

你可以通过执行 ANALYZE TABLE tablename 来手动更新表的统计信息。
需要注意的是，执行 ANALYZE TABLE 时，MySQL 会对表加一个读锁，因此在更新过程中可能会影响其他查询的并发性能。

4.dict_stats_thread 线程：

InnoDB 有一个后台线程 dict_stats_thread，专门负责处理统计信息的更新。
这个线程会根据需要自动更新统计信息，确保统计信息的准确性和及时性。

5.行变更阈值：

当表中发生变更的行数超过一定阈值时，统计信息也会被自动更新。
具体来说，如果变更的行数超过 16 + n_rows / 16（即大约 6.25% 的行数），或者表中有超过 1/6 的行被修改，或者表中有超过 20 亿条记录被修改时，统计信息会被自动更新。

（1）相关参数

1. innodb_stats_persistent：

参数含义：是否启用持久化统计信息功能。

默认值：ON

作用：

控制统计信息是否持久化到物理表中。

在早期版本的MySQL中，统计信息不持久化，而在新版本中，持久化统计信息是默认选项。

启用后，统计信息更加稳定和精确，尤其对于大表，可以节省收集统计信息所需的资源。

如果关闭（OFF），统计信息可能会频繁重新计算，导致查询执行计划不稳定。

建议：

对于生产环境，建议保持默认值ON，以确保统计信息的稳定性和准确性。

2. innodb_stats_auto_recalc：

参数含义：是否自动触发更新统计信息。

默认值：ON

触发阈值：当表数据变化超过10%时，自动触发统计信息的重新计算。

作用：

InnoDB会长期追踪每张表的行数，判断更新的记录是否超过表记录总数的10%。如果超过，则将表加入后台的recalc pool中，异步重新计算统计信息。

由于是异步操作，统计信息的重新计算可能会有延迟（通常几秒钟）。

如果在对表进行重要修改后需要立即获取最新统计信息，可以手动执行ANALYZE TABLE。

注意事项：

如果禁用此参数，需要在索引列发生重大更改后，手动执行ANALYZE TABLE以确保统计信息的准确性。

此参数仅对启用innodb_stats_persistent的表生效。

在CREATE TABLE或ALTER TABLE时，可以通过STATS_AUTO_RECALC语法指定是否启用自动重新计算。

建议：

对于频繁更新的表，建议保持默认值ON，以减少手动维护的成本。

3. innodb_stats_persistent_sample_pages：

参数含义：持久化统计信息采样的索引页数。

默认值：20

作用：

控制统计信息计算时采样的索引页数。

增加该值可以提高统计信息的准确性，从而优化查询执行计划，但会增加ANALYZE TABLE的I/O开销。

该值越大，统计信息越精确，但耗时越长；该值越小，统计信息越不精确，但耗时越短。

使用场景：

统计信息不准确，优化器选择次优计划：

如果发现统计信息不准确，可以逐步增加innodb_stats_persistent_sample_pages的值，直到统计信息足够精确。

但需注意，过高的值会导致ANALYZE TABLE执行缓慢。

ANALYZE TABLE执行太慢：

如果ANALYZE TABLE执行时间过长，可以适当减少innodb_stats_persistent_sample_pages的值。

但需注意，过低的值可能导致统计信息不准确，进而影响查询性能。

建议：

根据表的规模和查询性能需求，动态调整该值，找到精度和性能的平衡点。

4. innodb_stats_include_delete_marked：

参数含义：是否包含标记删除的行。

默认值：OFF

作用：

在MySQL 5.7.16中引入此参数。

默认情况下，如果未提交的事务中有标记删除的行，InnoDB在收集统计信息时会排除这些行。

这可能导致除READ UNCOMMITTED之外的事务隔离级别下，查询执行计划不准确。

启用此参数后，InnoDB在计算统计信息时会包含标记删除的行，从而避免执行计划不准确的问题。

建议：

如果系统中存在大量未提交的删除操作，建议启用此参数，以确保统计信息的准确性。

（2）配置每张表的统计信息参数

innodb_stats_persistent、innodb_stats_auto_recalc和innodb_stats_persistent_sample_pages是全局配置选项。若要覆盖这些系统范围的设置并为各个表配置统计信息参数，可以在CREATE TABLE或ALTER TABLE语句中定义STATS_PERSISTENT、STATS_AUTO_RECALC和STATS_SAMPLE_PAGES子句。

1. STATS_PERSISTENT：

含义：指定是否为InnoDB表启用持久化统计信息。

设置值：

DEFAULT：表示表的持久化统计信息设置由全局参数innodb_stats_persistent决定。

1：启用表的持久化统计信息。

0：关闭表的持久化统计信息。

作用：

启用后，表的统计信息会持久化到物理表中，统计信息更加稳定和精确。

关闭后，统计信息不会持久化，可能会频繁重新计算，导致查询执行计划不稳定。

建议：

对于需要稳定统计信息的大表，建议设置为1。

如果表的统计信息变化频繁且对性能要求较高，可以设置为0。

2. STATS_AUTO_RECALC：

含义：指定是否自动触发InnoDB表的持久统计信息重新计算。

设置值：

DEFAULT：表示表的自动重新计算设置由全局参数innodb_stats_auto_recalc决定。

1：当表中10%的数据发生更改时，自动重新计算统计信息。

0：禁用自动重新计算功能。

作用：

启用后，InnoDB会自动监控表的数据变化，当变化超过10%时，触发统计信息的重新计算。

禁用后，需要手动执行ANALYZE TABLE来更新统计信息。

建议：

对于数据变化频繁的表，建议设置为1，以减少手动维护成本。

对于数据变化较少的表，可以设置为0，以降低后台计算的开销。

3. STATS_SAMPLE_PAGES：

含义：指定在估计索引列的基数和其他统计信息时要采样的索引页数。

设置值：

DEFAULT：表示采样的索引页数由全局参数innodb_stats_persistent_sample_pages决定。

自定义值：可以指定具体的采样页数。

作用：

控制统计信息计算时采样的索引页数。

增加该值可以提高统计信息的准确性，但会增加ANALYZE TABLE的I/O开销。

减少该值可以降低计算开销，但可能导致统计信息不准确。

使用场景：

统计信息不准确，优化器选择次优计划：

如果发现统计信息不准确，可以逐步增加STATS_SAMPLE_PAGES的值，直到统计信息足够精确。

ANALYZE TABLE执行太慢：

如果ANALYZE TABLE执行时间过长，可以适当减少STATS_SAMPLE_PAGES的值。

建议：

根据表的规模和查询性能需求，动态调整该值，找到精度和性能的平衡点。

案例一：create表时配置表的持久化统计信息

CREATE TABLE `liu_mysqloltp_ywcs_table` (
`id` int(8) NOT NULL auto_increment,
`data` varchar(255),
`date` datetime,
PRIMARY KEY  (`id`),
INDEX `DATE_IX` (`date`)
) ENGINE=InnoDB,STATS_PERSISTENT=1,STATS_AUTO_RECALC=1,STATS_SAMPLE_PAGES=25;

（3）查看统计信息

table statistics相关视图：

mysql> select * from mysql.innodb_table_stats where table_name='表名';

database_name：数据库名

table_name：表名

last_update：统计信息最后一次更新时间，sql执行计划受统计信息影响。

n_rows：表的行数

clustered_index_size：聚集索引的页的数量

sum_of_other_index_sizes：其他索引的页的数量

mysql> select * from information_schema.tables where table_name='表名';

mysql> select * from information_schema.statistics where table_name='表名';

注意：mysql.innodb_table_stats会在持久化统计信息下自动更新，而information_schema.tables和information_schema.statistics不会自动更新需要手动执行analyze table或者mysqlcheck命令方式收集，所以统计信息以按照mysql.innodb_table_stats表的信息为准。

index statistics相关视图：

mysql> select * from mysql.innodb_index_stats where table_name='表名'; ---会在持久化统计信息下自动更新

database_name：数据库名

table_name：表名

index_name：索引名

last_update：统计信息最后一次更新时间，sql执行计划受统计信息影响。

stat_name：统计信息名

stat_value：统计信息的值

sample_size：采样大小

stat_description：类型说明

（4）手动收集统计信息

4.1 analyze方式收集，oracle也支持（analyze是单表收集统计信息）

在 MySQL 中，InnoDB 和 MyISAM 存储引擎都支持通过执行 ANALYZE TABLE tablename 来收集表和索引的统计信息。这些统计信息用于优化查询执行计划，确保查询优化器能够基于准确的数据分布做出最佳决策（单表9亿行的收集秒级完成，即使整个实例有2T，并且上千张表，通过mysqlcheck工具进行所有库的收集，也是几分钟就完成，亲测）

然而，除非执行计划明显不准确，否则不建议轻易执行 ANALYZE TABLE。原因如下：

性能影响：

对于大表，ANALYZE TABLE 可能会对性能产生一定影响。不过，根据实际测试，即使单表有 9 亿行数据，收集统计信息也可以在秒级完成。对于整个实例（例如 2T 数据、上千张表），使用 mysqlcheck 工具对所有库进行统计信息收集，通常也只需几分钟即可完成。

锁机制：

在执行 ANALYZE TABLE 时，InnoDB 会持有表的 读锁（READ ONLY 锁），这可能会导致用户对该表的写入、更新和删除操作被短暂阻塞。
此外，ANALYZE TABLE 还会将表从 Table Definition Cache 中刷出，因此需要获取一个 Flush Lock。如果此时有长事务正在使用该表，则必须等待长事务结束后才能继续执行。

数据字典更新：

在 MySQL 8.0 中，ANALYZE TABLE 会更新数据字典中的统计信息表。如果 innodb_read_only 开关被打开，可能会导致 ANALYZE TABLE 执行失败。

注意：ANALYZE、CHECK、OPTIMIZE、ALTER TABLE执行期间将对表进行锁定，因此一定注意要在数据库不繁忙的时候执行相关的操作。

5.7语法：

ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
TABLE tbl_name [, tbl_name] ...

8.0语法（8.0中支持了直方图统计信息，因此analyze table还扩充了Histogram语法）：

ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
TABLE tbl_name [, tbl_name] ...ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]TABLE tbl_nameUPDATE HISTOGRAM ON col_name [, col_name] ...[WITH N BUCKETS]ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]TABLE tbl_nameDROP HISTOGRAM ON col_name [, col_name] ...

InnoDB表的ANALYZE TABLE复杂性（消耗的读取）：

1)采样的页数，由innodb_stats_persistent_sample_pages定义。

2)表中索引列的数量（由多个数相加而成，参考下面案例）。

3)分区数量。如果表没有分区，则分区数被视为1。

总结：ANALYZE TABLE复杂性度=innodb_stats_persistent_sample_pages * 表中索引列的数量（多个数相加而成） * 分区数 * innodb_page_size

通常结果值越大，ANALYZE InnoDB TABLE的执行时间越长。

innodb_stats_persistent_sample_pages定义在全局级别采样的页数。要设置单个表的采样页数，请使用带有CREATE TABLE或ALTER TABLE的STATS_SAMPLE_PAGES选项。

如果innodb_stats_persistent = OFF，则采样的页数由innodb_stats_transient_sample_pages定义。

案例一：计算ANALYZE TABLE复杂性（消耗的读取）

ANALYZE TABLE复杂性度=innodb_stats_persistent_sample_pages * 表中索引列的数量（多个数相加而成） * 分区数 * innodb_page_size
O(n_sample
* (n_cols_in_uniq_i
+ n_cols_in_non_uniq_i
+ n_cols_in_pk * (1 + n_non_uniq_i))
* n_part * innodb_page_size)

n_sample：是取样的页数(定义为innodb_stats_persistent_sample_pages)

n_cols_in_uniq_i：所有唯一索引中所有列的总数(不包括主键列)

n_cols_in_non_uniq_i：所有非唯一索引中所有列的总数

n_cols_in_pk：主键中的列数(如果没有定义主键，InnoDB在内部创建单列主键)

n_non_uniq_i：表中非唯一索引的数目

n_part：是分区的数量。如果没有定义分区，则该表被视为单个分区。

innodb_page_size：innodb每个页的大小是16K，且不可更改

SQL> CREATE TABLE t (a INT,b INT,c INT,d INT,e INT,f INT,g INT,h INT,PRIMARY KEY (a, b),UNIQUE KEY i1uniq (c, d),KEY i2nonuniq (e, f),KEY i3nonuniq (g, h)
);SQL> SELECT index_name, stat_name, stat_descriptionFROM mysql.innodb_index_stats WHEREdatabase_name='test' ANDtable_name='t' ANDstat_name like 'n_diff_pfx%';n_cols_in_uniq_i：所有唯一索引中不包括主键列的所有列的总数为2（c和d）
n_cols_in_non_uniq_i：所有非唯一索引中所有列的总数，为4（e，f，g和h）
n_cols_in_pk：主键中的列数为2（a和b）
n_non_uniq_i：表中非唯一索引的数量是2（i2nonuniq和i3nonuniq））
n_part：分区数，是1。

那么读取t表：

innodb_stats_persistent_sample_pages=20

n_cols_in_uniq_i =2

n_cols_in_non_uniq_i=4

n_cols_in_pk=2

n_non_uniq_i=2

n_part=1

innodb_page_size=16kb

估计表t读取20*(2+4+2*(1+2))*1*16kb=3840kb，为3.75M

4.2 mysqlcheck工具方式收集（mysqlcheck工具是全表全库收集统计信息）

mysqlcheck是用来检查、修复、优化、分析表。只有在数据库运行的状态下才可运行，意味着不用停止服务操作。

mysqlcheck其实就是CHECK TABLE(检查表), REPAIR TABLE(修复表), ANALYZE TABLE(分析表)以及OPTIMIZE TABLE(优化表)的便捷操作集合，利用指定参数将对于的SQL语句发送到数据库中进行执行。同样对于那些存储引擎的的支持，也受对于表维护SQL语句的限制(如check 则不支持MEMORY表, repair 则不支持 InnoDB表)

注意：ANALYZE、CHECK、OPTIMIZE、ALTER TABLE执行期间将对表进行锁定，因此一定注意要在数据库不繁忙的时候执行相关的操作。

mysqlcheck参数：

参数选项	描述
-A, --all-databases	选择所有的库
-B, --databases	选择多个库
-a, --analyze	分析表ANALYZE TABLE
-c, --check	检查表CHECK TABLE
-C, --check-only-changed	最后一次检查之后变动的表CHECK TABLE
-m, --medium-check	近似完全检查，速度比--extended稍快CHECK TABLE
-o, --optimize	优化表OPTIMIZE TABLE
--auto-repair	自动修复表
-g, --check-upgrade	检查表是否有版本变更，可用 auto-repair修复
-F, --fast	只检查没有正常关闭的表
-f, --force	忽悠错误，强制执行
-e, --extended	表的百分百完全检查，速度缓慢
-q, --quick	最快的检查方式，在repair 时使用该选项，则只会修复 index tree
-r, --repair	修复表REPAIR TABLE
-s, --silent	只打印错误信息
-V, --version	显示版本

收集库所有表的统计信息：

收集liudbywcs库：

[root@mgr1 ~]# mysqlcheck -uroot -p123456 -S /liu_data/mysql8.0/data/3306/liu.sock --analyze --databases liudbywcs

收集所有库：

[root@mgr1 ~]# mysqlcheck -uroot -p123456 -S /liu_data/mysql8.0/data/3306/liu.sock --analyze --all-databases
SQL> select * from mysql.innodb_table_stats where database_name='liudbywcs';   ---liudbywcs库所有表的统计信息更新为最新

（5）8.0版本直方图的最新变化

MySQL 8.0 版本中对直方图（Histogram）统计信息进行了多项改进和优化，进一步提升了查询优化器的决策能力。以下是 MySQL 8.0 版本中直方图的主要变化和改进：

直方图类型的支持：

MySQL 8.0 引入了两种类型的直方图：

等频直方图（Singleton Histogram）：
适用于列中唯一值较少的情况，每个桶（bucket）只包含一个唯一值及其频率。

等高直方图（Equi-Height Histogram）：
适用于列中唯一值较多的情况，每个桶包含一定范围的值，并记录该范围内的频率。

这两种直方图类型使得 MySQL 能够更灵活地适应不同类型的数据分布，从而提供更准确的统计信息。

直方图的持久化：

在 MySQL 8.0 之前，直方图信息是临时存储在内存中的，重启后会丢失。而在 8.0 版本中，直方图信息可以持久化到磁盘，这意味着：

直方图信息在数据库重启后仍然有效。

减少了重新生成直方图的开销，特别是在大数据量的场景下。

通过innodb_stats_persistent参数可以控制统计信息（包括直方图）是否持久化。

直方图的自动更新：

MySQL 8.0 引入了直方图的自动更新机制。当表中的数据发生较大变化时（例如大量插入、删除或更新操作），MySQL 会自动重新计算直方图，以确保统计信息的准确性。

这一特性通过innodb_stats_auto_recalc参数控制，默认情况下是启用的。

直方图的创建和管理：

在 MySQL 8.0 中，直方图的创建和管理更加灵活：

创建直方图：
使用ANALYZE TABLE语句可以为指定列创建直方图。例如：
ANALYZE TABLE table_name UPDATE HISTOGRAM ON column_name;
删除直方图：
使用ANALYZE TABLE语句可以删除直方图。例如：
ANALYZE TABLE table_name DROP HISTOGRAM ON column_name;
查看直方图信息：
可以通过information_schema.COLUMN_STATISTICS表查看直方图的详细信息。

直方图对查询优化的影响：

直方图的引入和优化使得 MySQL 优化器能够更准确地估算查询的选择性（Selectivity），从而制定更高效的执行计划。例如：

对于非均匀分布的数据（如某些值出现频率极高），直方图可以帮助优化器更好地选择索引或决定是否使用索引。

在复杂查询中，直方图可以帮助优化器更准确地估算连接（Join）和过滤（Filter）操作的成本。

性能提升：

直方图的改进显著提升了 MySQL 在处理复杂查询时的性能，特别是在以下场景中：

数据分布不均匀的列。

多列查询（如范围查询、等值查询）中需要更准确的选择性估算。

大数据量场景下，优化器需要更精确的统计信息来制定执行计划。

注意事项：

直方图的创建和维护可能会带来一定的开销，特别是在数据量非常大的情况下。因此，建议在高选择性列（如经常用于查询条件的列）上创建直方图。

直方图的信息是基于采样数据生成的，因此可能存在一定的误差。在极端情况下，可能需要手动更新直方图以确保其准确性。

总结：

MySQL 8.0 版本中对直方图的改进使得统计信息更加准确和可靠，从而显著提升了查询优化器的决策能力。通过合理使用直方图，可以更好地优化数据库性能，特别是在处理复杂查询和大数据量场景时。

（6）解决统计信息差别较大的问题（执行计划受统计信息影响，统计信息不准会导致执行计划不准）

如果自动更新持久化统计信息后发现与实际count(*)数据量差距较大，可考虑增加表采样的数据页，两种方式修改：

修改一：全局变量（影响所有表）

innodb_stats_persistent_sample_pages默认20个页面。持久化统计信息采样的页数。分析配置的页数，优化器根据统计信息给出执行计划

缺点：过多地增加innodb_stats_persistent_sample_pages可能会导致ANALYZE TABLE运行缓慢。

该值设置的越大，统计出的n_rows值越精确，但是统计耗时也就最久

该值设置的越小，统计出的n_rows值越不精确，但是统计耗时特别少。

修改二：CREATE/ALTER表的参数（只影响设置的表）

ALTER TABLE TABLE_NAME STATS_SAMPLE_PAGES=40; ---经测试，此处STATS_SAMPLE_PAGES的最大值是65535，超出会报错。

STATS_SAMPLE_PAGES：指定在估计索引列的基数和其他统计信息(例如由ANALYZE TABLE计算的统计信息)时要采样的索引页数。表示持久化统计信息采样的页数由innodb_stats_persistent_sample_pages配置选项确定。

案例一：通过设置STATS_SAMPLE_PAGES或者设置innodb_stats_persistent_sample_pages解决统计信息不准问题

1）创建表
SQL> create table liu_mysqloltp_ywcs_tb_700w like liu_mysqloltp_ywcs_tb;
SQL> insert into liu_mysqloltp_ywcs_tb_700w select * from liu_mysqloltp_ywcs_tb limit 7000000;SQL> select * from mysql.innodb_table_stats where table_name='liu_mysqloltp_ywcs_tb_700w';
---liu_mysqloltp_ywcs_tb_700w真实有700万行数据，由于innodb_stats_persistent_sample_pages进行自动持久化统计信息采样只采集20页，那么就会有误差
2）设置STATS_SAMPLE_PAGES：
SQL> ALTER TABLE liu_mysqloltp_ywcs_tb_700w STATS_SAMPLE_PAGES=65535;    ---此处STATS_SAMPLE_PAGES的最大值是65535，超出会报错。
SQL> analyze table liu_mysqloltp_ywcs_tb_700w;SQL> select * from mysql.innodb_table_stats where table_name='liu_mysqloltp_ywcs_tb_700w';    
---收集单表的STATS_SAMPLE_PAGES的最大值是65535个页，超出会报错。65535页还是不能给出准确的行数
3）设置innodb_stats_persistent_sample_pages：

注意：ANALYZE TABLE复杂性度=innodb_stats_persistent_sample_pages * 表中索引列的数量（多个数相加而成） * 分区数 * innodb_page_size，那么过多地增加innodb_stats_persistent_sample_pages，ANALYZE InnoDB TABLE的执行时间越长。
SQL> ALTER TABLE liu_mysqloltp_ywcs_tb_700w STATS_SAMPLE_PAGES=default;                 ---恢复默认STATS_SAMPLE_PAGES，由innodb_stats_persistent_sample_pages配置选项确定
SQL> show variables like 'innodb_stats_persistent_sample_pages';     ---默认采集20页
算出innodb_stats_persistent_sample_pages最合适的值。公式：innodb_stats_persistent_sample_pages=ANALYZE TABLE复杂性度(大小)/表中索引列的数量(多个数相加而成)/分区数/innodb_page_size
SQL> show create table liu_mysqloltp_ywcs_tb_700w\G;SQL> SELECT index_name, stat_name, stat_descriptionFROM mysql.innodb_index_stats WHEREdatabase_name='liudbywcs' ANDtable_name='liu_mysqloltp_ywcs_tb_700w' ANDstat_name like 'n_diff_pfx%';
详细算法参考上面（4）手动收集统计信息的案例一：计算ANALYZE TABLE复杂性（消耗的读取）

n_cols_in_uniq_i：所有唯一索引中不包括主键列的所有列的总数为0

n_cols_in_non_uniq_i：所有非唯一索引中所有列的总数，为0

n_cols_in_pk：主键中的列数为1（id）

n_non_uniq_i：表中非唯一索引的数量是0

n_part：分区数，是1。

那么读取liu_mysqloltp_ywcs_tb_700w表：

n_cols_in_uniq_i =0

n_cols_in_non_uniq_i=0

n_cols_in_pk=1

n_non_uniq_i=0

n_part=1

innodb_page_size=16kb

innodb_stats_persistent_sample_pages=(1611Mx1024)/(0+0+1*(1+0))/1/16kb=103104
SQL> set global innodb_stats_persistent_sample_pages=103104; 
SQL> analyze table liu_mysqloltp_ywcs_tb_700w;SQL> select * from mysql.innodb_table_stats where table_name='liu_mysqloltp_ywcs_tb_700w'; 给出了最准确的统计信息

1.2 非持久化统计信息（既innodb_stats_persistent=OFF，默认on，不推荐使用仅了解）

非持久化统计信息存储在内存里，如果数据库重启统计信息将丢失，在下一次访问表时重新计算。会导致频繁地重新计算统计信息，这可能会导致查询执行计划的变化。不推荐使用也不是默认值。

当innodb_stats_persistent = OFF或使用STATS_PERSISTENT = 0创建或更改单张表时，统计信息不会保留到磁盘。相反统计信息存储在内存中，并在服务器关闭时丢失。某些业务和某些条件下也会定期更新统计数据。

非持久化统计信息的自动更新机制（前提innodb_stats_on_metadata设置为on，默认off）：

1.手动更新统计信息：

通过执行 ANALYZE TABLE tablename 可以手动更新统计信息。需要注意的是，执行过程中会对表加 读锁（READ ONLY 锁），可能会短暂阻塞写入操作。

2.innodb_stats_on_metadata设置为 ON：

当 innodb_stats_on_metadata=ON 时，执行以下操作会触发统计信息的重新计算：

SHOW TABLE STATUS

SHOW INDEX

查询 INFORMATION_SCHEMA 下的 TABLES 或 STATISTICS 表。

3.启用 --auto-rehash 选项：

在 MySQL 客户端中，默认启用了 --auto-rehash 选项。该选项会导致客户端打开所有 InnoDB 表，而打开表的操作会触发统计信息的重新计算。

4.表第一次被打开：

当表第一次被打开时，MySQL 会自动计算并更新其统计信息。

5.表中数据发生较大变化：

如果自上一次更新统计信息后，表中超过 1/16 的数据发生了修改（例如插入、更新、删除等操作），统计信息会被自动更新。

非持久化统计信息的缺点：非持久化统计信息的主要缺点在于其 临时性 和 不可靠性：

统计信息不会持久化存储，因此在数据库重启后，所有统计信息都会丢失。

如果数据库重启后有大量表需要重新计算统计信息，可能会对实例性能造成较大影响，尤其是在高并发或大数据量的场景下。

总结：

由于非持久化统计信息的上述缺点，目前在生产环境中更推荐使用持久化统计信息（Persistent Statistics）。持久化统计信息会将统计信息存储到磁盘中，即使数据库重启也不会丢失，从而避免了频繁重新计算统计信息带来的性能开销。

（1）相关参数

一、innodb_stats_on_metadata：

参数含义：控制是否在以下操作期间更新统计信息：

执行SHOW TABLE STATUS。

访问INFORMATION_SCHEMA.TABLES或INFORMATION_SCHEMA.STATISTICS。

默认值：OFF

作用：

当禁用时（OFF），可以提高具有大量表或索引的模式的访问速度。

同时，禁用该参数还可以提高涉及InnoDB表的查询执行计划的稳定性。

适用场景：

仅在优化器统计信息配置为非持久性时生效（即innodb_stats_persistent=OFF或表级设置STATS_PERSISTENT=0）。

如果启用了持久化统计信息（innodb_stats_persistent=ON），该参数无效。

注意事项：

即使关闭持久化统计信息（innodb_stats_persistent=OFF），设置innodb_stats_on_metadata为ON或OFF，在执行SHOW TABLE STATUS或访问INFORMATION_SCHEMA表时，统计信息也不会更新。

生产环境建议：必须开启持久化统计信息（innodb_stats_persistent=ON），这是默认选项。

二、innodb_stats_transient_sample_pages：

参数含义：控制每次随机采样的索引页数量。

默认值：8

作用：

当innodb_stats_persistent=OFF时，该参数会影响所有InnoDB表和索引的索引采样。

修改该值可能会对系统性能产生显著影响：

小值（如1或2）：

可能导致基数估计不准确，从而影响查询优化器的执行计划选择。

大值（如100）：

可能需要更多的磁盘读取，导致性能下降。

可能导致打开表或执行SHOW TABLE STATUS的时间显著增加。

优化器可能会根据不同的索引选择性估计选择非常不同的查询计划。

建议：

在非持久化统计信息模式下，根据表的规模和查询性能需求，动态调整该值。

避免设置过小或过大的值，以平衡统计信息的准确性和系统性能。

（2）设置非持久化统计信息的两种方式

一、全局变量（影响所有表）

innodb_stats_persistent

默认值：ON

作用：

控制是否启用持久化统计信息功能。

在早期版本的MySQL中，统计信息不持久化，而在新版本中，持久化统计信息是默认选项。

启用后，统计信息会被持久化到物理表中，统计信息更加稳定和精确，尤其对于大表，可以节省收集统计信息所需的资源。

如果关闭（OFF），统计信息可能会频繁重新计算，导致查询执行计划不稳定。

建议：

对于生产环境，建议保持默认值ON，以确保统计信息的稳定性和准确性。

二、表级参数（只影响设置的表）

STATS_PERSISTENT
含义：指定是否为InnoDB表启用持久化统计信息。

设置值：

DEFAULT：表示表的持久化统计信息设置由全局参数innodb_stats_persistent决定。

1：启用表的持久化统计信息。

0：关闭表的持久化统计信息。

作用：

启用后，表的统计信息会持久化到物理表中，统计信息更加稳定和精确。

关闭后，统计信息不会持久化，可能会频繁重新计算，导致查询执行计划不稳定。
使用方法：
在CREATE TABLE或ALTER TABLE语句中指定STATS_PERSISTENT参数。
例如：
CREATE TABLE example_table (id INT PRIMARY KEY,name VARCHAR(100)
) STATS_PERSISTENT=1;
或
ALTER TABLE example_table STATS_PERSISTENT=1;
建议：

对于需要稳定统计信息的大表，建议设置为1。

如果表的统计信息变化频繁且对性能要求较高，可以设置为0。

感谢各位集帅读到这里，经过3天的“苦思冥想”和“奋笔疾书”，关于MySQL持久化和非持久化统计信息的介绍到这里就结束啦！如果你们觉得这篇文章还不错，那就不要吝啬你们的大拇指👍，一个小小的点赞，对我来说就是莫大的鼓励和支持💪

1.1 持久化统计信息（既innodb_stats_persistent=ON，默认on，生产必须持久化）

（1）相关参数

（2）配置每张表的统计信息参数

案例一：create表时配置表的持久化统计信息

（3）查看统计信息

（4）手动收集统计信息

4.1 analyze方式收集，oracle也支持（analyze是单表收集统计信息）

案例一：计算ANALYZE TABLE复杂性（消耗的读取）

4.2 mysqlcheck工具方式收集（mysqlcheck工具是全表全库收集统计信息）

（5）8.0版本直方图的最新变化

（6）解决统计信息差别较大的问题（执行计划受统计信息影响，统计信息不准会导致执行计划不准）

案例一：通过设置STATS_SAMPLE_PAGES或者设置innodb_stats_persistent_sample_pages解决统计信息不准问题

1.2 非持久化统计信息（既innodb_stats_persistent=OFF，默认on，不推荐使用仅了解）

（1）相关参数

（2）设置非持久化统计信息的两种方式

一、全局变量（影响所有表）

二、表级参数（只影响设置的表）

相关文章：