配置持久性优化器统计信息
本节介绍如何为表配置持久性和非持久性优化器统计信息InnoDB
。
持续的优化程序统计信息在服务器重新启动后会保留下来,从而提高了计划的稳定性和查询性能的一致性。持久性优化器统计信息还具有以下额外好处,从而提供了控制和灵活性:
- 您可以使用
innodb_stats_auto_recalc
配置选项来控制在对表进行重大更改后是否自动更新统计信息。 - 您可以将
STATS_PERSISTENT
,STATS_AUTO_RECALC
和STATS_SAMPLE_PAGES
子句与CREATE TABLE
和ALTER TABLE
语句一起使用,以配置各个表的优化器统计信息。 - 您可以在
mysql.innodb_table_stats
和mysql.innodb_index_stats
表中查询优化器统计数据。 - 您可以参见和表的
last_update
列,以参见统计信息的最新更新时间。mysql.innodb_table_stats
mysql.innodb_index_stats
- 您可以手动修改
mysql.innodb_table_stats
和mysql.innodb_index_stats
表,以强制执行特定的查询优化计划或测试替代计划,而无需修改数据库。
默认情况下(innodb_stats_persistent=ON
)启用了持久性优化器统计信息功能。
非持久性优化器统计信息将在每次服务器重新启动时以及执行某些其他操作后清除,并在下次访问表时重新计算。结果,在重新计算统计信息时可能会产生不同的估计,从而导致执行计划的选择不同以及查询性能的变化。
本节还提供有关估计ANALYZE TABLE
复杂度的信息,这在尝试在准确的统计信息和ANALYZE TABLE
执行时间之间取得平衡时可能很有用。
持久性优化器统计信息功能通过将统计信息存储到磁盘并使它们在服务器重新启动时保持持久性来提高计划的稳定性,因此优化器更有可能每次为给定查询做出一致的选择。
当innodb_stats_persistent=ON
或使用定义单个表时,Optimizer统计信息将保留在磁盘上STATS_PERSISTENT=1
。innodb_stats_persistent
默认情况下启用。
以前,重新启动服务器时以及执行某些其他类型的操作后,会清除优化器统计信息,并在下次访问表时重新计算。因此,在重新计算统计信息时会产生不同的估计,从而导致查询执行计划中的选择不同以及查询性能发生变化。
永久统计信息存储在mysql.innodb_table_stats
和mysql.innodb_index_stats
表中。请参见“ InnoDB持久统计表”。
如果您不想将优化器统计信息持久化到磁盘上,请参见“配置非持久性优化器统计信息参数”
配置持久性优化器统计信息的自动统计信息计算
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
是全局变量。要覆盖这些系统的设置和配置优化统计参数为单个表,您可以定义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
设置确定。1
当表数据的10%发生更改时,将重新计算统计值。一个值0
可防止对该表进行自动重新计算。使用0值时,ANALYZE TABLE
对表进行实质性更改后,可用于重新计算统计信息。STATS_SAMPLE_PAGES
指定ANALYZE TABLE
例如通过操作为索引列计算基数和其他统计信息时要采样的索引页数。
在以下CREATE TABLE
示例中指定了所有三个子句:
CREATE TABLE `t1` ( `id` int(8) NOT NULLauto_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;
配置InnoDB Optimizer统计信息的采样页数
优化器使用有关键分布的估计统计信息,根据索引的相对选择性为执行计划选择索引。诸如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
复杂性。表的主键中的列数也很重要,因为主键列被附加到每个非唯一索引中。有关相关信息,请参见“估计InnoDB表的分析表复杂性”。
在永久统计信息计算中包括删除标记的记录
默认情况下,InnoDB
在计算统计信息时读取未提交的数据。如果是未提交的事务,它从表中删除行,则在计算行估计和索引统计信息时会排除带有删除标记的记录,这可能导致使用事务在表上同时进行操作的其他事务的执行计划不理想。隔离级别不是READ UNCOMMITTED
。为避免这种情况,innodb_stats_include_delete_marked
可以启用以确保在计算持久性优化器统计信息时包括删除标记的记录。
当innodb_stats_include_delete_marked
启用时,ANALYZE TABLE
重新计算统计数据时,会考虑删除标记的记录。
innodb_stats_include_delete_marked
是影响所有InnoDB
表的全局设置,并且仅适用于持久性优化器统计信息。
InnoDB持久统计表
持久统计信息功能依赖于mysql
数据库中名为innodb_table_stats
和的内部管理表innodb_index_stats
。这些表是在所有安装,升级和从源构建过程中自动设置的。
innodb_table_stats的列
栏名 | 描述 |
---|---|
database_name | 数据库名称 |
table_name | 表名,分区名或子分区名 |
last_update | 指示最后一次InnoDB 更新此行的时间戳 |
n_rows | 表中的行数 |
clustered_index_size | 主索引的大小,以页为单位 |
sum_of_other_index_sizes | 其他(非主)索引的总大小(以页为单位) |
innodb_index_stats的列
栏名 | 描述 |
---|---|
database_name | 数据库名称 |
table_name | 表名,分区名或子分区名 |
index_name | 索引名称 |
last_update | 指示上次更新行的时间戳 |
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持久统计表示例
该innodb_table_stats
表每个表包含一行。以下示例演示了收集的数据类型。
该表t1
包含一个主索引(columns a
,b
)和一个辅助索引(columns c
,d
),以及唯一索引(columns 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
页)以及其他索引的合并大小(2
页)。
mysql>SELECT *FROM mysql.innodb_table_statsWHERE 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_descriptionFROM mysql.innodb_index_statsWHERE 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_pfxNN
:其中stat_name
=n_diff_pfx01
,此stat_value
列在索引的第一列中显示不同值的数量。其中stat_name
=n_diff_pfx02
,此stat_value
列显示索引的前两列中不同值的数量,依此类推。其中stat_name
=,该列显示以逗号分隔的索引列的列表。n_diff_pfxNN
stat_description
为了进一步说明提供基数数据的统计信息,请再次考虑先前介绍的表格示例。如下所示,使用主索引(columns ,),辅助索引(columns ,)和唯一索引(columns ,)创建表:n_diff_pfxNN
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
,其中stat_name LIKE 'n_diff%'
,下面的返回结果集:
mysql>SELECT index_name, stat_name, stat_value, stat_descriptionFROM mysql.innodb_index_statsWHERE 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
is1
,表示在索引的第一列(列a
)中存在一个唯一的不同值。a
通过参见a
表中列中的数据来确认列中不同值的数量t1
,其中只有一个不同值(1
)。计数的列(a
)显示在stat_description
结果集的列中。 - 其中
index_name
=PRIMARY
和stat_name
=n_diff_pfx02
,stat_value
is是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
is1
,表示在索引的第一列(列c
)中存在一个唯一的不同值。c
通过参见c
table 中列中的数据来确认列中不同值的数量t1
,其中只有一个不同值:(10
)。计数的列(c
)显示在stat_description
结果集的列中。 - 其中
index_name
=i1
和stat_name
=n_diff_pfx02
,stat_value
是2
,表示在索引(c,d
)的前两列中有两个不同的值。通过参见列和表中的数据来确认列中c
的不同值的数量,其中有两个不同的值:()和()。计数的列()显示在结果集的列中。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
is5
,表示在索引(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
is是2
,表示在索引的第一列(列e
)中有两个不同的值。e
通过参见e
表中列中的数据来确认列中不同值的数量t1
,其中有两个不同值:(100
)和(200
)。计数的列(e
)显示在stat_description
结果集的列中。 - 其中
index_name
=i2uniq
和stat_name
=n_diff_pfx02
,stat_value
is是5
,表示在索引(e,f
)的两列中有五个不同的值。列中的e
和的不同值的数量f
通过参见列e
和f
表中的数据来确认t1
,其中有五个不同的值:(100,101
),(200,102
),(100,103
),(200,104
)和(100,105
)。计数的列(e,f
)显示在stat_description
结果集的列。
使用innodb_index_stats表检索索引大小
您可以使用表检索表,分区或子分区的索引大小innodb_index_stats
。在以下示例中,为table检索索引大小t1
。有关表t1
和相应索引统计信息的定义,请参见“ InnoDB持久统计信息表示例”。
mysql>SELECT SUM(stat_value) pages, index_name, SUM(stat_value)*@@innodb_page_size sizeFROM mysql.innodb_index_statsWHERE 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
子句一起使用以检索索引大小。例如,以下查询检索table的分区的索引大小t1
:
mysql>SELECT SUM(stat_value) pages, index_name, SUM(stat_value)*@@innodb_page_size sizeFROM mysql.innodb_index_statsWHERE table_name like 't1#P%' AND stat_name = 'size'GROUP BY index_name;