怎么解决InnoDB Persistent Statistics疑问
发布时间：2021-12-17 09:46:17 所属栏目：MySql教程 来源：互联网
导读：这篇文章主要讲解了怎么解决InnoDB Persistent Statistics问题，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习怎么解决InnoDB Persistent Statistics问题吧！ 背景： MySQL的优化器是通过innodb收集到的数
|
这篇文章主要讲解了“怎么解决InnoDB Persistent Statistics问题”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“怎么解决InnoDB Persistent Statistics问题”吧！ 背景： MySQL的优化器是通过innodb收集到的数据来选择最优的执行计划，但因为这些数据会随着某些操作而重新计算，造成执行计划会多次变化，出现不精确和不稳定的问题。 这些导致重新计算的操作有： 1.重启 2.访问表 3.表中数据改变（1/16 以上的DML） 4.show table status 及 show index for table 5.analyze table 6.and so on 为了解决这个问题，在mysql 5.6 时，加入了持续优化统计，不再自动重新统计，持续统计数据是作为系统表存储在innodb_table_stats和innodb_index_stats中的，在上次的分享中也有提到过。 如何进行持续优化统计： mysql>show variables like '%innodb_stats%'; +--------------------------------------+-------------+ | Variable_name | Value | +--------------------------------------+-------------+ | innodb_stats_auto_recalc | ON | | innodb_stats_method | nulls_equal | | innodb_stats_on_metadata | OFF | | innodb_stats_persistent | ON | | innodb_stats_persistent_sample_pages | 20 | | innodb_stats_sample_pages | 8 | | innodb_stats_transient_sample_pages | 8 | +--------------------------------------+-------------+ 1、对于所有innodb表，可以设置全局参数 全局参数： innodb_stats_persistent 是否开启统计 innodb_stats_auto_recalc 自动重新统计 innodb_stats_persistent_sample_pages 随机取样页数 innodb_stats_on_metadata 该参数主要为元数据索引统计分析，如查询information_schema中的某些表，还有show table status 也会造成innodb 随机提取数据，很容易导致查询性能大幅抖动，在5.6之后的版本该参数已经很鸡肋了，不开启完全不影响数据统计的准确性。 2、单表 (1) stats_persistent 对于innodb表是否保证持续统计 ALTER TABLE table_name stats_persistent=1 默认是由innodb_stats_persistent选项决定的 (2) stats_auto_recalc 对于innodb表是否自动计算持续统计 默认是由innodb_stats_auto_recalc 选项决定的，为1 时， 当有10%的数据发生改变时，就重新计算,按照我的测试大概超过10% (3) stats_sample_pages 指定随机索引页的数量 example: CREATE TABLE `t1` ( `id` int NOT NULL AUTO_INCREMENT, `data` varchar(255) DEFAULT NULL, `date` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_date` (`date`) ) ENGINE=InnoDB CHARSET=utf8 STATS_PERSISTENT=1 STATS_AUTO_RECALC=1 STATS_SAMPLE_PAGES=25 Innodb 统计示例： mysql>select * from t2 ; +----+------+------+------+------+ | a | b | c | d | e | +----+------+------+------+------+ | 1 | 1 | 1 | 1 | 1 | | 2 | 1 | 1 | 2 | 2 | | 3 | 1 | 1 | 3 | 3 | | 4 | 1 | 1 | 4 | 4 | | 5 | 1 | 1 | 5 | 5 | | 6 | 1 | 1 | 6 | 6 | | 7 | 1 | 1 | 7 | 7 | | 8 | 1 | 1 | 8 | 8 | | 9 | 1 | 1 | 9 | 9 | | 10 | 1 | 1 | 10 | 10 | +----+------+------+------+------+ 10 rows in set (0.01 sec) mysql>select * from mysql.innodb_table_stats G *************************** 1. row *************************** database_name: test table_name: t2 last_update: 2016-02-24 18:58:22 n_rows: 8 clustered_index_size: 1 sum_of_other_index_sizes: 2 1 row in set (0.00 sec) 使用analyze table立即更新统计数据 mysql>analyze table t2 ; +---------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------+---------+----------+----------+ | test.t2 | analyze | status | OK | +---------+---------+----------+----------+ 1 row in set (0.01 sec) mysql>select * from mysql.innodb_table_stats G *************************** 1. row *************************** database_name: test table_name: t2 last_update: 2016-02-24 19:00:23 n_rows: 10 clustered_index_size: 1 sum_of_other_index_sizes: 2 1 row in set (0.01 sec) 可以看到统计已经改变 取样页数量的影响 基于索引的相对选择度，mysql 查询优化器通过键的分布(即cardinality)统计来选择索引的执行计划，而使用analyze table 会导致innodb 从表上的每个索引取随机页来估计索引的选择度。 为了控制统计的准确性和稳定性，可以改变以下参数 innodb_stats_persistent_sample_pages 默认值是20 统计并不精确，优化器选择的是理想的计划，如explain， 精确的统计是通过比较索引的实际基数与索引统计表中的估计值，如 select distinct在索引列 当然，如果开启了自动更新，在几秒钟，行变更达到10%的阀值也会更新的 innodb_stats_persistent_sample_pages 增加该值，虽然会使统计更加准确，但同时可能需要更多的磁盘读，会造成打开表或执行show table status ，而且对于analyze table来说，也很慢，因为它的复杂性计算与该参数相关，innodb_stats_sample_pages * 索引列* 分区数量 ；但也不能过小，比如1或2，会导致统计不准确。 感谢各位的阅读，以上就是“怎么解决InnoDB Persistent Statistics问题”的内容了，经过本文的学习后，相信大家对怎么解决InnoDB Persistent Statistics问题这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！ （编辑：洛阳站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐