MySQL5.6引入了一個新的系統(tǒng)變量eq_range_index_dive_limit。
查閱MySQL5.6官方文檔得知，MySQL在執(zhí)行等值范圍查詢例如select ... from xxx where xxx in(...)時，優(yōu)化器在計(jì)算執(zhí)行計(jì)劃成本時會根據(jù)條件個數(shù)采用不同的方式以減小選擇執(zhí)行計(jì)劃的開銷。
當(dāng)條件數(shù)N小于eq_range_index_dive_limit時，優(yōu)化器認(rèn)為此時條件個數(shù)尚可，可以采用成本較高但更為精確的index dive方式來計(jì)算執(zhí)行成本；當(dāng)N大于或等于eq_range_index_dive_limit時，優(yōu)化器會認(rèn)為此時使用index dive的方式計(jì)算成本帶來的開銷過大，此時MySQL優(yōu)化器會根據(jù)index statistics直接估算成本。
大部分情況下，where條件中使用的索引列的選擇性都還是不錯的，使用index statistic直接估算返回行數(shù)并不會有太大偏差，并且能夠避免index dive帶來的開銷，在IN條件較多的情況下，能快速找到正確的執(zhí)行計(jì)劃，提升系統(tǒng)性能。然而，不均勻分布的索引也不罕見，這種情況下，eq_range_index_dive_limit可能會顯著影響查詢執(zhí)行計(jì)劃，這里借用網(wǎng)上的一個案例：

創(chuàng)新互聯(lián)是一家集網(wǎng)站建設(shè),北戴河企業(yè)網(wǎng)站建設(shè),北戴河品牌網(wǎng)站建設(shè),網(wǎng)站定制,北戴河網(wǎng)站建設(shè)報(bào)價(jià),網(wǎng)絡(luò)營銷,網(wǎng)絡(luò)優(yōu)化,北戴河網(wǎng)站推廣為一體的創(chuàng)新建站企業(yè)，幫助傳統(tǒng)企業(yè)提升企業(yè)形象加強(qiáng)企業(yè)競爭力。可充分滿足這一群體相比中小企業(yè)更為豐富、高端、多元的互聯(lián)網(wǎng)需求。同時我們時刻保持專業(yè)、時尚、前沿，時刻以成就客戶成長自我，堅(jiān)持不斷學(xué)習(xí)、思考、沉淀、凈化自己，讓我們?yōu)楦嗟钠髽I(yè)打造出實(shí)用型網(wǎng)站。

有一個表“t”。主鍵由從“id1”開始的多個列組成。表t中有1.67M行，id1的基數(shù)是46K（這些數(shù)字可以通過SHOW TABLE STATUS / SHOW INDEX收集）。因此，每個id1平均有36行（1.67M / 46K = 36），但實(shí)際的id1分布是不均勻的。有接近1M行，其中id1在1和10之間。

mysql> explain select count（*）from t force index（PRIMARY）where id1 in（1,2,3,4,5,6,7,8,9）\G 
***************** 1.行********** * 
id：1 
select_type：SIMPLE 
table：t 
type：range 
possible_keys：PRIMARY 
key：PRIMARY 
key_len：8 
ref：NULL 
rows：912388 
extra：using where;using index 
1 row（0.00 sec）

MySQL估計(jì)912K行匹配，其中id1 IN（1..9）。這接近實(shí)際數(shù)字。 MySQL5.6引入了持久化優(yōu)化器統(tǒng)計(jì)，使統(tǒng)計(jì)信息更準(zhǔn)確。

mysql>explain select count（*）from t force index（PRIMARY）where id1 in（1,2,3,4,5,6,7,8,9,10）\G 
***************** 1.行********** * 
id：1 
select_type：SIMPLE 
table：t 
type：range 
possible_keys：PRIMARY 
key：PRIMARY 
key_len：8 
ref：NULL 
rows：360 
extra：using where;using index 
1 row（0.00 sec）

當(dāng)添加一個IN條件（id1 IN（1..10））時，突然估計(jì)的行數(shù)下降到360！這比實(shí)際匹配的行數(shù)小得多。估計(jì)的行數(shù)越來越少（或更大）經(jīng)常使MySQL選擇不正確的查詢執(zhí)行計(jì)劃，所以這是真的很嚴(yán)重。

估計(jì)的行數(shù)變化很大的原因是一個新的系統(tǒng)變量eq_range_index_dive_limit。如在線手冊所述，“如果eq_range_index_dive_limit大于0，如果有eq_range_index_dive_limit或更多相等范圍”，優(yōu)化器將使用現(xiàn)有索引統(tǒng)計(jì)信息而不是索引潛水。默認(rèn)eq_range_index_dive_limit為10.因此，當(dāng)設(shè)置10個或更多IN條件時，MySQL會跳過索引dive，并從統(tǒng)計(jì)信息中估計(jì)行數(shù)。在這個例子中，MySQL估計(jì)360行（1.67M（表t的估計(jì)總行數(shù)）/ 46K（基數(shù)id1）* 10（IN條件）== 360）。

通過增加eq_range_index_dive_limit足夠大，MySQL不會錯誤地估計(jì)行。

mysql> set session eq_range_index_dive_limit = 1000; 
query OK，0 row affected（0.00秒）

mysql>explain select count（*）from t force index（PRIMARY）where id1 in（1,2,3,4,5,6,7,8,9,10）\G 
***************** 1.行********** * 
id：1 
select_type：SIMPLE 
table：t 
type：range 
possible_keys：PRIMARY 
key：PRIMARY 
key_len：8 
ref：NULL 
rows：937684 
extra：using where;using index 
1 row（0.00 sec）

由于SQL強(qiáng)制走了主鍵索引，在這個例子中MySQL并沒有選錯執(zhí)行計(jì)劃，但eq_range_index_dive_limit對于MySQL選擇執(zhí)行計(jì)劃的影響顯而易見。
在eq_range_index_dive_limit設(shè)置過小且索引分布極不均勻的情況下，MySQL可能會由于成本計(jì)算誤差太大，導(dǎo)致選擇錯誤的執(zhí)行計(jì)劃這一災(zāi)難性后果！
如果是業(yè)務(wù)特征決定了需要執(zhí)行多次類似于上述案例中的SQL，DBA應(yīng)考慮關(guān)閉該特性：
set global eq_range_index_dive_limit = 0;
總結(jié)：
eq_range_index_dive_limit有助于減少查詢執(zhí)行計(jì)劃的index dive成本，但5.6版本缺省值為10，有點(diǎn)偏小，DBA應(yīng)根據(jù)業(yè)務(wù)特點(diǎn)選擇合理的值或者關(guān)閉該特性。
注：該參數(shù)在MySQL 5.7中缺省值為200。

網(wǎng)站名稱：eq_range_index_dive_limit的作用
地址分享：http://chinadenli.net/article10/jggpdo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供手機(jī)網(wǎng)站建設(shè)、品牌網(wǎng)站建設(shè)、網(wǎng)站設(shè)計(jì)公司、自適應(yīng)網(wǎng)站、電子商務(wù)、微信公眾號

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)