MySQL執(zhí)行SQL會(huì)經(jīng)過(guò)SQL解析和查詢優(yōu)化的過(guò)程，解析器將SQL分解成數(shù)據(jù)結(jié)構(gòu)并傳遞到后續(xù)步驟，查詢優(yōu)化器發(fā)現(xiàn)執(zhí)行SQL查詢的最佳方案、生成執(zhí)行計(jì)劃。查詢優(yōu)化器決定SQL如何執(zhí)行，依賴于數(shù)據(jù)庫(kù)的統(tǒng)計(jì)信息，下面我們介紹MySQL 5.7中innodb統(tǒng)計(jì)信息的相關(guān)內(nèi)容。

MySQL統(tǒng)計(jì)信息的存儲(chǔ)分為兩種，非持久化和持久化統(tǒng)計(jì)信息。

一、非持久化統(tǒng)計(jì)信息

非持久化統(tǒng)計(jì)信息存儲(chǔ)在內(nèi)存里，如果數(shù)據(jù)庫(kù)重啟，統(tǒng)計(jì)信息將丟失。有兩種方式可以設(shè)置為非持久化統(tǒng)計(jì)信息：

非持久化統(tǒng)計(jì)信息在以下情況會(huì)被自動(dòng)更新：

非持久化統(tǒng)計(jì)信息的缺點(diǎn)顯而易見(jiàn)，數(shù)據(jù)庫(kù)重啟后如果大量表開始更新統(tǒng)計(jì)信息，會(huì)對(duì)實(shí)例造成很大影響，所以目前都會(huì)使用持久化統(tǒng)計(jì)信息。

二、持久化統(tǒng)計(jì)信息

5.6.6開始，MySQL默認(rèn)使用了持久化統(tǒng)計(jì)信息，即INNODB_STATS_PERSISTENT=ON，持久化統(tǒng)計(jì)信息保存在表mysql.innodb_table_stats和mysql.innodb_index_stats。

持久化統(tǒng)計(jì)信息在以下情況會(huì)被自動(dòng)更新：

innodb_table_stats是表的統(tǒng)計(jì)信息，innodb_index_stats是索引的統(tǒng)計(jì)信息，各字段含義如下：

為更好的理解innodb_index_stats，建一張測(cè)試表做說(shuō)明：

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;

寫入數(shù)據(jù)如下：

查看t1表的統(tǒng)計(jì)信息，需主要關(guān)注stat_name和stat_value字段

tat_name=size時(shí)：stat_value表示索引的頁(yè)的數(shù)量

stat_name=n_leaf_pages時(shí)：stat_value表示葉子節(jié)點(diǎn)的數(shù)量

stat_name=n_diff_pfxNN時(shí)：stat_value表示索引字段上唯一值的數(shù)量，此處做一下具體說(shuō)明：

1、n_diff_pfx01表示索引第一列distinct之后的數(shù)量，如PRIMARY的a列，只有一個(gè)值1，所以index_name='PRIMARY' and stat_name='n_diff_pfx01'時(shí)，stat_value=1。

2、n_diff_pfx02表示索引前兩列distinct之后的數(shù)量，如i2uniq的e,f列，有4個(gè)值，所以index_name='i2uniq' and stat_name='n_diff_pfx02'時(shí)，stat_value=4。

3、對(duì)于非唯一索引，會(huì)在原有列之后加上主鍵索引，如index_name='i1' and stat_name='n_diff_pfx03'，在原索引列c,d后加了主鍵列a，(c,d,a)的distinct結(jié)果為2。

了解了stat_name和stat_value的具體含義，就可以協(xié)助我們排查SQL執(zhí)行時(shí)為什么沒(méi)有使用合適的索引，例如某個(gè)索引n_diff_pfxNN的stat_value遠(yuǎn)小于實(shí)際值，查詢優(yōu)化器認(rèn)為該索引選擇度較差，就有可能導(dǎo)致使用錯(cuò)誤的索引。

三、統(tǒng)計(jì)信息不準(zhǔn)確的處理

我們查看執(zhí)行計(jì)劃，發(fā)現(xiàn)未使用正確的索引，如果是innodb_index_stats中統(tǒng)計(jì)信息差別較大引起，可通過(guò)以下方式處理：

1、手動(dòng)更新統(tǒng)計(jì)信息，注意執(zhí)行過(guò)程中會(huì)加讀鎖：

ANALYZETABLE TABLE_NAME;

2、如果更新后統(tǒng)計(jì)信息仍不準(zhǔn)確，可考慮增加表采樣的數(shù)據(jù)頁(yè)，兩種方式可以修改：

a) 全局變量INNODB_STATS_PERSISTENT_SAMPLE_PAGES，默認(rèn)為20；

b) 單個(gè)表可以指定該表的采樣：

ALTER TABLE TABLE_NAME STATS_SAMPLE_PAGES=40;

經(jīng)測(cè)試，此處STATS_SAMPLE_PAGES的最大值是65535，超出會(huì)報(bào)錯(cuò)。

目前MySQL并沒(méi)有提供直方圖的功能，某些情況下（如數(shù)據(jù)分布不均）僅僅更新統(tǒng)計(jì)信息不一定能得到準(zhǔn)確的執(zhí)行計(jì)劃，只能通過(guò)index hint的方式指定索引。新版本8.0會(huì)增加直方圖功能，讓我們期待MySQL越來(lái)越強(qiáng)大的功能吧！

最新評(píng)論