一 介紹

為何要有索引?

一般的應(yīng)用系統(tǒng)埂软，讀寫比例在10:1左右，而且插入操作和一般的更新操作很少出現(xiàn)性能問題峻凫，在生產(chǎn)環(huán)境中妒茬，我們遇到最多的，也是最容易出問題的蔚晨，還是一些復(fù)雜的查詢操作乍钻，因此對(duì)查詢語句的優(yōu)化顯然是重中之重。說起加速查詢铭腕，就不得不提到索引了银择。

什么是索引？

索引在MySQL中也叫做“鍵”累舷，是存儲(chǔ)引擎用于快速找到記錄的一種數(shù)據(jù)結(jié)構(gòu)浩考。索引對(duì)于良好的性能
非常關(guān)鍵，尤其是當(dāng)表中的數(shù)據(jù)量越來越大時(shí)被盈，索引對(duì)于性能的影響愈發(fā)重要析孽。
索引優(yōu)化應(yīng)該是對(duì)查詢性能優(yōu)化最有效的手段了。索引能夠輕易將查詢性能提高好幾個(gè)數(shù)量級(jí)只怎。
索引相當(dāng)于字典的音序表袜瞬，如果要查某個(gè)字，如果不使用音序表身堡，則需要從幾百頁中逐頁去查邓尤。

40
        15               35          66
   6    11   19          21   39     55    100

二 索引的原理

一 索引原理

索引的目的在于提高查詢效率，與我們查閱圖書所用的目錄是一個(gè)道理：先定位到章贴谎，然后定位到該章下的一個(gè)小節(jié)汞扎，然后找到頁數(shù)。相似的例子還有：查字典擅这，查火車車次澈魄，飛機(jī)航班等

本質(zhì)都是：通過不斷地縮小想要獲取數(shù)據(jù)的范圍來篩選出最終想要的結(jié)果，同時(shí)把隨機(jī)的事件變成順序的事件仲翎，也就是說痹扇，有了這種索引機(jī)制，我們可以總是用同一種查找方式來鎖定數(shù)據(jù)谭确。

數(shù)據(jù)庫也是一樣帘营，但顯然要復(fù)雜的多，因?yàn)椴粌H面臨著等值查詢逐哈，還有范圍查詢(>芬迄、<、between昂秃、in)禀梳、模糊查詢(like)杜窄、并集查詢(or)等等。數(shù)據(jù)庫應(yīng)該選擇怎么樣的方式來應(yīng)對(duì)所有的問題呢算途？我們回想字典的例子塞耕，能不能把數(shù)據(jù)分成段，然后分段查詢呢嘴瓤？最簡(jiǎn)單的如果1000條數(shù)據(jù)扫外，1到100分成第一段，101到200分成第二段廓脆，201到300分成第三段......這樣查第250條數(shù)據(jù)筛谚，只要找第三段就可以了，一下子去除了90%的無效數(shù)據(jù)停忿。但如果是1千萬的記錄呢驾讲，分成幾段比較好？稍有算法基礎(chǔ)的同學(xué)會(huì)想到搜索樹席赂，其平均復(fù)雜度是lgN吮铭，具有不錯(cuò)的查詢性能。但這里我們忽略了一個(gè)關(guān)鍵的問題颅停，復(fù)雜度模型是基于每次相同的操作成本來考慮的谓晌。而數(shù)據(jù)庫實(shí)現(xiàn)比較復(fù)雜，一方面數(shù)據(jù)是保存在磁盤上的便监，另外一方面為了提高性能扎谎，每次又可以把部分?jǐn)?shù)據(jù)讀入內(nèi)存來計(jì)算，因?yàn)槲覀冎涝L問磁盤的成本大概是訪問內(nèi)存的十萬倍左右烧董，所以簡(jiǎn)單的搜索樹難以滿足復(fù)雜的應(yīng)用場(chǎng)景。

索引的目的在于提高查詢效率胧奔，與我們查閱圖書所用的目錄是一個(gè)道理：先定位到章逊移，然后定位到該章下的一個(gè)小節(jié)，然后找到頁數(shù)龙填。相似的例子還有：查字典胳泉，查火車車次，飛機(jī)航班等

本質(zhì)都是：通過不斷地縮小想要獲取數(shù)據(jù)的范圍來篩選出最終想要的結(jié)果岩遗，同時(shí)把隨機(jī)的事件變成順序的事件扇商，也就是說，有了這種索引機(jī)制宿礁，我們可以總是用同一種查找方式來鎖定數(shù)據(jù)案铺。

數(shù)據(jù)庫也是一樣，但顯然要復(fù)雜的多梆靖，因?yàn)椴粌H面臨著等值查詢控汉，還有范圍查詢(>笔诵、<、between姑子、in)乎婿、模糊查詢(like)、并集查詢(or)等等街佑。數(shù)據(jù)庫應(yīng)該選擇怎么樣的方式來應(yīng)對(duì)所有的問題呢谢翎？我們回想字典的例子，能不能把數(shù)據(jù)分成段沐旨，然后分段查詢呢岳服？最簡(jiǎn)單的如果1000條數(shù)據(jù)，1到100分成第一段希俩，101到200分成第二段吊宋，201到300分成第三段......這樣查第250條數(shù)據(jù)，只要找第三段就可以了颜武，一下子去除了90%的無效數(shù)據(jù)璃搜。但如果是1千萬的記錄呢，分成幾段比較好鳞上？稍有算法基礎(chǔ)的同學(xué)會(huì)想到搜索樹这吻，其平均復(fù)雜度是lgN，具有不錯(cuò)的查詢性能篙议。但這里我們忽略了一個(gè)關(guān)鍵的問題唾糯，復(fù)雜度模型是基于每次相同的操作成本來考慮的。而數(shù)據(jù)庫實(shí)現(xiàn)比較復(fù)雜鬼贱，一方面數(shù)據(jù)是保存在磁盤上的移怯，另外一方面為了提高性能，每次又可以把部分?jǐn)?shù)據(jù)讀入內(nèi)存來計(jì)算这难，因?yàn)槲覀冎涝L問磁盤的成本大概是訪問內(nèi)存的十萬倍左右舟误，所以簡(jiǎn)單的搜索樹難以滿足復(fù)雜的應(yīng)用場(chǎng)景。

索引的目的在于提高查詢效率姻乓，與我們查閱圖書所用的目錄是一個(gè)道理：先定位到章嵌溢，然后定位到該章下的一個(gè)小節(jié)，然后找到頁數(shù)蹋岩。相似的例子還有：查字典赖草，查火車車次，飛機(jī)航班等

本質(zhì)都是：通過不斷地縮小想要獲取數(shù)據(jù)的范圍來篩選出最終想要的結(jié)果剪个，同時(shí)把隨機(jī)的事件變成順序的事件秧骑，也就是說，有了這種索引機(jī)制，我們可以總是用同一種查找方式來鎖定數(shù)據(jù)腿堤。

數(shù)據(jù)庫也是一樣阀坏，但顯然要復(fù)雜的多，因?yàn)椴粌H面臨著等值查詢笆檀，還有范圍查詢(>忌堂、<、between酗洒、in)士修、模糊查詢(like)、并集查詢(or)等等樱衷。數(shù)據(jù)庫應(yīng)該選擇怎么樣的方式來應(yīng)對(duì)所有的問題呢棋嘲？我們回想字典的例子，能不能把數(shù)據(jù)分成段矩桂，然后分段查詢呢沸移？最簡(jiǎn)單的如果1000條數(shù)據(jù)，1到100分成第一段侄榴，101到200分成第二段雹锣，201到300分成第三段......這樣查第250條數(shù)據(jù)，只要找第三段就可以了癞蚕，一下子去除了90%的無效數(shù)據(jù)蕊爵。但如果是1千萬的記錄呢，分成幾段比較好桦山？稍有算法基礎(chǔ)的同學(xué)會(huì)想到搜索樹攒射，其平均復(fù)雜度是lgN，具有不錯(cuò)的查詢性能恒水。但這里我們忽略了一個(gè)關(guān)鍵的問題会放，復(fù)雜度模型是基于每次相同的操作成本來考慮的。而數(shù)據(jù)庫實(shí)現(xiàn)比較復(fù)雜寇窑，一方面數(shù)據(jù)是保存在磁盤上的鸦概，另外一方面為了提高性能，每次又可以把部分?jǐn)?shù)據(jù)讀入內(nèi)存來計(jì)算甩骏，因?yàn)槲覀冎涝L問磁盤的成本大概是訪問內(nèi)存的十萬倍左右，所以簡(jiǎn)單的搜索樹難以滿足復(fù)雜的應(yīng)用場(chǎng)景先慷。

二 磁盤IO與預(yù)讀

前面提到了訪問磁盤饮笛，那么這里先簡(jiǎn)單介紹一下磁盤IO和預(yù)讀，磁盤讀取數(shù)據(jù)靠的是機(jī)械運(yùn)動(dòng)论熙，每次讀取數(shù)據(jù)花費(fèi)的時(shí)間可以分為尋道時(shí)間福青、旋轉(zhuǎn)延遲、傳輸時(shí)間三個(gè)部分，尋道時(shí)間指的是磁臂移動(dòng)到指定磁道所需要的時(shí)間无午，主流磁盤一般在5ms以下媒役；旋轉(zhuǎn)延遲就是我們經(jīng)常聽說的磁盤轉(zhuǎn)速，比如一個(gè)磁盤7200轉(zhuǎn)宪迟，表示每分鐘能轉(zhuǎn)7200次酣衷，也就是說1秒鐘能轉(zhuǎn)120次，旋轉(zhuǎn)延遲就是1/120/2 = 4.17ms次泽；傳輸時(shí)間指的是從磁盤讀出或?qū)?shù)據(jù)寫入磁盤的時(shí)間穿仪，一般在零點(diǎn)幾毫秒，相對(duì)于前兩個(gè)時(shí)間可以忽略不計(jì)意荤。那么訪問一次磁盤的時(shí)間啊片，即一次磁盤IO的時(shí)間約等于5+4.17 = 9ms左右，聽起來還挺不錯(cuò)的玖像，但要知道一臺(tái)500 -MIPS（Million Instructions Per Second）的機(jī)器每秒可以執(zhí)行5億條指令紫谷，因?yàn)橹噶钜揽康氖请姷男再|(zhì)，換句話說執(zhí)行一次IO的時(shí)間可以執(zhí)行約450萬條指令捐寥，數(shù)據(jù)庫動(dòng)輒十萬百萬乃至千萬級(jí)數(shù)據(jù)笤昨，每次9毫秒的時(shí)間，顯然是個(gè)災(zāi)難上真。下圖是計(jì)算機(jī)硬件延遲的對(duì)比圖咬腋，供大家參考：

Paste_Image.png

]
考慮到磁盤IO是非常高昂的操作，計(jì)算機(jī)操作系統(tǒng)做了一些優(yōu)化睡互，當(dāng)一次IO時(shí)根竿，不光把當(dāng)前磁盤地址的數(shù)據(jù)，而是把相鄰的數(shù)據(jù)也都讀取到內(nèi)存緩沖區(qū)內(nèi)就珠，因?yàn)榫植款A(yù)讀性原理告訴我們寇壳，當(dāng)計(jì)算機(jī)訪問一個(gè)地址的數(shù)據(jù)的時(shí)候，與其相鄰的數(shù)據(jù)也會(huì)很快被訪問到妻怎。每一次IO讀取的數(shù)據(jù)我們稱之為一頁(page)壳炎。具體一頁有多大數(shù)據(jù)跟操作系統(tǒng)有關(guān)，一般為4k或8k逼侦，也就是我們讀取一頁內(nèi)的數(shù)據(jù)時(shí)候匿辩，實(shí)際上才發(fā)生了一次IO，這個(gè)理論對(duì)于索引的數(shù)據(jù)結(jié)構(gòu)設(shè)計(jì)非常有幫助榛丢。

三 索引的數(shù)據(jù)結(jié)構(gòu)

前面講了索引的基本原理铲球，數(shù)據(jù)庫的復(fù)雜性，又講了操作系統(tǒng)的相關(guān)知識(shí)晰赞，目的就是讓大家了解稼病，任何一種數(shù)據(jù)結(jié)構(gòu)都不是憑空產(chǎn)生的选侨，一定會(huì)有它的背景和使用場(chǎng)景，我們現(xiàn)在總結(jié)一下然走，我們需要這種數(shù)據(jù)結(jié)構(gòu)能夠做些什么援制，其實(shí)很簡(jiǎn)單，那就是：每次查找數(shù)據(jù)時(shí)把磁盤IO次數(shù)控制在一個(gè)很小的數(shù)量級(jí)芍瑞，最好是常數(shù)數(shù)量級(jí)晨仑。那么我們就想到如果一個(gè)高度可控的多路搜索樹是否能滿足需求呢？就這樣啄巧，b+樹應(yīng)運(yùn)而生寻歧。

Paste_Image.png

b+樹的查找過程如圖所示钳榨，如果要查找數(shù)據(jù)項(xiàng)29舰罚，那么首先會(huì)把磁盤塊1由磁盤加載到內(nèi)存，此時(shí)發(fā)生一次IO薛耻，在內(nèi)存中用二分查找確定29在17和35之間营罢，鎖定磁盤塊1的P2指針，內(nèi)存時(shí)間因?yàn)榉浅６蹋ㄏ啾却疟P的IO）可以忽略不計(jì)饼齿，通過磁盤塊1的P2指針的磁盤地址把磁盤塊3由磁盤加載到內(nèi)存饲漾，發(fā)生第二次IO，29在26和30之間缕溉，鎖定磁盤塊3的P2指針考传，通過指針加載磁盤塊8到內(nèi)存，發(fā)生第三次IO证鸥，同時(shí)內(nèi)存中做二分查找找到29僚楞，結(jié)束查詢，總計(jì)三次IO枉层。真實(shí)的情況是泉褐，3層的b+樹可以表示上百萬的數(shù)據(jù)，如果上百萬的數(shù)據(jù)查找只需要三次IO鸟蜡，性能提高將是巨大的膜赃，如果沒有索引，每個(gè)數(shù)據(jù)項(xiàng)都要發(fā)生一次IO揉忘，那么總共需要百萬次的IO跳座，顯然成本非常非常高。

b+樹性質(zhì)1.索引字段要盡量的小：通過上面的分析癌淮，我們知道IO次數(shù)取決于b+數(shù)的高度h躺坟，假設(shè)當(dāng)前數(shù)據(jù)表的數(shù)據(jù)為N，每個(gè)磁盤塊的數(shù)據(jù)項(xiàng)的數(shù)量是m乳蓄，則有h=㏒(m+1)N咪橙，當(dāng)數(shù)據(jù)量N一定的情況下，m越大虚倒，h越忻勒臁；而m = 磁盤塊的大小 / 數(shù)據(jù)項(xiàng)的大小魂奥，磁盤塊的大小也就是一個(gè)數(shù)據(jù)頁的大小菠剩，是固定的，如果數(shù)據(jù)項(xiàng)占的空間越小耻煤，數(shù)據(jù)項(xiàng)的數(shù)量越多具壮，樹的高度越低准颓。這就是為什么每個(gè)數(shù)據(jù)項(xiàng)，即索引字段要盡量的小棺妓，比如int占4字節(jié)攘已，要比bigint8字節(jié)少一半。這也是為什么b+樹要求把真實(shí)的數(shù)據(jù)放到葉子節(jié)點(diǎn)而不是內(nèi)層節(jié)點(diǎn)怜跑，一旦放到內(nèi)層節(jié)點(diǎn)样勃，磁盤塊的數(shù)據(jù)項(xiàng)會(huì)大幅度下降，導(dǎo)致樹增高性芬。當(dāng)數(shù)據(jù)項(xiàng)等于1時(shí)將會(huì)退化成線性表峡眶。2.索引的最左匹配特性：當(dāng)b+樹的數(shù)據(jù)項(xiàng)是復(fù)合的數(shù)據(jù)結(jié)構(gòu)，比如(name,age,sex)的時(shí)候植锉，b+數(shù)是按照從左到右的順序來建立搜索樹的辫樱，比如當(dāng)(張三,20,F)這樣的數(shù)據(jù)來檢索的時(shí)候，b+樹會(huì)優(yōu)先比較name來確定下一步的所搜方向汽煮，如果name相同再依次比較age和sex搏熄，最后得到檢索的數(shù)據(jù)；但當(dāng)(20,F)這樣的沒有name的數(shù)據(jù)來的時(shí)候暇赤，b+樹就不知道下一步該查哪個(gè)節(jié)點(diǎn)心例，因?yàn)榻⑺阉鳂涞臅r(shí)候name就是第一個(gè)比較因子，必須要先根據(jù)name來搜索才能知道下一步去哪里查詢鞋囊。比如當(dāng)(張三,F)這樣的數(shù)據(jù)來檢索時(shí)止后，b+樹可以用name來指定搜索方向，但下一個(gè)字段age的缺失溜腐，所以只能把名字等于張三的數(shù)據(jù)都找到译株，然后再匹配性別是F的數(shù)據(jù)了， 這個(gè)是非常重要的性質(zhì)挺益，即索引的最左匹配特性歉糜。

三 MySQL索引管理

一 功能

#1. 索引的功能就是加速查找
#2. mysql中的primary key，unique望众，聯(lián)合唯一也都是索引匪补，這些索引除了加速查找以外，還有約束的功能

二 MySQL常用的索引

普通索引INDEX：加速查找

唯一索引：
    -主鍵索引PRIMARY KEY：加速查找+約束（不為空烂翰、不能重復(fù)）
    -唯一索引UNIQUE:加速查找+約束（不能重復(fù)）

聯(lián)合索引：
    -PRIMARY KEY(id,name):聯(lián)合主鍵索引
    -UNIQUE(id,name):聯(lián)合唯一索引
    -INDEX(id,name):聯(lián)合普通索引

舉個(gè)例子來說夯缺，比如你在為某商場(chǎng)做一個(gè)會(huì)員卡的系統(tǒng)。

這個(gè)系統(tǒng)有一個(gè)會(huì)員表
有下列字段：
會(huì)員編號(hào) INT
會(huì)員姓名 VARCHAR(10)
會(huì)員身份證號(hào)碼 VARCHAR(18)
會(huì)員電話 VARCHAR(10)
會(huì)員住址 VARCHAR(50)
會(huì)員備注信息 TEXT

那么這個(gè) 會(huì)員編號(hào)甘耿，作為主鍵踊兜，使用 PRIMARY
會(huì)員姓名 如果要建索引的話，那么就是普通的 INDEX
會(huì)員身份證號(hào)碼 如果要建索引的話佳恬，那么可以選擇 UNIQUE （唯一的捏境，不允許重復(fù)）

#除此之外還有全文索引于游，即FULLTEXT
會(huì)員備注信息 ， 如果需要建索引的話典蝌，可以選擇全文搜索曙砂。
用于搜索很長(zhǎng)一篇文章的時(shí)候，效果最好骏掀。
用在比較短的文本，如果就一兩行字的柱告，普通的 INDEX 也可以截驮。
但其實(shí)對(duì)于全文搜索，我們并不會(huì)使用MySQL自帶的該索引际度，而是會(huì)選擇第三方軟件如Sphinx葵袭，專門來做全文搜索。

#其他的如空間索引SPATIAL乖菱，了解即可坡锡，幾乎不用

各個(gè)索引的應(yīng)用場(chǎng)景

三 索引的兩大類型hash與btree

#我們可以在創(chuàng)建上述索引的時(shí)候，為其指定索引類型窒所，分兩類
hash類型的索引：查詢單條快鹉勒，范圍查詢慢
btree類型的索引：b+樹，層數(shù)越多吵取，數(shù)據(jù)量指數(shù)級(jí)增長(zhǎng)（我們就用它禽额，因?yàn)閕nnodb默認(rèn)支持它）

#不同的存儲(chǔ)引擎支持的索引類型也不一樣
InnoDB 支持事務(wù)，支持行級(jí)別鎖定皮官，支持 B-tree脯倒、Full-text 等索引，不支持 Hash 索引捺氢；
MyISAM 不支持事務(wù)藻丢，支持表級(jí)別鎖定，支持 B-tree摄乒、Full-text 等索引悠反，不支持 Hash 索引；
Memory 不支持事務(wù)缺狠，支持表級(jí)別鎖定问慎，支持 B-tree、Hash 等索引挤茄，不支持 Full-text 索引如叼；
NDB 支持事務(wù)，支持行級(jí)別鎖定穷劈，支持 Hash 索引笼恰，不支持 B-tree踊沸、Full-text 等索引；
Archive 不支持事務(wù)社证，支持表級(jí)別鎖定逼龟，不支持 B-tree、Hash追葡、Full-text 等索引腺律；

四 創(chuàng)建/刪除索引的語法

#方法一：創(chuàng)建表時(shí)
    　　CREATE TABLE 表名 (
                字段名1  數(shù)據(jù)類型 [完整性約束條件…],
                字段名2  數(shù)據(jù)類型 [完整性約束條件…],
                [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
                [索引名]  (字段名[(長(zhǎng)度)]  [ASC |DESC]) 
                );


#方法二：CREATE在已存在的表上創(chuàng)建索引
        CREATE  [UNIQUE | FULLTEXT | SPATIAL ]  INDEX  索引名 
                     ON 表名 (字段名[(長(zhǎng)度)]  [ASC |DESC]) ;


#方法三：ALTER TABLE在已存在的表上創(chuàng)建索引
        ALTER TABLE 表名 ADD  [UNIQUE | FULLTEXT | SPATIAL ] INDEX
                             索引名 (字段名[(長(zhǎng)度)]  [ASC |DESC]) ;
                             
#刪除索引：DROP INDEX 索引名 ON 表名字;

四 測(cè)試索引

一準(zhǔn)備

#1. 準(zhǔn)備表
create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);

#2. 創(chuàng)建存儲(chǔ)過程，實(shí)現(xiàn)批量插入記錄
delimiter $$ #聲明存儲(chǔ)過程的結(jié)束符號(hào)為$$
create procedure auto_insert1()
BEGIN
    declare i int default 1;
    while(i<3000000)do
        insert into s1 values(i,'egon','male',concat('egon',i,'@oldboy'));
        set i=i+1;
    end while;
END$$ #$$結(jié)束
delimiter ; #重新聲明分號(hào)為結(jié)束符號(hào)

#3. 查看存儲(chǔ)過程
show create procedure auto_insert1\G 

#4. 調(diào)用存儲(chǔ)過程
call auto_insert1();

二 在沒有索引的前提下測(cè)試查詢速度

#無索引：mysql根本就不知道到底是否存在id等于333333333的記錄宜肉，只能把數(shù)據(jù)表從頭到尾掃描一遍匀钧，此時(shí)有多少個(gè)磁盤塊就需要進(jìn)行多少IO操作，所以查詢速度很慢
mysql> select * from s1 where id=333333333;
Empty set (0.33 sec)

三 在表中已經(jīng)存在大量數(shù)據(jù)的前提下谬返，為某個(gè)字段段建立索引之斯，建立速度會(huì)很慢

Paste_Image.png

四 在索引建立完畢后，以該字段為查詢條件時(shí)遣铝，查詢速度提升明顯

Paste_Image.png

PS：

1. mysql先去索引表里根據(jù)b+樹的搜索原理很快搜索到id等于333333333的記錄不存在佑刷，IO大大降低，因而速度明顯提升

2. 我們可以去mysql的data目錄下找到該表酿炸，可以看到占用的硬盤空間多了

3. 需要注意瘫絮，如下圖

Paste_Image.png

五 總結(jié)

#1. 一定是為搜索條件的字段創(chuàng)建索引，比如select * from s1 where id = 333;就需要為id加上索引

#2. 在表中已經(jīng)有大量數(shù)據(jù)的情況下梁沧，建索引會(huì)很慢檀何，且占用硬盤空間，建完后查詢速度加快
比如create index idx on s1(id);會(huì)掃描表中所有的數(shù)據(jù)廷支，然后以id為數(shù)據(jù)項(xiàng)频鉴，創(chuàng)建索引結(jié)構(gòu)，存放于硬盤的表中恋拍。
建完以后垛孔，再查詢就會(huì)很快了。

#3. 需要注意的是：innodb表的索引會(huì)存放于s1.ibd文件中施敢，而myisam表的索引則會(huì)有單獨(dú)的索引文件table1.MYI