MYSQL:索引

一 介紹

為何要有索引?

一般的應(yīng)用系統(tǒng)，讀寫比例在10:1左右落萎，而且插入操作和一般的更新操作很少出現(xiàn)性能問題，在生產(chǎn)環(huán)境中炭剪，我們遇到最多的练链，也是最容易出問題的，還是一些復(fù)雜的查詢操作奴拦，因此對查詢語句的優(yōu)化顯然是重中之重媒鼓。說起加速查詢，就不得不提到索引了。

什么是索引绿鸣？

索引在MySQL中也叫做“鍵”疚沐，是存儲引擎用于快速找到記錄的一種數(shù)據(jù)結(jié)構(gòu)。索引對于良好的性能
非常關(guān)鍵潮模，尤其是當(dāng)表中的數(shù)據(jù)量越來越大時(shí)亮蛔，索引對于性能的影響愈發(fā)重要。
索引優(yōu)化應(yīng)該是對查詢性能優(yōu)化最有效的手段了擎厢。索引能夠輕易將查詢性能提高好幾個數(shù)量級究流。
索引相當(dāng)于字典的音序表，如果要查某個字动遭，如果不使用音序表芬探，則需要從幾百頁中逐頁去查。

                      30

        10                          40

   5         15               35          66

1    6    11   19          21   39     55    100

你是否對索引存在誤解厘惦？

索引是應(yīng)用程序設(shè)計(jì)和開發(fā)的一個重要方面偷仿。若索引太多，應(yīng)用程序的性能可能會受到影響宵蕉。而索引太少炎疆，對查詢性能又會產(chǎn)生影響，要找到一個平衡點(diǎn)国裳，這對應(yīng)用程序的性能至關(guān)重要形入。一些開發(fā)人員總是在事后才想起添加索引----我一直認(rèn)為，這源于一種錯誤的開發(fā)模式缝左。如果知道數(shù)據(jù)的使用亿遂，從一開始就應(yīng)該在需要處添加索引。開發(fā)人員往往對數(shù)據(jù)庫的使用停留在應(yīng)用的層面渺杉，比如編寫SQL語句蛇数、存儲過程之類，他們甚至可能不知道索引的存在是越，或認(rèn)為事后讓相關(guān)DBA加上即可耳舅。DBA往往不夠了解業(yè)務(wù)的數(shù)據(jù)流，而添加索引需要通過監(jiān)控大量的SQL語句進(jìn)而從中找到問題倚评，這個步驟所需的時(shí)間肯定是遠(yuǎn)大于初始添加索引所需的時(shí)間浦徊，并且可能會遺漏一部分的索引。當(dāng)然索引也并不是越多越好天梧，我曾經(jīng)遇到過這樣一個問題：某臺MySQL服務(wù)器iostat顯示磁盤使用率一直處于100%盔性，經(jīng)過分析后發(fā)現(xiàn)是由于開發(fā)人員添加了太多的索引，在刪除一些不必要的索引之后呢岗，磁盤使用率馬上下降為20%冕香∮汲ⅲ可見索引的添加也是非常有技術(shù)含量的。

二 索引的原理

一 索引原理

索引的目的在于提高查詢效率悉尾，與我們查閱圖書所用的目錄是一個道理：先定位到章突那，然后定位到該章下的一個小節(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)對所有的問題呢祥楣？我們回想字典的例子开财，能不能把數(shù)據(jù)分成段，然后分段查詢呢误褪？最簡單的如果1000條數(shù)據(jù)责鳍，1到100分成第一段，101到200分成第二段兽间，201到300分成第三段......這樣查第250條數(shù)據(jù)历葛，只要找第三段就可以了，一下子去除了90%的無效數(shù)據(jù)嘀略。但如果是1千萬的記錄呢恤溶，分成幾段比較好？稍有算法基礎(chǔ)的同學(xué)會想到搜索樹帜羊，其平均復(fù)雜度是lgN宏娄，具有不錯的查詢性能。但這里我們忽略了一個關(guān)鍵的問題逮壁，復(fù)雜度模型是基于每次相同的操作成本來考慮的孵坚。而數(shù)據(jù)庫實(shí)現(xiàn)比較復(fù)雜粮宛，一方面數(shù)據(jù)是保存在磁盤上的，另外一方面為了提高性能卖宠，每次又可以把部分?jǐn)?shù)據(jù)讀入內(nèi)存來計(jì)算巍杈，因?yàn)槲覀冎涝L問磁盤的成本大概是訪問內(nèi)存的十萬倍左右，所以簡單的搜索樹難以滿足復(fù)雜的應(yīng)用場景扛伍。

二 磁盤IO與預(yù)讀

前面提到了訪問磁盤筷畦，那么這里先簡單介紹一下磁盤IO和預(yù)讀，磁盤讀取數(shù)據(jù)靠的是機(jī)械運(yùn)動刺洒，每次讀取數(shù)據(jù)花費(fèi)的時(shí)間可以分為尋道時(shí)間鳖宾、旋轉(zhuǎn)延遲、傳輸時(shí)間三個部分逆航，尋道時(shí)間指的是磁臂移動到指定磁道所需要的時(shí)間鼎文，主流磁盤一般在5ms以下；旋轉(zhuǎn)延遲就是我們經(jīng)常聽說的磁盤轉(zhuǎn)速因俐，比如一個磁盤7200轉(zhuǎn)董朝，表示每分鐘能轉(zhuǎn)7200次领曼，也就是說1秒鐘能轉(zhuǎn)120次捡多，旋轉(zhuǎn)延遲就是1/120/2 = 4.17ms绪励；傳輸時(shí)間指的是從磁盤讀出或?qū)?shù)據(jù)寫入磁盤的時(shí)間，一般在零點(diǎn)幾毫秒澳眷，相對于前兩個時(shí)間可以忽略不計(jì)胡嘿。那么訪問一次磁盤的時(shí)間，即一次磁盤IO的時(shí)間約等于5+4.17 = 9ms左右钳踊，聽起來還挺不錯的灶平，但要知道一臺500 -MIPS（Million Instructions Per Second）的機(jī)器每秒可以執(zhí)行5億條指令，因?yàn)橹噶钜揽康氖请姷男再|(zhì)箍土，換句話說執(zhí)行一次IO的時(shí)間可以執(zhí)行約450萬條指令逢享，數(shù)據(jù)庫動輒十萬百萬乃至千萬級數(shù)據(jù)，每次9毫秒的時(shí)間吴藻，顯然是個災(zāi)難瞒爬。下圖是計(jì)算機(jī)硬件延遲的對比圖，供大家參考：

[圖片上傳失敗...(image-b9d450-1599385193512)]

考慮到磁盤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ī)訪問一個地址的數(shù)據(jù)的時(shí)候粥血，與其相鄰的數(shù)據(jù)也會很快被訪問到柏锄。每一次IO讀取的數(shù)據(jù)我們稱之為一頁(page)酿箭。具體一頁有多大數(shù)據(jù)跟操作系統(tǒng)有關(guān)，一般為4k或8k趾娃，也就是我們讀取一頁內(nèi)的數(shù)據(jù)時(shí)候缭嫡，實(shí)際上才發(fā)生了一次IO，這個理論對于索引的數(shù)據(jù)結(jié)構(gòu)設(shè)計(jì)非常有幫助抬闷。

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

前面講了索引的基本原理妇蛀，數(shù)據(jù)庫的復(fù)雜性，又講了操作系統(tǒng)的相關(guān)知識笤成，目的就是讓大家了解评架，任何一種數(shù)據(jù)結(jié)構(gòu)都不是憑空產(chǎn)生的，一定會有它的背景和使用場景炕泳，我們現(xiàn)在總結(jié)一下纵诞，我們需要這種數(shù)據(jù)結(jié)構(gòu)能夠做些什么，其實(shí)很簡單喊崖，那就是：每次查找數(shù)據(jù)時(shí)把磁盤IO次數(shù)控制在一個很小的數(shù)量級挣磨，最好是常數(shù)數(shù)量級雇逞。那么我們就想到如果一個高度可控的多路搜索樹是否能滿足需求呢荤懂？就這樣，b+樹應(yīng)運(yùn)而生（B+樹是通過二叉查找樹塘砸，再由平衡二叉樹节仿，B樹演化而來）。

[圖片上傳失敗...(image-a6b3aa-1599385193512)]

如上圖掉蔬，是一顆b+樹廊宪，關(guān)于b+樹的定義可以參見B+樹，這里只說一些重點(diǎn)女轿，淺藍(lán)色的塊我們稱之為一個磁盤塊箭启，可以看到每個磁盤塊包含幾個數(shù)據(jù)項(xiàng)（深藍(lán)色所示）和指針（黃色所示），如磁盤塊1包含數(shù)據(jù)項(xiàng)17和35蛉迹，包含指針P1傅寡、P2、P3北救，P1表示小于17的磁盤塊荐操，P2表示在17和35之間的磁盤塊，P3表示大于35的磁盤塊珍策。真實(shí)的數(shù)據(jù)存在于葉子節(jié)點(diǎn)即3托启、5、9攘宙、10屯耸、13拐迁、15、28肩民、29唠亚、36、60持痰、75灶搜、79、90工窍、99割卖。非葉子節(jié)點(diǎn)只不存儲真實(shí)的數(shù)據(jù)，只存儲指引搜索方向的數(shù)據(jù)項(xiàng)患雏，如17鹏溯、35并不真實(shí)存在于數(shù)據(jù)表中。

b+樹的查找過程 如圖所示淹仑，如果要查找數(shù)據(jù)項(xiàng)29丙挽，那么首先會把磁盤塊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紫皇，性能提高將是巨大的慰安，如果沒有索引，每個數(shù)據(jù)項(xiàng)都要發(fā)生一次IO聪铺，那么總共需要百萬次的IO化焕，顯然成本非常非常高。

b+樹性質(zhì) 1.索引字段要盡量的小：通過上面的分析铃剔，我們知道IO次數(shù)取決于b+數(shù)的高度h撒桨，假設(shè)當(dāng)前數(shù)據(jù)表的數(shù)據(jù)為N查刻，每個磁盤塊的數(shù)據(jù)項(xiàng)的數(shù)量是m，則有h=㏒(m+1)N凤类，當(dāng)數(shù)據(jù)量N一定的情況下穗泵，m越大，h越忻瞻獭佃延；而m = 磁盤塊的大小 / 數(shù)據(jù)項(xiàng)的大小，磁盤塊的大小也就是一個數(shù)據(jù)頁的大小夷磕，是固定的履肃，如果數(shù)據(jù)項(xiàng)占的空間越小，數(shù)據(jù)項(xiàng)的數(shù)量越多坐桩，樹的高度越低尺棋。這就是為什么每個數(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)會大幅度下降，導(dǎo)致樹增高擦俐。當(dāng)數(shù)據(jù)項(xiàng)等于1時(shí)將會退化成線性表脊阴。 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+樹會優(yōu)先比較name來確定下一步的所搜方向埋合，如果name相同再依次比較age和sex，最后得到檢索的數(shù)據(jù)萄传；但當(dāng)(20,F)這樣的沒有name的數(shù)據(jù)來的時(shí)候甚颂，b+樹就不知道下一步該查哪個節(jié)點(diǎn)，因?yàn)榻⑺阉鳂涞臅r(shí)候name就是第一個比較因子秀菱，必須要先根據(jù)name來搜索才能知道下一步去哪里查詢振诬。比如當(dāng)(張三,F)這樣的數(shù)據(jù)來檢索時(shí)，b+樹可以用name來指定搜索方向衍菱，但下一個字段age的缺失赶么，所以只能把名字等于張三的數(shù)據(jù)都找到，然后再匹配性別是F的數(shù)據(jù)了脊串， 這個是非常重要的性質(zhì)辫呻，即索引的最左匹配特性清钥。

四 聚集索引與輔助索引

在數(shù)據(jù)庫中，B+樹的高度一般都在24層放闺，這也就是說查找某一個鍵值的行記錄時(shí)最多只需要2到4次IO祟昭，這倒不錯。因?yàn)楫?dāng)前一般的機(jī)械硬盤每秒至少可以做100次IO怖侦，24次的IO意味著查詢時(shí)間只需要0.02~0.04秒篡悟。

數(shù)據(jù)庫中的B+樹索引可以分為聚集索引（clustered index）和輔助索引（secondary index），

聚集索引與輔助索引相同的是：不管是聚集索引還是輔助索引匾寝，其內(nèi)部都是B+樹的形式恰力，即高度是平衡的，葉子結(jié)點(diǎn)存放著所有的數(shù)據(jù)旗吁。

聚集索引與輔助索引不同的是：葉子結(jié)點(diǎn)存放的是否是一整行的信息

1踩萎、聚集索引

#InnoDB存儲引擎表示索引組織表，即表中數(shù)據(jù)按照主鍵順序存放很钓。而聚集索引（clustered index）就是按照每張表的主鍵構(gòu)造一棵B+樹香府，同時(shí)葉子結(jié)點(diǎn)存放的即為整張表的行記錄數(shù)據(jù)，也將聚集索引的葉子結(jié)點(diǎn)稱為數(shù)據(jù)頁码倦。聚集索引的這個特性決定了索引組織表中數(shù)據(jù)也是索引的一部分企孩。同B+樹數(shù)據(jù)結(jié)構(gòu)一樣，每個數(shù)據(jù)頁都通過一個雙向鏈表來進(jìn)行鏈接袁稽。

#如果未定義主鍵勿璃，MySQL取第一個唯一索引（unique）而且只含非空列（NOT NULL）作為主鍵，InnoDB使用它作為聚簇索引推汽。

#如果沒有這樣的列补疑，InnoDB就自己產(chǎn)生一個這樣的ID值，它有六個字節(jié)歹撒，而且是隱藏的莲组，使其作為聚簇索引。

#由于實(shí)際的數(shù)據(jù)頁只能按照一棵B+樹進(jìn)行排序暖夭，因此每張表只能擁有一個聚集索引锹杈。在多少情況下，查詢優(yōu)化器傾向于采用聚集索引迈着。因?yàn)榫奂饕軌蛟贐+樹索引的葉子節(jié)點(diǎn)上直接找到數(shù)據(jù)竭望。此外由于定義了數(shù)據(jù)的邏輯順序，聚集索引能夠特別快地訪問針對范圍值得查詢裕菠。

[圖片上傳失敗...(image-47c974-1599385193512)]

聚集索引的好處之一：它對主鍵的排序查找和范圍查找速度非骋澹快，葉子節(jié)點(diǎn)的數(shù)據(jù)就是用戶所要查詢的數(shù)據(jù)。如用戶需要查找一張表枫振，查詢最后的10位用戶信息喻圃，由于B+樹索引是雙向鏈表，所以用戶可以快速找到最后一個數(shù)據(jù)頁粪滤，并取出10條記錄

#參照第六小結(jié)測試索引的準(zhǔn)備階段來創(chuàng)建出表s1
mysql> desc s1; #最開始沒有主鍵
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   |     | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| gender | char(6)     | YES  |     | NULL    |       |
| email  | varchar(50) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> explain select * from s1 order by id desc limit 10; #Using filesort斧拍，需要二次排序
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+----------------+
|  1 | SIMPLE      | s1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 2633472 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+----------------+
1 row in set, 1 warning (0.11 sec)

mysql> alter table s1 add primary key(id); #添加主鍵
Query OK, 0 rows affected (13.37 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from s1 order by id desc limit 10; #基于主鍵的聚集索引在創(chuàng)建完畢后就已經(jīng)完成了排序，無需二次排序
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
|  1 | SIMPLE      | s1    | NULL       | index | NULL          | PRIMARY | 4       | NULL |   10 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.04 sec)

聚集索引的好處之二：范圍查詢（range query）杖小，即如果要查找主鍵某一范圍內(nèi)的數(shù)據(jù)肆汹，通過葉子節(jié)點(diǎn)的上層中間節(jié)點(diǎn)就可以得到頁的范圍，之后直接讀取數(shù)據(jù)頁即可

mysql> alter table s1 drop primary key;
Query OK, 2699998 rows affected (24.23 sec)
Records: 2699998  Duplicates: 0  Warnings: 0

mysql> desc s1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   |     | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| gender | char(6)     | YES  |     | NULL    |       |
| email  | varchar(50) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.12 sec)

mysql> explain select * from s1 where id > 1 and id < 1000000; #沒有聚集索引予权，預(yù)估需要檢索的rows數(shù)如下
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | s1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 2690100 |    11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> alter table s1 add primary key(id);
Query OK, 0 rows affected (16.25 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from s1 where id > 1 and id < 1000000; #有聚集索引昂勉，預(yù)估需要檢索的rows數(shù)如下
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | s1    | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 1343355 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.09 sec)

2、輔助索引

表中除了聚集索引外其他索引都是輔助索引（Secondary Index扫腺，也稱為非聚集索引）岗照，與聚集索引的區(qū)別是：輔助索引的葉子節(jié)點(diǎn)不包含行記錄的全部數(shù)據(jù)。

葉子節(jié)點(diǎn)除了包含鍵值以外笆环，每個葉子節(jié)點(diǎn)中的索引行中還包含一個書簽（bookmark）攒至。該書簽用來告訴InnoDB存儲引擎去哪里可以找到與索引相對應(yīng)的行數(shù)據(jù)。

由于InnoDB存儲引擎是索引組織表躁劣，因此InnoDB存儲引擎的輔助索引的書簽就是相應(yīng)行數(shù)據(jù)的聚集索引鍵迫吐。如下圖

[圖片上傳失敗...(image-f92fd2-1599385193511)]

輔助索引的存在并不影響數(shù)據(jù)在聚集索引中的組織，因此每張表上可以有多個輔助索引账忘，但只能有一個聚集索引志膀。當(dāng)通過輔助索引來尋找數(shù)據(jù)時(shí)，InnoDB存儲引擎會遍歷輔助索引并通過葉子級別的指針獲得只想主鍵索引的主鍵鳖擒，然后再通過主鍵索引來找到一個完整的行記錄溉浙。

舉例來說，如果在一棵高度為3的輔助索引樹種查找數(shù)據(jù)败去，那需要對這個輔助索引樹遍歷3次找到指定主鍵放航，如果聚集索引樹的高度同樣為3烈拒，那么還需要對聚集索引樹進(jìn)行3次查找圆裕，最終找到一個完整的行數(shù)據(jù)所在的頁，因此一共需要6次邏輯IO訪問才能得到最終的一個數(shù)據(jù)頁荆几。

[圖片上傳失敗...(image-86a274-1599385193511)]

五 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)合普通索引

各個索引的應(yīng)用場景

舉個例子來說诞吱，比如你在為某商場做一個會員卡的系統(tǒng)舟奠。

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

那么這個 會員編號竭缝，作為主鍵，使用 PRIMARY
會員姓名 如果要建索引的話沼瘫，那么就是普通的 INDEX
會員身份證號碼 如果要建索引的話抬纸，那么可以選擇 UNIQUE （唯一的，不允許重復(fù)）

#除此之外還有全文索引耿戚，即FULLTEXT
會員備注信息 湿故， 如果需要建索引的話，可以選擇全文搜索膜蛔。
用于搜索很長一篇文章的時(shí)候坛猪，效果最好。
用在比較短的文本皂股，如果就一兩行字的墅茉，普通的 INDEX 也可以。
但其實(shí)對于全文搜索呜呐，我們并不會使用MySQL自帶的該索引躁锁，而是會選擇第三方軟件如Sphinx，專門來做全文搜索卵史。

#其他的如空間索引SPATIAL战转，了解即可，幾乎不用

三 索引的兩大類型hash與btree

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

#不同的存儲引擎支持的索引類型也不一樣
InnoDB 支持事務(wù)址晕，支持行級別鎖定膀懈，支持 B-tree、Full-text 等索引谨垃，不支持 Hash 索引启搂；
MyISAM 不支持事務(wù)，支持表級別鎖定刘陶，支持 B-tree胳赌、Full-text 等索引，不支持 Hash 索引匙隔；
Memory 不支持事務(wù)疑苫，支持表級別鎖定，支持 B-tree、Hash 等索引捍掺，不支持 Full-text 索引撼短；
NDB 支持事務(wù)，支持行級別鎖定挺勿，支持 Hash 索引阔加，不支持 B-tree、Full-text 等索引满钟；
Archive 不支持事務(wù)胜榔，支持表級別鎖定，不支持 B-tree湃番、Hash夭织、Full-text 等索引；

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

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

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

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

#刪除索引：DROP INDEX 索引名 ON 表名字;

示范

#方式一
create table t1(
    id int,
    name char,
    age int,
    sex enum('male','female'),
    unique key uni_id(id),
    index ix_name(name) #index沒有key
);

#方式二
create index ix_age on t1(age);

#方式三
alter table t1 add index ix_sex(sex);

#查看
mysql> show create table t1;
| t1    | CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `name` char(1) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sex` enum('male','female') DEFAULT NULL,
  UNIQUE KEY `uni_id` (`id`),
  KEY `ix_name` (`name`),
  KEY `ix_age` (`age`),
  KEY `ix_sex` (`sex`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

六 測試索引

一 準(zhǔn)備

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

#2\. 創(chuàng)建存儲過程吠撮，實(shí)現(xiàn)批量插入記錄
delimiter $$ #聲明存儲過程的結(jié)束符號為$$
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 ; #重新聲明分號為結(jié)束符號

#3\. 查看存儲過程
show create procedure auto_insert1\G 

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

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

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

三 在表中已經(jīng)存在大量數(shù)據(jù)的前提下弄屡，為某個字段段建立索引，建立速度會很慢

[圖片上傳失敗...(image-4c65ce-1599385193511)]

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

[圖片上傳失敗...(image-9541c4-1599385193511)]

PS：

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

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

  3. 需要注意，如下圖

[圖片上傳失敗...(image-e35242-1599385193511)]

五 總結(jié)

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

#2\. 在表中已經(jīng)有大量數(shù)據(jù)的情況下壶笼，建索引會很慢，且占用硬盤空間雁刷，建完后查詢速度加快
比如create index idx on s1(id);會掃描表中所有的數(shù)據(jù)覆劈，然后以id為數(shù)據(jù)項(xiàng)，創(chuàng)建索引結(jié)構(gòu)沛励，存放于硬盤的表中责语。
建完以后，再查詢就會很快了侯勉。

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

MySAM索引文件和數(shù)據(jù)文件是分離的，索引文件僅保存數(shù)據(jù)記錄的地址址貌。而在innodb中，表數(shù)據(jù)文件本身就是按照B+Tree（BTree即Balance True）組織的一個索引結(jié)構(gòu)，這棵樹的葉節(jié)點(diǎn)data域保存了完整的數(shù)據(jù)記錄练对。這個索引的key是數(shù)據(jù)表的主鍵遍蟋，因此innodb表數(shù)據(jù)文件本身就是主索引。
因?yàn)閕nndob的數(shù)據(jù)文件要按照主鍵聚集螟凭，所以innodb要求表必須要有主鍵（Myisam可以沒有）虚青，如果沒有顯式定義，則mysql系統(tǒng)會自動選擇一個可以唯一標(biāo)識數(shù)據(jù)記錄的列作為主鍵螺男，如果不存在這種列，則mysql會自動為innodb表生成一個隱含字段作為主鍵奢人，這字段的長度為6個字節(jié)，類型為長整型.

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 人面猴
    序言：七十年代末拷淘，一起剝皮案震驚了整個濱河市，隨后出現(xiàn)的幾起案子扁瓢，更是在濱河造成了極大的恐慌挽铁，老刑警劉巖楣铁，帶你破解...
    沈念sama閱讀 212,332評論 6贊 493
  • 死咒
    序言：濱河連續(xù)發(fā)生了三起死亡事件溃列，死亡現(xiàn)場離奇詭異，居然都是意外死亡，警方通過查閱死者的電腦和手機(jī)，發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 90,508評論 3贊 385
  • 救了他兩次的神仙讓他今天三更去死
    文/潘曉璐 我一進(jìn)店門猪腕，熙熙樓的掌柜王于貴愁眉苦臉地迎上來彻采，“玉大人岭粤，你說我怎么就攤上這事√厮瘢” “怎么了剃浇？”我有些...
    開封第一講書人閱讀 157,812評論 0贊 348
  • 道士緝兇錄：失蹤的賣姜人
    文/不壞的土叔 我叫張陵，是天一觀的道長猎物。 經(jīng)常有香客問我虎囚，道長，這世上最難降的妖魔是什么蔫磨？ 我笑而不...
    開封第一講書人閱讀 56,607評論 1贊 284
  • ?港島之戀（遺憾婚禮）
    正文 為了忘掉前任淘讥，我火速辦了婚禮，結(jié)果婚禮上堤如，老公的妹妹穿的比我還像新娘蒲列。我一直安慰自己窒朋，他們只是感情好，可當(dāng)我...
    茶點(diǎn)故事閱讀 65,728評論 6贊 386
  • 惡毒庶女頂嫁案：這布局不是一般人想出來的
    文/花漫 我一把揭開白布嫉嘀。 她就那樣靜靜地躺著炼邀，像睡著了一般魄揉。 火紅的嫁衣襯著肌膚如雪剪侮。 梳的紋絲不亂的頭發(fā)上，一...
    開封第一講書人閱讀 49,919評論 1贊 290
  • 城市分裂傳說
    那天洛退，我揣著相機(jī)與錄音瓣俯，去河邊找鬼。 笑死兵怯，一個胖子當(dāng)著我的面吹牛彩匕，可吹牛的內(nèi)容都是我干的。 我是一名探鬼主播媒区，決...
    沈念sama閱讀 39,071評論 3贊 410
  • 雙鴛鴦連環(huán)套：你想象不到人心有多黑
    文/蒼蘭香墨 我猛地睜開眼驼仪，長吁一口氣：“原來是場噩夢啊……” “哼！你這毒婦竟也來了袜漩？” 一聲冷哼從身側(cè)響起绪爸，我...
    開封第一講書人閱讀 37,802評論 0贊 268
  • 萬榮殺人案實(shí)錄
    序言：老撾萬榮一對情侶失蹤，失蹤者是張志新（化名）和其女友劉穎宙攻，沒想到半個月后奠货，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體，經(jīng)...
    沈念sama閱讀 44,256評論 1贊 303
  • ?護(hù)林員之死
    正文 獨(dú)居荒郊野嶺守林人離奇死亡座掘，尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 36,576評論 2贊 327
  • ?白月光啟示錄
    正文 我和宋清朗相戀三年递惋，在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片溢陪。...
    茶點(diǎn)故事閱讀 38,712評論 1贊 341
  • 活死人
    序言：一個原本活蹦亂跳的男人離奇死亡萍虽，死狀恐怖，靈堂內(nèi)的尸體忽然破棺而出形真，到底是詐尸還是另有隱情杉编，我是刑警寧澤，帶...
    沈念sama閱讀 34,389評論 4贊 332
  • ?日本核電站爆炸內(nèi)幕
    正文 年R本政府宣布没酣，位于F島的核電站王财，受9級特大地震影響，放射性物質(zhì)發(fā)生泄漏裕便。R本人自食惡果不足惜绒净，卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 40,032評論 3贊 316
  • 男人毒藥：我在死后第九天來索命
    文/蒙蒙 一、第九天 我趴在偏房一處隱蔽的房頂上張望偿衰。 院中可真熱鬧挂疆，春花似錦改览、人聲如沸。這莊子的主人今日做“春日...
    開封第一講書人閱讀 30,798評論 0贊 21
  • 一樁弒父案宝当，背后竟有這般陰謀
    文/蒼蘭香墨 我抬頭看了看天上的太陽。三九已至胆萧，卻和暖如春庆揩，著一層夾襖步出監(jiān)牢的瞬間，已是汗流浹背跌穗。 一陣腳步聲響...
    開封第一講書人閱讀 32,026評論 1贊 266
  • 情欲美人皮
    我被黑心中介騙來泰國打工订晌， 沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留，地道東北人蚌吸。 一個月前我還...
    沈念sama閱讀 46,473評論 2贊 360
  • 代替公主和親
    正文 我出身青樓锈拨，卻偏偏與公主長得像，于是被迫代替她去往敵國和親羹唠。 傳聞我的和親對象是個殘疾皇子奕枢，可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 43,606評論 2贊 350