本文是在假定讀者了解了直方圖是什么，直方圖如何進(jìn)行添加維護(hù)的前提下，圍繞直方圖與索引的對比、何時應(yīng)該添加直方圖，及直方圖如何幫助優(yōu)化器選擇更優(yōu)的執(zhí)行計劃這幾個方面來介紹直方圖。

直方圖的作用

直方圖用于描述數(shù)據(jù)表中列上的數(shù)據(jù)分布，這些關(guān)于數(shù)據(jù)分布的內(nèi)容，可以幫助優(yōu)化器更準(zhǔn)確地估計給定的where子句或聯(lián)接條件將過濾掉表中多少數(shù)據(jù)。這里我們要清楚的一點，直方圖并不能像索引一樣可以幫助減少要訪問的行數(shù)，它只是幫助優(yōu)化器選擇更合適的執(zhí)行計劃。MySQL8開始支持直方圖，但實際上直方圖在MySQL中，不像在其他數(shù)據(jù)庫中那樣有用，因為MySQL能夠通過index dive，直接訪問索引對應(yīng)的B+樹，來計算某個掃描區(qū)間內(nèi)對應(yīng)的索引記錄條數(shù)，所以直方圖不能與同一列上的索引一起使用，而且優(yōu)化器優(yōu)先使用索引。

直方圖與索引相比，優(yōu)點是什么

MySQL的索引既可以有效估算索引范圍內(nèi)的行數(shù)，又可以幫助減少要訪問的記錄，為什么還要引入直方圖呢？與索引相比，直方圖的一個好處是，在確定過濾條件返回行數(shù)時直方圖比索引成本要低，直方圖的統(tǒng)計信息可以輕松用于優(yōu)化器，而索引在確定查詢計劃時，要執(zhí)行下潛操作來估算行數(shù)，并且每次執(zhí)行查詢時都要重復(fù)執(zhí)行這樣的操作。講到這里可能大家會有一個疑問，不是有索引統(tǒng)計信息嗎，為何每次都要執(zhí)行下潛操作來估算呢？其實MySQL是這樣設(shè)計的，有一個參數(shù)eq_range_index_dive_limit(默認(rèn)值200), 對于索引列而言，當(dāng)存在與此參數(shù)設(shè)置相等或更大的區(qū)間范圍過濾條件時，優(yōu)化器將從下潛轉(zhuǎn)換為只使用索引統(tǒng)計信息來估算匹配行的數(shù)量。因為MySQL認(rèn)為使用index dive估算比統(tǒng)計信息更準(zhǔn)確，但是當(dāng)過濾的區(qū)間范圍條件多，比如使用in來過濾，條目值達(dá)到1000，過濾區(qū)間就會有1000個，這樣index dive的成本太高，MySQL就傾向于使用索引統(tǒng)計信息了。似乎跑題了呢，回歸正傳。與索引相比，直方圖的第二個好處是，索引維護(hù)有代價，執(zhí)行DML操作時需要維護(hù)索引，所以索引多了就會影響DML操作的效率，直方圖統(tǒng)計信息只需在非業(yè)務(wù)高峰定期收集即可，對DML操作無影響。與索引相比，直方圖的第三個好處是，索引會增加表空間文件的大小，而直方圖統(tǒng)計信息占用的空間可忽略不計。

既無索引又無直方圖，優(yōu)化器如何估算返回行數(shù)

如果過濾條件上既沒有索引也沒有直方圖，優(yōu)化器如何估算過濾比例呢，優(yōu)化器會根據(jù)MySQL代碼中內(nèi)置的默認(rèn)規(guī)則來估計過濾比例，相當(dāng)于根據(jù)自己的想法瞎猜。默認(rèn)的過濾比例以一個列表形式來展示如下：

舉個例子；執(zhí)行語句：explain select * from t1 where temporary= 'N';對t1表的字段 temporary 進(jìn)行等值過濾。

從上圖可以看出，優(yōu)化器按規(guī)則估算過濾比例，filtered為10%，也就是估算返回行數(shù)為rows * filtered/100=7183行，而實際返回行數(shù)為72214，filtered=72214/72435=99.69。這個差異可謂很大了。

收集一下該列上直方圖的統(tǒng)計信息后，再去查看執(zhí)行計劃中的filtered，此時filtered就相當(dāng)精確了。

數(shù)據(jù)分布不均勻時，MySQL以不變應(yīng)萬變的處理規(guī)則，估算肯定是相當(dāng)不準(zhǔn)確的，因此在選擇執(zhí)行計劃時就有可能做出錯誤的決策。索引的維護(hù)有代價，不能在每個涉及條件的列上都加上索引，那么在不適合創(chuàng)建索引的列上創(chuàng)建直方圖，可以作為索引的補(bǔ)充，幫助優(yōu)化器更好的選擇執(zhí)行計劃。

何時應(yīng)該添加直方圖

因為MySQL在sql優(yōu)化階段會對索引進(jìn)行下潛操作來估算返回行數(shù)，導(dǎo)致直方圖在MySQL中使用空間是有限的，那么究竟要在哪些列上創(chuàng)建直方圖，才能有效發(fā)揮直方圖的作用呢？創(chuàng)建直方圖的最佳候選是符合下列條件的列：

數(shù)據(jù)分布不均勻，或者具有太多值，以至于優(yōu)化器粗略估算無法很好的估計數(shù)據(jù)的選擇行。

選擇性差的列（否則索引可能是更好的選擇）

用于在where子句或聯(lián)接條件過濾表的數(shù)據(jù)。如果不對列進(jìn)行過濾，則優(yōu)化器無法使用直方圖。

隨著時間推移，數(shù)據(jù)分布逐漸穩(wěn)定的列。直方圖統(tǒng)計信息不會自動更新。如果在數(shù)據(jù)分布頻繁變化的列上添加直方圖，則直方圖統(tǒng)計信息可能不準(zhǔn)確。

其實直方圖對于單表訪問用處不大，主要體現(xiàn)在表聯(lián)接時，表的聯(lián)接方式有多種選擇時，直方圖才可以幫助確定何種選擇最好。舉個例子來說明。a1,a2兩個表做關(guān)聯(lián)查詢。兩個表結(jié)構(gòu)信息如下圖所示：

關(guān)聯(lián)查詢語句：select * from a1,a2 where a1.id=a2.id and a1.temporary='N' and a2.status='NOVALID';兩表在關(guān)聯(lián)條件的字段上都有索引，又都有額外的過濾條件，優(yōu)化器在選擇走嵌套聯(lián)接時，有兩種可能，一種a1驅(qū)動a2，一種是a2驅(qū)動a1，哪種方式更好，取決于兩表使用過濾條件過濾后哪個表返回的行數(shù)少，因為我們知道嵌套聯(lián)接時，小表驅(qū)動大表效率高。而a1表的temporary字段，a2表的status字段數(shù)據(jù)分布不均勻，選擇性差，不適合建立索引。這個時候直方圖就有用武之地了。已知a1表的temporary='N'條件過濾性差，a2表的status='NOVALID'的過濾性好，用a2驅(qū)動a1效率會更高。因為沒有直方圖時，優(yōu)化器不知道誰的過濾性好，按等值過濾的默認(rèn)規(guī)則filtered=10進(jìn)行過濾，在選擇執(zhí)行計劃時就有可能做出錯誤決策。我們先看沒有收集直方圖時的執(zhí)行計劃。如下圖所示：

從圖中可以看出優(yōu)化器選擇了a1驅(qū)動a2, a1表過濾后估算的行數(shù)為7049，而實際為72214,a2表作為被驅(qū)動表被掃描72214次。執(zhí)行總耗時280ms。 下面對a2表的status列收集直方圖，然后再執(zhí)行關(guān)聯(lián)查詢，如下圖所示：

從上圖可以看出，有了直方圖后，優(yōu)化器選擇了a2驅(qū)動a1, a2表過濾后估算的行數(shù)為8，實際也為8，a1作為被驅(qū)動表只掃描了8次。執(zhí)行總耗時87ms，效率提升了3倍。

體會到直方圖的作用了嗎，直方圖告訴了優(yōu)化器數(shù)據(jù)分布，讓優(yōu)化器估算更準(zhǔn)確，進(jìn)而讓優(yōu)化器做出了英明的決策。