14.8.11.1 配置持久性优化器统计参数

持久性优化器统计信息功能通过将统计信息存储到磁盘并使它们在服务器重新启动时保持持久性来改进plan stability，因此optimizer更有可能每次为给定查询做出一致的选择。

当innodb_stats_persistent=ON或使用STATS_PERSISTENT=1定义单个 table 时，优化器统计信息将保留在磁盘上。 innodb_stats_persistent默认为启用状态。

以前，重新启动服务器时以及执行某些其他类型的操作后，会清除优化器统计信息，并在下次访问 table 时重新计算。因此，当重新计算统计信息时会产生不同的估计，从而导致查询执行计划中的选择不同以及查询性能发生变化。

永久统计信息存储在mysql.innodb_table_stats和mysql.innodb_index_statstable 中。参见第 14.8.11.1.5 节“ InnoDB 持久统计 table”。

如果您不想将优化器统计信息持久保存到磁盘，请参阅第 14.8.11.2 节，“配置非持久性优化器统计参数”

14.8.11.1.1 为持久性优化器统计信息配置自动统计信息

默认情况下启用的innodb_stats_auto_recalc变量控制当 table 的行更改超过其行的 10％时是否自动计算统计信息。您还可以通过在创建或更改 table 时指定STATS_AUTO_RECALC子句来配置各个 table 的自动统计信息重新计算。

由于自动统计信息重新计算的异步特性(在后台发生)，即使运行了innodb_stats_auto_recalc的 DML 操作(影响到 table 的 10％以上)，也可能不会立即重新计算统计信息。在某些情况下，统计信息的重新计算可能会延迟几秒钟。如果立即需要最新的统计信息，请运行ANALYZE TABLE来启动同步(前景)统计信息的重新计算。

如果禁用了innodb_stats_auto_recalc，则可以在对索引列进行实质性更改之后执行ANALYZE TABLE语句，从而确保优化程序统计信息的准确性。您可能还考虑将ANALYZE TABLE添加到在加载数据后运行的安装脚本中，并在活动较少时按计划运行ANALYZE TABLE。

将索引添加到现有 table 时，或者添加或删除列时，无论innodb_stats_auto_recalc的值如何，都会计算索引统计信息并将其添加到innodb_index_statstable 中。

14.8.11.1.2 为各个 table 配置优化器统计参数

innodb_stats_persistent，innodb_stats_auto_recalc和innodb_stats_persistent_sample_pages是全局变量。要覆盖这些系统范围的设置并为单个 table 配置优化器统计信息参数，可以在CREATE TABLE或ALTER TABLE语句中定义STATS_PERSISTENT，STATS_AUTO_RECALC和STATS_SAMPLE_PAGES子句。

在以下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;
14.8.11.1.3 配置 InnoDB Optimizer 统计信息的采样页数

优化器基于索引的相对selectivity，使用有关键分布的估计值statistics选择执行计划的索引。诸如ANALYZE TABLE之类的操作使InnoDB对 table 中每个索引的随机页面进行采样，以估计索引的cardinality。这种采样技术称为random dive。

innodb_stats_persistent_sample_pages控制采样页数。您可以在运行时调整设置，以 Management 优化器使用的统计信息估计的质量。默认值为 20.遇到以下问题时，请考虑修改设置：

如果确定统计数据不够准确，则应增加innodb_stats_persistent_sample_pages的值，直到统计数据估计值足够准确为止。但是，过多增加innodb_stats_persistent_sample_pages可能会导致ANALYZE TABLE运行缓慢。

如果无法在准确的统计信息和ANALYZE TABLE执行时间之间取得平衡，请考虑减少 table 中索引列的数量或限制分区的数量以降低ANALYZE TABLE的复杂性。table 的主键中的列数也很重要，因为主键列被附加到每个非唯一索引中。

有关相关信息，请参见第 14.8.11.3 节“估计 InnoDBtable 的分析 table 复杂性”。

14.8.11.1.4 在永久统计信息计算中包括删除标记的记录

默认情况下，InnoDB在计算统计信息时读取未提交的数据。如果是未提交的事务，它从 table 中删除行，则在计算行估计和索引统计信息时会排除带有删除标记的记录，这可能导致使用事务同时在 table 上进行操作的其他事务的执行计划不理想。 READ UNCOMMITTED以外的隔离级别。为避免这种情况，可以启用innodb_stats_include_delete_marked以确保在计算持久性优化器统计信息时包括删除标记的记录。

启用innodb_stats_include_delete_marked时，ANALYZE TABLE在重新计算统计信息时会考虑删除标记的记录。

innodb_stats_include_delete_marked是会影响所有InnoDBtable 的全局设置，并且仅适用于持久性优化器统计信息。

innodb_stats_include_delete_marked是 MySQL 5.7.16 中引入的。

14.8.11.1.5 InnoDB 持久统计 table

持久性统计信息功能依赖于mysql数据库中名为innodb_table_stats和innodb_index_stats的内部 Managementtable。这些 table 是在所有安装，升级和从源构建过程中自动设置的。

table14.4 innodb_table_stats 的列

Column name Description
database_name Database name
table_name table 名，分区名或子分区名
last_update 指示上次更新行的时间戳
n_rows table 中的行数
clustered_index_size 主索引的大小，以页为单位
sum_of_other_index_sizes 其他(非主)索引的总大小(以页为单位)

table14.5 innodb_index_stats 的列

Column name Description
database_name Database name
table_name table 名，分区名或子分区名
index_name Index name
last_update 指示InnoDB更新此行的最后时间的时间戳记
stat_name 统计信息的名称，其值在stat_value列中报告
stat_value 在stat_name列中命名的统计信息的值
sample_size stat_value列中提供的估算 samples 页数
stat_description stat_name列中命名的统计信息的描述

innodb_table_stats和innodb_index_statstable 包括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_statstable 可以手动更新，从而可以在不修改数据库的情况下强制执行特定的查询优化计划或测试替代计划。如果您手动更新统计信息，请使用FLUSH TABLE tbl_name语句加载更新的统计信息。

持久统计信息被视为本地信息，因为它们与服务器实例相关。因此，在自动统计信息重新计算时，不会复制innodb_table_stats和innodb_index_statstable。如果运行ANALYZE TABLE以启动统计信息的同步重新计算，则将复制此语句(除非您禁止对其进行日志记录)，并且将对副本进行重新计算。

14.8.11.1.6 InnoDB 持久统计 table 示例

innodb_table_statstable 每个 table 包含一行。以下示例演示了收集的数据类型。

tablet1包含一个主索引(第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;

插入五行 samples 数据后，tablet1如下所示：

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，则假设达到已更改 table 行的 10％阈值，统计信息将在几秒钟内自动更新)：

mysql> ANALYZE TABLE t1;
+---------+---------+----------+----------+
| Table   | Op      | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.t1 | analyze | status   | OK       |
+---------+---------+----------+----------+

tablet1的 table 统计信息显示InnoDB上次更新 table 统计信息(2014-03-14 14:36:34)，table 中的行数(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_statstable 包含每个索引的多个行。 innodb_index_statstable 中的每一行都提供与特定索引统计信息相关的数据，该统计信息在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列显示以下统计信息类型：

为了进一步说明提供基数数据的n_diff_pfxNN统计量，请再次考虑先前介绍的t1table 示例。如下所示，使用主索引(列a，b)，辅助索引(列c，d)和唯一索引(列e，f)创建t1table：

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;

插入五行 samples 数据后，tablet1如下所示：

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%行。行数等于索引中的列数。

Note

对于非唯一索引，InnoDB附加主键的列。

对于二级索引(i1)，有四个n_diff%行。二级索引(c,d)仅定义了两列，但二级索引有四行n_diff%行，因为InnoDB为所有非唯一索引添加了主键。结果，有四个n_diff%行，而不是两个，分别用于辅助索引列(c,d)和主键列(a,b)。

对于唯一索引(i2uniq)，有两个n_diff%行。

14.8.11.1.7 使用 innodb_index_statstable 检索索引大小

您可以使用innodb_index_statstable 来检索 table，分区或子分区的索引大小。在以下示例中，检索 tablet1的索引大小。有关 tablet1的定义和相应的索引统计信息，请参见第 14.8.11.1.6 节“ InnoDB 持久统计 table 示例”。

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子句使用相同的查询来检索索引大小。例如，以下查询检索 tablet1的分区的索引大小：

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;
首页