一杖狼、索引簡介
1披蕉、索引是什么
索引是個什么東東梆暖？
1伞访、MySQL官方對索引的定義為：索引(Index)是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構『洳担可以得到索引的本質(zhì)：索引是數(shù)據(jù)結(jié)構
2厚掷、你可以簡單理解為"排好序的快速查找數(shù)據(jù)結(jié)構"，即索引 = 排序 + 查找
3级解、一般來說索引本身占用內(nèi)存空間也很大冒黑，不可能全部存儲在內(nèi)存中，因此索引往往以文件形式存儲在硬盤上
4勤哗、我們平時所說的索引抡爹，如果沒有特別指明，都是指B樹(多路搜索樹芒划，并不一定是二叉樹)結(jié)構組織的索引冬竟。
5、聚集索引民逼，次要索引泵殴，覆蓋索引，復合索引拼苍，前綴索引笑诅，唯一索引默認都是使用B+樹索引，統(tǒng)稱索引疮鲫。當然苟鸯，除了B+樹這種類型的索引之外，還有哈希索引(hash index)等棚点。
2早处、索引原理
索引的目的在于提高查詢效率，與我們查閱圖書所用的目錄是一個道理：先定位到章瘫析，然后定位到該章下的一個小節(jié)砌梆，然后找到頁數(shù)默责。相似的例子還有：查字典，查火車車次咸包，飛機航班等
本質(zhì)都是：通過不斷地縮小想要獲取數(shù)據(jù)的范圍來篩選出最終想要的結(jié)果桃序，同時把隨機的事件變成順序的事件，也就是說烂瘫，有了這種索引機制媒熊，我們可以總是用同一種查找方式來鎖定數(shù)據(jù)。
1坟比、數(shù)據(jù)庫也是一樣芦鳍，數(shù)據(jù)庫中，在數(shù)據(jù)之外葛账，數(shù)據(jù)庫系統(tǒng)還維護著滿足特定查找算法的數(shù)據(jù)結(jié)構柠衅，這些數(shù)據(jù)結(jié)構以某種方式引用（指向）數(shù)據(jù)，這樣就可以在這些數(shù)據(jù)結(jié)構上實現(xiàn)高級查找算法籍琳。這種數(shù)據(jù)結(jié)構菲宴，就是索引。
2趋急、下圖就是一種可能的索引方式示例：
左邊是數(shù)據(jù)表喝峦，一共有兩列七條記錄，最左邊的十六進制數(shù)字是數(shù)據(jù)記錄的物理地址
為了加快col2的查找呜达，可以維護一個右邊所示的二叉查找樹谣蠢，每個節(jié)點分別包含索引鍵值和一個指向?qū)獢?shù)據(jù)記錄物理地址的指針，這樣就可以運用二叉查找在一定的復雜度內(nèi)獲取到相應數(shù)據(jù)闻丑，從而快速的檢索出符合條件的記錄。
3勋颖、MySQL 索引結(jié)構
索引搜索過程
1嗦嗡、Btree 索引
【初始化介紹】
1、一顆 b 樹饭玲， 淺藍色的塊我們稱之為一個磁盤塊侥祭， 可以看到每個磁盤塊包含幾個數(shù)據(jù)項（深藍色所示） 和指針（黃色所示）
2、如磁盤塊 1 包含數(shù)據(jù)項 17 和 35茄厘， 包含指針 P1矮冬、 P2、 P3
3次哈、P1 表示小于 17 的磁盤塊胎署， P2 表示在 17 和 35 之間的磁盤塊， P3 表示大于 35 的磁盤塊
4窑滞、真實的數(shù)據(jù)存在于葉子節(jié)點和非葉子節(jié)點中琼牧，如17恢筝、35并不真實存在于數(shù)據(jù)表中
【查找過程】
1、如果要查找數(shù)據(jù)項 29巨坊， 那么首先會把磁盤塊 1 由磁盤加載到內(nèi)存撬槽， 此時發(fā)生一次 IO， 在內(nèi)存中用二分查找確定 29在 17 和 35 之間趾撵， 鎖定磁盤塊 1 的 P2 指針侄柔， 內(nèi)存時間因為非常短（相比磁盤的 IO） 可以忽略不計
2、通過磁盤塊 1的 P2 指針的磁盤地址把磁盤塊 3 由磁盤加載到內(nèi)存占调， 發(fā)生第二次 IO暂题， 29 在 26 和 30 之間， 鎖定磁盤塊 3 的 P2 指針
3妈候、通過指針加載磁盤塊 8 到內(nèi)存敢靡， 發(fā)生第三次 IO， 同時內(nèi)存中做二分查找找到 29苦银， 結(jié)束查詢啸胧， 總計三次 IO。
2幔虏、B+tree 索引
【B+Tree 與 BTree 的查找過程】
1陷谱、在 B 樹中， 越靠近根節(jié)點的記錄查找時間越快瑟蜈， 只要找到關鍵字即可確定記錄的存在烟逊； 而 B+ 樹中每個記錄的查找時間基本是一樣的， 都需要從根節(jié)點走到葉子節(jié)點铺根， 而且在葉子節(jié)點中還要再比較關鍵字宪躯。
2、從這個角度看 B 樹的性能好像要比 B+ 樹好位迂， 而在實際應用中卻是 B+ 樹的性能要好些访雪。 因為 B+ 樹的非葉子節(jié)點不存放實際的數(shù)據(jù)，這樣每個節(jié)點可容納的元素個數(shù)比 B 樹多掂林， 樹高比 B 樹小臣缀， 這樣帶來的好處是減少磁盤訪問次數(shù)。
3泻帮、盡管 B+ 樹找到一個記錄所需的比較次數(shù)要比 B 樹多精置， 但是一次磁盤訪問的時間相當于成百上千次內(nèi)存比較的時間， 因此實際中B+ 樹的性能可能還會好些锣杂， 而且 B+樹的葉子節(jié)點使用指針連接在一起氯窍， 方便順序遍歷（范圍搜索）饲常， 這也是很多數(shù)據(jù)庫和文件系統(tǒng)使用 B+樹的緣故。
【性能提升】
真實的情況是狼讨， 3 層的 B+ 樹可以表示上百萬的數(shù)據(jù)贝淤， 如果上百萬的數(shù)據(jù)查找只需要三次 IO， 性能提高將是巨大的政供，如果沒有索引播聪， 每個數(shù)據(jù)項都要發(fā)生一次 IO， 那么總共需要百萬次的 IO布隔， 顯然成本非常非常高离陶。
【思考： 為什么說 B+樹比 B-樹更適合實際應用中操作系統(tǒng)的文件索引和數(shù)據(jù)庫索引？】
1衅檀、B+樹的磁盤讀寫代價更低：B+樹的內(nèi)部結(jié)點并沒有指向關鍵字具體信息的指針招刨。 因此其內(nèi)部結(jié)點相對 B 樹更小。 如果把所有同一內(nèi)部結(jié)點的關鍵字存放在同一盤塊中哀军， 那么盤塊所能容納的關鍵字數(shù)量也越多沉眶。 一次性讀入內(nèi)存中的需要查找的關鍵字也就越多。 相對來說 IO 讀寫次數(shù)也就降低了杉适。
2谎倔、B+樹的查詢效率更加穩(wěn)定：由于非終結(jié)點并不是最終指向文件內(nèi)容的結(jié)點， 而只是葉子結(jié)點中關鍵字的索引猿推。 所以任何關鍵字的查找必須走一條從根結(jié)點到葉子結(jié)點的路片习。 所有關鍵字查詢的路徑長度相同， 導致每一個數(shù)據(jù)的查詢效率相當蹬叭。
索引樹性質(zhì)
1藕咏、索引字段要盡量的小：通過上面的分析，我們知道IO次數(shù)取決于b+數(shù)的高度h秽五，假設當前數(shù)據(jù)表的數(shù)據(jù)為N孽查，每個磁盤塊的數(shù)據(jù)項的數(shù)量是m，則有h=㏒(m+1)N筝蚕，當數(shù)據(jù)量N一定的情況下卦碾，m越大铺坞，h越衅鹂怼；而m = 磁盤塊的大小 / 數(shù)據(jù)項的大小济榨，磁盤塊的大小也就是一個數(shù)據(jù)頁的大小坯沪，是固定的，如果數(shù)據(jù)項占的空間越小擒滑，數(shù)據(jù)項的數(shù)量越多腐晾，樹的高度越低叉弦。這就是為什么每個數(shù)據(jù)項，即索引字段要盡量的小藻糖，比如int占4字節(jié)淹冰，要比bigint8字節(jié)少一半。這也是為什么b+樹要求把真實的數(shù)據(jù)放到葉子節(jié)點而不是內(nèi)層節(jié)點巨柒，一旦放到內(nèi)層節(jié)點樱拴，磁盤塊的數(shù)據(jù)項會大幅度下降，導致樹增高洋满。當數(shù)據(jù)項等于1時將會退化成線性表晶乔。
2、索引的最左匹配特性（即從左往右匹配）：當b+樹的數(shù)據(jù)項是復合的數(shù)據(jù)結(jié)構牺勾，比如(name,age,sex)的時候正罢，b+數(shù)是按照從左到右的順序來建立搜索樹的，比如當(張三,20,F)這樣的數(shù)據(jù)來檢索的時候驻民，b+樹會優(yōu)先比較name來確定下一步的所搜方向翻具，如果name相同再依次比較age和sex，最后得到檢索的數(shù)據(jù)川无；但當(20,F)這樣的沒有name的數(shù)據(jù)來的時候呛占，b+樹就不知道下一步該查哪個節(jié)點，因為建立搜索樹的時候name就是第一個比較因子懦趋，必須要先根據(jù)name來搜索才能知道下一步去哪里查詢晾虑。比如當(張三,F)這樣的數(shù)據(jù)來檢索時，b+樹可以用name來指定搜索方向仅叫，但下一個字段age的缺失帜篇，所以只能把名字等于張三的數(shù)據(jù)都找到，然后再匹配性別是F的數(shù)據(jù)了诫咱， 這個是非常重要的性質(zhì)笙隙，即索引的最左匹配特性。
4坎缭、索引優(yōu)劣勢
優(yōu)點
- 索引大大減小了服務器需要掃描的數(shù)據(jù)量
- 索引可以幫助服務器避免排序和臨時表
- 索引可以將隨機IO變成順序IO
- 索引對于InnoDB（對索引支持行級鎖）非常重要竟痰，因為它可以讓查詢鎖更少的元組。在MySQL5.1和更新的版本中掏呼，InnoDB可以在服務器端過濾掉行后就釋放鎖坏快，但在早期的MySQL版本中，InnoDB直到事務提交時才會解鎖憎夷。對不需要的元組的加鎖莽鸿，會增加鎖的開銷，降低并發(fā)性。 InnoDB僅對需要訪問的元組加鎖祥得，而索引能夠減少InnoDB訪問的元組數(shù)兔沃。但是只有在存儲引擎層過濾掉那些不需要的數(shù)據(jù)才能達到這種目的。一旦索引不允許InnoDB那樣做（即索引達不到過濾的目的）级及，MySQL服務器只能對InnoDB返回的數(shù)據(jù)進行WHERE操作乒疏，此時，已經(jīng)無法避免對那些元組加鎖了饮焦。如果查詢不能使用索引缰雇，MySQL會進行全表掃描，并鎖住每一個元組追驴，不管是否真正需要械哟。
- 關于InnoDB、索引和鎖：InnoDB在二級索引上使用共享鎖（讀鎖）殿雪，但訪問主鍵索引需要排他鎖（寫鎖）
缺點
- 雖然索引大大提高了查詢速度暇咆，同時卻會降低更新表的速度，如對表進行INSERT丙曙、UPDATE和DELETE爸业。因為更新表時，MySQL不僅要保存數(shù)據(jù)亏镰，還要保存索引文件扯旷。
- 建立索引會占用磁盤空間的索引文件。一般情況這個問題不太嚴重索抓，但如果你在一個大表上創(chuàng)建了多種組合索引钧忽，索引文件的會膨脹很快。
- 如果某個數(shù)據(jù)列包含許多重復的內(nèi)容逼肯，為它建立索引就沒有太大的實際效果耸黑。
對于非常小的表，大部分情況下簡單的全表掃描更高效篮幢；
索引只是提高效率的一個因素大刊，如果你的MySQL有大數(shù)據(jù)量的表，就需要花時間研究建立最優(yōu)秀的索引三椿，或優(yōu)化查詢語句缺菌。
因此應該只為最經(jīng)常查詢和最經(jīng)常排序的數(shù)據(jù)列建立索引。
MySQL里同一個數(shù)據(jù)表里的索引總數(shù)限制為16個搜锰。
5伴郁、MySQL 索引分類
參考資料：https://www.cnblogs.com/luyucheng/p/6289714.html
1、普通索引：是最基本的索引纽乱，它沒有任何限制蛾绎，即一個索引只包含單個列昆箕，一個表可以有多個單列索引鸦列；建議一張表索引不要超過5個租冠，優(yōu)先考慮復合索引
2、唯一索引：與前面的普通索引類似薯嗤，不同的就是：索引列的值必須唯一顽爹，但允許有空值。如果是組合索引骆姐，則列值的組合必須唯一
3镜粤、主鍵索引：是一種特殊的唯一索引，一個表只能有一個主鍵玻褪，不允許有空值肉渴。一般是在建表的時候同時創(chuàng)建主鍵索引：
4、復合索引：指多個字段上創(chuàng)建的索引带射，只有在查詢條件中使用了創(chuàng)建索引時的第一個字段同规，索引才會被使用。使用組合索引時遵循最左前綴集合
5窟社、全文索引：主要用來查找文本中的關鍵字券勺，而不是直接與索引中的值相比較。fulltext索引跟其它索引大不相同灿里，它更像是一個搜索引擎关炼，而不是簡單的where語句的參數(shù)匹配
6、MySQL 索引語法
建立索引的 SQL 語句
創(chuàng)建索引：
如果是CHAR和VARCHAR類型匣吊，length可以小于字段實際長度儒拂；
如果是BLOB和TEXT類型，必須指定length色鸳。
CREATE [UNIQUE] INDEX indexName ON mytable(columnname(length));
' or '
ALTER mytable ADD [UNIQUE] INDEX [indexName] ON(columnname(length));
刪除索引
DROP INDEX [indexName] ON mytable;
查看索引（\G表示將查詢到的橫向表格縱向輸出侣灶，方便閱讀）
SHOW INDEX FROM table_name\G
使用 ALTER 命令，有四種方式來添加數(shù)據(jù)表的索引：
- alter table tbl_name add primary key (column_list)：該語句添加一個主鍵缕碎，這意味著索引值必須是唯一的褥影，且不能為NULL。
- alter table tbl_name add unique index_name(column_list)：這條語句創(chuàng)建索引的值必須是唯一的（除了null外咏雌，null可能會出現(xiàn)多次）凡怎。
- alter table tbl_name add index index_name(column_list)：添加普通索引，索引值可出現(xiàn)多次赊抖。
- alter table tbl_name add fulltext index_name(column_list)：該語句指定了索引為FULLTEXT统倒，用于全文索引。
帶你看看 mysql 索引：Index_type 為 BTREE
mysql> show index from tbl_emp;
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tbl_emp | 0 | PRIMARY | 1 | id | A | 8 | NULL | NULL | | BTREE | | |
| tbl_emp | 1 | fk_dept_Id | 1 | deptId | A | 8 | NULL | NULL | YES | BTREE | | |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
7氛雪、何時需要建索引
哪些情況下適合建立索引
- 主鍵自動建立唯一索引
- 頻繁作為查詢的條件的字段應該創(chuàng)建索引
- 查詢中與其他表關聯(lián)的字段房匆，外鍵關系建立索引
- 頻繁更新的字段不適合創(chuàng)建索引
- Where 條件里用不到的字段不創(chuàng)建索引
- 單間/組合索引的選擇問題，Who？（在高并發(fā)下傾向創(chuàng)建組合索引）
- 查詢中排序的字段浴鸿，排序字段若通過索引去訪問將大大提高排序的速度
- 查詢中統(tǒng)計或者分組字段
哪些情況不要創(chuàng)建索引
- 表記錄太少
- 經(jīng)常增刪改的表
- 數(shù)據(jù)重復且分布平均的表字段井氢，因此應該只為經(jīng)常查詢和經(jīng)常排序的數(shù)據(jù)列建立索 引。注意岳链，如果某個數(shù)據(jù)列包含許多重復的內(nèi)容花竞，為它建立索引就沒有太大的實際效果。
案例分析：
1掸哑、假如一個表有10萬行記錄约急，有一個字段A只有T和F兩種值，且每個值的分布概率大約為50%苗分，那么對這種表A字段建索引一般不會提高數(shù)據(jù)庫的查詢速度厌蔽。
2、索引的選擇性是指索引列中不同值的數(shù)目與表中記錄數(shù)的比摔癣。如果一個表中有2000條記錄躺枕，表索引列有1980個不同的值，那么這個索引的選擇性就是1980/2000=0.99供填。
3拐云、一個索引的選擇性越接近于1，這個索引的效率就越高近她。
