1. 為什么會有這篇文章楼镐？

選擇DB作為Training的第一個系列忿族，而不是其他，是因為這貨太重要驻啤，而且也是大多數(shù)開發(fā)人員忽略的最多的地方菲驴。為什么一開始就選擇索引，是因為查詢是我們寫的最多的SQL語句骑冗，索引也是能最快最方便提升一個查詢速度的捷徑赊瞬，這個99%的開發(fā)人員都知道這貨是干這個事情的，但是僅僅不到10%的開發(fā)人員能真正了解和用好它贼涩。所以有了這個系列已經(jīng)這個文章的想法巧涧。

2. 索引類型

對索引最簡單的理解就是一本書的頁碼，可以通過書的目錄（這個就相當(dāng)于是數(shù)據(jù)庫中的索引）對應(yīng)的頁碼可以找到這頁的所有內(nèi)容遥倦。在這節(jié)中我們從MySql中索引類型來說一下數(shù)據(jù)庫中的索引工作原理谤绳，從索引的數(shù)據(jù)結(jié)構(gòu)算法上拆分，在MySql的Innodb存儲引擎里面有兩個類型袒哥，BTree和Hash類型索引缩筛，從索引順序和整行數(shù)據(jù)物理順序一致或不一致來區(qū)分的話，分為聚族(聚集)索引和非聚族(非聚集)索引堡称。下面我們首先來說一下BTree和Hash類型瞎抛。

2.1 BTree索引

BTree索引在我們MySql的Innodb中使用的是最多的索引類型，我們一般說的索引就是BTree類型的索引粮呢。BTree的全稱是Binary Tree婿失，就是我們熟悉的二叉樹數(shù)據(jù)結(jié)構(gòu)；下面我們就用一張圖來說明一下BTree在MySql的工作原理啄寡；

圖1豪硅，非聚族索引

2.2 Hash索引

Hash索引我們用的不是特別多，但是在某些場景下我們選擇Hash索引可能更適用一點兒挺物，我們還是先用一張圖來看一下Hash索引在Mysql里面的工作原理懒浮，他工作原理和Java中的HashMap有些類似：

圖2，Hash索引

2.3 BTree和Hash索引各自特點

1.從順序性上來說识藤，BTree是有序的砚著，所以在Order By的時候BTree索引會被使用上，而Hash不會被使用痴昧；Hash索引的無序性也讓它在執(zhí)行范圍查找的時候也不能被使用稽穆，而BTree可以；但是這種順序性在插入新數(shù)據(jù)的時候Hash索引是優(yōu)于BTree索引的赶撰，因為插入的時候BTree需要遍歷樹舌镶，最壞的時候遍歷整個樹來創(chuàng)建對應(yīng)的索引來保證順序柱彻，而Hash本身的無序性，不需要這樣做餐胀；

2.從查詢條件符使用限制來說哟楷，BTree所以可以用在=，<>, >, <, IN, Between, Like(這里說的Like指Like 'ryan%'否灾，是后匹配卖擅，前匹配不支持的)，而Hash索引還是因為它的無序性墨技，它不支持范圍查詢條件符惩阶，它僅僅支持等值查詢條件=, IN不支持范圍條件>, <, <=, >=, Between, Like之類的；

3.索引效率上來說健提，假如都用到了索引Hash索引比BTree索引更加快的琳猫，為什么？這主要從兩點來說私痹，第一是Hash索引的存儲很緊湊，它可以把較長的字符串轉(zhuǎn)換成一串較短數(shù)字统刮，另外它只存儲了hash值和對Row的指針紊遵，這也是為什么它無序了，而BTree存儲了整個索引列的值侥蒙，所以從這塊講他的效率也是相當(dāng)快的暗膜；第二是從結(jié)構(gòu)算法來講，不知道你們看到右邊的復(fù)雜度沒有鞭衩，一個是O(log2n)学搜，一個O(1+(bucket capacity-1))，BTree隨著表的變大他的樹的深度也會隨著增加论衍，最壞是遍歷整個樹的深度瑞佩；而Hash索引如果沒有Hash值的碰撞他的復(fù)雜度只有O(1)，大部分是O(1)坯台，如果有碰撞炬丸，也只是(bucket capacity-1)的增加，所以從算法的復(fù)雜度來說蜒蕾，Hash索引執(zhí)行效率也是優(yōu)于BTree的稠炬；

4.在多列組合上面來說，BTree索引優(yōu)于Hash索引咪啡，如果(A, B, C)3列作為一個組合索引首启，如果使用A作為搜索條件，BTree索引可以利用上撤摸，而Hash索引不會用上毅桃，這個是因為Hash索引是將ABC這3列給Hash value了褒纲，所以A的部分索引的話是不能使用的；

2.4 聚族索引

聚族索引他的索引順序和數(shù)據(jù)的存儲順序是一致的疾嗅，可以理解成他們是在一起的外厂，就像一本書的頁碼和該頁碼中的內(nèi)容；通常我們一個表的主鍵就是聚族索引代承，如果一個表沒有定義主鍵汁蝶，那么MySql的Innodb會自動生成一個主鍵列，以及主鍵索引论悴；另外在innodb中聚族索引是按順序進(jìn)行物理存儲層面存數(shù)據(jù)的掖棉，所以建立聚族索引最好選擇自增長的數(shù)字作為聚族索引，這樣正好在每次最后一位索引值增加新的索引值膀估，如果換成GUID列作為聚族索引幔亥，因為GUID是隨即生成，并且無序的所以每次插入一行紀(jì)錄的時候察纯，創(chuàng)建聚族索引為了保證聚族索引的順序帕棉，會去查找指定的順序位置，產(chǎn)生額外的開銷饼记，另外GUID占用36位unicode字符串香伴，不管是比較字符串，還是存儲需要的長度都是開銷挺大的具则，所以聚族索引優(yōu)先選擇整數(shù)列作為聚族索引即纲；

圖3，聚族索引

2.5 非聚族索引

在一個表只有一個聚族索引博肋，除開聚族索引低斋，該表剩下的索引都是非聚族索引；非聚族索引是基于聚族索引的匪凡，這話怎么講呢膊畴？看下圖，我們在name上建立了一個BTree的非聚族索引锹雏，最終匹配到對應(yīng)Name以后巴比，該非聚族索引還存儲了主鍵，我們使用主鍵再在聚族索引BTree找到真正對應(yīng)的該Row的數(shù)據(jù)礁遵，所以非聚集索引還有另外一個稱號叫“二級索引”轻绞；

圖4，非聚族索引

3. MySql的執(zhí)行計劃

由于下一節(jié)我們會用到執(zhí)行計劃來判斷我們查詢語句是否用了合理的索引佣耐，所以這節(jié)我們先來了解執(zhí)行計劃政勃，首先我們來看一個很簡單的執(zhí)行計劃：

圖5，執(zhí)行計劃

1.const一般是聚族索引或者Unique索引丸冕，直接Where條件或者多表聯(lián)合查詢是只只返回一條數(shù)據(jù)的情況下使用，如下：

select * from ORDER_ITEM where ID=12422;

2.eq_ref是用于聚族索引或者Unique索引在作為多表聯(lián)合查詢的關(guān)聯(lián)列的時候薛窥，如下：

select * from ORDER A LEFT JOIN ORDER_ITEM B ON A.ID=B.ORDER_ID WHERE B.ID (123, 4242);
這里會用到兩個索引胖烛，其中第二個索引A表就是用eq_ref索引；

3.ref是用在用在重復(fù)的非聚族索引上面诅迷，作為Where條件佩番，或者多表聯(lián)合查詢的條件時會使用到，如下：

select * from ORDER_ITEM where ORDER_ID=1;
select * from ORDER A LEFT JOIN ORDER_ITEM B ON B.ORDER_ID=A.ID where A.ID IN (1,2);
這里會用到兩個索引罢杉，其中第二個索引B表就是用ref索引趟畏；

4.index_merge這個類型一般是優(yōu)于建立了兩個單列索引，innodb為了執(zhí)行效率滩租，幫助你優(yōu)化講兩個單列索引合并成1個多列索引赋秀，如下:

select * from ORDER_ITEM WHERE ORDER_ID=1 OR ITEM_ID=1301;

5.range這個類型估計我們看的最多最多了，使用了索引列的作為Where后的范圍查詢條件都會是這種類型律想，Between, IN, >, <, >=, <=沃琅，如下：

select * from ORDER_ITEM WHERE ID < 22234;

6.index這個就是掃描整個索引值；

select * ORDER_ID from ORDER_ITEM ORDER BY ORDER_ID;
這個就是ORDER BY和返回列使用了整個索引值蜘欲，由于沒有加任何條件，就走了這種類型晌柬；

7.all這個是最慢最慢的姥份，在沒有建立索引的列作為條件語句使用，以及ON條件中使用年碘，以及沒有任何條件返回列中沒有索引(那怕是一列沒有索引)澈歉，都會走這個all類型；

select * from ORDER_ITEM;
select * from ORDER_ITEM where ITEM_NAME='apple';

4. 索引使用技巧

4.1 索引的選擇性

我們使用SHOW INDEX執(zhí)行語句來顯示INDEX的一個情況屿衅，如下：

圖5埃难，Index屬性狀態(tài)

我們主要關(guān)注Cardinality這個屬性，對于主鍵列聚族索引來說它的值等于表的Row Size的涤久，那Cardinality這個屬性是什么意思呢涡尘？看看官方的解釋：

An estimate of the number of unique values in the index. This is updated by running ANALYZE TABLE
or myisamchk -a. Cardinality
is counted based on statistics stored as integers, so the value is not necessarily exact even for small tables. The higher the cardinality, the greater the chance that MySQL uses the index when doing joins.

從第一句已經(jīng)說明它的意思，它是一個索引唯一的個數(shù)值响迂，所以我說Primary Key的這個值就是Row Szie考抄，那么對于Primary Key的選擇性就是100%，那對于非聚族索引而且是非唯一的索引蔗彤，那么它的選擇性就是小于100%川梅，選擇性越高疯兼，那么如果該索引作為查詢條件那么它的查詢速度就越快。

4.2 多列組合索引

1.多列組合索引一般適用于查詢條件有多列贫途，而且同時出現(xiàn)在很多查詢場景中吧彪；或者說有一部分列在條件中，另外一部分用于了Order By丢早；還有一種場景就是返回字段很少列姨裸，也可以把這些少部分的列作為組合索引中的部分列，這樣索引就可以直接返回值香拉，而無序回表中獲壤惭铩；
2.多列組合索引是從左至右來應(yīng)用索引的凫碌，是有順序的扑毡，如果直接使用列中或者列尾作為索引條件，是不會走組合索引列的盛险；所以在這里我們應(yīng)該把使用度和選擇性都比較高的列作為組合列的第一個列瞄摊；

首先我們建立該索引(name, email, phone)，id為主鍵
下面SQL語句都會很好的使用索引：
select id,name from user where name='ryan' order by id;
select id,name from user where name='ryan' and email='ryan@..';
則下面的SQL語句不會使用該索引苦掘，而會走scan table的操作：

select id,name from user where email ='ryan@..';
select id,name from user where phone='152322....';

4.3 覆蓋索引

覆蓋索引其實就是指查詢條件中用到了索引换帜，并且查詢的結(jié)果直接用了索引中的列值，而沒有回表去查找數(shù)據(jù)鹤啡；覆蓋索引一般是用BTree實現(xiàn)惯驼，而且一般都是一個組合索引，看下面的示例：

如果我們建立一個name列的索引递瑰，然后返回的是PK ID祟牲，如下：
select id from user where name='ryan';
那么這個查詢的執(zhí)行效率是相當(dāng)高的，根據(jù)圖4的展示抖部，我們可以知道说贝，它不需要回表查詢ID，而是直接從name列上的索引返回了慎颗，而且大部分MySql索引都存儲在數(shù)據(jù)庫服務(wù)器內(nèi)存里面的乡恕；

再看一個示例：

如果我們建立一個(name, phone, email)的索引，該索引在如下查詢語句里面也可以看作覆蓋索引：
select id,name,phone,email from where name='ryan' order by name;

4.4 三星索引

三星索引是值俯萎，一個好索引應(yīng)該滿足傲宜，一是查詢條件中使用了索引；二是返回的列中都是索引中的值讯屈，沒有回表里面查找蛋哭；三是我們很好的利用組合索引的順序，來放在Order By中也使排序使用了索引涮母；但是我們很難造出三星索引谆趾，因為我們索引不單單是用在一條SQL語句躁愿，甚至我們有時候連覆蓋索引很難造出來。上面覆蓋索引這一節(jié)沪蓬，最后那個示例就一個三星索引的使用彤钟，另外我們在這個查詢語句使用的完美，但是該索引對其它查詢語句不一定就工作的很好跷叉，所以有時候我們要全局考慮逸雹，用盡可能少的索引去滿足整個系統(tǒng)查詢需要。而不是糾結(jié)一個查詢云挟，除非這個查詢使用頻繁度非常高梆砸，當(dāng)然這個查詢語句就另開小灶為他單獨定制了；

4.5 延遲關(guān)聯(lián)

我們在使用MySql分頁的時候都會寫這樣的語句：

select * from ORDER limit 10,10

這種使用limit的語句在翻前面幾頁的時候十分的塊园欣，但是如果執(zhí)行到下面這種語句的時候就會非常的慢：

select * from ORDER limit 10000,10

這是因為scan了表10000條出來帖世，結(jié)果拋棄了9990條沒用，只顯示了10行沸枯，這就會讓人感覺只有10行為什么都這么慢日矫，就不知道它scan了9990條所耗費的時間。為了解決這種問題绑榴，我們需要使用延遲關(guān)聯(lián)來解決哪轿，用聚族索引來加速查詢，再看下面的寫法：

select * from ORDER AS T1 INNER JOIN (select id from ORDER limit 10000,10) AS T2 ON T1.ID=T2.ID

這樣里面那個查詢翔怎，和外面查詢都走了索引窃诉，而且里面把返回列盡量的減少，這樣查詢速度是相當(dāng)快的赤套，里面那個select走的是index褐奴，外面走的是eq_ref;

4.6 索引與鎖

在數(shù)據(jù)庫執(zhí)行事務(wù)處理的時候，如果你用到了

select ... for update或者select ... lock in share mode

一個是X(排他所)于毙，一個是S(讀共享鎖)，這兩個鎖都會lock住表或者查詢結(jié)果幾行數(shù)據(jù)辅搬，這個取決于你where后面查詢條件建立了索引沒有，如果走的是聚族索引堪遂，那么鎖定的肯定是一行數(shù)據(jù)，如果走的是非聚族索引可能是查詢的幾行也可能是一個范圍溶褪；如果沒有走任何索引那將是很糟糕的結(jié)果鎖定整個表；還有就算走了索引猿妈，不適查詢了顯示結(jié)果幾行就鎖定了幾行數(shù)據(jù)巍虫，而且要看執(zhí)行計劃中的rows，用該索引走了幾行數(shù)據(jù)鳍刷，這個才是鎖定的真正行數(shù)占遥，如下面SQL：

select * from ORDER where ID < 5 and ID <> 1
顯示的結(jié)果肯定沒有ID等于1的那行數(shù)據(jù)，但是ID等于1的那行數(shù)據(jù)也被鎖定了输瓜，ID走的聚族索引瓦胎，但是實際走的range 4行的查詢計劃；