配置持久性优化器统计信息

本节介绍如何为表配置持久性和非持久性优化器统计信息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_statsmysql.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 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;

配置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_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_pfxNN：其中stat_name=n_diff_pfx01，此stat_value列在索引的第一列中显示不同值的数量。其中stat_name=n_diff_pfx02，此stat_value列显示索引的前两列中不同值的数量，依此类推。其中stat_name=，该列显示以逗号分隔的索引列的列表。n_diff_pfxNNstat_description

为了进一步说明提供基数数据的统计信息，请再次考虑先前介绍的表格示例。如下所示，使用主索引（columns ，），辅助索引（columns ，）和唯一索引（columns ，）创建表：n_diff_pfxNNt1t1abcdef

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_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_valueis 1，表示在索引的第一列（列a）中存在一个唯一的不同值。a通过参见a表中列中的数据来确认列中不同值的数量t1，其中只有一个不同值（1）。计数的列（a）显示在stat_description结果集的列中。
其中index_name=PRIMARY和stat_name=n_diff_pfx02，stat_valueis是5，表示在索引（a,b）的两列中有五个不同的值。通过参见列和表中的数据来确认列中的a和的不同值的数量，其中有五个不同的值：（），（），（），（）和（）。计数的列（）显示在结果集的列中。babt11,11,21,31,41,5a,bstat_description

对于二级索引（i1），有n_diff%四行。仅为辅助索引（c,d）定义了两列，但为辅助索引有n_diff%四行，因为InnoDB所有非唯一索引都以主键作为后缀。因此，n_diff%要考虑二级索引列（c,d）和主键列（a,b），需要四行而不是两行。

其中index_name=i1和stat_name=n_diff_pfx01，为stat_valueis 1，表示在索引的第一列（列c）中存在一个唯一的不同值。c通过参见ctable 中列中的数据来确认列中不同值的数量t1，其中只有一个不同值：（10）。计数的列（c）显示在stat_description结果集的列中。
其中index_name=i1和stat_name=n_diff_pfx02，stat_value是2，表示在索引（c,d）的前两列中有两个不同的值。通过参见列和表中的数据来确认列中c的不同值的数量，其中有两个不同的值：（）和（）。计数的列（）显示在结果集的列中。dcdt110,1110,12c,dstat_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_valueis 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,310,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_valueis是2，表示在索引的第一列（列e）中有两个不同的值。e通过参见e表中列中的数据来确认列中不同值的数量t1，其中有两个不同值：（100）和（200）。计数的列（e）显示在stat_description结果集的列中。
其中index_name=i2uniq和stat_name=n_diff_pfx02，stat_valueis是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 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子句一起使用以检索索引大小。例如，以下查询检索table的分区的索引大小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;