MySQL统计信息-阿里云开发者社区

1. InnoDB统计信息介绍

本节介绍如何为表配置持久性和非持久性优化程序统计信息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表中查询优化统计信息。
您可以查看表mysql.innodb_table_stats和mysql.innodb_index_stats的last_update列，确认上次更新统计信息的时间。
您可以手动修改mysql.innodb_table_stats和 mysql.innodb_index_stats表以强制执行特定的查询优化计划，或者在不修改数据库的情况下测试备用计划。

默认情况下（innodb_stats_persistent=ON）启用持久优化程序统计信息功能。

非持久优化器统计信息在每次重新启动服务器和其他一些操作后清除，并在下一个表访问时重新计算。因此，在重新计算统计数据时可能会产生不同的估计值，从而导致执行计划的不同选择和查询性能的变化。

2. 配置持久优化器统计参数

持久优化程序统计信息功能通过将统计信息存储到磁盘并使其在服务器重新启动期间保持不变来提高计划稳定性，以便优化程序更有可能每次为给定查询做出一致的选择。

当innodb_stats_persistent = ON或使用STATS_PERSISTENT = 1创建或更改单个表时，优化程序统计信息将持久保存到mysql.innodb_table_stats和mysql.innodb_index_stats表。
参数innodb_stats_persistent默认情况下是启用。要恢复使用非持久优化程序统计信息，可以使用ALTER TABLE tbl_name STATS_PERSISTENT = 0语句修改表。

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

2.1. 配置持久优化器统计信息的自动统计信息计算

innodb_stats_auto_recalc配置选项（默认情况下已启用）确定是否在表经历实质性更改（超过10％的行）时自动计算统计信息。您还可以使用CREATE TABLE或ALTER TABLE语句中的STATS_AUTO_RECALC子句为各个表配置自动统计信息重新计算。 innodb_stats_auto_recalc默认启用。

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

如果禁用了innodb_stats_auto_recalc，请在对索引列进行实质性更改后，通过为每个适用的表发出ANALYZE TABLE语句来确保优化程序统计信息的准确性。在将代表性数据加载到表中后，您可以在设置脚本中运行此语句，并在DML操作显着更改索引列的内容之后定期运行此语句，或者在活动较少时按计划运行。将新索引添加到现有表或添加或删除列时，将计算索引统计信息并将其添加到innodb_index_stats表，而不管innodb_stats_auto_recalc的值如何。

警告：要确保在创建新索引时收集统计信息，请启用innodb_stats_auto_recalc选项，或在启用持久统计模式后创建每个新索引后运行ANALYZE TABLE。

2.2. 配置各个表的优化器统计参数

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

STATS_PERSISTENT指定是否为InnoDB表启用持久统计信息。值DEFAULT导致表的持久统计信息设置由innodb_stats_persistent配置选项确定。值1启用表的持久统计信息，而值0关闭此功能。通过CREATE TABLE或ALTER TABLE语句启用持久性统计信息后，在将代表性数据加载到表中后，发出ANALYZE TABLE语句以计算统计信息。
STATS_AUTO_RECALC指定是否自动重新计算InnoDB表的持久统计信息。值DEFAULT导致表的持久统计信息设置由innodb_stats_auto_recalc配置选项确定。值1导致在表中10％的数据发生更改时重新计算统计信息。值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;

2.3. 配置InnoDB优化器统计信息的采样页数

MySQL查询优化器使用关于关键分布的估计统计信息，根据索引的相对选择性为执行计划选择索引。诸如ANALYZE TABLE之类的操作使InnoDB从表上的每个索引中对随机页面进行采样，以估计索引的基数。（这种技术称为随机潜水。）

为了控制统计估计的质量（以及查询优化器的更好信息），您可以使用参数innodb_stats_persistent_sample_pages更改采样页面的数量，该参数可以在运行时设置。

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复杂性。表的主键中的列数也很重要，因为主键列会附加到每个非唯一索引。

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

默认情况下，InnoDB在计算统计信息时会读取未提交的数据。对于从表中删除行的未提交事务，InnoDB会排除在计算行估计和索引统计信息时删除标记的记录，这可能导致同时使用表上运行的其他事务的非最佳执行计划除READ UNCOMMITTED之外的事务隔离级别。为了避免这种情况，可以启用innodb_stats_include_delete_marked以确保在计算持久优化器统计信息时InnoDB包含删除标记的记录。

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

innodb_stats_include_delete_marked是一个影响所有InnoDB表的全局设置，它仅适用于持久优化器统计信息。

innodb_stats_include_delete_marked是在MySQL 5.7.16中引入的。

2.5. 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	一个时间戳，指示上次InnoDB 更新此行的时间
stat_name	统计信息的名称，其值在stat_value列中报告
stat_value	stat_name 列中命名的统计信息的值
sample_size	为stat_value列中提供的估算采样的页数
stat_description	stat_name列中指定的统计信息的描述

innodb_table_stats和innodb_index_stats表都包含一个last_update列，显示InnoDB上次更新索引统计信息的时间，如以下示例所示：

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命令以使MySQL重新加载更新的统计信息。

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

2.6. InnoDB持久性统计表示例

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

表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;

插入五行样本数据后，表格如下所示：

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列显示索引第一列中的不同（distinct）值的数量。 where stat_name = n_diff_pfx02，stat_value列显示索引前两列中的不同（distinct）值的数量，依此类推。此外，在stat_name = n_diff_pfxNN的情况下，stat_description列显示了计算的索引列的逗号分隔列表。

为了进一步说明提供基数数据的n_diff_pfxNN统计量，再次考虑之前引入的t1表示例。如下所示，使用主索引（列a，b），辅助索引（列c，d）和唯一索引（列e，f）创建t1表：

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;

插入五行样本数据后，表格如下所示：

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_value为1，表示索引的第一列中存在单个不同的值（列a）。通过查看表t1中的列a中的数据来确认列a中的不同值的数量，其中存在单个不同的值（1）。计数列（a）显示在结果集的stat_description列中。
其中index_name = PRIMARY和stat_name = n_diff_pfx02，stat_value为5，表示索引（a，b）的两列中有五个不同的值。通过查看表t1中列a和b中的数据来确认列a和b中的不同值的数量，其中有五个不同的值：（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，表示索引的第一列（列c）中存在单个不同的值。通过查看表t1中列c中的数据来确认列c中的不同值的数量，其中存在单个不同的值：（10）。计数列（c）显示在结果集的stat_description列中。
其中index_name = i1和stat_name = n_diff_pfx02，stat_value为2，表示索引（c，d）的前两列中有两个不同的值。通过查看表t1中列c和d中的数据来确认列c和d中的不同值的数量，其中存在两个不同的值：（10,11）和（10,12）。计数列（c，d）显示在结果集的stat_description列中。
其中index_name = i1和stat_name = n_diff_pfx03，stat_value为2，表示索引的前三列中有两个不同的值（c，d，a）。通过查看表c1中的列c，d和a中的数据来确认列c，d和a中的不同值的数量，其中有两个不同的值：（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）。通过查看表t1中列c，d，a和b中的数据来确认列c，d，a和b中的不同值的数量，其中有五个不同的值：（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，表示索引的第一列中有两个不同的值（列e）。通过查看表t1中的列e中的数据来确认列e中的不同值的数量，其中存在两个不同的值：（100）和（200）。计数列（e）显示在结果集的stat_description列中。
其中index_name = i2uniq和stat_name = n_diff_pfx02，stat_value为5，表示索引的两列中有五个不同的值（e，f）。通过查看表t1中的列e和f中的数据来确认列e和f中的不同值的数量，其中存在五个不同的值：（100,101），（200,102），（100,103），（200,104）和（ 100105）。计数列（e，f）显示在结果集的stat_description列中。

2.7. 使用innodb_index_stats表检索索引大小

可以使用innodb_index_stats表检索表，分区或子分区的索引大小。在以下示例中，为表t1检索索引大小。

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;

3. 配置非持续优化统计参数

本节介绍如何配置非持久优化程序统计信息。当innodb_stats_persistent = OFF或使用STATS_PERSISTENT = 0创建或更改单个表时，优化程序统计信息不会保留到磁盘。相反，统计信息存储在内存中，并在服务器关闭时丢失。某些业务和某些条件下也会定期更新统计数据。

从MySQL 5.6.6开始，默认情况下，优化程序统计信息会持久保存到磁盘，并由innodb_stats_persistent配置选项启用。

3.1 优化程序统计信息更新

在以下情况下更新非持久优化程序统计信息：

运行ANALYZE TABLE。
运行SHOW TABLE STATUS，SHOW INDEX或查询INFORMATION_SCHEMA.TABLES或INFORMATION_SCHEMA.STATISTICS表并启用innodb_stats_on_metadata选项。
默认情况下，在MySQL 5.6.6中启用持久优化程序统计信息时，innodb_stats_on_metadata的默认设置已更改为OFF。启用innodb_stats_on_metadata可能会降低具有大量表或索引的模式的访问速度，并降低涉及InnoDB表的查询的执行计划的稳定性。使用SET语句全局配置innodb_stats_on_metadata。

SET GLOBAL innodb_stats_on_metadata=ON

innodb_stats_on_metadata仅在优化程序统计信息配置为非持久性时（禁用innodb_stats_persistent时）应用。

打开mysql客户端并启用了--auto-rehash选项，这是默认设置。 auto-rehash选项会导致打开所有InnoDB表，并且open table操作会导致重新计算统计信息。
要改善mysql客户端的启动时间并更新统计信息，可以使用--disable-auto-rehash选项关闭自动重新连接。自动重新哈希功能可以为交互式用户自动完成数据库，表和列名称的名称。
第一次打开一个表。
自上次统计更新以来，InnoDB检测到1/16的表已被修改。

3.2. 配置采样页数

MySQL查询优化器使用关于关键分布的估计统计信息，根据索引的相对选择性为执行计划选择索引。当InnoDB更新优化程序统计信息时，它会对表中每个索引的随机页面进行采样，以估计索引的基数。（这种技术称为随机潜水。）

为了控制统计估计的质量（以及查询优化器的更好信息），您可以使用参数innodb_stats_transient_sample_pages更改采样页面的数量。默认的采样页数为8，这可能不足以产生准确的估计值，导致查询优化器选择的索引较差。此技术对于连接中使用的大型表和表尤其重要。对这些表进行不必要的全表扫描可能是一个重大的性能问题。innodb_stats_transient_sample_pages是一个可以在运行时设置的全局参数。

当innodb_stats_persistent = 0时，innodb_stats_transient_sample_pages的值会影响所有InnoDB表和索引的索引采样。更改索引样本大小时，请注意以下潜在的重大影响：

像1或2这样的小值可能导致基数估计不准确。
增加innodb_stats_transient_sample_pages值可能需要更多磁盘读取。大于8（例如，100）的值可能导致打开表或执行SHOW TABLE STATUS所花费的时间显着减慢。
优化器可能会根据索引选择性的不同估计选择非常不同的查询计划。

无论innodb_stats_transient_sample_pages的值是什么，最适合系统，设置选项并将其保留在该值。选择一个值，可以对数据库中的所有表进行合理准确的估计，而不需要过多的I / O.由于除了执行ANALYZE TABLE之外的其他时间都会自动重新计算统计信息，因此增加索引样本大小，运行ANALYZE TABLE，然后再次减小样本大小是没有意义的。

较小的表通常比较大的表需要更少的索引样本。如果您的数据库有许多大表，请考虑使用较高的innodb_stats_transient_sample_pages值，而不是使用较大的表。

4. 估算InnoDB表的ANALYZE TABLE复杂度

InnoDB表的ANALYZE TABLE复杂性取决于：

采样的页数，由innodb_stats_persistent_sample_pages定义。
表中索引列的数量
分区数量。如果表没有分区，则分区数被视为1。

使用这些参数，估计ANALYZE TABLE复杂度的近似公式为：
innodb_stats_persistent_sample_pages的值表中索引列数分区数

通常，结果值越大，ANALYZE TABLE的执行时间越长。

注：
innodb_stats_persistent_sample_pages定义在全局级别采样的页数。要设置单个表的采样页数，请使用带有CREATE TABLE或ALTER TABLE的STATS_SAMPLE_PAGES选项。有关更多信息，请参见第14.8.11.1节“配置持久优化器统计信息参数”。
如果innodb_stats_persistent = OFF，则采样的页数由innodb_stats_transient_sample_pages定义。有关其他信息，请参见第14.8.11.2节“配置非持久优化器统计信息参数”。

有关估计ANALYZE TABLE复杂性的更深入方法，请考虑以下示例。
在Big O表示法中，ANALYZE TABLE复杂度描述为：

O(n_sample
  * (n_cols_in_uniq_i
     + n_cols_in_non_uniq_i
     + n_cols_in_pk * (1 + n_non_uniq_i))
  * n_part)

哪里：

n_sample是采样的页数（由innodb_stats_persistent_sample_pages定义）
n_cols_in_uniq_i是所有唯一索引中所有列的总数（不包括主键列）
n_cols_in_non_uniq_i是所有非唯一索引中所有列的总数
n_cols_in_pk是主键中的列数（如果未定义主键，InnoDB会在内部创建单列主键）
n_non_uniq_i是表中非唯一索引的数量
n_part是分区数。如果未定义分区，则该表被视为单个分区。

现在，考虑下面的表（表t），它有一个主键（2列），一个唯一索引（2列）和两个非唯一索引（每列两列）：

CREATE TABLE t (
  a INT,
  b INT,
  c INT,
  d INT,
  e INT,
  f INT,
  g INT,
  h INT,
  PRIMARY KEY (a, b),
  UNIQUE KEY i1uniq (c, d),
  KEY i2nonuniq (e, f),
  KEY i3nonuniq (g, h)
);

对于上述算法所需的列和索引数据，查询表t的mysql.innodb_index_stats持久索引统计表。 n_diff_pfx％统计信息显示为每个索引计算的列。例如，列a和b计入主键索引。对于非唯一索引，除了用户定义的列之外，还会计算主键列（a，b）。

mysql> SELECT index_name, stat_name, stat_description
       FROM mysql.innodb_index_stats WHERE
       database_name='test' AND
       table_name='t' AND
       stat_name like 'n_diff_pfx%';
  +------------+--------------+------------------+
  | index_name | stat_name    | stat_description |
  +------------+--------------+------------------+
  | PRIMARY    | n_diff_pfx01 | a                |
  | PRIMARY    | n_diff_pfx02 | a,b              |
  | i1uniq     | n_diff_pfx01 | c                |
  | i1uniq     | n_diff_pfx02 | c,d              |
  | i2nonuniq  | n_diff_pfx01 | e                |
  | i2nonuniq  | n_diff_pfx02 | e,f              |
  | i2nonuniq  | n_diff_pfx03 | e,f,a            |
  | i2nonuniq  | n_diff_pfx04 | e,f,a,b          |
  | i3nonuniq  | n_diff_pfx01 | g                |
  | i3nonuniq  | n_diff_pfx02 | g,h              |
  | i3nonuniq  | n_diff_pfx03 | g,h,a            |
  | i3nonuniq  | n_diff_pfx04 | g,h,a,b          |
  +------------+--------------+------------------+

根据上面显示的索引统计数据和表定义，可以确定以下值：

n_cols_in_uniq_i，不计算主键列的所有唯一索引中所有列的总数，为2（c和d）
n_cols_in_non_uniq_i，所有非唯一索引中所有列的总数，为4（e，f，g和h）
n_cols_in_pk，主键中的列数为2（a和b）
n_non_uniq_i，表中非唯一索引的数量是2（i2nonuniq和i3nonuniq））
n_part，分区数为1。

您现在可以计算innodb_stats_persistent_sample_pages （2 + 4 + 2 （1 + 2）） 1以确定扫描的叶页数。将innodb_stats_persistent_sample_pages设置为默认值20，并且默认页面大小为16 KiB（innodb_page_size = 16384），您可以估计为表t读取20 12 * 16384字节，或大约4 MiB。

5. 相关参数

5.1 innodb_stats_method=nulls_equal

在收集有关InnoDB表索引值分布的统计信息时，服务器如何处理NULL值
允许的值为nulls_equal，nulls_unequal和nulls_ignored
- 对于nulls_equal，所有NULL索引值被认为是相等的，所有的null值只当作一个
- 对于nulls_unequal，NULL值被认为是不等的，并且每个NULL都会被计算一次
- 对于nulls_ignored，将忽略NULL值
用于生成表统计、索引统计信息的方法会影响优化器行为，如何为查询执行选择索引
全局变量，动态变量，枚举类型，默认值为nulls_equal，有效值为： nulls_equal、nulls_unequal、nulls_ignored

5.2 innodb_stats_auto_recalc=ON

innodb引擎表中的数据发生显着变化后会自动重新计算持久性统计信息。触发阈值是表中行数据的10％发生了变化。要使得自动更新持久化统计信息生效，必须启用innodb_stats_persistent系统参数，启用innodb_stats_persistent之后所有表的统计信息都会自动重新计算，当然在innodb_stats_persistent变量未启用时，可以显式通过在CREATE TABLE或ALTER TABLE语句中指定STATS_PERSISTENT = 1来指定表需要执行自动统计信息重新计算。采样生成统计信息的采样页数由innodb_stats_persistent_sample_pages系统参数控制
全局变量，动态变量，布尔值，默认值为ON

5.3 innodb_stats_include_delete_marked=OFF

默认情况下，InnoDB在计算统计信息时会读取未提交的数据。但在对表中数据执行删除操作时未提交事务的数据行，在InnoDB在计算行估计和索引统计信息时会排除掉这些未提交的DELETE操作对应的打有删除标记的记录，这可能导致在除了READ COMCOMIT隔离级别之外的隔离级别中，在表上并行执行的其他事务的执行计划不是最佳的。为了避免这种情况，可以启用innodb_stats_include_delete_marked，以确保在计算持久性统计信息时，InnoDB把打有删除标记的记录包含进来
当启用innodb_stats_include_delete_marked时，ANALYZE TABLE在重新计算统计信息时会考虑删除标记的记录
innodb_stats_include_delete_marked是会影响所有InnoDB表的全局设置。另外，它仅适用于持久化器统计信息，不适用于非持久化统计信息
全局变量，动态变量，布尔型，默认值为OFF，5.6.35版本引入

5.4 innodb_stats_on_metadata=OFF

该系统参数仅用于非持久化统计信息
当innodb_stats_persistent被禁用（该系统参数是开启持久化统计信息用的）或innodb_stats_persistent参数开启但某表在创建或修改表定义时使用建表选项STATS_PERSISTENT = 0来关闭该表的持久化统计信息时，所有Innodb表或指定了建表选项STATS_PERSISTENT = 0的表的优化器统计信息不会持久存储到磁盘，此时如果启用innodb_stats_on_metadata系统参数，InnoDB会在执行语句（如SHOW TABLE STATUS或SHOW INDEX）或访问INFORMATION_SCHEMA.TABLES、INFORMATION_SCHEMA.STATISTICS表时触发更新非持久性统计信息。（这些更新类似于ANALYZE TABLE触发的统计信息更新），但当禁用innodb_stats_on_metadata系统参数时，InnoDB会在执行可能触发非持久统计信息更新的语句或访问相关表时，不更新非持久化统计信息。禁用该变量在访问大表时可以提高访问速度（小数据量的表建议开启该参数，以提高查询优化器的执行路径判断正确率）。它还可以提高涉及InnoDB表的查询的执行计划的稳定性（开启了这个参数之后执行计划不稳定是因为索引统计信息处于更新状态时，查询优化器可能会忽略这个索引）
触发非持久化统计信息可能的场景有如下一些：
- analyze table
- 第一次open table
- 还有在访问如：information_schema.TABLES、information_schema.STATISTICS、information_schema.PARTITIONS、information_schema.KEY_COLUMN_USAGE、information_schema.TABLE_CONSTRAINTS、information_schema.REFERENTIAL_CONSTRAINTS
- show table status like ‘%tablename%’ 也会触发更新统计信息的操作
- show index from tb_name [where key_name='xx']; 也会触发更新统计信息
- 激活innodb监视器表，如创建innodb_monitor，innodb_lock_monitor表等
- 表中有大量记录修改时，如：对数据表的DML操作占到表总记录数的十六分之一时
收集主要统计信息：
- rec_per_key //每一个key，包含多少记录。在存储引擎
- records_in_table //当前表上，有多少记录。在存储引擎
- rec_in_range //当前表上，在指定范围上有多少记录
全局变量，动态变量，布尔型，5.6.5及其之前的5.6.x版本默认为ON，5.6.6及其之后的5.6.x版本默认为OFF，5.7.x及其之后的版本默认为OFF，要修改该变量必须有super权限

5.5 innodb_stats_persistent=ON

控制是否将InnoDB索引统计信息持久化磁盘中。
索引统计信息可能会频繁地重新计算统计数据，这可能导致查询执行计划频繁地变化。持久化统计信息的更新只会在执行analyze table语句时才会触发，这样，数据库重启时可以直接读取这个值（从mysql.innodb_index_stats和mysql.innodb_table_stats两个表中读取，这两个表就是统计信息的持久表）
创建表时或修改表定义时，使用CREATE TABLE和ALTER TABLE语句的STATS_PERSISTENT子句来覆盖系统变量innodb_stats_persistent的设置并为各个表设置持久性统计信息，您也可以在创建表之前在全局级别启用innodb_stats_persistent
全局变量，动态变量，布尔型，默认值为ON
PS：以下给出索引统计信息和表统计信息表中记录的数据，和show table status、show index from tb_name查询的数据做对比，你就知道统计信息中到底记录的是什么东西了

mysql> select * from mysql.innodb_index_stats limit 1\G
*************************** 1. row ***************************
   database_name: employees
      table_name: departments
      index_name: PRIMARY
     last_update: 2016-04-04 20:29:30
       stat_name: n_diff_pfx01
      stat_value: 9
     sample_size: 1
stat_description: dept_no
1 row in set (0.00 sec)

mysql> select * from mysql.innodb_table_stats limit 1\G
*************************** 1. row ***************************
           database_name: employees
              table_name: departments
             last_update: 2016-04-04 20:29:30
                  n_rows: 9
    clustered_index_size: 1
sum_of_other_index_sizes: 1
1 row in set (0.00 sec)

mysql> use employees;
Database changed

mysql> show table status like 'departments'\G
*************************** 1. row ***************************
           Name: departments
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 9
 Avg_row_length: 1820
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2016-04-04 20:29:10
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> show index from departments;
+-------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table       | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| departments |          0 | PRIMARY   |            1 | dept_no     | A         |           9 |     NULL | NULL   |      | BTREE      |         |               |
| departments |          0 | dept_name |            1 | dept_name   | A         |           9 |     NULL | NULL   |      | BTREE      |         |               |
+-------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

5.6 innodb_stats_persistent_sample_pages=20

估算索引列的基数和其他统计信息时的采样索引页数，例如由ANALYZE TABLE触发的计算索引统计信息时的采样索引页数。增加该系统参数的值可以提高索引统计的准确性，这可以改善查询执行计划的精确性，但在对InnoDB表执行ANALYZE TABLE时会增加I / O消耗。
innodb_stats_persistent_sample_pages仅适用于为表启用innodb_stats_persistent时（持久化统计信息）; 当innodb_stats_persistent被禁用时，innodb_stats_transient_sample_pages代替（该系统参数为非持久化统计信息的采样页数）
注意：innodb_stats_persistent_sample_pages设置较高的值可能会导致ANALYZE TABLE执行时间变长
全局变量，动态变量，整型值，默认值为20

5.7. innodb_stats_transient_sample_pages=8

估算索引列的基数和其他统计信息的采样索引页数，例如由ANALYZE TABLE触发的计算统计信息的采样索引页数。默认值为8.增加该值可提高索引统计信息的准确性，从而可以改善查询执行计划的精确性，但在第一次打开InnoDB表或重新计算统计信息时会增加I / O消耗。
innodb_stats_transient_sample_pages仅适用于对表禁用innodb_stats_persistent系统参数时;当启用innodb_stats_persistent持久化统计信息系统参数时，非持久化统计信息使用innodb_stats_persistent_sample_pages系统变量定义的采样页数代替
注意：innodb_stats_transient_sample_pages设置较高的值可能导致ANALYZE TABLE执行时间冗长。ANALYZE TABLE操作时估算索引统计信息需要访问的更多的数据库页数
全局变量，动态变量，整型值，默认值为8，5.6.2版本引入，代替5.6.2之前版本的innodb_stats_sample_pages变量，innodb_stats_sample_pages变量在5.6.3版本废弃

参考文档

https://dev.mysql.com/doc/refman/5.7/en/innodb-performance-optimizer-statistics.html

MySQL统计信息

1. InnoDB统计信息介绍

2. 配置持久优化器统计参数

2.1. 配置持久优化器统计信息的自动统计信息计算

2.2. 配置各个表的优化器统计参数

2.3. 配置InnoDB优化器统计信息的采样页数

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

2.5. InnoDB持久性统计表

2.6. InnoDB持久性统计表示例

2.7. 使用innodb_index_stats表检索索引大小

3. 配置非持续优化统计参数

3.1 优化程序统计信息更新

3.2. 配置采样页数

4. 估算InnoDB表的ANALYZE TABLE复杂度

5. 相关参数

5.1 innodb_stats_method=nulls_equal

5.2 innodb_stats_auto_recalc=ON

5.3 innodb_stats_include_delete_marked=OFF

5.4 innodb_stats_on_metadata=OFF

5.5 innodb_stats_persistent=ON

5.6 innodb_stats_persistent_sample_pages=20

5.7. innodb_stats_transient_sample_pages=8

参考文档

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

MySQL统计信息

1. InnoDB统计信息介绍

2. 配置持久优化器统计参数

2.1. 配置持久优化器统计信息的自动统计信息计算

2.2. 配置各个表的优化器统计参数

2.3. 配置InnoDB优化器统计信息的采样页数

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

2.5. InnoDB持久性统计表

2.6. InnoDB持久性统计表示例

2.7. 使用innodb_index_stats表检索索引大小

3. 配置非持续优化统计参数

3.1 优化程序统计信息更新

3.2. 配置采样页数

4. 估算InnoDB表的ANALYZE TABLE复杂度

5. 相关参数

5.1 innodb_stats_method=nulls_equal

5.2 innodb_stats_auto_recalc=ON

5.3 innodb_stats_include_delete_marked=OFF

5.4 innodb_stats_on_metadata=OFF

5.5 innodb_stats_persistent=ON

5.6 innodb_stats_persistent_sample_pages=20

5.7. innodb_stats_transient_sample_pages=8

参考文档

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像