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;

+--------------------------+---------------------+------+-----+-------------------+-----------------------------+

+--------------------------+---------------------+------+-----+-------------------+-----------------------------+

+--------------------------+---------------------+------+-----+-------------------+-----------------------------+

6 rows in set (0.11 sec)

mysql> desc mysql.innodb_index_stats ;

+------------------+---------------------+------+-----+-------------------+-----------------------------+

+------------------+---------------------+------+-----+-------------------+-----------------------------+

+------------------+---------------------+------+-----+-------------------+-----------------------------+

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;

+-----------------+---------+----------+----------+

+-----------------+---------+----------+----------+

+-----------------+---------+----------+----------+

1 row in set (0.03 sec)

--檢視錶統計資訊

mysql> SELECT * FROM mysql.innodb_table_stats WHERE table_name like 'test_stat';

+---------------+------------+---------------------+--------+----------------------+--------------------------+

+---------------+------------+---------------------+--------+----------------------+--------------------------+

| 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';

+------------+--------------+------------+-----------------------------------+

+------------+--------------+------------+-----------------------------------+

| PRIMARY | n_diff_pfx01 | 1 | a |

| PRIMARY | n_diff_pfx02 | 5 | a,b |

| 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 |

+------------+--------------+------------+-----------------------------------+

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統計資訊的儲存分為兩種，非持久化和持久化統計資訊。

一、非持久化統計資訊

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

1 

全域性變數，

INNODB_

STATS_PERSISTENT

=OFF

2 CREATE/ALTER

表的引數，

STATS_PERSISTENT=0

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

1 

執行

ANALYZE TABLE

2 innodb_stats_on_metadata=ON

情況下，執

SHOW TABLE STATUS, SHOW INDEX, 

查詢

 INFORMATION_SCHEMA下的TABLES, STATISTICS

3 

啟用

--auto-rehash

功能情況下，使用

mysql client

登入

4 

表第一次被開啟

5 

距上一次更新統計資訊，表

1/16

的資料被修改

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

二、持久化統計資訊

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

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

1 

INNODB_

STATS

_AUTO_RECALC=ON

情況下，表中

10%

的資料被修改

2 

增加新的索引

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

innodb_table_stats

database_name

資料庫名

table_name

表名

last_update

統計資訊最後一次更新時間

n_rows

表的行數

clustered_index_size

聚集索引的頁的數量

sum_of_other_index_sizes

其他索引的頁的數量

innodb_index_stats

database_name

資料庫名

table_name

表名

index_name

索引名

last_update

統計資訊最後一次更新時間

stat_name

統計資訊名

stat_value

統計資訊的值

sample_size

取樣大小

stat_description

型別說明

為更好的理解 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;

寫入資料如下：

0?wx_fmt=png

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

0?wx_fmt=png

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、手動更新統計資訊，注意執行過程中會加讀鎖：

ANALYZETABLE 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寶典今日頭條號地址：

http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826

........................................................................................................................

● 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

● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解

● 版權所有，歡迎分享本文，轉載請保留出處

........................................................................................................................

●

小麥苗的微店

：

https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

●

小麥苗出版的資料庫類叢書

：

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，

學習最實用的資料庫技術。

........................................................................................................................

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2644274/，如需轉載，請註明出處，否則將追究法律責任。

innodb_table_stats
database_name	資料庫名
table_name	表名
last_update	統計資訊最後一次更新時間
n_rows	表的行數
clustered_index_size	聚集索引的頁的數量
sum_of_other_index_sizes	其他索引的頁的數量

innodb_index_stats
database_name	資料庫名
table_name	表名
index_name	索引名
last_update	統計資訊最後一次更新時間
stat_name	統計資訊名
stat_value	統計資訊的值
sample_size	取樣大小
stat_description	型別說明

MySQL統計資訊系列

MySQL統計資訊簡介

二、持久化統計資訊

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

相關文章