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

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

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

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

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

博主作为一名官方文档手册爱好者，从最初阅读 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复杂性（消耗的读取）

mysqlcheck%E5%91%BD%E4%BB%A4%E6%96%B9%E5%BC%8F%E6%94%B6%E9%9B%86%EF%BC%88mysqlcheck%E5%91%BD%E4%BB%A4%E6%98%AF%E5%85%A8%E8%A1%A8%E5%85%A8%E5%BA%93%E6%94%B6%E9%9B%86%E7%BB%9F%E8%AE%A1%E4%BF%A1%E6%81%AF%EF%BC%89-toc" name="tableOfContents" style="margin-left:80px">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）的小伙伴来说，理解这个概念应该并不困难。在 Oracle 中，统计信息非常丰富，后续我也会专门介绍，但今天我们的重点还是放在 MySQL 上。

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

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

Oracle和MySQL统计信息的区别：

Oracle：统计信息是在特定的时间收集的全实例的，不是自动收集。当对象还没有统计信息时，那么先通过动态采样技术来选择执行计划，默认2级别的动态采样，采取对象的64个数据块进行分析。

MySQL：默认的持久化统计信息自动进行收集统计信息，但是需要注意MySQL InnoDB默认是以表为单位来收集和存储统计数据的。

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

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

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

1)innodb_stats_auto_recalc为on，自动更新统计信息。阈值是表中行数的10%发生更改。

2)create table、create index、alter table、truncate table等涉及数据修改的DDL语句

3)手动更新统计信息，注意执行过程中会加读锁：analyze table tablename。

4)dict_stats_thread线程专门处理统计信息。

5)而如果变更的行数超过16+n_rows/16（6.25%）或者表修改的行超过1/6或者20亿条时

（1）相关参数

一、innodb_stats_persistent

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

默认值：ON

作用：变量控制统计信息是否持久化，统计信息在早期的MySQL中是不持久化，在新版本的MySQL中持久化统计信息是默认的选项。当变量打开时，统计信息就会被持久化到物理表中，统计信息会更加的稳定和精确，对于大表也节省了收集统计信息的所需资源。如果为off，可能会频繁地重新计算统计信息，这可能会导致查询执行计划的变化。



二、innodb_stats_auto_recalc

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

默认值：ON

触发阈值：表变化的数据是否超过10%，超过自动收集统计信息。

作用：InnoDB会长期追踪每一张表的行数，判断更新的记录是否超过表记录总数的1/10，超过那么就把这张表加入到后台的recalc pool中。由于自动统计信息重新计算（发生在后台）是异步，在运行影响超过10％的表的DML操作时（即innodb_stats_auto_recalc启用后），可能不会立即重新计算统计信息。在某些情况下，统计重新计算可能会延迟几秒钟(10s)。如果在更改表的重要部分之后立即需要最新统计信息，请运行ANALYZE TABLE以启动统计信息的同步（前台）重新计算。

如果禁用了innodb_stats_auto_recalc，请在对索引列进行实质性更改后，通过为每个适用的表发出ANALYZE TABLE语句来确保统计信息的准确性。

此设置适用于启用innodb_stats_persistent选项时创建的表。也可以在CREATE TABLE或者ALTER TABLE时通过STATS_AUTO_RECALC语法来指定比率。



三、innodb_stats_persistent_sample_pages

参数含义：持久化统计信息采样的索引页数。分析配置的页数，优化器根据统计信息给出执行计划

默认值：20

作用：在估计索引列的基数和其他统计信息(例如由ANALYZE TABLE计算的统计信息)时要采样的索引页数。增加该值可以提高索引统计的准确性，从而改进查询执行计划，但代价是在InnoDB表执行ANALYZE TABLE时增加I/O。

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

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

1)统计信息不够准确，优化器选择次优计划：如果确定统计信息不够准确，则应增加innodb_stats_persistent_sample_pages的值，直到统计估计值足够准确。但是过多地增加innodb_stats_persistent_sample_pages可能会导致ANALYZE TABLE运行缓慢。

2)ANALYZE TABLE太慢：在这种情况下，应减少innodb_stats_persistent_sample_pages，直到ANALYZE TABLE执行时间可以接受。但是过多地降低该值可能会导致生成不准确的统计信息和执行计划的问题。



四、innodb_stats_include_delete_marked

默认值：OFF

作用：在5.7.16中引入的此参数，默认为不启用，表示在未提交的事务有从表中删除行，则InnoDB在收集统计信息时，将会排除这些delete_marked行。这可能会导致除READ UNCOMMITTED之外的事务隔离级别的事务，运行的不是最佳的执行计划。

为了避免这种情况，可以启用innodb_stats_include_delete_marked以确保在计算持久化统计信息时InnoDB包含Delete-marked记录。

（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子句。

一、STATS_PERSISTENT

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

设置值：

DEFAULT:表示表的持久统计信息设置由innodb_stats_persistent配置选项确定

1:表示启用表的持久统计信息

0:关闭此功能

二、STATS_AUTO_RECALC

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

设置值：

DEFAULT:表示表的持久统计信息设置由innodb_stats_auto_recalc配置选项确定

1:表示表中10％的数据发生更改时将重新计算统计信息

0:禁用自动重新计算此表

三、STATS_SAMPLE_PAGES

含义：指定在估计索引列的基数和其他统计信息(例如由ANALYZE TABLE计算的统计信息)时要采样的索引页数。

设置值：

DEFAULT:表示持久化统计信息采样的页数由innodb_stats_persistent_sample_pages配置选项确定

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

CREATE TABLE't1`(
`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是单表收集统计信息）

innodb和myisam存储引擎都可以通过执行analyze table tablename来收集表和索引的统计信息。除非执行计划不准确，否则不要轻易执行该操作，如果是很大的表该操作会影响表的性能（单表9亿行的收集秒级完成，即使整个实例有2T，并且上千张表，通过mysqlcheck工具进行所有库的收集，也是几分钟就完成，亲测）

由于Analyze table会更新数据字典里的统计信息表（8.0）因此在innodb_read_only 开关被打开时有可能会导致执行失败。在analyze table的过程中会持有InnoDB表的read only锁，因此会存在短暂的阻塞用户写入更新删除的操作。除此之外analyze table要把table从table definition cache刷出来，因此还会需要一个flush lock，此时如果有长事务使用了这张表，那么必须等待长事务结束。

注意：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

mysqlcheck%E5%91%BD%E4%BB%A4%E6%96%B9%E5%BC%8F%E6%94%B6%E9%9B%86%EF%BC%88mysqlcheck%E5%91%BD%E4%BB%A4%E6%98%AF%E5%85%A8%E8%A1%A8%E5%85%A8%E5%BA%93%E6%94%B6%E9%9B%86%E7%BB%9F%E8%AE%A1%E4%BF%A1%E6%81%AF%EF%BC%89" name="4.2%20mysqlcheck%E5%91%BD%E4%BB%A4%E6%96%B9%E5%BC%8F%E6%94%B6%E9%9B%86%EF%BC%88mysqlcheck%E5%91%BD%E4%BB%A4%E6%98%AF%E5%85%A8%E8%A1%A8%E5%85%A8%E5%BA%93%E6%94%B6%E9%9B%86%E7%BB%9F%E8%AE%A1%E4%BF%A1%E6%81%AF%EF%BC%89" style="margin-left:0.0001pt; text-align:justify">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	显示版本

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

收集test库：

[root@mgr1 ~]# mysqlcheck -uroot -p123456 -S /mysql/data/3306/mysql.sock --analyze --databases test

收集所有库：

[root@mgr1 ~]# mysqlcheck -uroot -p123456 -S /mysql/data/3306/mysql.sock --analyze --all-databases

SQL> select * from mysql.innodb_table_stats where database_name='test';     ---test库所有表的统计信息更新为最新

（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 tb_700w like tb;
SQL> insert into tb_700w select * from tb limit 7000000;SQL> select * from mysql.innodb_table_stats where table_name='tb_700w';
---tb_700w真实有700万行数据，由于innodb_stats_persistent_sample_pages进行自动持久化统计信息采样只采集20页，那么就会有误差
2）设置STATS_SAMPLE_PAGES：
SQL> ALTER TABLE tb_700w STATS_SAMPLE_PAGES=65535;    ---此处STATS_SAMPLE_PAGES的最大值是65535，超出会报错。
SQL> analyze table tb_700w;SQL> select * from mysql.innodb_table_stats where table_name='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 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 tb_700w\G;SQL> SELECT index_name, stat_name, stat_descriptionFROM mysql.innodb_index_stats WHEREdatabase_name='test' ANDtable_name='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。

那么读取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 tb_700w;SQL> select * from mysql.innodb_table_stats where table_name='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

2)设置innodb_stats_on_metadata=ON（默认off），执行SHOW TABLE STATUS , SHOW INDEX，查询INFORMATION_SCHEMA下的TABLES, STATISTICS

3)启用--auto-rehash选项，这是默认设置。--auto-rehash选项会打开所有InnoDB表，打开表的操作会导致统计数据重新计算。使用mysql client登录。

4)表第一次被打开

5)距上一次更新统计信息，表1/16的数据被修改

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

（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）设置非持久化统计信息的两种方式

1)全局变量（影响所有表）

innodb_stats_persistent=OFF ---默认on，启用持久化统计信息。变量控制统计信息是否持久化，统计信息在早期的MySQL中是不持久化，在新版本的MySQL中持久化统计信息是默认的选项。当变量打开时，统计信息就会被持久化到物理表中，统计信息会更加的稳定和精确，对于大表也节省了收集统计信息的所需资源。如果为off，可能会频繁地重新计算统计信息，这可能会导致查询执行计划的变化。

2)CREATE/ALTER表的参数（只影响设置的表）

STATS_PERSISTENT=0 ---是否启用InnoDB表的持久统计功能。默认值由innodb_stats_persistent配置选项决定。值1启用表的持久统计，而值0关闭此特性。在通过CREATE TABLE或ALTER TABLE语句启用持久统计信息后，在将代表性数据加载到表中之后，发出ANALYZE TABLE语句来计算统计信息。

STATS_PERSISTENT：指定是否为InnoDB表启用持久统计信息。默认值由innodb_stats_persistent配置选项确定。1:表示启用表的持久统计信息; 0:关闭此功能