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

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中文網其它相關文章！