MySQL統計資訊系列

Mysql的統計資訊  官方文件： https://dev.mysql.com/doc/refman/5.7/en/innodb-persistent-stats.html

--統計資訊可以持久化儲存在資料庫的表中

 

--持久化儲存的條件 ：引數innodb_stats_persistent=on(預設為on) 或者 建表時加上 STATS_PERSISTENT=1

mysql> show variables like 'innodb_stats_persistent';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_stats_persistent | ON    |
+-------------------------+-------+
1 row in set (0.00 sec)

 

 

--持久化儲存的表為：mysql.innodb_table_stats 和 mysql.innodb_index_stats 

mysql> desc mysql.innodb_table_stats;
+--------------------------+---------------------+------+-----+-------------------+-----------------------------+
| Field                    | Type                | Null | Key | Default           | Extra                       |
+--------------------------+---------------------+------+-----+-------------------+-----------------------------+   
| database_name            | varchar(64)         | NO   | PRI | NULL              |                             |    --Database name
| table_name               | varchar(199)        | NO   | PRI | NULL              |                             |    --Table name, partition name, or subpartition name
| last_update              | timestamp           | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |    --A timestamp indicating the last time that InnoDB updated this row
| n_rows                   | bigint(20) unsigned | NO   |     | NULL              |                             |    --The number of rows in the table
| clustered_index_size     | bigint(20) unsigned | NO   |     | NULL              |                             |    --The size of the primary index, in pages
| sum_of_other_index_sizes | bigint(20) unsigned | NO   |     | NULL              |                             |    --The total size of other (non-primary) indexes, in pages
+--------------------------+---------------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.11 sec)
mysql>  desc mysql.innodb_index_stats ;
+------------------+---------------------+------+-----+-------------------+-----------------------------+
| Field            | Type                | Null | Key | Default           | Extra                       |
+------------------+---------------------+------+-----+-------------------+-----------------------------+
| database_name    | varchar(64)         | NO   | PRI | NULL              |                             |    --Database name
| table_name       | varchar(199)        | NO   | PRI | NULL              |                             |    --Table name, partition name, or subpartition name
| index_name       | varchar(64)         | NO   | PRI | NULL              |                             |    --Index name
| last_update      | timestamp           | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |    --A timestamp indicating the last time that InnoDB updated this row
| stat_name        | varchar(64)         | NO   | PRI | NULL              |                             |    --The name of the statistic, whose value is reported in the stat_value column
| stat_value       | bigint(20) unsigned | NO   |     | NULL              |                             |    --The value of the statistic that is named in stat_name column
| sample_size      | bigint(20) unsigned | YES  |     | NULL              |                             |    --The number of pages sampled for the estimate provided in the stat_value column
| stat_description | varchar(1024)       | NO   |     | NULL              |                             |    --Description of the statistic that is named in the stat_name column
+------------------+---------------------+------+-----+-------------------+-----------------------------+
8 rows in set (0.00 sec)

 

 

--統計資訊是否重新收集(10%行資料變化就自動收集)，設定innodb_stats_auto_recalc=on(預設為on)

--當資料變化超過10%後，會延遲幾秒才收集，如果想立即收集統計資訊 可手動執行 ANALYZE TABLE

--當新增新的索引到已有的表中、或者在已有表中增加，刪除列 時 索引的統計資訊都會新增到mysql.innodb_index_stats表中，忽略innodb_stats_auto_recalc引數是否生效

mysql> show variables like 'innodb_stats_auto_recalc';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_stats_auto_recalc | ON    |
+--------------------------+-------+
1 row in set (0.00 sec)

 

 

--收集統計資訊時，隨機抽取的索引頁數，innodb_stats_persistent_sample_pages，預設20

--增加innodb_stats_persistent_sample_pages會使得收集時間變長，增加IO

mysql> show variables like 'innodb_stats_persistent_sample_pages';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| innodb_stats_persistent_sample_pages | 20    |
+--------------------------------------+-------+
1 row in set (0.01 sec)
---------------------

--建立測試表

mysql> CREATE TABLE test_stat (
    -> a INT, b INT, c INT, d INT, e INT, f INT,
    -> PRIMARY KEY (a, b), KEY i1 (c, d), UNIQUE KEY i2uniq (e, f)
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.33 sec)
 
mysql> insert into test_stat values (1,1,10,11,100,101),(1,2,10,11,200,102),(1,3,10,11,100,103),(1,4,10,12,200,104),(1,5,10,12,100,105);
Query OK, 5 rows affected (0.15 sec)
Records: 5  Duplicates: 0  Warnings: 0
 
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from test_stat;
+---+---+------+------+------+------+
| a | b | c    | d    | e    | f    |
+---+---+------+------+------+------+
| 1 | 1 |   10 |   11 |  100 |  101 |
| 1 | 2 |   10 |   11 |  200 |  102 |
| 1 | 3 |   10 |   11 |  100 |  103 |
| 1 | 4 |   10 |   12 |  200 |  104 |
| 1 | 5 |   10 |   12 |  100 |  105 |
+---+---+------+------+------+------+
5 rows in set (0.03 sec)

 

 

--分析表

mysql> ANALYZE TABLE test_stat;
+-----------------+---------+----------+----------+
| Table           | Op      | Msg_type | Msg_text |
+-----------------+---------+----------+----------+
| flydb.test_stat | analyze | status   | OK       |
+-----------------+---------+----------+----------+
1 row in set (0.03 sec)

 

 

--檢視錶統計資訊

mysql> SELECT * FROM mysql.innodb_table_stats WHERE table_name like 'test_stat';
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name | last_update         | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| flydb         | test_stat  | 2018-11-07 14:46:57 |      5 |                    1 |                        2 |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
1 row in set (0.00 sec)

 

 

--檢視索引統計資訊

mysql> SELECT index_name, stat_name, stat_value, stat_description
         FROM mysql.innodb_index_stats 
        WHERE table_name like 'test_stat';
+------------+--------------+------------+-----------------------------------+
| index_name | stat_name    | stat_value | stat_description                  |
+------------+--------------+------------+-----------------------------------+
| PRIMARY    | n_diff_pfx01 |          1 | a                                 |
| PRIMARY    | n_diff_pfx02 |          5 | a,b                               |
| PRIMARY    | n_leaf_pages |          1 | Number of leaf pages in the index |
| PRIMARY    | size         |          1 | Number of pages in the index      |
| i1         | n_diff_pfx01 |          1 | c                                 |
| i1         | n_diff_pfx02 |          2 | c,d                               |
| i1         | n_diff_pfx03 |          2 | c,d,a                             |
| i1         | n_diff_pfx04 |          5 | c,d,a,b                           |
| i1         | n_leaf_pages |          1 | Number of leaf pages in the index |
| i1         | size         |          1 | Number of pages in the index      |
| i2uniq     | n_diff_pfx01 |          2 | e                                 |
| i2uniq     | n_diff_pfx02 |          5 | e,f                               |
| i2uniq     | n_leaf_pages |          1 | Number of leaf pages in the index |
| i2uniq     | size         |          1 | Number of pages in the index      |
+------------+--------------+------------+-----------------------------------+
14 rows in set (0.02 sec)

--說明

The stat_name column shows the following types of statistics:

* size: Where stat_name=size, the stat_value column displays the total number of pages in the index.

* n_leaf_pages: Where stat_name=n_leaf_pages, the stat_value column displays the number of leaf pages in the index.

* n_diff_pfxNN: Where stat_name=n_diff_pfx01, the stat_value column displays the number of distinct values in the first column of the index. Where stat_name=n_diff_pfx02, the stat_value column displays the number of distinct values in the first two columns of the index, and so on. Additionally, where stat_name=n_diff_pfxNN, the stat_description column shows a comma separated list of the index columns that are counted.

--計算大小

mysql> SELECT SUM(stat_value) pages, index_name,
              SUM(stat_value)*@@innodb_page_size size
         FROM mysql.innodb_index_stats 
        WHERE table_name='test_stat'
          AND stat_name = 'size' 
        GROUP BY index_name;
+-------+------------+-------+
| pages | index_name | size  |
+-------+------------+-------+
|     1 | PRIMARY    | 16384 |
|     1 | i1         | 16384 |
|     1 | i2uniq     | 16384 |
+-------+------------+-------+
3 rows in set (0.13 sec)
---------------------

MySQL統計資訊簡介

MySQL執行SQL會經過SQL解析和查詢最佳化的過程，解析器將SQL分解成資料結構並傳遞到後續步驟，查詢最佳化器發現執行SQL查詢的最佳方案、生成執行計劃。查詢最佳化器決定SQL如何執行，依賴於資料庫的統計資訊，下面我們介紹MySQL 5.7中innodb統計資訊的相關內容。

MySQL統計資訊的儲存分為兩種，非持久化和持久化統計資訊。

一、非持久化統計資訊

非持久化統計資訊儲存在記憶體裡，如果資料庫重啟，統計資訊將丟失。有兩種方式可以設定為非持久化統計資訊：

非持久化統計資訊在以下情況會被自動更新：

非持久化統計資訊的缺點顯而易見，資料庫重啟後如果大量表開始更新統計資訊，會對例項造成很大影響，所以目前都會使用持久化統計資訊。

二、持久化統計資訊

5.6.6開始，MySQL預設使用了持久化統計資訊，即 INNODB_STATS_PERSISTENT=ON ，持久化統計資訊儲存在表 mysql.innodb_table_stats 和 mysql.innodb_index_stats 。

 持久化統計資訊在以下情況會被自動更新：

innodb_table_stats 是表的統計資訊， innodb_index_stats 是索引的統計資訊，各欄位含義如下：

為更好的理解 innodb_index_stats ，建一張測試表做說明：

CREATE TABLE t1 ( a INT, b INT, c INT, d INT, e INT, f INT,PRIMARY KEY (a, b), KEY i1 (c, d), UNIQUE KEY i2uniq (e, f)) ENGINE=INNODB;

寫入資料如下：

檢視t1表的統計資訊，需主要關注 stat_name 和 stat_value 欄位

stat_name=size 時： stat_value 表示索引的頁的數量

stat_name=n_leaf_pages 時： stat_value 表示葉子節點的數量

stat_name=n_diff_pfxNN 時： stat_value 表示索引欄位上唯一值的數量，此處做一下具體說明：

1、 n_diff_pfx01 表示索引第一列 distinct 之後的數量，如 PRIMARY 的a列，只有一個值1，所以 index_name='PRIMARY' and stat_name='n_diff_pfx01' 時， stat_value=1 。

2、 n_diff_pfx02 表示索引前兩列 distinct 之後的數量，如 i2uniq 的 e,f 列，有4個值，所以 index_name='i2uniq' and stat_name='n_diff_pfx02' 時， stat_value=4 。

3、對於非唯一索引，會在原有列之後加上主鍵索引，如 index_name=’i1’ and stat_name=’n_diff_pfx03’ ，在原索引列c,d後加了主鍵列 a，(c,d,a) 的 distinct 結果為2。

瞭解了 stat_name 和 stat_value 的具體含義，就可以協助我們排查SQL執行時為什麼沒有使用合適的索引，例如某個索引 n_diff_pfxNN 的 stat_value 遠小於實際值，查詢最佳化器認為該索引選擇度較差，就有可能導致使用錯誤的索引。

三、統計資訊不準確的處理

我們檢視執行計劃，發現未使用正確的索引，如果是 innodb_index_stats 中統計資訊差別較大引起，可透過以下方式處理：

1、手動更新統計資訊，注意執行過程中會加讀鎖：

ANALYZE TABLE TABLE_NAME ;

2、如果更新後統計資訊仍不準確，可考慮增加表取樣的資料頁，兩種方式可以修改：

a) 全域性變數 INNODB_STATS_PERSISTENT_SAMPLE_PAGES ，預設為20；

b) 單個表可以指定該表的取樣：

ALTER TABLE TABLE_NAME STATS_SAMPLE_PAGES=40 ;

經測試，此處 STATS_SAMPLE_PAGES 的最大值是65535，超出會報錯。

目前MySQL並沒有提供直方圖的功能，某些情況下（如資料分佈不均）僅僅更新統計資訊不一定能得到準確的執行計劃，只能透過 index hint 的方式指定索引。新版本8.0會增加直方圖功能，讓我們期待MySQL越來越強大的功能吧！

---

---

About Me

........................................................................................................................ ● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除 ● 本文在itpub（ http://blog.itpub.net/26736162 ）、部落格園（ http://www.cnblogs.com/lhrbest ）和個人weixin公眾號（ xiaomaimiaolhr ）上有同步更新 ● 本文itpub地址： http://blog.itpub.net/26736162 ● 本文部落格園地址： http://www.cnblogs.com/lhrbest ● 本文pdf版、個人簡介及小麥苗雲盤地址： http://blog.itpub.net/26736162/viewspace-1624453/ ● 資料庫筆試面試題庫及解答： http://blog.itpub.net/26736162/viewspace-2134706/ ● DBA寶典今日頭條號地址： ........................................................................................................................ ● QQ群號： 230161599 （滿） 、618766405 ● weixin群：可加我weixin，我拉大家進群，非誠勿擾 ● 聯絡我請加QQ好友 （ 646634621 ） ，註明新增緣由 ● 於 2019-05-01 06:00 ~ 2019-05-30 24:00 在魔都完成 ● 最新修改時間：2019-05-01 06:00 ~ 2019-05-30 24:00 ● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解 ● 版權所有，歡迎分享本文，轉載請保留出處 ........................................................................................................................ ● 小麥苗的微店 ： ● 小麥苗出版的資料庫類叢書 ： http://blog.itpub.net/26736162/viewspace-2142121/ ● 小麥苗OCP、OCM、高可用網路班 ： http://blog.itpub.net/26736162/viewspace-2148098/ ● 小麥苗騰訊課堂主頁 ： https://lhr.ke.qq.com/ ........................................................................................................................ 使用 weixin客戶端 掃描下面的二維碼來關注小麥苗的weixin公眾號（ xiaomaimiaolhr ）及QQ群（DBA寶典）、新增小麥苗weixin， 學習最實用的資料庫技術。 ........................................................................................................................