MySQL統計資訊的詳細概述

本篇文章透過統計資訊的概念介紹以及MYSQL統計資訊的優點等面向全面概述了MySQL統計資訊的相關知識點，希望可以幫助到有需求的朋友們。

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

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

一、非持久化統計資訊

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

#1 全域變量，

INNODB_STATS_PERSISTENT=OFF

2 CREATE/ALTER表的參數，

STATS_PERSISTENT=0

##非持久化統計資料在下列情況會自動更新：

##5 距上一次更新統計信息，表1/16的數據被修改#非持久化統計信息的缺點顯而易見，數據庫重啟後如果大量表開始更新統計訊息，會對實例造成很大影響，所以目前都會使用持久化統計資料。

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.6.6開始，MySQL預設使用了持久化統計信息，即INNODB_STATS_PERSISTENT=ON，持久化統計信息保存在表mysql.innodb_table_stats和mysql. innodb_index_stats。

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

1 INNODB_STATS_AUTO_RECALC=ON#2 增加新的索引 innodb_table_stats是表格的統計信息，innodb_index_stats是索引的統計信息，各字段含義如下：

情況下，表中10%的資料被修改

innodb_table_statsinnodb_table_statsdatabase_nametable_name

	innodb_table_stats
innodb_table_stats
資料庫名稱
表格名稱	last_update

統計資料最後一次更新時間
表的行數聚集索引的頁的數量其他索引的頁的數量#innodb_index_stats#database_name資料庫名稱table_name

##n_rows
	clustered_index_size
	sum_of_other_index_sizes
	##表名稱
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;

寫入資料如下：

查看t1表的統計信息，需主要關注stat_name和stat_value字段

tat_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越來越強大的功能吧！

相關推薦：

實例解析：統計資訊管理、Spring註解開發與EasyUI

收集SQL Server統計資料_PHP教學課程

以上是MySQL統計資訊的詳細概述的詳細內容。更多資訊請關注PHP中文網其他相關文章！