Percona MySQL 5.6如何配置InnoDB优化器永久统计信息

这篇文章主要介绍Percona Mysql 5.6如何配置InnoDB优化器永久统计信息，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！

优化器永久统计信息通过把统计信息保存在磁盘上，使得MySQL在选择语句的执行计划时，会选择相对一致的执行计划，提升了SQL执行计划的稳定性。
当开启innodb_stats_persistent=ON这个参数时或在建表时带了STATS_PERSISTENT=1参数，优化器的统计信息会永久保存到磁盘上。在之前的版本，每当MySQL服务重启或执行某些特定操作时，优化器的统计信息会被清除。
在表下一次被访问时，MySQL会重新收集优化器统计信息，这样会导致统计信息的改变，从而导致MySQL在解析语句时执行计划的改变，进而影响查询性能。
优化器永久统计信息保存在mysql.innodb_table_stats和mysql.innodb_index_stats这两张表中。

mysql> select @@version;
+-----------------+
| @@version       |
+-----------------+
| 5.6.31-77.0-log |
+-----------------+
1 row in set (0.01 sec)

mysql> show variables like 'innodb_stats_persistent';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_stats_persistent | ON    |
+-------------------------+-------+
1 row in set (0.00 sec)
mysql> desc mysql.innodb_table_stats;
+--------------------------+---------------------+------+-----+-------------------+-----------------------------+
| Field                    | Type                | Null | Key | Default           | Extra                       |
+--------------------------+---------------------+------+-----+-------------------+-----------------------------+
| database_name            | varchar(64)         | NO   | PRI | NULL              |                             |
| table_name               | varchar(64)         | NO   | PRI | NULL              |                             |
| last_update              | timestamp           | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| n_rows                   | bigint(20) unsigned | NO   |     | NULL              |                             |
| clustered_index_size     | bigint(20) unsigned | NO   |     | NULL              |                             |
| sum_of_other_index_sizes | bigint(20) unsigned | NO   |     | NULL              |                             |
+--------------------------+---------------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.00 sec)
mysql> select * from mysql.innodb_table_stats;
+---------------+---------------+---------------------+---------+----------------------+--------------------------+
| database_name | table_name    | last_update         | n_rows  | clustered_index_size | sum_of_other_index_sizes |
+---------------+---------------+---------------------+---------+----------------------+--------------------------+
| fire          | t1            | 2016-06-11 23:12:34 |  392945 |                  801 |                      481 |
| fire          | t2            | 2016-06-11 23:15:12 | 2080004 |                 4070 |                     2341 |
| fire          | test          | 2016-06-09 01:23:06 |       0 |                    1 |                        0 |
| mysql         | gtid_executed | 2016-06-07 01:28:28 |       0 |                    1 |                        0 |
| sys           | sys_config    | 2016-06-07 01:28:30 |       2 |                    1 |                        0 |
+---------------+---------------+---------------------+---------+----------------------+--------------------------+
5 rows in set (0.08 sec)

mysql> desc mysql.innodb_index_stats;
+------------------+---------------------+------+-----+-------------------+-----------------------------+
| Field            | Type                | Null | Key | Default           | Extra                       |
+------------------+---------------------+------+-----+-------------------+-----------------------------+
| database_name    | varchar(64)         | NO   | PRI | NULL              |                             |
| table_name       | varchar(64)         | NO   | PRI | NULL              |                             |
| index_name       | varchar(64)         | NO   | PRI | NULL              |                             |
| last_update      | timestamp           | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| stat_name        | varchar(64)         | NO   | PRI | NULL              |                             |
| stat_value       | bigint(20) unsigned | NO   |     | NULL              |                             |
| sample_size      | bigint(20) unsigned | YES  |     | NULL              |                             |
| stat_description | varchar(1024)       | NO   |     | NULL              |                             |
+------------------+---------------------+------+-----+-------------------+-----------------------------+
8 rows in set (0.00 sec)
mysql> select * from mysql.innodb_index_stats;
+---------------+---------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name    | index_name      | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+---------------+---------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| fire          | t1            | GEN_CLUST_INDEX | 2016-06-11 23:12:34 | n_diff_pfx01 |     392945 |          20 | DB_ROW_ID                         |
| fire          | t1            | GEN_CLUST_INDEX | 2016-06-11 23:12:34 | n_leaf_pages |        763 |        NULL | Number of leaf pages in the index |
| fire          | t1            | GEN_CLUST_INDEX | 2016-06-11 23:12:34 | size         |        801 |        NULL | Number of pages in the index      |
| fire          | t1            | idx_t1_a        | 2016-06-11 23:12:34 | n_diff_pfx01 |          2 |           4 | a                                 |
| fire          | t1            | idx_t1_a        | 2016-06-11 23:12:34 | n_diff_pfx02 |     395866 |          20 | a,DB_ROW_ID                       |
| fire          | t1            | idx_t1_a        | 2016-06-11 23:12:34 | n_leaf_pages |        403 |        NULL | Number of leaf pages in the index |
| fire          | t1            | idx_t1_a        | 2016-06-11 23:12:34 | size         |        481 |        NULL | Number of pages in the index      |
| fire          | t2            | GEN_CLUST_INDEX | 2016-06-11 23:15:12 | n_diff_pfx01 |    2079570 |          20 | DB_ROW_ID                         |
| fire          | t2            | GEN_CLUST_INDEX | 2016-06-11 23:15:12 | n_leaf_pages |       4038 |        NULL | Number of leaf pages in the index |
| fire          | t2            | GEN_CLUST_INDEX | 2016-06-11 23:15:12 | size         |       4070 |        NULL | Number of pages in the index      |
| fire          | t2            | idx_t2_a        | 2016-06-11 23:15:12 | n_diff_pfx01 |          3 |           5 | a                                 |
| fire          | t2            | idx_t2_a        | 2016-06-11 23:15:12 | n_diff_pfx02 |    2084334 |          20 | a,DB_ROW_ID                       |
| fire          | t2            | idx_t2_a        | 2016-06-11 23:15:12 | n_leaf_pages |       2122 |        NULL | Number of leaf pages in the index |
| fire          | t2            | idx_t2_a        | 2016-06-11 23:15:12 | size         |       2341 |        NULL | Number of pages in the index      |
| fire          | test          | GEN_CLUST_INDEX | 2016-06-09 01:23:06 | n_diff_pfx01 |          0 |           1 | DB_ROW_ID                         |
| fire          | test          | GEN_CLUST_INDEX | 2016-06-09 01:23:06 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| fire          | test          | GEN_CLUST_INDEX | 2016-06-09 01:23:06 | size         |          1 |        NULL | Number of pages in the index      |
| mysql         | gtid_executed | PRIMARY         | 2016-06-07 01:28:28 | n_diff_pfx01 |          0 |           1 | source_uuid                       |
| mysql         | gtid_executed | PRIMARY         | 2016-06-07 01:28:28 | n_diff_pfx02 |          0 |           1 | source_uuid,interval_start        |
| mysql         | gtid_executed | PRIMARY         | 2016-06-07 01:28:28 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mysql         | gtid_executed | PRIMARY         | 2016-06-07 01:28:28 | size         |          1 |        NULL | Number of pages in the index      |
| sys           | sys_config    | PRIMARY         | 2016-06-07 01:28:30 | n_diff_pfx01 |          2 |           1 | variable                          |
| sys           | sys_config    | PRIMARY         | 2016-06-07 01:28:30 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| sys           | sys_config    | PRIMARY         | 2016-06-07 01:28:30 | size         |          1 |        NULL | Number of pages in the index      |
+---------------+---------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
24 rows in set (0.00 sec)

--配置优化器永久统计信息的自动收集
当表中条目发生改变时（10%以上的行发生改变），innodb_stats_auto_recalc参数决定是否重新收集统计信息。这个参数默认是开启的。可以在CREATE TABLE、ALTER TABLE语句上面添加STATS_AUTO_RECALC选项来开启指定表的统计信息自动收集。
统计信息的自动收集是在后台以异步的方式进行的。当对一张表执行了影响表中10%行数的DML操作，在innodb_stats_auto_recalc参数开启的情况下，统计信息可能不会立刻开始重新收集，这个收集可能会延迟几十秒。如果需要最新的统计信息，可以执行ANALYZE TABLE语句，在前台统计收集统计信息。
mysql> show variables like 'innodb_stats_auto_recalc';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_stats_auto_recalc | ON    |
+--------------------------+-------+
1 row in set (0.21 sec)

如果innodb_stats_auto_recalc参数没有开启时，在表中索引字段数据发生大的改变时，例如表中被导入大量数据，或表有阶段性的大改变索引字段的DML操作，需要及时执行ANALYZE TABLE语句，来保证优化器统计信息的准确性。当在一张已存在的表上创建索引时，不管是否开启innodb_stats_auto_recalc参数，索引的统计信息会自动收集并保存在innodb_index_stats表中。

--配置优化器统计信息Sampled Pages的数量
在执行计划中，MySQL查询优化器根据索引的selectivity，使用索引分布统计信息来选择使用的索引。当执行ANALYZE TABLE操作时，InnoDB会对每个索引进行采样来估算cardinality（某字段非重复值的数量），这个技术被称为random dives。可以通过innodb_stats_persistent_sample_pages参数来改变采样使用的页数，这个参数的默认值是20。当发生下面情况时，可以考虑修改这个参数：

1、在EXPLaiN输出中，统计信息不准确，优化器选择了非最优的执行计划。可以通过比较SELECT DISTINCT索引字段和mysql.innodb_index_stats表中的索引的cardinality，来查看索引实际的cardinality的准确性。
如果统计信息不准确，应该增加innodb_stats_persistent_sample_pages这个参数的值，直到统计信息足够准确为止。如果将这个参数的值增加太大，会导致ANALYZE TABLE操作运行缓慢。

2、ANALYZE TABLE操作太慢。这时可以考虑减小innodb_stats_persistent_sample_pages这个参数的值，直到ANALYZE TABLE的执行时间能在一个接受的范围内。然而，将这个参数的值设的太小，可能会导致统计信息的不准确，进而影响执行计划的优劣。

3、如果在统计信息的准确性和ANALYZE TABLE执行时间之间不能取得平衡，考虑减少表中索引字段的数量或减少ANALYZE TABLE所分析的分区数量。

以上是“Percona MySQL 5.6如何配置InnoDB优化器永久统计信息”这篇文章的所有内容，感谢各位的阅读！希望分享的内容对大家有帮助，更多相关知识，欢迎关注编程网数据库频道！