本篇介紹 MySQL 表如何計算統計信息。表統計信息是資料庫基於成本的優化器最重要的參考信息；統計信息不準確，優化器可能給出不夠優化的執行計劃或者是錯誤的執行計劃。
對統計信息的計算分為非持久化統計信息（實時計算）與持久化統計信息。
非持久化統計信息
統計信息沒有保存在磁碟上，而是頻繁的實時計算統計信息；
每次對表的訪問都會重新計算其統計信息；
假設針對一張大表的頻繁查詢，那麼每次都要重新計算統計信息，很耗費資源。
持久化統計信息
把一張表在某一時刻的統計信息值保存在磁碟上；
避免每次查詢時重新計算；
如果表更新不是很頻繁，或者沒有達到 MySQL 必須重新計算統計信息的臨界值，可直接從磁碟上獲取；
即使 MySQL 服務重啟，也可以快速的獲取統計信息值；
統計信息的持久化可以針對全局設置也可以針對單表設置。
接下來，詳細說 MySQL 統計信息如何計算，何時計算，效果評估等問題。
在 MySQL Server 層來控制是否自動計算統計信息的分布，並且來決策是持久化還是非持久化。
一、持久化統計相關參數：
innodb_stats_persistent ：是否開啟統計信息持久化，默認開啟。
innodb_stats_auto_recalc ：是否自動重新計算持久化統計信息，默認開啟。
二、具體的更新策略為：
當一張表數據變化超過 10% 後，MySQL 會針對這張表統計信息的更新時間戳做一個判斷，檢查最後一次更新的時間是否超過 10 秒；如果不到 10 秒，把這張表加到一個統計信息更新隊列中，到時間了再重新計算；如果超過了 10 秒，直接重新計算，並且更新時間戳。
目前這個超時時間寫死在 MySQL 代碼裡，暫時不能更改。不過在某些 MySQL 分支版還可以控制這個時間，比如 Percona。
innodb_stats_include_delete_marked ：更新持久化統計信息時，是否會計算已經標記為刪除的行。默認是關閉的，會獲取未提交的髒數據。開啟這個選項，MySQL 計算統計信息時只會考慮已經提交的數據。
innodb_stats_persistent_sample_pages ：用於更新持久化索引分布或者其他統計信息的隨機基數頁，默認 20 個。頁數越多，統計信息也就越準確，也就有助於查詢優化器選擇最優的查詢計劃。
什麼時候考慮更改這個值呢？
當查詢計劃不是很準確時。比如對比指定表在系統表 mysql.innodb_index_stats 的數據跟 distinct 查詢的結果，如果相差太大，可以考慮增加這個值。
當 analyze table 變的非常慢時，可能是這個值設置的太大了，此時要考慮減小這個值。
三、非持久化統計信息參數
innodb_stats_transient_sample_pages：設置非持久化統計信息的採樣頁數目，默認 8 個。
innodb_stats_on_metadata：當統計信息配置為非持久化時生效，默認關閉。參數 innodb_stats_persistent 為 0 或者建表時屬性 STATS_PERSISTENT=0 才起作用。
當開啟後，對以下元數據的訪問會自動更新統計信息：
show table status
show index
information_schema.tables
information_schema.statistics
所以開啟這個選項會額外增加訪問表的開銷，特別是大表。
還有一些其他的場景會自動更新非持久化統計信息，比如：
表第一次被訪問；
InnoDB 檢測到有十六分之一的表自從上次統計信息計算後被更新了，這時觸發自動更新；
MySQL 客戶端默認選項 --auto-rehash 打開所有 InnoDB 表，導致所有 InnoDB 表被自動更新統計信息；
四、表屬性控制
STATS_AUTO_RECALC
用來指定是否要自動計算指定 InnoDB 表的統計信息。
三個值：default, 0, 1
default：也就是默認值，依賴 server 端參數 innodb_stats_auto_recalc 的設置效果
0：表示禁用統計信息的自動重新計算，也就是永遠不重新計算，需要手動執行 analyze table
1：表示當表數據有 10% 的數據變化後，則重新計算持久化統計信息。
STATS_PERSISTENT
用來指定是否要開啟指定 InnoDB 表的統計信息持久化。
三個值：default, 0, 1
default：依賴 server 端參數 innodb_stats_persistent 的設置
0：表示不需要持久化統計信息
1：表示開啟持久化統計信息
STATS_SAMPLE_PAGES
用來指定計算統計信息時的採樣頁數量。
五、手動更新統計信息
analyze table 用來手動更新表統計信息。建議在業務低峰時執行。
六、持久化表統計元數據信息
優化器通過兩張元數據表裡的數據來決定查詢最優執行計劃。
表統計信息保存在表 mysql.innodb_table_stats 裡
比如表 ytt_sample_persist 的統計信息
重要列說明：
n_rows：表的行數
clustered_index_size：主鍵的數據頁個數
sum_of_other_index_sizes：二級索引的數據頁個數
以下例子可以看到表 ytt_sample_persist 表行數大概為 36W 行，聚簇索引頁數為 15162，二級索引頁數為 4113。
這些值都是基於採樣頁來計算的，所以是一個預估值。
mysql> select n_rows,clustered_index_size,sum_of_other_index_sizes from innodb_table_stats where database_name ='ytt' and table_name = 'ytt_sample_persist';
+---+--+-+
| n_rows | clustered_index_size | sum_of_other_index_sizes |
+---+--+-+
| 356960 | 15162 | 4113 |
+---+--+-+
1 row in set (0.00 sec)
其實表 ytt_sample_persist 真實數據為 40W 行。
mysql> select count(*) from ytt_sample_persist;++| count(*) |++| 406644 |++1 row in set (0.90 sec)
強制更新統計信息，
mysql> analyze table ytt_sample_persist;+----+----+++| Table | Op | Msg_type | Msg_text |+----+----+++|+----+----+++1 row in set (0.28 sec)
統計信息已經更新為最新，數據又離真實數據接近一點。
mysql> select n_rows,clustered_index_size,sum_of_other_index_sizes from innodb_table_stats where database_name ='ytt' and table_name ='ytt_sample_persist';
+---+--+-+
| n_rows | clustered_index_size | sum_of_other_index_sizes |
+---+--+-+
| 387202 | 16380 | 4562 |
+---+--+-+
1 row in set (0.01 sec)
索引統計信息保存在表 mysql.innodb_index_stats 裡
比如表 ytt_sample_persist 索引統計信息
Index_name：索引名字
stat_name / stat_value：統計名字和對應的值
sample_size：採樣頁個數
stat_description：統計名字詳細信息描述
比如表 ytt_sample_persist 的聯合主鍵統計信息如下：
stat_name 顯示的值 n_diff_pfx01 代表聯合主鍵中第一個列狀態名字，對應的 stat_value 為第一個列的唯一值個數；n_diff_pfx02 代表第二列狀態名字，對應的 stat_value 為前兩列的唯一值個數，以此類推。
n_leaf_pages /stat_value 代表葉子節點的頁數目；size 代表索引的總頁數。
mysql> select index_name,stat_name,stat_value,sample_size,stat_description from innodb_index_stats where database_name ='ytt' and table_name ='ytt_sample_persist' and index_name = 'PRIMARY';
+--+----+--+---++
| index_name | stat_name | stat_value | sample_size | stat_description |
+--+----+--+---++
| PRIMARY | n_diff_pfx01 | 14137 | 20 | i1 |
| PRIMARY | n_diff_pfx02 | 75398 | 20 | i1,i2 |
| PRIMARY | n_diff_pfx03 | 387202 | 20 | i1,i2,i3 |
| PRIMARY | n_leaf_pages | 15708 | NULL | Number of leaf pages in the index |
| PRIMARY | size | 16380 | NULL | Number of pages in the index |
+--+----+--+---++
5 rows in set (0.00 sec)
那關於表的統計信息相關知識點就介紹到此，了解這塊對我們優化 SQL 來說，會更加得心應手。
總結
簡單總結下，本篇主要介紹了 MySQL表和索引的統計信息計算，包括持久化統計信息與非持久化統計信息。如果後期有 SQL 走的執行計劃不對，或者不是最優的，那就可以斷定相關統計信息太舊了，需要及時更新。比如有時候多表 JOIN 的順序不對，導致查詢效率變差，需要人工介入等等。