怎么解決InnoDB Persistent Statistics問題

這篇文章主要講解了“怎么解決InnoDB Persistent Statistics問題”，文中的講解內(nèi)容簡單清晰，易于學(xué)習(xí)與理解，下面請(qǐng)大家跟著小編的思路慢慢深入，一起來研究和學(xué)習(xí)“怎么解決InnoDB Persistent Statistics問題”吧！

背景：
        MySQL的優(yōu)化器是通過innodb收集到的數(shù)據(jù)來選擇最優(yōu)的執(zhí)行計(jì)劃，但因?yàn)檫@些數(shù)據(jù)會(huì)隨著某些操作而重新計(jì)算，造成執(zhí)行計(jì)劃會(huì)多次變化，出現(xiàn)不精確和不穩(wěn)定的問題。

這些導(dǎo)致重新計(jì)算的操作有： 
    1.重啟
    2.訪問表
    3.表中數(shù)據(jù)改變（1/16 以上的DML）
    4.show table status 及 show index for table
    5.analyze  table
    6.and so on
       為了解決這個(gè)問題，在mysql 5.6 時(shí)，加入了持續(xù)優(yōu)化統(tǒng)計(jì)，不再自動(dòng)重新統(tǒng)計(jì)，持續(xù)統(tǒng)計(jì)數(shù)據(jù)是作為系統(tǒng)表存儲(chǔ)在innodb_table_stats和innodb_index_stats中的，在上次的分享中也有提到過。

如何進(jìn)行持續(xù)優(yōu)化統(tǒng)計(jì)：
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、對(duì)于所有innodb表，可以設(shè)置全局參數(shù)
全局參數(shù)：
    innodb_stats_persistent            是否開啟統(tǒng)計(jì)
    innodb_stats_auto_recalc        自動(dòng)重新統(tǒng)計(jì)
    innodb_stats_persistent_sample_pages   隨機(jī)取樣頁數(shù)
    innodb_stats_on_metadata    該參數(shù)主要為元數(shù)據(jù)索引統(tǒng)計(jì)分析，如查詢information_schema中的某些表，還有show table  status  也會(huì)造成innodb 隨機(jī)提取數(shù)據(jù)，很容易導(dǎo)致查詢性能大幅抖動(dòng)，在5.6之后的版本該參數(shù)已經(jīng)很雞肋了，不開啟完全不影響數(shù)據(jù)統(tǒng)計(jì)的準(zhǔn)確性。   
2、單表
    (1)  stats_persistent  對(duì)于innodb表是否保證持續(xù)統(tǒng)計(jì)
          ALTER  TABLE  table_name  stats_persistent=1
          默認(rèn)是由innodb_stats_persistent選項(xiàng)決定的
    (2) stats_auto_recalc   對(duì)于innodb表是否自動(dòng)計(jì)算持續(xù)統(tǒng)計(jì)
          默認(rèn)是由innodb_stats_auto_recalc 選項(xiàng)決定的，為1 時(shí)，

         當(dāng)有10%的數(shù)據(jù)發(fā)生改變時(shí)，就重新計(jì)算,按照我的測試大概超過10%
    (3) stats_sample_pages  指定隨機(jī)索引頁的數(shù)量
   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 統(tǒng)計(jì)示例：

 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立即更新統(tǒng)計(jì)數(shù)據(jù)

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)
   可以看到統(tǒng)計(jì)已經(jīng)改變

取樣頁數(shù)量的影響
        基于索引的相對(duì)選擇度，mysql 查詢優(yōu)化器通過鍵的分布(即cardinality)統(tǒng)計(jì)來選擇索引的執(zhí)行計(jì)劃，而使用analyze table 會(huì)導(dǎo)致innodb 從表上的每個(gè)索引取隨機(jī)頁來估計(jì)索引的選擇度。

       為了控制統(tǒng)計(jì)的準(zhǔn)確性和穩(wěn)定性，可以改變以下參數(shù)   
    innodb_stats_persistent_sample_pages 默認(rèn)值是20
    統(tǒng)計(jì)并不精確，優(yōu)化器選擇的是理想的計(jì)劃，如explain，
    精確的統(tǒng)計(jì)是通過比較索引的實(shí)際基數(shù)與索引統(tǒng)計(jì)表中的估計(jì)值，如 select distinct在索引列

當(dāng)然，如果開啟了自動(dòng)更新，在幾秒鐘，行變更達(dá)到10%的閥值也會(huì)更新的
   innodb_stats_persistent_sample_pages 增加該值，雖然會(huì)使統(tǒng)計(jì)更加準(zhǔn)確，但同時(shí)可能需要更多的磁盤讀，會(huì)造成打開表或執(zhí)行show table  status ，而且對(duì)于analyze table來說，也很慢，因?yàn)樗膹?fù)雜性計(jì)算與該參數(shù)相關(guān)，innodb_stats_sample_pages * 索引列* 分區(qū)數(shù)量 ；但也不能過小，比如1或2，會(huì)導(dǎo)致統(tǒng)計(jì)不準(zhǔn)確。

感謝各位的閱讀，以上就是“怎么解決InnoDB Persistent Statistics問題”的內(nèi)容了，經(jīng)過本文的學(xué)習(xí)后，相信大家對(duì)怎么解決InnoDB Persistent Statistics問題這一問題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是億速云，小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章，歡迎關(guān)注！