本节介绍如何配置非持久优化器统计信息。innodb_stats_persistent=OFF
当使用 .创建或更改单个表
时,优化器统计信息不会持久保存到磁盘
STATS_PERSISTENT=0
。相反,统计信息存储在内存中,并在服务器关闭时丢失。某些操作和在某些条件下也会定期更新统计信息。
从 MySQL 5.6.6 开始,优化器统计信息默认保存到磁盘,由
innodb_stats_persistent
配置选项启用。有关持久优化器统计信息的信息,请参阅第 14.8.11.1 节,“配置持久优化器统计参数”。
优化器统计更新
非持久优化器统计信息在以下情况下更新:
在启用该选项 的情况下 运行
SHOW TABLE STATUS
、SHOW INDEX
或查询INFORMATION_SCHEMA.TABLES
或INFORMATION_SCHEMA.STATISTICS
表 。innodb_stats_on_metadata
在 MySQL 5.6.6 中默认启用持久优化器统计信息时 的默认设置
innodb_stats_on_metadata
更改为。OFF
启用innodb_stats_on_metadata
可能会降低具有大量表或索引的模式的访问速度,并降低涉及InnoDB
表的查询的执行计划的稳定性。innodb_stats_on_metadata
使用SET
语句全局配置。SET GLOBAL innodb_stats_on_metadata=ON
笔记innodb_stats_on_metadata
仅当优化器 统计信息配置为非持久性时innodb_stats_persistent
(禁用时)才适用。在启用选项的情况下启动mysql客户端
--auto-rehash
,这是默认设置。该auto-rehash
选项导致InnoDB
打开所有表,并且打开表操作导致重新计算统计信息。要缩短mysql客户端 的启动时间并更新统计信息,您可以
auto-rehash
使用该--disable-auto-rehash
选项 关闭 。该auto-rehash
功能为交互式用户启用数据库、表和列名称的自动名称完成。首先打开一个表。
InnoDB
检测到自上次更新统计信息以来,表的 1 / 16 已被修改。
配置采样页数
MySQL 查询优化器使用有关键分布的估计
统计信息,根据索引的相对选择性为执行计划
选择索引。当InnoDB
更新优化器统计信息时,它会从表上的每个索引中随机抽样页面以估计索引的
基数。(这种技术被称为
随机潜水。)
为了让您控制统计估计的质量(从而为查询优化器提供更好的信息),您可以使用参数更改采样页面的数量
innodb_stats_transient_sample_pages
。抽样页面的默认数量是 8,这可能不足以产生准确的估计,从而导致查询优化器选择糟糕的索引。此技术对于大型表和
连接中使用的表尤其重要。对此类表进行不必要
的全表扫描可能是一个严重的性能问题。有关调整此类查询的提示,请参阅
第 8.2.1.20 节,“避免全表扫描”。
innodb_stats_transient_sample_pages
是可以在运行时设置的全局参数。
当 时, 的值
影响所有
表和索引
innodb_stats_transient_sample_pages
的索引采样。当您更改指数样本大小时,请注意以下潜在的重大影响:
InnoDB
innodb_stats_persistent=0
像 1 或 2 这样的小值可能会导致对基数的估计不准确。
增加该
innodb_stats_transient_sample_pages
值可能需要更多的磁盘读取。远大于 8 的值(比如 100)可能导致打开表或执行SHOW TABLE STATUS
.优化器可能会根据对索引选择性的不同估计来选择非常不同的查询计划。
无论哪个值
innodb_stats_transient_sample_pages
最适合系统,设置选项并将其保留在该值。选择一个值,该值可以在不需要过多 I/O 的情况下对数据库中的所有表进行相当准确的估计。因为统计信息会在执行 时以外的不同时间自动重新计算
,所以增加索引样本大小、运行,然后再次减少样本大小
ANALYZE TABLE
是没有意义的
。ANALYZE TABLE
较小的表通常比较大的表需要较少的索引样本。如果您的数据库有许多大表,请考虑使用
innodb_stats_transient_sample_pages
比大多数较小表时更高的值。