概念描述
在MySQL8.0中，统计信息（Statistics）是优化器（Optimizer）用来生成执行计划的重要依据，它直接影响SQL性能。
统计信息管理
MySQL提供了两种统计信息的管理方式：非持久化统计信息（Non-Persistent Statistics）和持久化统计信息（Persistent Statistics）。这两种方式在存储、更新机制以及对执行计划的影响上有所不同。
1. 非持久性优化器统计信息
非持久优化器化统计信息（non-persistent optimizer statistics）是指MySQL InnoDB 存储引擎的统计信息仅存储在内存中，而不会持久化到磁盘。当 MySQL 服务重启时，这些统计信息会丢失，并在下次访问表时重新计算。非持久化统计信息的行为由参数 innodb_stats_persistent 控制，当该参数设置为 OFF 时，统计信息即为非持久化的，缺省情况下MySQL的统计信息是持久化的（innodb_stats_persistent=ON）。
非持久优化器统计信息通常会在以下几种情况下触发更新：
1） 手动执行 analyze table 命令。
2） 在innodb_stats_on_metadata=ON 的情况下，执行show table status,show index status或者查询information_schem库下的 tables表和statistics表。
说明：默认情况下innodb_stats_on_metadata是关闭的，开启innodb_stats_on_metadata会降低具有大量表或者索引的库的访问速度，并减少查询语句执行计划的稳定性。
3） MySQL客户端连接时启用自动补全功能 --auto-rehash （默认启用）
说明：禁用它（ --no-auto-rehash ）可以加快连接速度，减少内存占用，但需要手动输入完整的 SQL 语句
4） 首次打开表时。
5） 自上次统计信息更新后，innodb检测到表有1/16的数据被修改时。
innodb_stats_transient_sample_pages参数
以上几种情况下会触发非持久优化器统计信息的自动更新，对于非持久优化器统计信息还有一个参数来控制 innodb_stats_transient_sample_pages，统计信息数据更新机制是基于innodb表的索引页的数据量来估算的，默认情况下这个参数就是用于控制 innodb表的统计信息采样页面数量，默认为8个页。增大 innodb_stats_transient_sample_pages 的值会提高统计信息的准确性，但会增加计算开销，减小该值会降低统计信息的准确性，但也会减少计算开销。
在大多数情况下，默认值 8 是一个合理的平衡点，既能提供足够的统计信息准确性，又不会带来过多的性能开销。如果发现查询优化器选择了不理想的执行计划，可以尝试逐步增大 innodb_stats_transient_sample_pages 的值，观察查询性能是否改善。如果查询性能要求极高，且表数据量较大，可以适当减小该值以减少开销。
2. 持久性优化器统计信息
在MySQL5.6版本之前，InnoDB表的统计信息是动态计算的（即“非持久化统计信息”），这些统计信息不会持久化到磁盘，而是在每次需要时进行计算。这种方式虽然灵活，但是有一定的缺点：
1. 统计信息可能会频繁变化，导致查询优化器选择的执行计划不太稳定。
2. 动态计算统计信息会增加查询的开销，尤其是在表数据量较大时。
为了解决以上问题，从MySQL5.6版本开始引入了持久化统计信息功能，将统计信息持久化存储到磁盘，并在表数据发生重大变化时自动更新。
持久化统计信息主要由参数innodb_stats_persistent决定，该值默认为ON（启用持久化统计信息）。
3. 持久化统计信息存储在哪里？
1. mysql.innodb_table_stats表
该表存储了innodb表的统计信息，包括表的行数，数据页数量等：
mysql >desc mysql.innodb_table_stats; +--------------------------+-----------------+------+-----+-------------------+-----------------------------------------------+ | Field | Type | Null | Key | Default | Extra | +--------------------------+-----------------+------+-----+-------------------+-----------------------------------------------+ | database_name | varchar(64) | NO | PRI | NULL | | | table_name | varchar(199) | NO | PRI | NULL | | | last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP | | n_rows | bigint unsigned | NO | | NULL | | | clustered_index_size | bigint unsigned | NO | | NULL | | | sum_of_other_index_sizes | bigint unsigned | NO | | NULL | | +--------------------------+-----------------+------+-----+-------------------+-----------------------------------------------+ 6 rows in set (0.00 sec) -- 主要字段说明： database_name ：数据库名 table_name ：表名 last_update ：最近更新时间 n_rows ：表的行数 clustered_index_size ：聚集索引的大小，单位为页pages sum_of_other_index_sizes ：其他索引的总大小，单位为页pages复制
2. mysql.innodb_index_stats表
该表存储了InnoDB表索引的统计信息，包括索引的基数、页数等
mysql > desc mysql.innodb_index_stats; +------------------+-----------------+------+-----+-------------------+-----------------------------------------------+ | Field | Type | Null | Key | Default | Extra | +------------------+-----------------+------+-----+-------------------+-----------------------------------------------+ | database_name | varchar(64) | NO | PRI | NULL | | | table_name | varchar(199) | NO | PRI | NULL | | | index_name | varchar(64) | NO | PRI | NULL | | | last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP | | stat_name | varchar(64) | NO | PRI | NULL | | | stat_value | bigint unsigned | NO | | NULL | | | sample_size | bigint unsigned | YES | | NULL | | | stat_description | varchar(1024) | NO | | NULL | | +------------------+-----------------+------+-----+-------------------+-----------------------------------------------+ 8 rows in set (0.00 sec) -- 主要字段说明： database_name ：数据库名 table_name ：表名 index_name ：索引名 last_update ：最近更新时间 stat_name : 统计信息的名称（如 n_diff_pfx01，n_leaf_pages，size 等）。 stat_value ：统计信息值 sample_size ：样本大小 stat_description ：统计信息描述 示例：查询large_table表索引的统计信息 mysql >select * from mysql.innodb_index_stats where table_name='large_table'; +---------------+-------------+---------------+---------------------+--------------+------------+-------------+-----------------------------------+ | database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description | +---------------+-------------+---------------+---------------------+--------------+------------+-------------+-----------------------------------+ | testdb | large_table | PRIMARY | 2025-03-14 14:53:46 | n_diff_pfx01 | 496403 | 20 | id | | testdb | large_table | PRIMARY | 2025-03-14 14:53:46 | n_leaf_pages | 4111 | NULL | Number of leaf pages in the index | | testdb | large_table | PRIMARY | 2025-03-14 14:53:46 | size | 4134 | NULL | Number of pages in the index | | testdb | large_table | idx_lt_field1 | 2025-03-14 14:53:46 | n_diff_pfx01 | 97645 | 20 | field1 | | testdb | large_table | idx_lt_field1 | 2025-03-14 14:53:46 | n_diff_pfx02 | 500041 | 20 | field1,id | | testdb | large_table | idx_lt_field1 | 2025-03-14 14:53:46 | n_leaf_pages | 662 | NULL | Number of leaf pages in the index | | testdb | large_table | idx_lt_field1 | 2025-03-14 14:53:46 | size | 803 | NULL | Number of pages in the index | +---------------+-------------+---------------+---------------------+--------------+------------+-------------+-----------------------------------+ 7 rows in set (0.00 sec) --说明 stat_name 列中常见的统计信息名称及其含义： 1. n_diff_pfx01 表示索引第一列的不同值数量 2. n_diff_pfx02 表示索引前两列的不同值数量:如果索引是多列索引（例如 (col1, col2)），n_diff_pfx02 表示 col1 和 col2 组合的唯一性，值越大，表示这两列的组合越唯一；值越小，表示这两列的组合重复性越高，如果 n_diff_pfx02 的值接近表的行数，说明这两列的组合非常唯一，优化器更倾向于使用该索引。 3. n_leaf_pages 表示索引的叶子页数量。叶子页是实际存储索引数据的页。 4. size 表示索引的总页数，包括叶子页和非叶子页。复制
4. 持久化统计信息的准确性由谁来决定？
在MySQL8.0中，持久化统计信息的准确性由采样数据和统计信息计算方式决定。MySQL 通过分析表的索引和数据分布来生成统计信息，这些统计信息直接影响查询优化器的决策。
MySQL 通过以下方式决定统计信息的准确性：
（1）采样数据：MySQL 使用 innodb_stats_persistent_sample_pages 参数控制采样页数。默认值为20，表示从表中随机采样20个数据页来计算统计信息，采样页数越多，统计信息越准确，但计算开销也越大。
（2）统计信息计算方式：基数估算（通过分析索引中的不同值Cardinality来估算查询的选择性）和直方图（MySQL 8.0 引入了直方图统计信息，用于更精确地估算数据分布）。
（3）自动重新计算：innodb_stats_auto_recalc参数默认启用控制此行为 -> 如果表中超过 10% 的数据发生了变化，那么MySQL将会自动重新计算统计信息。
（4）手动更新：可以使用analyze table命令手动更新统计信息
analyze table table_name;复制
5. 统计信息的准确性如何受影响？
持久性优化器统计信息的准确性可能受到以下因素的影响：
(1) 采样页数不足
如果 innodb_stats_persistent_sample_pages 设置过小，采样数据可能不足以准确反映表的实际数据分布，导致统计信息不准确。
(2) 数据分布不均匀
如果表中的数据分布不均匀（例如某些值出现频率极高），统计信息可能无法准确反映查询的选择性。
(3) 索引结构变化
如果表的索引结构发生变化（例如添加或删除索引），统计信息可能过时，导致查询优化器选择次优的执行计划。
(4) 表数据变化
如果表的数据发生大量变化（例如插入、更新或删除大量数据），统计信息可能过时，需要重新计算。
(5) 直方图统计信息未启用
如果未启用直方图统计信息，MySQL 可能无法准确估算复杂查询的选择性。
6. 如何提高统计信息的准确性？
(1) 增加采样页数
-- 增加 innodb_stats_persistent_sample_pages 的值，例如： SET GLOBAL innodb_stats_persistent_sample_pages = 50;复制
(2) 启用直方图统计信息
-- 使用 ANALYZE TABLE 命令生成直方图统计信息，例如： ANALYZE TABLE table_name UPDATE HISTOGRAM ON column_name;复制
(3) 定期更新统计信息：定期执行 ANALYZE TABLE 命令，确保统计信息是最新的。
(4) 优化表的碎片整理
--表碎片可能导致统计信息不准确，定期对表进行优化，以减少碎片 OPTIMIZE TABLE example_table; 或者： ALTER TABLE my_table ENGINE=InnoDB; 说明：两者都会导致表被锁定，因此在生产环境中使用时需要谨慎，尤其是在大表上执行这些操作时。复制
(5) 优化索引：确保表的索引设计合理，避免冗余或无效的索引。
7. 相关参数
以下是与持久性优化器统计信息相关的重要参数：
- innodb_stats_persistent: 是否启用持久性统计信息（默认 ON）。
- innodb_stats_auto_recalc: 是否自动重新计算统计信息（默认 ON）。
- innodb_stats_persistent_sample_pages: 采样页数（默认 20）。
- innodb_stats_method: 统计信息计算方法（如 nulls_equal、nulls_unequal 等）。
总结
持久性优化器统计信息的准确性由很多因素都有关系包括采样数据、统计信息计算方式，表数据分布等。为了提高准确性，可以增加采样页数、启用直方图统计信息、定期更新统计信息，并优化索引设计。通过合理配置和监控，可以确保查询优化器选择最优的执行计划。
参考文档
https://dev.mysql.com/doc/refman/8.0/en/innodb-persistent-stats.html
https://dev.mysql.com/doc/refman/8.0/en/innodb-statistics-estimation.html
https://dev.mysql.com/doc/refman/8.0/en/innodb-performance-optimizer-statistics.html
