MySQL中Innodb如何計算索引的統計資訊？

MySQL查詢優化器的執行計劃是根據統計資訊中鍵值的分佈選擇合適的索引，這是基於索引的選擇性的。innodb通過抽樣的方式來計算統計資訊，首先隨機的讀取少量的索引頁面，然後以此為樣本計算索引的統計資訊。老的innodb預設樣本頁面數為8，新版本可以通過innodb_stats_transient_sample_pages（5.6.3之前是innodb_stats_sample_pages）來設定樣本頁的數量。樣本頁的數量設定的更大，理論上來說是可以得到更準確的統計資訊，特別是對於超大的表。但是具體設定多大合適還是需要根據實際情況

 

innodb索引的統計資訊儲存方式有兩種，一種是非永續性儲存，既儲存在記憶體中，如果伺服器重啟就會丟失；一種是永續性儲存，即儲存到磁碟上，可以永久儲存。通過引數innodb_stats_persistent來控制。在MySQL5.6.6之後，預設是永續性儲存。

 

兩種儲存方式：

1ã  非永續性儲存，通過設定innodb_stats_persistent=OFF或者使用STATS_PERSISTENT=0建立，通過以下操作可以觸發計算統計資訊：

a)        
執行analyze table

b)        
在使用show table status、show index等命令的時候，或者在查詢系統表INFORMATION_SCHEMA.TABLES 和 INFORMATION_SCHEMA.STATISTICS的時候。需要一個引數控制是否會觸發更新統計資訊，innodb_stats_on_metadata=on時。

 

這裡需要注意的是，資料庫中有大量的表或者索引的時候，會給資料庫的IO帶來更大的壓力；並且如果頻繁的更新統計資訊，MySQL的執行計劃的穩定性也會受到影響。

c)        
在啟動mysql客戶端的時候採用–auto-rehash引數。

d)        
一個表首次被開啟的時候。

e)        
表發生非常大的變化的時候（大小變化超過1/16或者新插入20億行資料）。

2ã  永續性儲存，設定innodb_stats_persistent=ON，或者STATS_PERSISTENT=1建立。

持久化的資訊儲存在MySQL的系統表mysql.innodb_table_stats
和mysql.innodb_index_stats 中。

 

因為是永續性儲存到磁碟上，所以在表一段時間之後或者是進行大的改動的時候需要手動執行analyze table來更新統計資訊。

 

總結：建議設定永續性儲存到磁碟上，可以得到更穩定的執行計劃，並且在系統重啟之後可以更快速的生成統計資訊。但是需要週期性的執行analyze table來手動更新統計資訊，否則統計資訊永遠不變。