5.6 B+樹索引的使用
5.6.1 不同應(yīng)用中B+樹索引的使用
在數(shù)據(jù)庫中存在兩種類型的應(yīng)用，OLTP和OLAP應(yīng)用乏奥。在OLTP應(yīng)用中买窟，查詢操作只從數(shù)據(jù)庫中取得一小部分?jǐn)?shù)據(jù)，一般可能都在10條記錄以下喷户，甚至在很多時候只取1條記錄，如根據(jù)主鍵值來取得用戶信息访锻，根據(jù)訂單號取得訂單的詳細(xì)信息褪尝，這都是典型OLTP應(yīng)用的查詢語句闹获。在這種情況下，B+樹索引建立后河哑，對該索引的使用應(yīng)用只是通過該索引取得表中少部分?jǐn)?shù)據(jù)避诽。這是建立B+樹索引才是有意義的。否則即使建立了璃谨，優(yōu)化器也可能選擇不使用索引沙庐。在OLAP應(yīng)用中，都需要訪問表中大量的數(shù)據(jù)睬罗，根據(jù)這些數(shù)據(jù)來產(chǎn)生查詢的結(jié)果轨功，對于OLAP中復(fù)雜的查詢，要涉及到多張表之間的聯(lián)接操作容达，因此索引的添加仍然是有意義的古涧。
5.6.2 聯(lián)合索引
聯(lián)合索引是指對表上多個列進(jìn)行索引。前面討論的情況都是只對表上的一個列進(jìn)行索引花盐。聯(lián)合索引的創(chuàng)建方法與單個索引的創(chuàng)建方法一樣羡滑，不同之處僅在于有多個索引列。
創(chuàng)建如下表t
從本質(zhì)上來看算芯，聯(lián)合索引也是一顆B+樹柒昏，不同的是聯(lián)合索引的鍵值的數(shù)量大于等于2，不是1
對于查詢
select * from t where a=xxx and b=xxx 顯然是可以使用（a熙揍，b）這個聯(lián)合索引的职祷。對于單個的a列查詢select * from t where a=xxx 也可以使用這個（a，b）索引届囚。但對于 select * from t where b=xxx有梆，則不可以使用這顆B+樹索引。聯(lián)合索引的第二個好處是已經(jīng)對第二個鍵值進(jìn)行了排序處理意系∧嘁可以避免一次排序操作。
創(chuàng)建如下表 buy_log
插入如下數(shù)據(jù)
創(chuàng)建如下索引
對于 select * from buy_date where userid = 2蛔添，優(yōu)化器的選擇為如圖5-23所示：
possible_key表示有兩個索引可供使用痰催，分別是單個的userid索引和(userid，buy_date)的聯(lián)合索引迎瞧。但是優(yōu)化器最終選擇的索引userid夸溶，因為該索引的葉子節(jié)點包含單個鍵值，所以理論上一個頁能存放的記錄應(yīng)該更多凶硅。
對于 select * from buy_log where userid = 1 order by buy_date desc limit 3
執(zhí)行計劃如下：
可以使用userid索引蜘醋，也可以使用（userid，buy_date）索引咏尝。但是這次優(yōu)化器使用了（user_id压语，buy_date）的聯(lián)合索引userid_2，因為在這個聯(lián)合索引中buy_date已經(jīng)排序好了编检。根據(jù)該聯(lián)合索引取出數(shù)據(jù)胎食，無需再對buy_date做一次額外的排序操作。
若強制使用userid索引允懂，則執(zhí)行計劃如下所示：
在Extra選項中可以看到Using filesort厕怜，即需要額外的一次排序操作才能完成查詢。
對于聯(lián)合索引（a蕾总，b粥航，c）來說，
select * from table where a = xxx order by b
select * from table where a = xxx and b = xxx order by c
可以通過聯(lián)合索引來得到結(jié)果生百，但是對于
select * from table where a =xxx order by c 來說递雀，聯(lián)合索引不能直接得到結(jié)果，還需要執(zhí)行一次filesort排序操作蚀浆，因為索引（a缀程，c）未排序
5.6.3 覆蓋索引
InnoDB存儲引擎支持覆蓋索引（covering index，或稱覆蓋索引）市俊，即從輔助索引中就可以得到查詢記錄杨凑，而不需要查詢聚集索引中的記錄。使用覆蓋索引的一個好處是摆昧，輔助索引不包含整行記錄的所有信息撩满，故其大小要遠(yuǎn)小于聚集索引，因此可以減少大量的IO操作绅你。
覆蓋索引的另一個好處是對某些統(tǒng)計問題而言的伺帘。要進(jìn)行如下查詢：
select count(*) from buy_log
InnoDB存儲引擎并不會選擇通過查詢聚集索引來進(jìn)行統(tǒng)計查詢。由于buy_log表上還有輔助索引勇吊，而輔助索引遠(yuǎn)小于聚集索引曼追，選擇輔助索引可以減少IO操作，故優(yōu)化器的選擇為如下圖所示：
possible_keys列為NULL汉规，但是實際執(zhí)行時優(yōu)化器卻選擇了userid索引礼殊，而列Extra的Using index就是代表了優(yōu)化器進(jìn)行了覆蓋操作操作。
在通常情況下针史，諸如（a晶伦，b）的聯(lián)合索引，一般是不可以選擇列b中所謂的查詢條件啄枕。但是如果是統(tǒng)計操作婚陪，并且是覆蓋索引的，則優(yōu)化器會進(jìn)行選擇频祝，如下述語句：
select count(*) from buy_log where buy_date >= '2009-01-01' and buy_date < '2009-02-01'
表buy_log中有（userid泌参，buy_date）的聯(lián)合索引脆淹，這里只根據(jù)列b進(jìn)行條件查詢，一般情況下是不能進(jìn)行該聯(lián)合索引的沽一，但是這句SQL查詢是統(tǒng)計操作盖溺，并且可以利用到覆蓋索引的信息，因此優(yōu)化器會選擇改聯(lián)合索引铣缠，其執(zhí)行計劃如下圖：
** 5.6.4 優(yōu)化器選擇不使用索引的情況 **
在某些情況下烘嘱，當(dāng)執(zhí)行EXPLAIN命令進(jìn)行SQL語句分析時，會發(fā)現(xiàn)優(yōu)化器并沒有選擇索引去查找數(shù)據(jù)蝗蛙，而是通過掃描聚集索引蝇庭，也就是直接進(jìn)行全部掃描來得到數(shù)據(jù)。這種情況多發(fā)生于范圍查找捡硅、JOIN鏈接操作等情況下哮内。例如：
select * from orderdetails where orderid > 10000 and orderid < 102000;
表orderdetails有（orderid,productid）的聯(lián)合索引，此外還有對于列orderid的單個索引病曾。上述SQL顯然是可以通過掃描orderid上的索引進(jìn)行數(shù)據(jù)的查找牍蜂。然而通過EXPLAIN命令，用戶會發(fā)現(xiàn)優(yōu)化器并沒有按照orderid上的索引來查找數(shù)據(jù)泰涂。優(yōu)化器選擇了primary聚集索引鲫竞，也就是表掃描，而非orderid輔助索引掃描逼蒙。
原因在于要選取的數(shù)據(jù)是整行信息从绘，而orderid索引不能覆蓋到我們要查詢的信息，因此在對orderid索引查詢到指定數(shù)據(jù)后是牢，還需要一次書簽訪問來查找整行數(shù)據(jù)的信息僵井。雖然orderid索引中數(shù)據(jù)是順序存放的，但是再一次進(jìn)行書簽查找的數(shù)據(jù)則是無序的驳棱，因此變?yōu)榱舜疟P上的離散讀操作批什。如果要求訪問的數(shù)據(jù)量很小，則優(yōu)化器還是會選擇輔助索引社搅，但是當(dāng)訪問的數(shù)據(jù)占整個表中數(shù)據(jù)的蠻大一部分時（一般20%左右）驻债，優(yōu)化器會選擇通過聚集索引來查找數(shù)據(jù)。因為順序讀要遠(yuǎn)遠(yuǎn)快于離散讀形葬。
** 索引提示 **
MySQL數(shù)據(jù)庫支持索引提示（INDEX HINT）合呐，顯示地告訴優(yōu)化器使用哪個索引。以下兩種情況可能需要用到index hint
- MySQL數(shù)據(jù)庫的優(yōu)化器錯誤地選擇了某個索引笙以，導(dǎo)致SQL語句運行的很慢淌实。這種情況在最新版的MySQL數(shù)據(jù)庫中很少見
- 某個SQL語句可以選擇的索引非常多，這是優(yōu)化器選擇執(zhí)行計劃時間的開銷可能會大于SQL語句本身。例如拆祈，優(yōu)化器分析range查詢本身就是比較耗時的操作恨闪。這是人為的分析最優(yōu)索引的選擇，通過index hint來強制使用優(yōu)化器不進(jìn)行各個執(zhí)行路徑的成本分析缘屹，直接選擇指定的索引來完成查詢凛剥。
使用use index只是告訴優(yōu)化器可以選擇該索引，實際上優(yōu)化器還是會再根據(jù)自己的判斷進(jìn)行選擇轻姿，只有使用force index才能是優(yōu)化器的最終選擇和用戶指定的索引一致。
** Multi-Range Read優(yōu)化 **
MySQL5.6 版本開始支持Multi-Range Read（MRR）優(yōu)化逻炊。MRR優(yōu)化的目的就是為了減少磁盤的隨機訪問互亮，并且將隨機訪問轉(zhuǎn)化為較為順序的數(shù)據(jù)訪問，這對于IO-bound類型的SQL查詢語句可帶來極大的性能提升余素。MRR優(yōu)化可適用于range豹休，ref，eq_ref類型的查詢桨吊。
MRR優(yōu)化有以下幾個好處：
- MRR使數(shù)據(jù)訪問變得較為順序威根。在查詢輔助索引時，首先根據(jù)得到的查詢結(jié)果视乐，按照主鍵排序的順序進(jìn)行書簽查找洛搀。
- 減少緩沖池中頁被替換的次數(shù)
- 批量處理對鍵值的查詢操作
對于InnoDB和MyISAM存儲引擎的范圍查詢和JOIN查詢，MRR的工作方式如下：
1佑淀、將查詢得到的輔助索引鍵值放于一個緩沖中留美，這是緩沖中的數(shù)據(jù)是根據(jù)輔助索引鍵來排序的
2、將緩存中的鍵值根據(jù)RowID進(jìn)行排序
3伸刃、根據(jù)RowID的排序順序來訪問實際的數(shù)據(jù)文件
此外谎砾，若InnoDB存儲引擎或者M(jìn)yISAM存儲引擎的緩沖池不是足夠大，即不能存放下一張表中的所有數(shù)據(jù)捧颅，此外頻繁的離散讀操作還會導(dǎo)致緩沖中的頁被替換出緩沖池景图，然后又不斷地讀入緩沖池。若是按照主鍵順序進(jìn)行訪問碉哑，則可以將此重復(fù)行為降為最低挚币。
MRR還可以將某些將某些范圍查詢，拆分為鍵值對谭梗，以此來進(jìn)行批量的數(shù)據(jù)查詢忘晤。這樣做的好處是可以在拆分過程中，直接過濾一些不符合查詢條件的數(shù)據(jù)激捏，例如：
select * from t where key_part1 >= 1000 and key_part1 < 2000 and key_part2 = 1000 设塔，表t有（key_part1，key_part2）的聯(lián)合索引，因此索引根據(jù)key_part1闰蛔，key_part2的位置關(guān)系進(jìn)行排序痕钢。如沒有MRR，此時查詢類型為Range序六，SQL優(yōu)化器會先將key_part1大于等于1000且小于2000的數(shù)據(jù)都取出任连，即使key_part2不等于1000，待取出行數(shù)據(jù)后再根據(jù)key_part2的條件進(jìn)行過濾例诀。這會導(dǎo)致無用數(shù)據(jù)被取出随抠。如果有大量的數(shù)據(jù)且其key_part2不等于1000，則啟用MRR優(yōu)化使性能有巨大提升繁涂，若是啟用了MRR優(yōu)化拱她，優(yōu)化器會先將查詢條件進(jìn)行拆分，然后再進(jìn)行數(shù)據(jù)查詢扔罪。就上述查詢而言秉沼，優(yōu)化器會將查詢條件進(jìn)行拆分為（1000,1000），（1001,1000）矿酵，...唬复，（1999,1000），最后根據(jù)這些拆分出來的條件進(jìn)行數(shù)據(jù)的查詢全肮。
是否啟動MRR優(yōu)化可以通過參數(shù)optimizer_switch中的標(biāo)記來控制敞咧。當(dāng)mrr為on時，表示啟動MRR優(yōu)化倔矾，mrr_cost_based標(biāo)記表示是否通過cost based的方式來選擇是否啟動mrr妄均。若將mrr設(shè)為on，mrr_cost_based設(shè)為off哪自，則總是啟用MRR優(yōu)化丰包。
通過參數(shù)read_rnd_buffer_size來控制鍵值的緩沖區(qū)大小，當(dāng)大于該值時壤巷，則執(zhí)行器對已經(jīng)緩存的數(shù)據(jù)根據(jù)RowID進(jìn)行排序邑彪，并通過RowID來取得行數(shù)據(jù)。該值默認(rèn)為256K
** Index Condition Pushdown（ICP）優(yōu)化 **
在進(jìn)行索引查詢時胧华，首先根據(jù)索引來查找記錄寄症，然后再根據(jù)where來過濾記錄。在支持ICP后矩动，MySQL數(shù)據(jù)庫會在取出索引的同時有巧，判斷是否可以進(jìn)行where條件過濾，也就是將where的部分過濾操作放在了存儲引擎層悲没。在某些查詢下篮迎，可以大大減少上層SQL層對記錄的索引，從而提高數(shù)據(jù)庫的整體性能
