Documentation Home

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

持久优化器统计功能 通过将统计信息存储到磁盘并使它们在服务器重新启动时持久化来 提高计划稳定性,以便优化器更有可能在每次给定查询时做出一致的选择。

innodb_stats_persistent=ON当使用 定义单个表 时,优化器统计信息将保存到磁盘 STATS_PERSISTENT=1innodb_stats_persistent默认情况下启用。

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

持久统计信息存储在 mysql.innodb_table_statsmysql.innodb_index_stats表中。请参阅 第 14.8.11.1.5 节,“InnoDB 持久统计表”

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

14.8.11.1.1 配置持久优化器统计的自动统计计算

默认情况下启用的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

14.8.11.1.2 为单个表配置优化器统计参数

innodb_stats_persistent, innodb_stats_auto_recalc, 和 innodb_stats_persistent_sample_pages 是全局变量。要覆盖这些系统范围的设置并为单个表配置优化器统计参数,您可以在or 语句 中定义STATS_PERSISTENTSTATS_AUTO_RECALCSTATS_SAMPLE_PAGES子句 。CREATE TABLEALTER 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;
14.8.11.1.3 配置InnoDB优化器统计的采样页数

优化器使用有关键分布的估计 统计信息,根据索引的相对选择性为执行计划 选择索引。诸如ANALYZE TABLE导致InnoDB从表上的每个索引中随机抽样页面以估计索引的 基数等操作。这种采样技术被称为 随机潜水

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

  1. 统计信息不够准确,优化器选择了次优计划,如 EXPLAIN输出所示。您可以通过将索引的实际基数(通过 SELECT DISTINCT在索引列上运行确定)与 mysql.innodb_index_stats表中的估计值进行比较来检查统计信息的准确性。

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

  2. ANALYZE TABLE太慢了。在这种情况下 innodb_stats_persistent_sample_pages ,应该减少直到ANALYZE TABLE执行时间可以接受为止。但是,将值减小太多可能会导致第一个问题,即不准确的统计信息和次优的查询执行计划。

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

    有关相关信息,请参阅 第 14.8.11.3 节,“估计 InnoDB 表的分析表复杂性”

14.8.11.1.4 在持久统计计算中包括删除标记的记录

默认情况下,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 中引入。

14.8.11.1.5 InnoDB 持久统计表

持久统计功能依赖于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_statsinnodb_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_statsinnodb_index_stats这使得在不修改数据库的情况下强制执行特定的查询优化计划或测试替代计划成为可能。如果您手动更新统计信息,请使用该 语句加载更新的统计信息。 FLUSH TABLE tbl_name

持久统计信息被认为是本地信息,因为它们与服务器实例相关。因此 ,当发生自动统计重新计算时,不会复制innodb_table_statsinnodb_index_stats表。如果您运行ANALYZE TABLE 以启动统计信息的同步重新计算,则会复制此语句(除非您抑制了它的日志记录),并且在副本上进行重新计算。

14.8.11.1.6 InnoDB 持久统计表示例

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

t1包含主索引(列 ab)二级索引(列cd)和唯一索引(列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)、聚簇索引大小 ( 1page) 以及其他索引的组合大小 ( 2pages)。

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_namestat_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

为了进一步说明 提供基数数据的统计信息,请再次考虑之前介绍的表示例。如下所示,该 表是使用主索引(列,)、二级索引(列, )和唯一索引(列 ,)创建的: 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, 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=PRIMARYstat_name= n_diff_pfx01stat_value即,表示索引的第一列(列)1中有一个不同的值 。a列中不同值的数量a通过查看a表中列中 的数据来确认t1,其中有一个不同值 ( 1)。被统计的列(a)显示在 stat_description结果集的列中。

  • 其中 index_name=PRIMARYstat_name= n_diff_pfx02stat_value5,表示索引的两列中有五个不同的值 ( a,b)。a 列和中不同值的数量b通过查看列ab 表中的数据来确认t1,其中有五个不同值:( 1,1)、( 1,2)、( 1,3)、( 1,4) 和 ( 1,5)。计数的列 ( a,b) 显示在 stat_description结果集的列中。

对于二级索引 ( i1),有n_diff%四行。只为二级索引 ( ) 定义了两列,但二级索引c,dn_diff%四行,因为InnoDB所有非唯一索引都带有主键后缀。因此,有n_diff%四行而不是两行来说明二级索引列 ( c,d) 和主键列 ( a,b)。

  • 其中index_name=i1stat_name= n_diff_pfx01stat_value即,表示索引的第一列(列)1中有一个不同的值 。c列中不同值的数量c通过查看c表中列中 的数据来确认t1,其中有一个不同值:( 10)。被统计的列(c)显示在 stat_description结果集的列中。

  • 其中index_name=i1stat_name= n_diff_pfx02stat_value2,表示索引的前两列中有两个不同的值 ( c,d)。通过查看列和 表中的数据来确认列 can中不同值的数量,其中有两个不同值:( ) 和 ( )。计数的列 ( ) 显示在结果集的列中。 dcdt110,1110,12c,dstat_description

  • 其中index_name=i1stat_name= n_diff_pfx03stat_value2,表示索引的前三列中有两个不同的值 ( c,d,a)。列 、和 中不同值的数量通过查看列 、 和表 中的数据来确认c,其中 有两个不同值:( ) 和 ( )。计数的列 ( ) 显示在 结果集的列中。 dacdat110,11,110,12,1c,d,astat_description

  • 其中index_name=i1stat_name= n_diff_pfx04stat_value5,表示索引的四列中有五个不同的值 ( c,d,a,b)。列 、和中不同值的数量 通过查看列 、 、 和表 中的数据来确认cd其中 有 五个不同值 :( )、( )、( )、( ) 和 (abcdabt110,11,1,110,11,1,210,11,1,310,12,1,410,12,1,5). 计数的列 ( c,d,a,b) 显示在 stat_description结果集的列中。

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

  • 其中 index_name=i2uniqstat_name= n_diff_pfx01stat_value2,表示索引的第一列(列e)中有两个不同的值。列中不同值的数量 e通过查看e表中列中 的数据来确认t1,其中有两个不同值:( 100) 和 ( 200)。被统计的列(e)显示在 stat_description结果集的列中。

  • Where index_name=i2uniq and stat_name=n_diff_pfx02, the stat_value is 5, which indicates that there are five distinct values in the two columns of the index (e,f). The number of distinct values in columns e and f is confirmed by viewing the data in columns e and f in table t1, 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 the stat_description column of the result set.

14.8.11.1.7 使用 innodb_index_stats 表检索索引大小

您可以使用表检索表、分区或子分区的索引大小 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;