MySQL innodb統計資訊

對innodb 統計資訊的控制可以通過如下幾個常用的variables 來實作

1、innodb_stats_persistent：

　　這個參數控制着innodb的統計資訊是否持久化到磁盤，先說明一下持久化到磁盤是什麼意思；通常來說統計資訊隻儲存在記憶體中，也就是說如果mysql服務一重新開機那麼之前

　　所有的統計資訊都沒有了，這個情況下mysql就要重新收集&計算了；如果統計資訊持久化到磁盤了，那麼就可以直接從磁盤中讀取；

　　為了真正的可以達到紅統計資訊持久化到磁盤光是innodb_stats_persistent=on是做不到的；innodb_stats_persistent=on 隻是說明了打開了mysql把表的統計

　　資訊持久化到磁盤的能力，但是這個能力用不用不是由表自己說了算；在create table 語句中指定STATS_PERSISTENT=1這個時候表就支援持久化工能了。

　　create table t(x int,y int) STATS_PERSISTENT=1;      

2、由1中的描述可知 innodb的表可以分成兩大類、一類是支援持久化的表，一類是不支援持久化的表；它們各自的統計資訊的收集方式又可以通過不同的變量來控制

3、innodb_stats_persistent_sample_pages 持久化統計資訊的采樣頁

4、innodb_stats_transient_sample_pages 非持久化統計資訊的采樣頁

5、innodb_stats_auto_recalc 變更超過10%的時候要不要自動收集統計資訊

6、innodb_stats_on_metadata 執行show table status | information_schema.tables 時是否自動收集統計資訊

7. 我們将一條查詢SQL送出給MySQL之後，MySQL在進行真正的查詢操作之前通常會經曆兩個階段：SQL解析和查詢優化。在SQL解析過程中，MySQL會将SQL解析為一個樹狀結構，而在查詢優化階段，MySQL會決定以什麼方式進行查詢，那麼MySQL以什麼方式進行查詢的抉擇依據是什麼呢？答案就是這篇文章要介紹的MySQL統計資訊，因為我廠的MySQL實際使用的是Percona分支，是以本文相關的實驗知識是基于Percona分支的。

帶着問題

• MySQL統計的資訊包括什麼内容？是用來做什麼的？
• MySQL統計資訊基于表和索引，表和索引是要變化的，那麼MySQL是如何保證資料的時效性的？
• MySQL的統計機制有什麼問題？統計政策如何選擇？

MySQL統計資訊

持久化存儲->

對于InnoDB存儲引起來說，統計資訊分别存儲在mysql庫的下面兩張表中:

• innodb_table_stats
• innodb_index_stats

innodb_table_stats存儲表次元的統計資訊，innodb_index_stats存儲索引次元的統計資訊。在持久化存儲的情況下，當設定為自動更新統計資訊的時候且表中有超過10%的資料被更新的時候會執行統計資訊的重新計算，而且重新統計不是立即執行的，而是等了一段時間，這個值在MySQL中被定義為MIN_RECAL_INTERVAL=10（秒）。

易失性存儲->

當innodb_stats_persistent=OFF的時候，MySQL統計資訊存儲在記憶體之後，很顯然當重新開機資料庫的時候，這些資訊會丢失。

在易失性存儲的情況下，統計資訊重新計算的時機和持久化存儲方式是不同的，我們來看看哪些條件會觸發該情況下統計資訊的重新計算：

• 執行ANALYZE TABLE指令
• 執行如下指令：SHOW TABLE STATUS, SHOW INDEX。
• 在innodb_stats_on_metadata選項開啟的情況下查詢INFORMATION_SCHEMA.TABLES表或INFORMATION_SCHEMA.STATISTICS表
• 通過--auto-rehash參數開啟用戶端連接配接，--auto-rehash參數導緻InnoDB表被打開，InnoDB表被打開導緻統計資訊被重新計算
• 表被第一次打開
• 距離上次統計之後，表的1/16的資料被更新

了解在什麼方式下統計資訊會被重新計算對于資料庫的使用優化是有幫助的，比如我們可以破壞一些條件而讓事情向着對我們有力的一面發展。

統計内容:

MySQL統計資訊包括哪些内容呢？MySQL分别從表次元和索引次元建構統計資訊。

表統計資訊:

innodb_table_stats表存儲的是表次元的統計資訊，innodb_table_stats表有6個字段，他們的各字段相關定義以及含義如下表所示：

字段名 字段類型 字段含義

database_name    verchar(64)    統計資訊所屬表的資料庫名

table_name    verchar(64)    統計資訊所屬的表名

last_update    timestamp    統計資訊最後一次更新的時間

n_rows    bigint(20) unsigned    表所包含的行數

clustered_index_size    bigint(20) unsigned    聚集索引的頁的數量

sum_of_other_index_size    bigint(20) unsigned    其他索引所占的頁的數量

我找了一張我們現存的表測試一下：

如上圖所示，CL_CommunityNavStatInfo表目前的記錄數為5281。

上面我們看到表中實際有5281行資料，但是統計出來的是5228行資料，這是因為什麼呢？這個問題留在精度問題部分進行讨論。

索引統計資訊

innidb_index_stats表存儲的是索引次元的統計資訊，innodb_index_stats表有8個字段，他們的各字段相關定義以及含義如下表所示：

字段名　　字段類型　　字段含義

database_name    varchar(64)    統計資訊所屬表的資料庫名

table_name    varchar(64)    統計資訊所屬表名

index_name    varchar(64)    統計資訊所屬索引名

last_update    timestamp    統計資訊更新的時間

stat_name    varchar(64)    統計資訊名稱

stat_value    bigint(20) unsigned    統計值

sample_size    bigint(20) unsigned    采樣大小

stat_description    varchar(64)    統計描述資訊

我們依然使用上面測試用到的CL_CommunityNavStatInfo表進行測試，先看看CL_CommunityNavStatInfo表的索引定義：

CL_CommunityNavStatInfo表建立了三個索引，我們通過innodb_index_stats表來看看這三個索引的統計資訊：

上圖為表CL_CommunityNavStatInfo所有索引的統計資訊，比如最後一行，size代表主鍵聚集是以所占頁數大小為161，葉子節點所占空大小為128頁，id的區分度為5228，這個數字其實也是統計的表的行數，sample_size為20表示采樣頁數。

精度問題

采樣大小

上面提到了sample_size這個數字，其實MySQL的統計資料是基于采樣資料估算的，而采樣的大小是使用者可控的，預設值為20，我們可以通過修改采樣大小來控制統計資訊的精确性，同時這也會影響性能。比如我們用下面指令将采樣大小調整為200：

SET global innodb_stats_persistent_sample_pages=200;

200是我們随表挑的一個大于所有資料頁數的數字，這樣保證統計資訊基于全量資料統計，通過ANALYZE TABLE CL_CommunityNavStatInfo;指令重新統計之後，再來看看統計資訊：

是不是無比的準确？再繼續看看索引的統計資訊：

現在的統計資訊已經是基于全量的資料統計了，雖然資料準确了，但是我們同時也損失了一部分的性能。

統計時機

定時輪訓

統計時機關心的是什麼時候進行統計資訊的更新。innodb_stats_auto_recalc參數用于控制是否讓MySQL自行在需要的時候更新統計資訊，當它的值為ON的時候，統計資訊的重新計算是異步的，MySQL有一個線程專門用來做這個事情，這個線程每隔10秒鐘回去看看要不要進行統計，否則我們需要使用ANALYZE TABLE指令來保證統計資訊的時效性。那麼我們是選擇将統計資訊的更新權利完全霸占還是将其授權給MySQL讓它自行更新呢？這個問題留給讀者思考。

總結

本文分别從MySQL統計資訊的存儲、内容、精度和統計時機方面對MySQL統計資訊進行了一定的學習，了解了MySQL統計資訊的相關知識，我認為我們至少可以解決一些實際問題了。比如：

我們目前應用的資料源MySQL關于統計方面的配置有沒有問題？

我們是否可以試着通過調整采樣大小來控制統計資訊的精确度？進而影響SQL優化器的決策？

我們是否可以通過統計資訊來估算表中資料所占用的存儲空間？

……

等等。