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

123次阅读

共计 8282 个字符，预计需要花费 21 分钟才能阅读完成。

这篇文章主要介绍 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)

– 配置优化器永久统计信息的自动收集
当表中条目发生改变时（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 优化器永久统计信息”这篇文章的所有内容，感谢各位的阅读！希望分享的内容对大家有帮助，更多相关知识，欢迎关注丸趣 TV 行业资讯频道！

正文完