MySQL 表如何計算統計信息-愛可生

本篇介紹 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 個。頁數越多，統計信息也就越準確，也就有助於查詢優化器選擇最優的查詢計劃。

什麼時候考慮更改這個值呢？

1. 當查詢計劃不是很準確時。比如對比指定表在系統表 mysql.innodb_index_stats 的數據跟 distinct 查詢的結果，如果相差太大，可以考慮增加這個值。
2. 當 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

所以開啟這個選項會額外增加訪問表的開銷，特別是大表。

還有一些其他的場景會自動更新非持久化統計信息，比如：

1. 表第一次被訪問；
2. InnoDB 檢測到有十六分之一的表自從上次統計信息計算後被更新了，這時觸發自動更新；
3. 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。

這些值都是基於採樣頁來計算的，所以是一個預估值。

<code>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)/<code>

其實表 ytt_sample_persist 真實數據為 40W 行。

<code>mysql> select count(*) from ytt_sample_persist;+----------+| count(*) |+----------+|   406644 |+----------+1 row in set (0.90 sec)/<code>

強制更新統計信息，

<code>mysql> analyze table ytt_sample_persist;+------------------------+---------+----------+----------+| Table                  | Op      | Msg_type | Msg_text |+------------------------+---------+----------+----------+|+------------------------+---------+----------+----------+1 row in set (0.28 sec)/<code>

統計信息已經更新為最新，數據又離真實數據接近一點。

<code>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)/<code>

比如表 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 代表索引的總頁數。

<code>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)/<code>

那關於表的統計信息相關知識點就介紹到此，瞭解這塊對我們優化 SQL 來說，會更加得心應手。

簡單總結下，本篇主要介紹了 MySQL 表和索引的統計信息計算，包括持久化統計信息與非持久化統計信息。如果後期有 SQL 走的執行計劃不對，或者不是最優的，那就可以斷定相關統計信息太舊了，需要及時更新。比如有時候多表 JOIN 的順序不對，導致查詢效率變差，需要人工介入等等。

閱讀更多 愛可生雲數據庫 的文章

關鍵字: MySQL 統計 愛可生