iT邦幫忙

2021 iThome 鐵人賽
DAY 21
0
自我挑戰組

那些Mysql我不知道的事系列 第 21 篇

InnoDB統計資料是如何收集的

13th鐵人賽
481 瀏覽

  • 分享至 

  • xImage
    •  

innodb以表為單位來搜集統計資料，分為兩種資料

  1. 基於磁碟的永久性統計資料。顧名思義存在磁碟，伺服器重啟依然存在。
  2. 基於記憶體的非永久性統計資料。存在記憶體，伺服器關閉就會把資料清除。

基於磁碟的永久性統計資料

當我們選擇把某表及該表索引的統計資料存放在磁碟中，實際上是把這些統計資料存在下面兩個表中。

mysql> show tables from mysql like 'innodb%stats';
+--------------------------------+
| Tables_in_mysql (innodb%stats) |
+--------------------------------+
| innodb_index_stats             |
| innodb_table_stats             |
+--------------------------------+
2 rows in set (0.11 sec)

innodb_table_stats：存放表的統記資料，每筆紀錄對應著一個表的統計資料。
innodb_index_stats：存放索引的統記資料，每筆紀錄對應著一個索引的統計項資料。

mysql> select * from mysql.innodb_table_stats;
+---------------+----------------------+---------------------+----------+----------------------+--------------------------+
| database_name | table_name           | last_update         | n_rows   | clustered_index_size | sum_of_other_index_sizes |
+---------------+----------------------+---------------------+----------+----------------------+--------------------------+
| mysql         | component            | 2021-09-02 12:49:09 |        1 |                    1 |                        0 |
| ryan_demo_db  | per2                 | 2021-09-10 18:15:15 | 18329781 |                50688 |                        0 |
| ryan_demo_db  | ryan_demo2_table     | 2021-09-07 16:29:23 |       16 |                    1 |                        0 |
| ryan_demo_db  | ryan_demo_table      | 2021-09-07 15:41:08 |       16 |                    1 |                        0 |
| ryan_demo_db  | score                | 2021-09-20 14:28:38 |        4 |                    1 |                        0 |
| ryan_demo_db  | single_table         | 2021-09-10 13:56:05 |    10146 |                   97 |                      144 |
| ryan_demo_db  | single_table2        | 2021-09-24 12:55:16 |        0 |                    1 |                        4 |
| ryan_demo_db  | student              | 2021-09-20 14:18:24 |        3 |                    1 |                        0 |
| ryan_demo_db  | t1                   | 2021-09-20 13:52:33 |        3 |                    1 |                        0 |
| ryan_demo_db  | t2                   | 2021-09-20 13:52:54 |        2 |                    1 |                        0 |
| sys           | sys_config           | 2018-07-17 11:08:23 |        6 |                    1 |                        0 |
+---------------+----------------------+---------------------+----------+----------------------+--------------------------+

可以直接看到資料庫下每個表的紀錄筆數(n_rows)，以及表的聚簇索引所佔用的頁面數(clustered_index_size)和表的其他索引所佔用的頁面數(sum_of_other_index_sizes)。

mysql> select * from mysql.innodb_index_stats;
+---------------+----------------------+-----------------------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name           | index_name                  | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+---------------+----------------------+-----------------------------+---------------------+--------------+------------+-------------+-----------------------------------+
| ryan_demo_db  | per2                 | GEN_CLUST_INDEX             | 2021-09-10 18:15:15 | n_diff_pfx01 |   18329781 |          20 | DB_ROW_ID                         |
| ryan_demo_db  | per2                 | GEN_CLUST_INDEX             | 2021-09-10 18:15:15 | n_leaf_pages |      50544 |        NULL | Number of leaf pages in the index |
| ryan_demo_db  | per2                 | GEN_CLUST_INDEX             | 2021-09-10 18:15:15 | size         |      50688 |        NULL | Number of pages in the index      |
| ryan_demo_db  | ryan_demo2_table     | GEN_CLUST_INDEX             | 2021-09-07 16:29:23 | n_diff_pfx01 |         16 |           1 | DB_ROW_ID                         |
| ryan_demo_db  | ryan_demo2_table     | GEN_CLUST_INDEX             | 2021-09-07 16:29:23 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| ryan_demo_db  | ryan_demo2_table     | GEN_CLUST_INDEX             | 2021-09-07 16:29:23 | size         |          1 |        NULL | Number of pages in the index      |
| ryan_demo_db  | ryan_demo_table      | GEN_CLUST_INDEX             | 2021-09-07 15:41:08 | n_diff_pfx01 |         16 |           1 | DB_ROW_ID                         |
| ryan_demo_db  | ryan_demo_table      | GEN_CLUST_INDEX             | 2021-09-07 15:41:08 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| ryan_demo_db  | ryan_demo_table      | GEN_CLUST_INDEX             | 2021-09-07 15:41:08 | size         |          1 |        NULL | Number of pages in the index      |
| ryan_demo_db  | score                | PRIMARY                     | 2021-09-20 14:28:38 | n_diff_pfx01 |          2 |           1 | number                            |
| ryan_demo_db  | score                | PRIMARY                     | 2021-09-20 14:28:38 | n_diff_pfx02 |          4 |           1 | number,subject                    |
| ryan_demo_db  | score                | PRIMARY                     | 2021-09-20 14:28:38 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| ryan_demo_db  | score                | PRIMARY                     | 2021-09-20 14:28:38 | size         |          1 |        NULL | Number of pages in the index      |
| ryan_demo_db  | single_table         | PRIMARY                     | 2021-09-10 13:56:05 | n_diff_pfx01 |      10146 |          20 | id                                |
| ryan_demo_db  | single_table         | PRIMARY                     | 2021-09-10 13:56:05 | n_leaf_pages |         70 |        NULL | Number of leaf pages in the index |
| ryan_demo_db  | single_table         | PRIMARY                     | 2021-09-10 13:56:05 | size         |         97 |        NULL | Number of pages in the index      |
| ryan_demo_db  | single_table         | idx_key1                    | 2021-09-10 13:56:05 | n_diff_pfx01 |      10000 |          17 | key1                              |
| ryan_demo_db  | single_table         | idx_key1                    | 2021-09-10 13:56:05 | n_diff_pfx02 |      10000 |          17 | key1,id                           |
| ryan_demo_db  | single_table         | idx_key1                    | 2021-09-10 13:56:05 | n_leaf_pages |         17 |        NULL | Number of leaf pages in the index |
| ryan_demo_db  | single_table         | idx_key1                    | 2021-09-10 13:56:05 | size         |         18 |        NULL | Number of pages in the index      |
| ryan_demo_db  | single_table         | idx_key3                    | 2021-09-10 13:56:05 | n_diff_pfx01 |      10000 |          17 | key3                              |
| ryan_demo_db  | single_table         | idx_key3                    | 2021-09-10 13:56:05 | n_diff_pfx02 |      10000 |          17 | key3,id                           |
| ryan_demo_db  | single_table         | idx_key3                    | 2021-09-10 13:56:05 | n_leaf_pages |         17 |        NULL | Number of leaf pages in the index |
| ryan_demo_db  | single_table         | idx_key3                    | 2021-09-10 13:56:05 | size         |         18 |        NULL | Number of pages in the index      |
| ryan_demo_db  | single_table         | idx_key_part                | 2021-09-10 13:56:05 | n_diff_pfx01 |      10000 |          59 | key_part1                         |
| ryan_demo_db  | single_table         | idx_key_part                | 2021-09-10 13:56:05 | n_diff_pfx02 |      10000 |          59 | key_part1,key_part2               |
| ryan_demo_db  | single_table         | idx_key_part                | 2021-09-10 13:56:05 | n_diff_pfx03 |      10000 |          59 | key_part1,key_part2,key_part3     |
| ryan_demo_db  | single_table         | idx_key_part                | 2021-09-10 13:56:05 | n_diff_pfx04 |      10000 |          59 | key_part1,key_part2,key_part3,id  |
| ryan_demo_db  | single_table         | idx_key_part                | 2021-09-10 13:56:05 | n_leaf_pages |         59 |        NULL | Number of leaf pages in the index |
| ryan_demo_db  | single_table         | idx_key_part                | 2021-09-10 13:56:05 | size         |         97 |        NULL | Number of pages in the index      |
| ryan_demo_db  | single_table         | uk_key2                     | 2021-09-10 13:56:05 | n_diff_pfx01 |      10000 |          10 | key2                              |
| ryan_demo_db  | single_table         | uk_key2                     | 2021-09-10 13:56:05 | n_leaf_pages |         10 |        NULL | Number of leaf pages in the index |
| ryan_demo_db  | single_table         | uk_key2                     | 2021-09-10 13:56:05 | size         |         11 |        NULL | Number of pages in the index      |
| ryan_demo_db  | single_table2        | PRIMARY                     | 2021-09-24 12:55:16 | n_diff_pfx01 |          0 |           1 | id                                |
| ryan_demo_db  | single_table2        | PRIMARY                     | 2021-09-24 12:55:16 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| ryan_demo_db  | single_table2        | PRIMARY                     | 2021-09-24 12:55:16 | size         |          1 |        NULL | Number of pages in the index      |
| ryan_demo_db  | single_table2        | idx_key1                    | 2021-09-24 12:55:16 | n_diff_pfx01 |          0 |           1 | key1                              |
| ryan_demo_db  | single_table2        | idx_key1                    | 2021-09-24 12:55:16 | n_diff_pfx02 |          0 |           1 | key1,id                           |
| ryan_demo_db  | single_table2        | idx_key1                    | 2021-09-24 12:55:16 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| ryan_demo_db  | single_table2        | idx_key1                    | 2021-09-24 12:55:16 | size         |          1 |        NULL | Number of pages in the index      |
| ryan_demo_db  | single_table2        | idx_key3                    | 2021-09-24 12:55:16 | n_diff_pfx01 |          0 |           1 | key3                              |
| ryan_demo_db  | single_table2        | idx_key3                    | 2021-09-24 12:55:16 | n_diff_pfx02 |          0 |           1 | key3,id                           |
| ryan_demo_db  | single_table2        | idx_key3                    | 2021-09-24 12:55:16 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| ryan_demo_db  | single_table2        | idx_key3                    | 2021-09-24 12:55:16 | size         |          1 |        NULL | Number of pages in the index      |
| ryan_demo_db  | single_table2        | idx_key_part                | 2021-09-24 12:55:16 | n_diff_pfx01 |          0 |           1 | key_part1                         |
| ryan_demo_db  | single_table2        | idx_key_part                | 2021-09-24 12:55:16 | n_diff_pfx02 |          0 |           1 | key_part1,key_part2               |
| ryan_demo_db  | single_table2        | idx_key_part                | 2021-09-24 12:55:16 | n_diff_pfx03 |          0 |           1 | key_part1,key_part2,key_part3     |
| ryan_demo_db  | single_table2        | idx_key_part                | 2021-09-24 12:55:16 | n_diff_pfx04 |          0 |           1 | key_part1,key_part2,key_part3,id  |
| ryan_demo_db  | single_table2        | idx_key_part                | 2021-09-24 12:55:16 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| ryan_demo_db  | single_table2        | idx_key_part                | 2021-09-24 12:55:16 | size         |          1 |        NULL | Number of pages in the index      |
| ryan_demo_db  | single_table2        | uk_key2                     | 2021-09-24 12:55:16 | n_diff_pfx01 |          0 |           1 | key2                              |
| ryan_demo_db  | single_table2        | uk_key2                     | 2021-09-24 12:55:16 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| ryan_demo_db  | single_table2        | uk_key2                     | 2021-09-24 12:55:16 | size         |          1 |        NULL | Number of pages in the index      |
| ryan_demo_db  | student              | PRIMARY                     | 2021-09-20 14:18:24 | n_diff_pfx01 |          3 |           1 | number                            |
| ryan_demo_db  | student              | PRIMARY                     | 2021-09-20 14:18:24 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| ryan_demo_db  | student              | PRIMARY                     | 2021-09-20 14:18:24 | size         |          1 |        NULL | Number of pages in the index      |
| ryan_demo_db  | t1                   | GEN_CLUST_INDEX             | 2021-09-20 13:52:33 | n_diff_pfx01 |          3 |           1 | DB_ROW_ID                         |
| ryan_demo_db  | t1                   | GEN_CLUST_INDEX             | 2021-09-20 13:52:33 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| ryan_demo_db  | t1                   | GEN_CLUST_INDEX             | 2021-09-20 13:52:33 | size         |          1 |        NULL | Number of pages in the index      |
| ryan_demo_db  | t2                   | GEN_CLUST_INDEX             | 2021-09-20 13:52:54 | n_diff_pfx01 |          2 |           1 | DB_ROW_ID                         |
| ryan_demo_db  | t2                   | GEN_CLUST_INDEX             | 2021-09-20 13:52:54 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| ryan_demo_db  | t2                   | GEN_CLUST_INDEX             | 2021-09-20 13:52:54 | size         |          1 |        NULL | Number of pages in the index      |
| sys           | sys_config           | PRIMARY                     | 2018-07-17 11:08:23 | n_diff_pfx01 |          6 |           1 | variable                          |
| sys           | sys_config           | PRIMARY                     | 2018-07-17 11:08:23 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| sys           | sys_config           | PRIMARY                     | 2018-07-17 11:08:23 | size         |          1 |        NULL | Number of pages in the index      |
+---------------+----------------------+-----------------------------+---------------------+--------------+------------+-------------+-----------------------------------+

可以直接看到索引下的統計項名稱(stat_name)、數量(stat_value)及對應的取樣頁面數(sample_size)。
統計項名稱有以下三種：

  • n_leaf_pages：該索引的葉子節點實際佔用多少頁面
  • size：該索引佔用多少頁面
  • n_diff_pfxNN：表示該索引列不重複的值有多少。其中的NN的意思，以idx_key_part來說
    n_diff_pfx01表示統計key_part1這一列不重複的值有多少
    n_diff_pfx02表示統計key_part1、key_part2這兩個列組合不重複的值有多少
    n_diff_pfx03表示統計key_part1、key_part2、key_part3這三個列組合不重複的值有多少
    n_diff_pfx04表示統計key_part1、key_part2、key_part3、id這四個列組合不重複的值有多少

而這兩個表可以透過設定參數自動更新也可以手動執行更新，這邊不再多做了解。

基於記憶體的非永久性統計資料

當系統變數innodb_stats_persistent設為off的時候，之後創建的表就都是非永久性的。
但由於後續的新版本已經很少使用這個方式，所以也就不深入說明它了。


上一篇
Mysql執行成本-Part2(連接查詢的成本、調節成本常數)
下一篇
子查詢最佳化
系列文
那些Mysql我不知道的事 共 30 篇
  1. 26
    神兵利器 - Optimizer trace
  2. 27
    調節磁碟和CPU的矛盾 - InnoDB的Buffer Pool
  3. 28
    小蛙借錢給你的交易概念！！
  4. 29
    待更新
  5. 30
    待更新
完整目錄
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言
iThome鐵人賽
參賽組數
1123 組
團體組數
52 組
累計文章數
23096 篇
完賽人數
656 人
iT+看影片追技術 看影片追技術 看更多
熱門tag 看更多
15th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽 鐵人賽 2019鐵人賽 2018鐵人賽 javascript 2017鐵人賽 python windows php c# windows server linux css react 程式設計 vue.js
熱門問題
熱門回答
熱門文章
IT邦幫忙