概念描述
在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