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

直方圖的作用

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

直方圖與索引相比，優點是什么

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

既無索引又無直方圖，優化器如何估算返回行數

如果過濾條件上既沒有索引也沒有直方圖，優化器如何估算過濾比例呢，優化器會根據MySQL代碼中內置的默認規則來估計過濾比例，相當于根據自己的想法瞎猜。默認的過濾比例以一個列表形式來展示如下：

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

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

收集一下該列上直方圖的統計信息后，再去查看執行計劃中的filtered，此時filtered就相當精確了。

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

何時應該添加直方圖

因為MySQL在sql優化階段會對索引進行下潛操作來估算返回行數，導致直方圖在MySQL中使用空間是有限的，那么究竟要在哪些列上創建直方圖，才能有效發揮直方圖的作用呢？創建直方圖的最佳候選是符合下列條件的列：

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

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

用于在where子句或聯接條件過濾表的數據。如果不對列進行過濾，則優化器無法使用直方圖。

隨著時間推移，數據分布逐漸穩定的列。直方圖統計信息不會自動更新。如果在數據分布頻繁變化的列上添加直方圖，則直方圖統計信息可能不準確。

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

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

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

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

體會到直方圖的作用了嗎，直方圖告訴了優化器數據分布，讓優化器估算更準確，進而讓優化器做出了英明的決策。