怎么解決InnoDB Persistent Statistics問題

165次閱讀

共計 3519 個字符，預計需要花費 9 分鐘才能閱讀完成。

這篇文章主要講解了“怎么解決 InnoDB Persistent Statistics 問題”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著丸趣 TV 小編的思路慢慢深入，一起來研究和學習“怎么解決 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 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 問題這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是丸趣 TV，丸趣 TV 小編將為大家推送更多相關知識點的文章，歡迎關注！

正文完