持久优化器统计特性得到了改进计划稳定通过将统计数据存储到磁盘并使其在服务器重新启动时持久化,这样优化器对于给定的查询,每次都更有可能做出一致的选择。
优化器统计信息被持久化到磁盘innodb_stats_persistent =对
或者当单独的表定义为STATS_PERSISTENT = 1
.innodb_stats_persistent
默认启用。
以前,在重新启动服务器和一些其他类型的操作之后,优化器统计信息将被清除,并在下一次表访问时重新计算。因此,在重新计算统计信息时,可能会产生不同的估计,从而导致查询执行计划的不同选择和查询性能的变化。
持久化统计信息存储在mysql.innodb_table_stats
而且mysql.innodb_index_stats
表。看到章节15.8.10.1.5,InnoDB持久统计表.
如果不希望将优化器统计信息持久化到磁盘,请参见第15.8.10.2节,“配置非持久优化器统计参数”
的innodb_stats_auto_recalc
变量,默认情况下是启用的,它控制当表发生超过10%的行更改时是否自动计算统计信息。属性,还可以为各个表配置自动统计信息重新计算STATS_AUTO_RECALC
子句在创建或修改表时。
由于自动统计信息重新计算的异步性质(发生在后台),在运行影响表10%以上的DML操作后,统计信息可能不会立即重新计算innodb_stats_auto_recalc
启用。在某些情况下,重新计算统计数据可能会延迟几秒钟。如果需要立即使用最新的统计数据,请运行分析表
启动统计数据的同步(前台)重新计算。
如果innodb_stats_auto_recalc
禁用时,可以通过执行分析表
语句在对索引列进行实质性更改之后。你也可以考虑添加分析表
设置在加载数据并运行后运行的脚本分析表
在活动较少的时候进行安排。
将索引添加到现有表时,或添加或删除列时,将计算索引统计信息并将其添加到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
条款创建表
或ALTER TABLE
语句。
STATS_PERSISTENT
指定是否启用持续的统计数据对于一个InnoDB
表格的值默认的
使表的持久统计信息设置由innodb_stats_persistent
设置。值为1
为表启用持久统计信息,而值为0
禁用该特性。为单个表启用持久统计信息后,使用分析表
在加载表数据后计算统计信息。STATS_AUTO_RECALC
指定是否自动重新计算持续的统计数据.的值默认的
使表的持久统计信息设置由innodb_stats_auto_recalc
设置。值为1
当10%的表数据发生更改时,导致重新计算统计信息。一个值0
防止自动重新计算表。当使用值为0时,使用分析表
在对表进行重大更改后重新计算统计数据。STATS_SAMPLE_PAGES
类计算索引列的基数和其他统计信息时,指定要采样的索引页数分析表
例如,操作。
所有三个子句都在下面指定创建表
例子:
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;
优化器使用estimated统计数据关于关键分布,为执行计划选择索引,基于相对选择性指数的。操作包括分析表
导致InnoDB
从表上的每个索引中随机抽取页面,以估计基数指数的。这种取样技术被称为随机的潜水.
的innodb_stats_persistent_sample_pages
控制采样页面的数量。您可以在运行时调整设置,以管理优化器使用的统计估计的质量。缺省值为20。遇到以下问题时,请考虑修改设置:
统计数据不够准确,优化器选择次优计划,如
解释
输出。您可以通过比较索引的实际基数(通过运行选择不同的
的索引列上)mysql.innodb_index_stats
表格如果确定统计数据不够准确,则
innodb_stats_persistent_sample_pages
应该增加,直到统计估计足够准确。增加innodb_stats_persistent_sample_pages
然而,太多可能会导致分析表
慢慢地跑。分析表
太慢了.在这种情况下innodb_stats_persistent_sample_pages
应该减少到分析表
执行时间可以接受。但是,将值降低太多可能导致第一个问题,即统计信息不准确和查询执行计划不理想。如果不能在精确的统计和
分析表
执行时,考虑减少表中索引列的数量或限制要减少的分区数量分析表
的复杂性。表的主键中的列数也很重要,因为主键列被追加到每个非唯一索引。
默认情况下,InnoDB
在计算统计信息时读取未提交的数据。对于从表中删除行的未提交事务,在计算行估计和索引统计信息时将排除已删除标记的记录,这可能导致使用事务隔离级别以外的事务并发地在表上操作的其他事务的非最佳执行计划读未提交
.为了避免这种情况,innodb_stats_include_delete_marked
可启用,以确保在计算持久优化器统计信息时包括已删除标记的记录。
当innodb_stats_include_delete_marked
启用,分析表
重新计算统计信息时考虑已删除标记的记录。
innodb_stats_include_delete_marked
全局设置是否会影响所有人InnoDB
表,并且仅适用于持久优化器统计信息。
类中的内部管理表是持久统计特性的基础mysql
数据库,命名innodb_table_stats
而且innodb_index_stats
.这些表是在所有安装、升级和从源代码构建过程中自动设置的。
表15.6 innodb_table_stats .列说明
列名 | 描述 |
---|---|
database_name |
数据库名称 |
table_name |
表名、分区名或子分区名 |
last_update |
一个时间戳,表示最后一次InnoDB 更新本行 |
n_rows |
表中的行数 |
clustered_index_size |
主索引的大小,以页为单位 |
sum_of_other_index_sizes |
其他(非主要)索引的总大小(以页为单位) |
表15.7 innodb_index_stats . properties字段说明
列名 | 描述 |
---|---|
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
可以手动更新表,这使得在不修改数据库的情况下强制执行特定的查询优化计划或测试替代计划成为可能。如果手动更新统计信息,请使用刷新表
语句加载更新的统计信息。tbl_name
持久统计信息被认为是本地信息,因为它们与服务器实例相关。的innodb_table_stats
而且innodb_index_stats
因此,当发生自动统计信息重新计算时,不会复制表。如果你跑了分析表
为了启动统计信息的同步重新计算,将复制语句(除非您抑制了它的日志记录),并在副本上进行重新计算。
的innodb_table_stats
表为每个表包含一行。下面的示例演示了收集的数据类型。
表格t1
包含主索引(列)一个
,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));
插入五行样本数据后,表t1
如下所示:
mysql> SELECT * FROM t1;+---+---+------+------+------+------+ | e d c a | | | | | f | +---+---+------+------+------+------+ | 10 1 | 1 | | 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 | +---+---+------+------+------+------+
执行命令,立即更新统计信息分析表
(如果innodb_stats_auto_recalc
启用后,统计信息将在几秒内自动更新,假设已达到更改表行的10%阈值):
mysql>分析表t1;+---------+---------+----------+----------+ | 表| Op | Msg_type | Msg_text | +---------+---------+----------+----------+ | 测试。t1 |分析| |好状态 | +---------+---------+----------+----------+
表的表统计信息t1
最后一遍InnoDB
更新表统计信息(2014-03-14 14:36:34
),表示表中的行数(5
),则聚集索引大小(1
页),以及其他索引的合并大小(2
页)。
mysql> SELECT * FROM mysqlinnodb_table_stats在哪里table_namelike '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_descriptioninnodb_index_stats WHERE table_name like t1;+------------+--------------+------------+-----------------------------------+ | index_name | stat_name | stat_value | stat_description | +------------+--------------+------------+-----------------------------------+ | 主要| n_diff_pfx01 | 1 | | |主| n_diff_pfx02 | 5 | a, b | | |主要n_leaf_pages | 1 |数量的叶子页索引| |主| | 1 |大小的页面数量在索引中| | i1 | n_diff_pfx01 c | 1 | | | i1 | n_diff_pfx02 | 2 | c, d | | i1 | n_diff_pfx03 | 2 |i1 c, d, | | | n_diff_pfx04 | 5 | c, d, a, b | | i1 | n_leaf_pages | 1 |数量的叶子页索引| | i1 | | 1 |大小的页面数量在索引中| | i2uniq | n_diff_pfx01 | 2 | e | | i2uniq | n_diff_pfx02 | 5 | e, f | | i2uniq | n_leaf_pages | 1 |数量的叶子页索引| | i2uniq | | 1 |大小的页面数量在索引中 | +------------+--------------+------------+-----------------------------------+
的stat_name
列显示以下类型的统计信息:
大小
:stat_name
=大小
,stat_value
列显示索引中的总页数。n_leaf_pages
:stat_name
=n_leaf_pages
,stat_value
列显示索引中的叶页数量。n_diff_pfx
:神经网络
stat_name
=n_diff_pfx01
,stat_value
列显示索引的第一列中不同值的数量。在哪里stat_name
=n_diff_pfx02
,stat_value
列显示索引的前两列中不同值的数量,依此类推。在哪里stat_name
=n_diff_pfx
,神经网络
stat_description
Column显示被计数的索引列的逗号分隔列表。
为了进一步说明n_diff_pfx
统计,它提供基数数据,再次考虑神经网络
t1
前面介绍过的表格示例。如下所示,t1
表是用主索引(列)创建的一个
,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));
插入五行样本数据后,表t1
如下所示:
mysql> SELECT * FROM t1;+---+---+------+------+------+------+ | e d c a | | | | | f | +---+---+------+------+------+------+ | 10 1 | 1 | | 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_descriptioninnodb_index_stats WHERE table_name like 't1' AND stat_name like 'n_diff%';+------------+--------------+------------+------------------+ | index_name | stat_name | stat_value | stat_description | +------------+--------------+------------+------------------+ | 主要| n_diff_pfx01 | 1 | | |主| n_diff_pfx02 | 5 | a, b | | i1 | n_diff_pfx01 c | 1 | | | i1 | n_diff_pfx02 | 2 | c, d | | i1 | n_diff_pfx03 | 2 | c, d,一个| | i1 | n_diff_pfx04 | 5 | c, d, a, b | | i2uniq | n_diff_pfx01 | 2 | e | | i2uniq | n_diff_pfx02 | 5 | e, f |+------------+--------------+------------+------------------+
为主要的
索引,有两个n_diff %
行。行数等于索引中的列数。
对于非唯一索引,InnoDB
追加主键的列。
在哪里
index_name
=主要的
而且stat_name
=n_diff_pfx01
,stat_value
是1
,这表明在索引(column . index)的第一列中只有一个不同的值一个
).列中不同值的数目一个
是否通过查看列中的数据确认一个
在表t1
,其中只有一个不同的值(1
).已计数的列(一个
)载于stat_description
结果集的列。在哪里
index_name
=主要的
而且stat_name
=n_diff_pfx02
,stat_value
是5
,表示索引的两列中有五个不同的值(a、b
).列中不同值的数目一个
而且b
是否通过查看列中的数据确认一个
而且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
,这表明在索引(column . index)的第一列中只有一个不同的值c
).列中不同值的数目c
是否通过查看列中的数据确认c
在表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
,一个
是否通过查看列中的数据确认c
,d
,一个
在表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
,一个
而且b
是否通过查看列中的数据确认c
,d
,一个
,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
,这表明在索引的第一列(column .)中有两个不同的值e
).列中不同值的数目e
是否通过查看列中的数据确认e
在表t1
,其中有两个不同的值:(One hundred.
)及(200
).已计数的列(e
)载于stat_description
结果集的列。在哪里
index_name
=i2uniq
而且stat_name
=n_diff_pfx02
,stat_value
是5
,表示索引的两列中有五个不同的值(e, f
).列中不同值的数目e
而且f
是否通过查看列中的数据确认e
而且f
在表t1
,其中有五个不同的值:(100101年
), (200102年
), (100103年
), (200104年
),及(100105年
).已计数的列(e, f
)载于stat_description
结果集的列。
属性可以检索表、分区或子分区的索引大小innodb_index_stats
表格在下面的示例中,检索表的索引大小t1
.表的定义t1
和相应的指数统计,见章节15.8.10.1.6,InnoDB持久统计表示例.
mysql> SELECT SUM(stat_value) pages, index_name, SUM(stat_value)*@@innodb_page_sizeinnodb_index_stats WHERE table_name='t1' AND stat_name =' size'+-------+------------+-------+ | 页| index_name |大小 | +-------+------------+-------+ | i1主要| | 16384 | | 1 | | 16384 | | 1 | i2uniq | 16384年 | +-------+------------+-------+
对于分区或子分区,可以使用相同的查询在哪里
子句检索索引大小。例如,下面的查询检索表分区的索引大小t1
:
mysql> SELECT SUM(stat_value) pages, index_name, SUM(stat_value)*@@innodb_page_sizeinnodb_index_stats WHERE table_name like 't1#P%' AND stat_name = 'size'