索引介紹
索引是什么
- 官方介紹索引是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)。更通俗的說或听，數(shù)據(jù)庫索引好比是一本書前面的目錄探孝，能加快數(shù)據(jù)庫的查詢速度。
- 一般來說索引本身也很大誉裆，不可能全部存儲(chǔ)在內(nèi)存中顿颅，因此索引往往是存儲(chǔ)在磁盤上的文件中的（可能存儲(chǔ)在單獨(dú)的索引文件中，也可能和數(shù)據(jù)一起存儲(chǔ)在數(shù)據(jù)文件中）足丢。
- 我們通常所說的索引粱腻，包括聚集索引、覆蓋索引斩跌、組合索引绍些、前綴索引、唯一索引等耀鸦，沒有特別說明柬批，默認(rèn)都是使用 B+樹 結(jié)構(gòu)組織（多路搜索樹，并不一定是二叉的）的索引袖订。
索引的優(yōu)勢和劣勢
優(yōu)勢：
- 可以提高數(shù)據(jù)檢索的效率氮帐，降低數(shù)據(jù)庫的IO成本，類似于書的目錄洛姑。 -- 檢索
- 通過索引列對數(shù)據(jù)進(jìn)行排序上沐，降低數(shù)據(jù)排序的成本，降低了CPU的消耗楞艾。 --排序
- 被索引的列會(huì)自動(dòng)進(jìn)行排序参咙，包括【單列索引】和【組合索引】，只是組合索引的排序要復(fù)雜一些产徊。
- 如果按照索引列的順序進(jìn)行排序昂勒，對應(yīng)order by語句來說，效率就會(huì)提高很多舟铜。
- where索引列在存儲(chǔ)引擎層處理
劣勢：
- 索引會(huì)占據(jù)磁盤空間
- 索引雖然會(huì)提高查詢效率，但是會(huì)降低更新表的效率奠衔。比如每次對表進(jìn)行增刪改操作谆刨，MySQL不僅要保存數(shù)據(jù)塘娶，還有保存或者更新對應(yīng)的索引文件。
索引的分類
單列索引
- 普通索引：MySQL中基本索引類型痊夭，沒有什么限制刁岸，允許在定義索引的列中插入重復(fù)值和空值，純粹為了查詢數(shù)據(jù)更快一點(diǎn)她我。 add index
- 唯一索引：索引列中的值必須是唯一的虹曙，但是允許為空值。add unique index
- 主鍵索引：是一種特殊的唯一索引番舆，不允許有空值酝碳。 pk
組合索引
- 在表中的多個(gè)字段組合上創(chuàng)建的索引 add index(col1,col2...)
- 組合索引的使用，需要遵循最左前綴原則（最左匹配原則恨狈，后面高級篇講解）疏哗。
- 一般情況下，建議使用組合索引代替單列索引（主鍵索引除外禾怠，具體原因后面知識點(diǎn)講解）返奉。
全文索引
- 只有在MyISAM引擎、InnoDB（5.6以后）上才能使用吗氏，而且只能在CHAR芽偏、VARCHAR、TEXT類型字段上使用全文索引弦讽。 參看 MySQL全文索引
- 優(yōu)先級最高哮针，先執(zhí)行，不會(huì)執(zhí)行其他索引
索引的使用
創(chuàng)建索引
- 單列索引之普通索引
CREATE INDEX index_name ON table(column(length)) ;
alter table table_name ADD INDEX index_name (column(length)) ;
- 單列索引之唯一索引
CREATE UNIQUE INDEX index_name ON table(column(length)) ;
alter table table_name add unique index index_name(column);
- 單列索引之全文索引
CREATE FULLTEXT INDEX index_name ON table(column(length)) ;
alter table table_name add fulltext index_name(column)
- 組合索引
ALTER TABLE article ADD INDEX index_titme_time (title(50), time(10)) ;
刪除索引
DROP INDEX index_name ON table;
查看索引
SHOW INDEX FROM table_name;
索引原理分析
索引的存儲(chǔ)結(jié)構(gòu)
索引存儲(chǔ)結(jié)構(gòu)
- 索引是在存儲(chǔ)引擎中實(shí)現(xiàn)的坦袍，也就是說不同的存儲(chǔ)引擎十厢，會(huì)使用不同的索引；
- MyISAM和InnoDB存儲(chǔ)引擎：只支持 B+TREE 索引捂齐，也就是說默認(rèn)使用BTREE蛮放，不能夠更換；
- MEMORY/HEAP存儲(chǔ)引擎：支持HASH和BTREE索引奠宜；
B樹和B+樹
B樹圖示
B樹是為了磁盤或其它存儲(chǔ)設(shè)備而設(shè)計(jì)的一種多叉（下面你會(huì)看到包颁，相對于二叉，B樹每個(gè)內(nèi)結(jié)點(diǎn)有多個(gè)分支压真，即多叉）平衡查找樹娩嚼。多叉平衡
- B樹的高度一般都是在2-4這個(gè)高度，樹的高度直接影響IO讀寫的次數(shù)滴肿。
- 如果是三層樹結(jié)構(gòu)---支撐的數(shù)據(jù)可以達(dá)到20G岳悟，如果是四層樹結(jié)構(gòu)---支撐的數(shù)據(jù)可以達(dá)到幾十T。
B樹和B+樹的區(qū)別
B樹 和 B+樹 的最大區(qū)別在于非葉子節(jié)點(diǎn)是否存儲(chǔ)數(shù)據(jù)的問題。
- B樹是非葉子節(jié)點(diǎn)和葉子節(jié)點(diǎn)都會(huì)存儲(chǔ)數(shù)據(jù)贵少。
- B+樹只有葉子節(jié)點(diǎn)才會(huì)存儲(chǔ)數(shù)據(jù)呵俏，而且存儲(chǔ)的數(shù)據(jù)都是在一行上，而且這些數(shù)據(jù)都是有指針指向的滔灶，也就是有順序的普碎。索引列 order by
非聚集索引（MyISAM）
- B+樹葉子節(jié)點(diǎn)只會(huì)存儲(chǔ)數(shù)據(jù)行（數(shù)據(jù)文件）的指針，簡單來說數(shù)據(jù)和索引不在一起录平，就是非聚集索引麻车。
- 非聚集索引包含主鍵索引和輔助索引都會(huì)存儲(chǔ)指針的值
主鍵索引
這里假設(shè)表一共有三列，假設(shè)我們以 Col1 為主鍵斗这，則上圖是一個(gè) MyISAM 表的主索引(Primary key)示意动猬。可以看出MyISAM 的索引文件僅僅保存數(shù)據(jù)記錄的地址涝影。
輔助索引（次要索引）
在 MyISAM 中枣察，主鍵索引和輔助索引(Secondary key)在結(jié)構(gòu)上沒有任何區(qū)別，只是主鍵索引要求 key 是唯一的燃逻，而輔助索引的 key 可以重復(fù)序目。如果我們在 Col2 上建立一個(gè)輔助索引，則此索引的結(jié)構(gòu)如下圖所示
同樣也是一顆 B+Tree伯襟，data 域保存數(shù)據(jù)記錄的地址猿涨。因此，MyISAM 中索引檢索的算法為首先按照 B+Tree 搜索算法搜索索引姆怪，如果指定的 Key 存在叛赚，則取出其data 域的值，然后以 data 域的值為地址稽揭，讀取相應(yīng)數(shù)據(jù)記錄俺附。
聚集索引（InnoDB）
- 主鍵索引（聚集索引）的葉子節(jié)點(diǎn)會(huì)存儲(chǔ)數(shù)據(jù)行，也就是說數(shù)據(jù)和索引是在一起溪掀，這就是聚集索引事镣。
- 輔助索引只會(huì)存儲(chǔ)主鍵值。
- 如果沒有沒有主鍵揪胃，則使用唯一索引建立聚集索引璃哟；如果沒有唯一索引，MySQL會(huì)按照一定規(guī)則創(chuàng)建聚集索引喊递。
主鍵索引
InnoDB 要求表必須有主鍵(MyISAM 可以沒有)随闪，如果沒有顯式指定，則 MySQL系統(tǒng)會(huì)自動(dòng)選擇一個(gè)可以唯一標(biāo)識數(shù)據(jù)記錄的列作為主鍵骚勘，如果不存在這種列铐伴，則MySQL 自動(dòng)為 InnoDB 表生成一個(gè)隱含字段作為主鍵，類型為長整形。
上圖是 InnoDB 主索引(同時(shí)也是數(shù)據(jù)文件)的示意圖盛杰，可以看到葉節(jié)點(diǎn)包含了完整的數(shù)據(jù)記錄挽荡。這種索引叫做聚集索引藐石。因?yàn)?InnoDB 的數(shù)據(jù)文件本身要按主鍵聚集即供。
輔助索引（次要索引）
第二個(gè)與 MyISAM 索引的不同是 InnoDB 的輔助索引 data 域存儲(chǔ)相應(yīng)記錄主鍵的值而不是地址。換句話說于微，InnoDB 的所有輔助索引都引用主鍵作為 data 域逗嫡。如下圖所示，將 Col3 當(dāng)做輔助索引株依，葉子節(jié)點(diǎn)引用主鍵索引驱证，而不是地址值：
聚集索引這種實(shí)現(xiàn)方式使得按主鍵的搜索十分高效，但是輔助索引搜索需要檢索兩遍索引：① 首先檢索輔助索引獲得主鍵恋腕；② 然后用主鍵到主索引中檢索獲得記錄抹锄。
select * from user where name = 'Alice';
-- 回表查詢，檢索兩次：非主鍵索引 ---> 主鍵索引 ---> 數(shù)據(jù)
引申：為什么不建議使用過長的字段作為主鍵?
因?yàn)樗休o助索引都引用主索引荠藤，過長的主索引會(huì)令輔助索引變得過大伙单。
同時(shí)，請盡量在 InnoDB 上采用自增字段做表的主鍵哈肖。
MyISAM和InnoDB的存儲(chǔ)結(jié)構(gòu)圖示
為了更形象說明這兩種索引的區(qū)別吻育，我們假想一個(gè)表如下圖存儲(chǔ)了 4 行數(shù)據(jù)。 其中 ID 作為主索引淤井，Name 作為輔助索引布疼。 圖示清晰的顯示了聚集索引和非聚集索引的差異：
