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

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

news 2025/2/24 22:15: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

  1. 收集方式：

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

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

  2. 动态采样：

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

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

                  

MySQL

  1. 收集方式：

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

    • 当表数据变化超过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 亿条记录被修改时，统计信息会被自动更新。

                 

（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开销。

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

  • 使用场景：

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

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

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

    2. 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开销。

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

  • 使用场景：

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

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

    2. 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

参数含义：表示是否InnoDB在（如SHOW TABLE STATUS）或访问INFORMATION_SCHEMA.TABLES或INFORMATION_SCHEMA.STATISTICS）操作期间更新统计信息。

默认值：OFF

作用：保留禁用的设置可以提高具有大量表或索引的模式的访问速度。它还可以提高涉及InnoDB表的查询的执行计划的稳定性。

此选项仅在优化器统计信息配置为非持久性时适用。当innodb_stats_persistent为off（默认on，启用持久化统计信息）时生效。或者使用STATS_PERSISTENT=0创建或修改单个表时，优化器统计信息不会被持久化到磁盘。在关闭持久化统计信息时，是否在show table status/查看information_schema的TABLES，STATISTICS表时更新统计信息（亲测关闭innodb_stats_persistent=off，在设置innodb_stats_on_metadata为on或者off下都使用show table status/查看information_schema的TABLES、STATISTICS表也不会更新统计信息，了解即可，生产环境必须开启持久化统计信息也是默认选项）

              

二、innodb_stats_transient_sample_pages

参数含义：表示每次随机采样页的数量

默认值：8

作用：当innodb_stats_persistent = 0时，innodb_stats_transient_sample_pages的值会影响所有InnoDB表和索引的索引采样。更改索引样本大小时，请注意以下潜在的重大影响：

像1或2这样的小值可能导致基数估计不准确。

增加innodb_stats_transient_sample_pages值可能需要更多磁盘读取。远大于8（例如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持久化和非持久化统计信息的介绍到这里就结束啦！如果你们觉得这篇文章还不错，那就不要吝啬你们的大拇指👍，一个小小的点赞，对我来说就是莫大的鼓励和支持💪


http://www.mrgr.cn/news/92135.html

相关文章：

  • Java 使用websocket
  • ClickHouse系列之ClickHouse安装
  • Linux上使用dify构建RAG
  • 第9章:LangChain结构化输出-示例4(基于大模型从自然语言中提取POJO)
  • vue：vite 代理服务器 proxy 配置
  • Go入门之struct
  • nodejs：vue 3 + vite 作为前端，将 html 填入＜iframe＞，在线查询英汉词典
  • Python游戏编程之赛车游戏6-2
  • 一文详解U盘启动UEFI/Legacy方式以及GPT/MBR关系
  • k8s集群部署
  • Linux常见问题
  • 【C语言】第八期——指针、二维数组与字符串
  • 关于order by的sql注入实验
  • 【Python LeetCode 专题】树
  • Ubuntu 22.04 一键部署MinerU1.1.0
  • dockerfile构建haproxy
  • superset
  • 本地部署AI模型 --- DeepSeek（一）
  • Day9 25/2/22 SAT
  • 玩机日记 13.5 群晖部署AList并配置SSL，安装opkg，使用rclone挂载到本地