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

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

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

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

特别说明💥：本篇文章部分理论性知识点均来源于版权归 MySQL 所有的官方公开文档手册和大模型 DeepSeek-V3 的见解，并结合了我个人的解读和案例演示。若需要调整，请联系，会尽快处理😄
  文章篇幅过长，CSDN阅读体验更佳：https://blog.csdn.net/naisiing/article/details/136292134?spm=1001.2014.3001.5502

官方文档对统计信息的详细介绍（MySQL8.0）：
https://dev.mysql.com/doc/refman/8.0/en/innodb-performance-optimizer-statistics.html

如果觉得文章对你有所帮助，欢迎点赞  收藏  加关注 

• 1、持久化统计信息（既innodb_stats_persistent=ON，默认on，生产必须持久化）
• 1.1 相关参数
• 1.2 配置每张表的统计信息参数
• 1.3 查看统计信息
• 1.4 手动收集统计信息
• 1.5 8.0版本直方图的最新变化
• 1.6 解决统计信息差别较大的问题（执行计划受统计信息影响，统计信息不准会导致执行计划不准）
• 2、非持久化统计信息（既innodb_stats_persistent=OFF，默认on，不推荐使用仅了解）
• 2.1 相关参数
• 2.2 设置非持久化统计信息的两种方式

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

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

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

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

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

Oracle和MySQL统计信息的区别：

Oracle

1. 收集方式：
1. 统计信息在特定时间手动收集，不是自动收集。
2. 使用DBMS_STATS包手动收集全实例的统计信息。
2. 动态采样：
1. 当对象没有统计信息时，通过动态采样技术选择执行计划。
2. 默认动态采样级别为2，采样对象的64个数据块进行分析。

MySQL

1. 收集方式：
1. 默认启用持久化统计信息（innodb_stats_persistent=ON），统计信息自动收集。
2. 当表数据变化超过10%时，自动触发统计信息重新计算（innodb_stats_auto_recalc=ON）。
2. 统计范围：
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 亿条记录被修改时，统计信息会被自动更新。

2. innodb_stats_auto_recalc：

• 参数含义： 是否自动触发更新统计信息。
• 默认值： ON
• 触发阈值： 当表数据变化超过10%时，自动触发统计信息的重新计算。
• 作用：

• 注意事项：

• 建议：

3. innodb_stats_persistent_sample_pages：

• 参数含义： 持久化统计信息采样的索引页数。
• 默认值： 20
• 作用：

• 使用场景：

• 建议：

4. innodb_stats_include_delete_marked：

• 参数含义： 是否包含标记删除的行（delete_marked）。
• 默认值： OFF
• 作用：

• 建议：

1.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表启用持久化统计信息。
• 设置值：

• 作用：

• 建议：

2. STATS_AUTO_RECALC：
• 含义： 指定是否自动触发InnoDB表的持久统计信息重新计算。
• 设置值：

• 作用：

• 建议：

3. STATS_SAMPLE_PAGES：

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

• 作用：

• 使用场景：

• 建议：

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

CREATE TABLE`liu_mysqloltp_ywcs_table` (
`id`int(8) NOTNULL 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;

1.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：类型说明

1.4 手动收集统计信息

一、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_name
    UPDATE HISTOGRAM ON col_name [, col_name] ...
        [WITH N BUCKETS]
 
ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
    TABLE tbl_name
    DROP 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),
UNIQUEKEY i1uniq (c, d),
KEY i2nonuniq (e, f),
KEY i3nonuniq (g, h)
);

SQL> SELECT index_name, stat_name, stat_description
       FROM mysql.innodb_index_stats WHERE
       database_name='test'AND
       table_name='t'AND
       stat_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

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

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

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

mysqlcheck参数：

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

收集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库所有表的统计信息更新为最新

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

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

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

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

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

缺点：

1. 过多地增加innodb_stats_persistent_sample_pages可能会导致ANALYZE TABLE运行缓慢。
2. 该值设置的越大，统计出的n_rows值越精确，但是统计耗时也就最久
3. 该值设置的越小，统计出的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_description
       FROM mysql.innodb_index_stats WHERE
       database_name='liudbywcs' AND
       table_name='liu_mysqloltp_ywcs_tb_700w' AND
       stat_name like 'n_diff_pfx%';

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=(1611M x 1024) / (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'; 
                       给出了最准确的统计信息

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 时，执行以下操作会触发统计信息的重新计算：

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

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

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

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

• 统计信息不会持久化存储，因此在数据库重启后，所有统计信息都会丢失。
• 如果数据库重启后有大量表需要重新计算统计信息，可能会对实例性能造成较大影响，尤其是在高并发或大数据量的场景下。

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

2.1 相关参数

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），这是默认选项。

2. innodb_stats_transient_sample_pages：

• 参数含义： 控制每次随机采样的索引页数量。
• 默认值： 8
• 作用：
• 当innodb_stats_persistent=OFF时，该参数会影响所有InnoDB表和索引的索引采样。
• 修改该值可能会对系统性能产生显著影响：

• 建议：
• 在非持久化统计信息模式下，根据表的规模和查询性能需求，动态调整该值。
• 避免设置过小或过大的值，以平衡统计信息的准确性和系统性能。

2.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持久化和非持久化统计信息的介绍到这里就结束啦！如果你们觉得这篇文章还不错，那就不要吝啬你们的大拇指👍，一个小小的点赞，对我来说就是莫大的鼓励和支持💪