持久优化器统计功能 通过将统计信息存储到磁盘并使它们在服务器重新启动时持久化来 提高计划稳定性,以便优化器更有可能在每次给定查询时做出一致的选择。
innodb_stats_persistent=ON
当使用 定义单个表
时,优化器统计信息将保存到磁盘
STATS_PERSISTENT=1
。
innodb_stats_persistent
默认情况下启用。
以前,优化器统计信息在重新启动服务器时以及在执行某些其他类型的操作后会被清除,并在下一次访问表时重新计算。因此,在重新计算统计数据时可能会产生不同的估计,从而导致查询执行计划的不同选择和查询性能的变化。
持久统计信息存储在
mysql.innodb_table_stats
和
mysql.innodb_index_stats
表中。请参阅
第 14.8.11.1.5 节,“InnoDB 持久统计表”。
如果您不想将优化器统计信息持久保存到磁盘,请参阅 第 14.8.11.2 节,“配置非持久性优化器统计参数”
默认情况下启用的innodb_stats_auto_recalc
变量控制当表中超过 10% 的行发生更改时是否自动计算统计信息。您还可以通过STATS_AUTO_RECALC
在创建或更改表时指定子句来为单个表配置自动统计信息重新计算。
由于在后台发生的自动统计重新计算的异步特性,在运行影响超过 10% 的表的 DML 操作后,即使
innodb_stats_auto_recalc
启用,统计也可能不会立即重新计算。在某些情况下,统计重新计算可能会延迟几秒钟。如果立即需要最新的统计信息,请运行ANALYZE TABLE
以启动统计信息的同步(前台)重新计算。
如果禁用,您可以通过在对索引列进行大量更改后innodb_stats_auto_recalc
执行语句来确保优化器统计信息的准确性。ANALYZE
TABLE
您还可以考虑添加
ANALYZE TABLE
到加载数据后运行的设置脚本,并
ANALYZE TABLE
在活动较少时按计划运行。
将索引添加到现有表时,或者添加或删除列时,innodb_index_stats
无论 的值如何,都会计算索引统计信息并将其添加到表中
innodb_stats_auto_recalc
。
innodb_stats_persistent
,
innodb_stats_auto_recalc
, 和
innodb_stats_persistent_sample_pages
是全局变量。要覆盖这些系统范围的设置并为单个表配置优化器统计参数,您可以在or
语句
中定义STATS_PERSISTENT
、
STATS_AUTO_RECALC
和
STATS_SAMPLE_PAGES
子句
。CREATE TABLE
ALTER TABLE
STATS_PERSISTENT
指定是否为表启用 持久统计信息InnoDB
。该值DEFAULT
导致表的持久统计信息设置由innodb_stats_persistent
设置确定。值为1
启用表的持久统计信息,而值为0
禁用该功能。为单个表启用持久化统计后,用于ANALYZE TABLE
加载表数据后计算统计信息。STATS_AUTO_RECALC
指定是否自动重新计算 持久统计信息。该值DEFAULT
导致表的持久统计信息设置由innodb_stats_auto_recalc
设置确定。当 10% 的表数据发生更改时,值1
会导致重新计算统计信息。值0
可防止自动重新计算表。当使用值 0 时,用于ANALYZE TABLE
在对表进行大量更改后重新计算统计信息。STATS_SAMPLE_PAGES
例如,当通过操作为索引列计算基数和其他统计信息时,指定要采样的索引页数ANALYZE TABLE
。
以下
CREATE TABLE
示例中指定了所有三个子句:
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;
优化器使用有关键分布的估计
统计信息,根据索引的相对选择性为执行计划
选择索引。诸如ANALYZE
TABLE
导致InnoDB
从表上的每个索引中随机抽样页面以估计索引的
基数等操作。这种采样技术被称为
随机潜水。
控制采样页面的
innodb_stats_persistent_sample_pages
数量。您可以在运行时调整设置以管理优化器使用的统计估计的质量。默认值为20。遇到以下问题时考虑修改设置:
统计信息不够准确,优化器选择了次优计划,如
EXPLAIN
输出所示。您可以通过将索引的实际基数(通过SELECT DISTINCT
在索引列上运行确定)与mysql.innodb_index_stats
表中的估计值进行比较来检查统计信息的准确性。如果确定统计不够准确,
innodb_stats_persistent_sample_pages
则应增加 的值,直到统计估计足够准确。但是,增加innodb_stats_persistent_sample_pages
太多可能会导致ANALYZE TABLE
运行缓慢。ANALYZE TABLE
太慢了。在这种情况下innodb_stats_persistent_sample_pages
,应该减少直到ANALYZE TABLE
执行时间可以接受为止。但是,将值减小太多可能会导致第一个问题,即不准确的统计信息和次优的查询执行计划。如果无法在准确统计和执行时间之间取得平衡
ANALYZE TABLE
,可以考虑减少表中的索引列数或限制分区数以降低ANALYZE TABLE
复杂性。表的主键中的列数也很重要,因为主键列附加到每个非唯一索引。有关相关信息,请参阅 第 14.8.11.3 节,“估计 InnoDB 表的分析表复杂性”。
默认情况下,InnoDB
在计算统计信息时读取未提交的数据。在从表中删除行的未提交事务的情况下,在计算行估计和索引统计信息时将删除标记的记录排除在外,这可能导致使用事务并发操作表的其他事务的非最佳执行计划以外的隔离级别
READ UNCOMMITTED
。为避免这种情况,
innodb_stats_include_delete_marked
可以启用以确保在计算持久优化器统计信息时包含删除标记的记录。
innodb_stats_include_delete_marked
启用时,
在
ANALYZE TABLE
重新计算统计信息时考虑删除标记的记录。
innodb_stats_include_delete_marked
是影响所有InnoDB
表的全局设置,它仅适用于持久优化器统计信息。
innodb_stats_include_delete_marked
在 MySQL 5.7.16 中引入。
持久统计功能依赖于mysql
数据库中名为
innodb_table_stats
和
的内部管理表innodb_index_stats
。这些表在所有安装、升级和从源代码构建过程中自动设置。
表 14.4 innodb_table_stats 的列
列名 | 描述 |
---|---|
database_name |
数据库名称 |
table_name |
表名、分区名或子分区名 |
last_update |
指示上次更新行的时间戳 |
n_rows |
表中的行数 |
clustered_index_size |
主索引的大小,以页为单位 |
sum_of_other_index_sizes |
其他(非主)索引的总大小,以页为单位 |
表 14.5 innodb_index_stats 的列
列名 | 描述 |
---|---|
database_name |
数据库名称 |
table_name |
表名、分区名或子分区名 |
index_name |
索引名称 |
last_update |
指示上次InnoDB
更新此行的时间戳 |
stat_name |
统计的名称,其值在
stat_value 列中报告 |
stat_value |
stat_name
在列中命名的统计值 |
sample_size |
stat_value 为列中提供的估计值抽样的页数
|
stat_description |
stat_name 列中命名的统计信息的描述
|
innodb_table_stats
和
innodb_index_stats
表包括一个
last_update
显示索引统计信息上次更新时间的列
:
mysql> SELECT * FROM innodb_table_stats \G
*************************** 1. row ***************************
database_name: sakila
table_name: actor
last_update: 2014-05-28 16:16:44
n_rows: 200
clustered_index_size: 1
sum_of_other_index_sizes: 1
...
mysql> SELECT * FROM innodb_index_stats \G
*************************** 1. row ***************************
database_name: sakila
table_name: actor
index_name: PRIMARY
last_update: 2014-05-28 16:16:44
stat_name: n_diff_pfx01
stat_value: 200
sample_size: 1
...
和表可以手动更新innodb_table_stats
,
innodb_index_stats
这使得在不修改数据库的情况下强制执行特定的查询优化计划或测试替代计划成为可能。如果您手动更新统计信息,请使用该
语句加载更新的统计信息。
FLUSH TABLE
tbl_name
持久统计信息被认为是本地信息,因为它们与服务器实例相关。因此
,当发生自动统计重新计算时,不会复制innodb_table_stats
和
innodb_index_stats
表。如果您运行ANALYZE TABLE
以启动统计信息的同步重新计算,则会复制此语句(除非您抑制了它的日志记录),并且在副本上进行重新计算。
该innodb_table_stats
表包含每个表的一行。以下示例演示了收集的数据类型。
表t1
包含主索引(列
a
,b
)二级索引(列c
,d
)和唯一索引(列e
,
f
):
CREATE TABLE t1 (
a INT, b INT, c INT, d INT, e INT, f INT,
PRIMARY KEY (a, b), KEY i1 (c, d), UNIQUE KEY i2uniq (e, f)
) ENGINE=INNODB;
插入五行样例数据后,表格
t1
如下:
mysql> SELECT * FROM t1;
+---+---+------+------+------+------+
| a | b | c | d | e | f |
+---+---+------+------+------+------+
| 1 | 1 | 10 | 11 | 100 | 101 |
| 1 | 2 | 10 | 11 | 200 | 102 |
| 1 | 3 | 10 | 11 | 100 | 103 |
| 1 | 4 | 10 | 12 | 200 | 104 |
| 1 | 5 | 10 | 12 | 100 | 105 |
+---+---+------+------+------+------+
要立即更新统计信息,请运行
ANALYZE TABLE
(如果
innodb_stats_auto_recalc
启用,假设达到更改表行的 10% 阈值,统计信息将在几秒钟内自动更新):
mysql> ANALYZE TABLE t1;
+---------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.t1 | analyze | status | OK |
+---------+---------+----------+----------+
表的表统计信息t1
显示上次InnoDB
更新的表统计信息 ( 2014-03-14 14:36:34
)、表中的行数 ( 5
)、聚簇索引大小 ( 1
page) 以及其他索引的组合大小 ( 2
pages)。
mysql> SELECT * FROM mysql.innodb_table_stats WHERE table_name like 't1'\G
*************************** 1. row ***************************
database_name: test
table_name: t1
last_update: 2014-03-14 14:36:34
n_rows: 5
clustered_index_size: 1
sum_of_other_index_sizes: 2
该innodb_index_stats
表包含每个索引的多行。表中的每一行都
提供与列中命名和描述
innodb_index_stats
的特定索引统计相关的数据
。例如:
stat_name
stat_description
mysql> SELECT index_name, stat_name, stat_value, stat_description
FROM mysql.innodb_index_stats WHERE table_name like 't1';
+------------+--------------+------------+-----------------------------------+
| index_name | stat_name | stat_value | stat_description |
+------------+--------------+------------+-----------------------------------+
| PRIMARY | n_diff_pfx01 | 1 | a |
| PRIMARY | n_diff_pfx02 | 5 | a,b |
| PRIMARY | n_leaf_pages | 1 | Number of leaf pages in the index |
| PRIMARY | size | 1 | Number of pages in the index |
| i1 | n_diff_pfx01 | 1 | c |
| i1 | n_diff_pfx02 | 2 | c,d |
| i1 | n_diff_pfx03 | 2 | c,d,a |
| i1 | n_diff_pfx04 | 5 | c,d,a,b |
| i1 | n_leaf_pages | 1 | Number of leaf pages in the index |
| i1 | size | 1 | Number of pages in the index |
| i2uniq | n_diff_pfx01 | 2 | e |
| i2uniq | n_diff_pfx02 | 5 | e,f |
| i2uniq | n_leaf_pages | 1 | Number of leaf pages in the index |
| i2uniq | size | 1 | Number of pages in the index |
+------------+--------------+------------+-----------------------------------+
该stat_name
列显示以下类型的统计信息:
size
: 其中stat_name
=size
,该stat_value
列显示索引中的总页数。n_leaf_pages
: 其中stat_name
=n_leaf_pages
,该stat_value
列显示索引中叶页的数量。n_diff_pfx
: 其中NN
stat_name
=n_diff_pfx01
,该stat_value
列显示索引第一列中不同值的数量。其中stat_name
=n_diff_pfx02
,该stat_value
列显示索引前两列中不同值的数量,依此类推。其中stat_name
= ,该列显示以逗号分隔的索引列列表。n_diff_pfx
NN
stat_description
为了进一步说明
提供基数数据的统计信息,请再次考虑之前介绍的表示例。如下所示,该
表是使用主索引(列,)、二级索引(列,
)和唯一索引(列
,)创建的:
n_diff_pfx
NN
t1
t1
a
b
c
d
e
f
CREATE TABLE t1 (
a INT, b INT, c INT, d INT, e INT, f INT,
PRIMARY KEY (a, b), KEY i1 (c, d), UNIQUE KEY i2uniq (e, f)
) ENGINE=INNODB;
插入五行样例数据后,表格
t1
如下:
mysql> SELECT * FROM t1;
+---+---+------+------+------+------+
| a | b | c | d | e | f |
+---+---+------+------+------+------+
| 1 | 1 | 10 | 11 | 100 | 101 |
| 1 | 2 | 10 | 11 | 200 | 102 |
| 1 | 3 | 10 | 11 | 100 | 103 |
| 1 | 4 | 10 | 12 | 200 | 104 |
| 1 | 5 | 10 | 12 | 100 | 105 |
+---+---+------+------+------+------+
当查询index_name
,
stat_name
, stat_value
, 和stat_description
, where
stat_name LIKE 'n_diff%'
时,返回如下结果集:
mysql> SELECT index_name, stat_name, stat_value, stat_description
FROM mysql.innodb_index_stats
WHERE table_name like 't1' AND stat_name LIKE 'n_diff%';
+------------+--------------+------------+------------------+
| index_name | stat_name | stat_value | stat_description |
+------------+--------------+------------+------------------+
| PRIMARY | n_diff_pfx01 | 1 | a |
| PRIMARY | n_diff_pfx02 | 5 | a,b |
| i1 | n_diff_pfx01 | 1 | c |
| i1 | n_diff_pfx02 | 2 | c,d |
| i1 | n_diff_pfx03 | 2 | c,d,a |
| i1 | n_diff_pfx04 | 5 | c,d,a,b |
| i2uniq | n_diff_pfx01 | 2 | e |
| i2uniq | n_diff_pfx02 | 5 | e,f |
+------------+--------------+------------+------------------+
对于PRIMARY
索引,有两
n_diff%
行。行数等于索引中的列数。
对于非唯一索引,InnoDB
附加主键的列。
其中
index_name
=PRIMARY
和stat_name
=n_diff_pfx01
,stat_value
即,表示索引的第一列(列)1
中有一个不同的值 。a
列中不同值的数量a
通过查看a
表中列中 的数据来确认t1
,其中有一个不同值 (1
)。被统计的列(a
)显示在stat_description
结果集的列中。其中
index_name
=PRIMARY
和stat_name
=n_diff_pfx02
,stat_value
即5
,表示索引的两列中有五个不同的值 (a,b
)。a
列和中不同值的数量b
通过查看列a
和b
表中的数据来确认t1
,其中有五个不同值:(1,1
)、(1,2
)、(1,3
)、(1,4
) 和 (1,5
)。计数的列 (a,b
) 显示在stat_description
结果集的列中。
对于二级索引 ( i1
),有n_diff%
四行。只为二级索引 ( ) 定义了两列,但二级索引c,d
有n_diff%
四行,因为InnoDB
所有非唯一索引都带有主键后缀。因此,有n_diff%
四行而不是两行来说明二级索引列 ( c,d
) 和主键列 ( a,b
)。
其中
index_name
=i1
和stat_name
=n_diff_pfx01
,stat_value
即,表示索引的第一列(列)1
中有一个不同的值 。c
列中不同值的数量c
通过查看c
表中列中 的数据来确认t1
,其中有一个不同值:(10
)。被统计的列(c
)显示在stat_description
结果集的列中。其中
index_name
=i1
和stat_name
=n_diff_pfx02
,stat_value
即2
,表示索引的前两列中有两个不同的值 (c,d
)。通过查看列和 表中的数据来确认列c
an中不同值的数量,其中有两个不同值:( ) 和 ( )。计数的列 ( ) 显示在结果集的列中。d
c
d
t1
10,11
10,12
c,d
stat_description
其中
index_name
=i1
和stat_name
=n_diff_pfx03
,stat_value
即2
,表示索引的前三列中有两个不同的值 (c,d,a
)。列 、和 中不同值的数量通过查看列 、 和表 中的数据来确认c
,其中 有两个不同值:( ) 和 ( )。计数的列 ( ) 显示在 结果集的列中。d
a
c
d
a
t1
10,11,1
10,12,1
c,d,a
stat_description
其中
index_name
=i1
和stat_name
=n_diff_pfx04
,stat_value
即5
,表示索引的四列中有五个不同的值 (c,d,a,b
)。列 、和中不同值的数量 通过查看列 、 、 和表 中的数据来确认c
,d
其中 有 五个不同值 :( )、( )、( )、( ) 和 (a
b
c
d
a
b
t1
10,11,1,1
10,11,1,2
10,11,1,3
10,12,1,4
10,12,1,5
). 计数的列 (c,d,a,b
) 显示在stat_description
结果集的列中。
对于唯一索引 ( i2uniq
),有两n_diff%
行。
其中
index_name
=i2uniq
和stat_name
=n_diff_pfx01
,stat_value
即2
,表示索引的第一列(列e
)中有两个不同的值。列中不同值的数量e
通过查看e
表中列中 的数据来确认t1
,其中有两个不同值:(100
) 和 (200
)。被统计的列(e
)显示在stat_description
结果集的列中。Where
index_name
=i2uniq
andstat_name
=n_diff_pfx02
, thestat_value
is5
, which indicates that there are five distinct values in the two columns of the index (e,f
). The number of distinct values in columnse
andf
is confirmed by viewing the data in columnse
andf
in tablet1
, in which there are five distinct values: (100,101
), (200,102
), (100,103
), (200,104
), and (100,105
). The counted columns (e,f
) are shown in thestat_description
column of the result set.
您可以使用表检索表、分区或子分区的索引大小
innodb_index_stats
。在以下示例中,检索表的索引大小
t1
。有关表
t1
和相应索引统计信息的定义,请参阅
第 14.8.11.1.6 节,“InnoDB 持久统计表示例”。
mysql> SELECT SUM(stat_value) pages, index_name,
SUM(stat_value)*@@innodb_page_size size
FROM mysql.innodb_index_stats WHERE table_name='t1'
AND stat_name = 'size' GROUP BY index_name;
+-------+------------+-------+
| pages | index_name | size |
+-------+------------+-------+
| 1 | PRIMARY | 16384 |
| 1 | i1 | 16384 |
| 1 | i2uniq | 16384 |
+-------+------------+-------+
对于分区或子分区,您可以使用带有修改后的WHERE
子句的相同查询来检索索引大小。例如,以下查询检索表分区的索引大小t1
:
mysql> SELECT SUM(stat_value) pages, index_name,
SUM(stat_value)*@@innodb_page_size size
FROM mysql.innodb_index_stats WHERE table_name like 't1#P%'
AND stat_name = 'size' GROUP BY index_name;