MySQL 配置InnoDB配置非持久优化器统计信息参数

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

从MySQL 5.6.6开始，默认情况下，优化器统计数据被持久化到磁盘上，由innodb_stats_persistent配置选项启用。

优化器数据更新
在以下情况出现时非持久化的优化器统计信息会被更新:
.执行analyze table

.运行show table status,show index，或者在innodb_stats_on_metadata选项被启用时查询information_schema.tables或information_schema.statistics表。

MySQL 5.6.6中，当持久化优化器统计信息被启用时，innodb_stats_on_metadata默认设置为OFF。启用innodb_stats_on_metadata可能会降低具有大量表或索引的模式的访问速度，并降低涉及InnoDB表的查询的执行计划的稳定性。innodb_stats_on_metadata使用SET语句全局配置。set global innodb_stats_on_metadata=ON

innodb_stats_on_metadata只适用于优化器统计信息配置为非持久化(当innodb_stats_persistent被禁用时)。

.启动mysql客户端时启用–auto-rehash选项，这是默认设置。auto-rehash选项会打开所有InnoDB表，打开表的操作会导致统计数据重新计算。为了提高mysql客户端的启动和更新统计信息时间，你可以使用–disable-auto-rehash选项关闭auto-rehash。自动auto-rehash特性允许交互用户自动完成数据库、表和列名的命名。

.表第一次打开。

.InnoDB检测到有1 / 16的表在上次统计数据更新后被修改。

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

为了控制统计信息评估的质量(从而为查询优化器提供更好的信息)，可以使用参数innodb_stats_transient_sample_pages更改抽样页面的数量。默认的抽样页面数是8，这可能不足以产生准确的评估，导致查询优化器的索引选择很差。这种技术对于大型表和连接中使用的表尤其重要。对这样的表进行不必要的全表扫描可能会造成严重的性能问题。

当innodb_stats_transient_sample_pages =0时，innodb_stats_persistent的值会影响所有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值。

评估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定义了在全局级别上采样的页面数量。要设置单个表的采样页数，请使用有STATS_SAMPLE_PAGES选项的CREATE TABLE或ALTER TABLE的语句。

如果innodb_stats_persistent=OFF，则由innodb_stats_transient_sample_pages定义采样的页面数

要了解估算ANALYZE TABLE复杂度的更深入的方法，请考虑以下示例:
在大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列)和两个非唯一索引(各有两列):

mysql> 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)
-> );
Query OK, 0 rows affected (0.13 sec)
对于上述算法所需的列和索引数据，查询mysql.innodb_index_stats来查看表t的持久索引统计信息。n_diff_pfx%显示了每个索引列的统计信息。
例如，列a和列b用于计算主键索引。对于非唯一索引，除了用户定义的列外，还要统计主键列(a,b)。

mysql> select index_name, stat_name, stat_description
-> from mysql.innodb_index_stats
-> where
-> database_name=‘mysql’ 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 |
±-----------±-------------±-----------------+
12 rows in set (0.01 sec)
根据上面显示的索引统计数据和表定义，可以确定以下值:
.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。

所有4MiB可能不是从磁盘读取的，因为一些叶页可能已经缓存在缓冲池中。