怎么解決InnoDB Persistent Statistics問題

這篇文章主要講解了“怎么解決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問題這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！