統計資訊記錄表|全方位認識 mysql 系統庫

在上一期 《資料庫物件資訊記錄表|全方位認識 mysql 系統庫》中，我們詳細介紹了mysql系統庫中的後設資料記錄表，本期我們將為大家帶來系列第四篇《統計資訊記錄表|全方位認識 mysql 系統庫》，下面請跟隨我們一起開始 mysql 系統庫的系統學習之旅吧。

1 | 統計資訊概述

如何配置統計資訊持久化最佳化。

• 持久化統計功能是透過將記憶體中的統計資料儲存到磁碟總，使其在資料庫重啟時可以快速重新讀入這些統計資訊而不用重新執行統計，從而使得查詢最佳化器可以利用這些持久化的統計資訊準確地選擇執行計劃（如果沒有這些持久化的統計資訊，那麼資料庫重啟之後記憶體中的統計資訊將會丟失，下一次訪問到某庫某表的時候，統計資訊需要重新計算，並且重新計算可能會因為估算值的差異導致查詢計劃發生變更，從而導致查詢效能可能發生變化） ，如果啟用統計資訊的持久化功能呢？當innodb_stats_persistent = ON時或者建表時使用了建表選項STATS_PERSISTENT = 1，則表示開啟統計資訊的持久化功能（注意，後者表示只開啟單表的統計資訊持久化且無論innodb_stats_persistent 引數是否啟用，前者代表開啟全域性所有表的統計資訊持久化。 innodb_stats_persistent系統變數預設開啟，如果要單獨關閉某個表的持久化統計資訊功能，可以透過語句ALTER TABLE tbl_name STATS_PERSISTENT = 0 來修改）。
• 持久統計資訊儲存在mysql.innodb_table_stats和mysql.innodb_index_stats表中，前者存放表結構、資料行相關的統計資訊，後者存放索引值相關的統計資訊。

如何配置統計資訊的持久化最佳化自動計算。

• innodb_stats_auto_recalc系統變數控制是否啟用統計資訊的自動計算功能，預設開啟，當自動計算功能開啟時，表中的資料量變更超過10%時會觸發統計資訊自動計算功能。如果innodb_stats_auto_recalc變數未啟用，您還可以在CREATE TABLE或ALTER TABLE語句中使用STATS_AUTO_RECALC子句為單個表配置統計資訊自動重新計算功能。

• 自動重新計算在後臺執行，所以 即使啟用了innodb_stats_auto_recalc系統變數，當表中的資料DML操作超過10%之後，統計資訊也可能不會立即重新計算， 某些情況下可能會延遲幾秒鐘，如果需要統計資訊精確，則你可以手動執行ANALYZE TABLE語句來確保最佳化程式統計資訊的準確性。

• 當某表新增新的索引時，無論系統引數innodb_stats_auto_recalc的值如何，都會觸發重新計算索引統計資訊並將其新增到innodb_index_stats表中。但要注意，這裡說的是會觸發重新計算索引統計資訊，而不是表mysql.innodb_table_stats表中的表及其資料相關的統計資訊，要想在新增索引時資料相關的統計資訊同時更新到mysql.innodb_table_stats表中，需要啟用系統變數innodb_stats_auto_recalc或者修改表的innodb_stats_auto_recalc建表選項，或者對錶執行ANALYZE TABLE語句。

如何配置單個表的統計資訊持久化最佳化。

• innodb_stats_persistent，innodb_stats_auto_recalc和innodb_stats_persistent_sample_pages是全域性的系統變數。如果你有需要忽略全域性變數的值而單獨指定某個表是否需要配置持久化統計資訊，那麼可以使用表的建表選項（STATS_PERSISTENT，STATS_AUTO_RECALC和STATS_SAMPLE_PAGES子句）來覆蓋系統變數設定的值，建表選項可以在CREATE TABLE或ALTER TABLE語句中指定。
  *  STATS_PERSISTENT： 指定是否啟用InnoDB表的持久統計資訊。如果不設定，預設為DEFAULT，表示表的持久統計資訊功能設定由innodb_stats_persistent系統變數確定。如果設定為1，則表示啟用該表的持久統計資訊，如果設定為則表示關閉此表的持久統計資訊功能。如果透過CREATE TABLE或ALTER TABLE語句啟用永續性統計資訊功能，那麼將在代表性資料載入到表中後，呼叫ANALYZE TABLE語句來計算統計資訊。
  * STATS_AUTO_RECALC：指定是否自動重新計算InnoDB表的持久統計資訊。預設值為DEFAULT，表示表的持久統計資訊重新計算功能由系統變數innodb_stats_auto_recalc的值確定。當設定為1時表示啟用自動重新計算功能。啟用之後當表中資料的10％發生變更時會重新計算統計資訊。當設定為0，表示關閉表的自動重新計算統計資訊功能，要注意的是，如果關閉之後，表的資料發生了較大更改，那麼請手動執行ANALYZE TABLE語句來重新計算統計資訊。否則有可能造成因為統計資訊不精確而導致執行計劃不精確。
  * STATS_SAMPLE_PAGES：設定估算索引列的基數和其他統計資料時要抽樣的索引頁數（例如：ANALYZE TABLE計算需要的取樣頁數）。

• 以下為這三個建表選項的使用示例：

CREATE TABLE `t1` (
`id` int(8) NOT NULL auto_increment,
`data` varchar(255),
`date` datetime,
PRIMARY KEY (`id`),
INDEX `DATE_IX` (`date`)
) ENGINE=InnoDB,
  STATS_PERSISTENT=1,
  STATS_AUTO_RECALC=1,
  STATS_SAMPLE_PAGES=25;

如何配置InnoDB最佳化器統計資訊的取樣頁數。

• MySQL查詢最佳化器使用關於索引的鍵值統計資訊來計算索引選擇度，根據選擇度來選擇執行計劃的索引。那麼這些統計資訊是如何得來的呢？例如：當執行ANALYZE TABLE之類的操作時，InnoDB會從表中的每個索引中抽取隨機頁面來估計索引的基數。（這種技術被稱為隨機取樣） ，取樣頁的數量由系統引數innodb_stats_persistent_sample_pages設定，預設為20，該變數為動態變數。通常情況下不需要修改，增大該變數置可能導致每次取樣時間變長(因為需要讀取更多的頁)，但如果確定預設的取樣數量會導致索引統計資訊不精確，那麼可以嘗試逐步增加該系統變數值，直到具有足夠精確的統計資訊為止。統計資訊是否精確可以透過SELECT DISTINCT(index_name)返回的值與mysql.innodb_index_stats持久統計資訊表中提供的估計值來進行對比檢查。

如何配置在持久統計資訊的計算中包括刪除標記的記錄。

• 預設情況下，InnoDB在計算統計資訊時會讀取未提交的資料。對於從表中執行刪除行的操作的未提交事務，InnoDB在估算行和索引統計資訊時會忽略這些被打上刪除標記的記錄，所以這可能會導致對該表執行並行查詢的其他事務的執行計劃並不精確。為了避免這種情況，可以啟用系統引數innodb_stats_include_delete_marked來確保InnoDB在計算持久化統計資訊時包含被打上刪除標記的記錄。當啟用innodb_stats_include_delete_marked時，執行ANALYZE TABLE語句時會統計被打上刪除標記的記錄。要注意的是：innodb_stats_include_delete_marked是全域性變數，且不能單獨設定某個表，innodb_stats_include_delete_marked是在MySQL 5.7.16中引入的。

統計資訊持久化依賴於mysql資料庫下的表innodb_table_stats和innodb_index_stats，這些表在安裝，升級和原始碼構建過程中會自動設定。

• innodb_table_stats和innodb_index_stats表都包含last_update列，表示InnoDB上次更新索引統計資訊的時間。

• innodb_table_stats和innodb_index_stats表是普通表，可以手動執行更新。透過手動更新統計資訊的功能，可以強制執行特定的查詢最佳化計劃或測試備選計劃，而無需修改資料庫。要注意：如果手動更新統計資訊，需要執行語句FLUSH TABLE tbl_name命令以使MySQL重新載入更新過後的統計資訊。

• 永續性統計資訊被視為本地資訊，因為它們與例項自身相關。因此innodb_table_stats和innodb_index_stats表的自動統計資訊資料變更不會在主備架構之間複製。但如果是手動執行ANALYZE TABLE語句來觸發統計資訊重新計算，那麼該ANALYZE TABLE語句本身會在主備架構之間複製，以在備庫啟動統計資訊的同步重新計算操作（除非在主庫操作時設定了set sql_log_bin=0之類的語句關閉了日誌記錄）。

2 | 統計資訊表詳解

2.1. innodb_table_stats

該表提供查詢表資料相關的統計資訊。

下面是該表中儲存的資訊內容。

root@localhost : test 08:00:46> use mysql
Database changed
root@localhost : mysql 08:01:30> select * from innodb_table_stats where table_name='test'\G
*************************** 1. row ***************************
           database_name: test
              table_name: test
             last_update: 2018-05-24 20:00:50
                  n_rows: 6
    clustered_index_size: 1
sum_of_other_index_sizes: 2
1 row in set (0.00 sec)

表欄位含義。

• database_name：資料庫名稱。

• table_name：表名、分割槽名或子分割槽名稱。

• last_update：表示InnoDB上次更新此統計資訊行的時間戳。

• n_rows：表中的估算資料記錄行數。

• clustered_index_size：主鍵索引的大小，以頁為單位的估算數值。

• sum_of_other_index_sizes：其他（非主鍵）索引的總大小，以頁為單位的估算數值。

2.2. innodb_index_stats

該表提供查詢索引相關的統計資訊。

下面是該表中儲存的資訊內容。

root@localhost : mysql 08:01:34> select * from innodb_index_stats where table_name='test';
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name | last_update | stat_name | stat_value | 
sample_size | stat_description |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| test | test | PRIMARY | 2018-05-24 20:00:50 | n_diff_pfx01 | 5 | 1 | a |
| test | test | PRIMARY | 2018-05-24 20:00:50 | n_diff_pfx02 | 6 | 1 | a,b |
| test | test | PRIMARY | 2018-05-24 20:00:50 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| test | test | PRIMARY | 2018-05-24 20:00:50 | size | 1 | NULL | Number of pages in the index |
| test | test | i1 | 2018-05-24 20:00:50 | n_diff_pfx01 | 5 | 1 | c |
| test | test | i1 | 2018-05-24 20:00:50 | n_diff_pfx02 | 5 | 1 | c,d |
| test | test | i1 | 2018-05-24 20:00:50 | n_diff_pfx03 | 6 | 1 | c,d,a |
| test | test | i1 | 2018-05-24 20:00:50 | n_diff_pfx04 | 6 | 1 | c,d,a,b |
| test | test | i1 | 2018-05-24 20:00:50 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| test | test | i1 | 2018-05-24 20:00:50 | size | 1 | NULL | Number of pages in the index |
| test | test | i2uniq | 2018-05-24 20:00:50 | n_diff_pfx01 | 6 | 1 | e |
| test | test | i2uniq | 2018-05-24 20:00:50 | n_diff_pfx02 | 6 | 1 | e,f |
| test | test | i2uniq | 2018-05-24 20:00:50 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| test | test | i2uniq | 2018-05-24 20:00:50 | size | 1 | NULL | Number of pages in the index |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
14 rows in set (0.00 sec)

表欄位含義。

• database_name：資料庫名稱。

• table_name：表名、分割槽表名、子分割槽表名稱。

• index_name：索引名稱。

• last_update：表示InnoDB上次更新此統計資訊行的時間戳。

• stat_name：統計資訊名稱，其對應的統計資訊值儲存在stat_value列。

• stat_value：儲存統計資訊名稱stat_name列對應的統計資訊值。

• sample_size：stat_value列中提供的統計資訊估計值的取樣頁數。

• stat_description：統計資訊名稱stat_name列中指定的統計資訊的說明資訊。

從表中查詢所得的資料中，我們可以看到：

• stat_name列一種有如下幾種統計值。

  * size： 當stat_name為size值時，stat_value列值表示索引中的總頁數量。

  * n_leaf_pages： 當stat_name為n_leaf_pages值時，stat_value列值顯示索引葉子頁的數量。

  * n_diff_pfxNN： NN代表數字（例如： 01，02等），當stat_name為n_diff_pfxNN時，stat_value列值顯示索引的first column(即索引的最前索引列，從索引定義順序的第一個列開始)列的唯一值數量，例如： 當NN為01時，stat_value列值就表示索引的第一個列的唯一值數量，當NN為02時，stat_value列值就表示索引的第一和第二個列的組合唯一值數量，以此類推。 此外，在stat_name = n_diff_pfxNN的情況下，stat_description列顯示一個以逗號分隔的計算索引統計資訊列的列表。

• 從index_name為PRIMARY資料行的stat_description列的描述資訊"a,b"中，我們可以看出 ，主鍵索引的統計資訊列實際上就等於定義的索引列數量。

• 從index_name為i2uniq資料行的stat_description列的描述資訊"e,f"中，我們可以看出 ，唯一索引的統計資訊列實際上就等於定義的索引列數量。

• 從index_name為i1資料行的stat_description列的描述資訊 "c,d,a,b"中，我們可以看出，普通索引（非唯一的輔助索引）的統計資訊列實際上除了定義的索引列之外，還包含了主鍵列。即對於非唯一索引在該表中記錄的統計資訊，InnoDB會附加主鍵列。

• 注意：MySQL 5.7中系統變數innodb_stats_persistent_sample_pages定義的持久化統計資訊取樣頁為20，這裡示例中的sample_size列值為1是因為表中資料量太小，在一個頁中已經足夠存放，所以實際取樣也只使用了1個頁，如果資料量夠多，則這裡顯示的值就會為innodb_stats_persistent_sample_pages系統變數指定的值。

PS：我們可以使用該表中的索引資訊頁數結合系統變數innodb_page_size的值來計算索引的資料大小，如下：

root@localhost : mysql 08:31:14> SELECT SUM(stat_value) pages, index_name, 
SUM(stat_value)*@@innodb_page_size size FROM mysql.innodb_index_stats WHERE 
table_name='dept_emp' AND stat_name = 'size' GROUP BY index_name;                         
+-------+------------+----------+
| pages | index_name | size |
+-------+------------+----------+
| 737 | PRIMARY | 12075008 |
| 353 | dept_no | 5783552 |
| 353 | emp_no | 5783552 |
+-------+------------+----------+
3 rows in set (0.01 sec)

本期內容就介紹到這裡，本期內容參考連結如下：

https://dev.mysql.com/doc/refman/5.7/en/innodb-persistent-stats.html

https://dev.mysql.com/doc/refman/5.7/en/innodb-persistent-stats.html

"翻過這座山，你就可以看到一片海！ "。 堅持閱讀我們的"全方位認識 mysql 系統庫"系列文章分享，你就可以系統地學完它。 謝謝你的閱讀，我們下期不見不散！

| 作者簡介

羅小波·沃趣科技高階資料庫技術專家

IT從業多年，主要負責MySQL 產品的資料庫支撐與售後二線支撐。曾參與版本釋出系統、輕量級監控系統、運維管理平臺、資料庫管理平臺的設計與編寫，熟悉MySQL體系結構，Innodb儲存引擎，喜好專研開源技術，多次在公開場合做過線下線上資料庫專題分享，發表過多篇資料庫相關的研究文章。