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

直方圖的作用

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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