數(shù)據(jù)庫 -- 索引并不是萬能的
索引是對數(shù)據(jù)庫表中一列或多列的值進(jìn)行排序的一種結(jié)構(gòu)，使用索引可快速訪問數(shù)據(jù)庫表中的特定信息。如果想按特定職員的姓來查找他或她，則與在表中搜索所有的行相比，索引有助于更快地獲取信息。但是索引也不是萬能的 ，有時(shí)候發(fā)現(xiàn)我們 sql 中索引不生效的，我們深入理解下索引的原理，以及誤區(qū),
InnoDB是如何存儲數(shù)據(jù)的？
MySQL把數(shù)據(jù)存儲和查詢操作抽象成了存儲引擎，不同的存儲引擎，對數(shù)據(jù)的存儲和讀取方式各不相同。MySQL支持多種存儲引擎，并且可以以表為粒度設(shè)置存儲引擎。因?yàn)橹С质挛?，我們最常用的是InnoDB
雖然數(shù)據(jù)保存在磁盤中，但其處理是在內(nèi)存進(jìn)行的。為了減少磁盤隨機(jī)讀取次數(shù)，InnoDB 采用頁而不是行但粒度來保存數(shù)據(jù)，即數(shù)據(jù)被分成若干頁，以頁為單位保存在磁盤中，InnoDB的頁大小，一般是16kb。各頁中又一個(gè)頁目錄，方便按照主鍵查詢記錄。
數(shù)據(jù)頁結(jié)構(gòu)：
頁目錄通過槽把記錄分成不同的小組，沒個(gè)小組有若干條記錄。如圖所示，記錄中最前面的小方塊的數(shù)字，代表的是當(dāng)前分組的記錄條數(shù)，最小和最大的槽指向 2個(gè)特殊的偽記錄。有了槽之后，我們按照主鍵搜索頁中記錄時(shí)，就可以采用二分法快速搜索，無需從最小記錄開始遍歷整個(gè)頁中記錄鏈表。
舉例：搜索主鍵(pk) = 15的記錄
- 先二分得出槽中間位是(0+6)/2=3 , 看到其指向的記錄是 12 < 15 , 所以需要從 #3 槽后繼續(xù)搜索；
- 再使用二分搜索出 #3槽和 #6槽的中間位 (3+6)/2=4.5 取整4，#4槽對應(yīng)的記錄是 16 > 15,所以記錄一定在#4槽中；
- 在從 #3 槽指向的12號記錄開始向下搜索3次，定位到15號記錄。
聚簇索引和非聚簇索引
InnoDB中，表數(shù)據(jù)文件本身就是按B+Tree組織的一個(gè)索引結(jié)構(gòu)，聚簇索引就是按照每張表的主鍵構(gòu)造一顆B+樹，同時(shí)葉子節(jié)點(diǎn)中存放的就是整張表的行記錄數(shù)據(jù)，也將聚集索引的葉子節(jié)點(diǎn)稱為數(shù)據(jù)頁。這個(gè)特性決定了索引組織表中數(shù)據(jù)也是索引的一部分；
一般建表會用一個(gè)自增主鍵做聚簇索引，沒有的話MySQL會默認(rèn)創(chuàng)建，但是這個(gè)主鍵如果更改代價(jià)較高，故建表時(shí)要考慮自增ID不能頻繁update這點(diǎn)。
我們?nèi)粘９ぷ髦?，根?jù)實(shí)際情況自行添加的索引都是輔助索引，輔助索引就是一個(gè)為了需找主鍵索引的二級索引，現(xiàn)在找到主鍵索引再通過主鍵索引找數(shù)據(jù)；
B+ 樹的特點(diǎn)包括：
: 最底層的節(jié)點(diǎn)叫作葉子節(jié)點(diǎn)，用來存放數(shù)據(jù)；
: 其他上層節(jié)點(diǎn)叫作非葉子節(jié)點(diǎn)，僅用來存放目錄項(xiàng)，作為索引；
: 非葉子節(jié)點(diǎn)分為不同層次，通過分層來降低每一層的搜索量；
: 所有節(jié)點(diǎn)按照索引鍵大小排序，構(gòu)成一個(gè)雙向鏈表，加速范圍查找。
- 因此，InnoDB 使用 B+ 樹，既可以保存實(shí)際數(shù)據(jù)，也可以加速數(shù)據(jù)搜索，這就是聚簇索
引。如果把上圖葉子節(jié)點(diǎn)下面方塊中的省略號看作實(shí)際數(shù)據(jù)的話，那么它就是聚簇索引的示
意圖。由于數(shù)據(jù)在物理上只會保存一份，所以包含實(shí)際數(shù)據(jù)的聚簇索引只能有一個(gè)。
- InnoDB 會自動使用主鍵（唯一定義一條記錄的單個(gè)或多個(gè)字段）作為聚簇索引的索引鍵
（如果沒有主鍵，就選擇第一個(gè)不包含 NULL 值的唯一列）。上圖方框中的數(shù)字代表了索
引鍵的值，對聚簇索引而言一般就是主鍵。
為了實(shí)現(xiàn)非主鍵字段的快速搜索，就引出了二級索引，也叫作非聚簇索引、輔助索引。二級索引，也是利用的 B + 數(shù)的數(shù)據(jù)結(jié)構(gòu)
這次二級索引的葉子節(jié)點(diǎn)中保存的不是實(shí)際數(shù)據(jù)，而是主鍵，獲得主鍵值后去聚簇索引中獲
得數(shù)據(jù)行。這個(gè)過程就叫作回表。
回表是什么意思？就是你執(zhí)行一條sql語句，需要從兩個(gè)b+索引中去取數(shù)據(jù)
表tbl有a,b,c三個(gè)字段，其中a是主鍵，b上建了索引，然后編寫sql語句
SELECT * FROM tbl WHERE a=1這樣不會產(chǎn)生回表，因?yàn)樗械臄?shù)據(jù)在a的索引樹中均能找到
SELECT * FROM tbl WHERE b=1這樣就會產(chǎn)生回表，因?yàn)閣here條件是b字段，那么會去b的索引樹里查找數(shù)據(jù)，但b的索引里面只有a,b兩個(gè)字段的值，沒有c，那么這個(gè)查詢?yōu)榱巳〉絚字段，就要取出主鍵a的值，然后去a的索引樹去找c字段的數(shù)據(jù)。
查了兩個(gè)索引樹，這就叫回表。索引覆蓋就是查這個(gè)索引能查到你所需要的所有數(shù)據(jù)，不需要去另外的數(shù)據(jù)結(jié)構(gòu)去查。其實(shí)就是不用回表。
考慮額外創(chuàng)建二級索引的代價(jià)
創(chuàng)建二級索引的代價(jià)，主要表現(xiàn)在維護(hù)代價(jià)、空間代價(jià)和回表代價(jià)三個(gè)方面。
- 維護(hù)代價(jià)：創(chuàng)建 N 個(gè)二級索引，就需要再創(chuàng)建 N 棵 B+ 樹，新增數(shù)據(jù)時(shí)不僅要修改聚簇索引，還需要修改這 N 個(gè)二級索引。
- 空間代價(jià)：雖然二級索引不保存原始數(shù)據(jù)，但要保存索引列的數(shù)據(jù)，所以會占用更多的空間
- 回表代碼：二級索引不保存原始數(shù)據(jù)，通過索引找到主鍵后需要再查詢聚簇索引，才能得到我們想要的數(shù)據(jù)
不是所有針對索引列的查詢都能用上索引
1. 索引只能匹配列前綴
比如下面的 LIKE 語句，搜索 name 后綴為 name123 的用戶無法走索引，執(zhí)行計(jì)劃的 type=ALL 代表了全表掃描：
EXPLAIN SELECT * FROM person WHERE NAME LIKE '%name123' LIMIT 100
把百分號放到后面走前綴匹配，type=range 表示走索引掃描，key=name_score 看到實(shí)際走了索引
EXPLAIN SELECT * FROM person WHERE NAME LIKE 'name123%' LIMIT 100
2. 條件涉及函數(shù)操作無法走索引。
比如搜索條件用到了 LENGTH 函數(shù)，肯定無法走索引
EXPLAIN SELECT * FROM person WHERE LENGTH(NAME)=7
3.聯(lián)合索引只能匹配左邊的列
對 name 和 score 建了聯(lián)合索引，但是僅按照 score 列搜索無法走索引
EXPLAIN SELECT * FROM person WHERE SCORE>45678
個(gè)人博客地址：http://blog.yanxiaolong.cn/
