-
索引的概念
MySQL 官方對索引的定義為：索引（Index）是幫助 MySQL 高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)普碎『鸱剩可以得到索引的本質(zhì)：索引是數(shù)據(jù)結(jié)構(gòu)÷槌担可以簡單理解為排好序的快速查找數(shù)據(jù)結(jié)構(gòu)缀皱。
在數(shù)據(jù)之外，數(shù)據(jù)庫系統(tǒng)還維護著滿足特定查找算法的數(shù)據(jù)結(jié)構(gòu)动猬，這些數(shù)據(jù)結(jié)構(gòu)以某種方式引用（指向）數(shù)據(jù)啤斗，這樣就可以在這些數(shù)據(jù)結(jié)構(gòu)上實現(xiàn)高級查找算法。這種數(shù)據(jù)結(jié)構(gòu)赁咙，就是索引钮莲。下圖就是一種可能的索引方式示例：
image.png
左邊是數(shù)據(jù)表，一共有兩列七條記錄彼水，最左邊的是數(shù)據(jù)記錄的物理地址崔拥。為了加快 Col2 的查找，可以維護一個右邊所示的二叉查找樹凤覆，每個節(jié)點分別包含索引鍵值和一個指向?qū)?yīng)數(shù)據(jù)記錄物理地址的指 針链瓦，這樣就可以運用二叉查找在一定的復(fù)雜度內(nèi)獲取到相應(yīng)數(shù)據(jù)，從而快速的檢索出符合條件的記錄盯桦。
一般來說索引本身也很大慈俯，不可能全部存儲在內(nèi)存中，因此索引往往以索引文件的形式存儲的磁盤上拥峦。
優(yōu)點：
1 提高數(shù)據(jù)檢索的效率贴膘，降低數(shù)據(jù)庫的IO成本。
2 通過索引列對數(shù)據(jù)進行排序事镣，降低數(shù)據(jù)排序的成本步鉴，降低了CPU的消耗。
劣勢：
1 雖然索引大大提高了查詢速度璃哟，同時卻會降低更新表的速度氛琢，如對表進行INSERT、UPDATE和DELETE随闪。因為 更新表時阳似，MySQL不僅要保存數(shù)據(jù)，還要保存一下索引文件每次更新添加了索引列的字段铐伴，都會調(diào)整因為 更新所帶來的鍵值變化后的索引信息撮奏。
2 實際上索引也是一張表俏讹，該表保存了主鍵與索引字段，并指向?qū)嶓w表的記錄畜吊，所以索引列也是要占用空間
的泽疆。
2.Mysql 的索引
2.1Btree 索引
一顆 b 樹，淺藍(lán)色的塊我們稱之為一個磁盤塊玲献，可以看到每個磁盤塊包含幾個數(shù)據(jù)項（深藍(lán)色所示）和指針（黃色所示）殉疼，如磁盤塊 1 包含數(shù)據(jù)項 17 和 35，包含指針 P1捌年、P2瓢娜、P3， P1 表示小于 17 的磁盤塊礼预，P2 表示在 17 和 35 之間的磁盤塊眠砾，P3 表示大于 35 的磁盤塊。真實的數(shù)據(jù)存在于葉子節(jié)點即 3托酸、5褒颈、9、10获高、13哈肖、15、28念秧、29淤井、36、60摊趾、75币狠、79、90砾层、99漩绵。 非葉子節(jié)點只不存儲真實的數(shù)據(jù)，只存儲指引搜索方向的數(shù)據(jù)項肛炮，如 17止吐、35 并不真實存在于數(shù)據(jù)表中。
查找過程：
如果要查找數(shù)據(jù)項 29侨糟，那么首先會把磁盤塊 1 由磁盤加載到內(nèi)存碍扔，此時發(fā)生一次 IO，在內(nèi)存中用二分查找確定 29 在 17 和 35 之間秕重，鎖定磁盤塊 1 的 P2 指針不同，內(nèi)存時間因為非常短（相比磁盤的 IO）可以忽略不計，通過磁盤塊 1 的 P2 指針的磁盤地址把磁盤塊 3 由磁盤加載到內(nèi)存，發(fā)生第二次 IO二拐，29 在 26 和 30 之間服鹅，鎖定磁盤塊 3 的 P2 指 針，通過指針加載磁盤塊 8 到內(nèi)存百新，發(fā)生第三次 IO企软，同時內(nèi)存中做二分查找找到 29，結(jié)束查詢饭望，總計三次 IO澜倦。
真實的情況是，3 層的 b+樹可以表示上百萬的數(shù)據(jù)杰妓，如果上百萬的數(shù)據(jù)查找只需要三次 IO，性能提高將是巨大的碘勉， 如果沒有索引巷挥，每個數(shù)據(jù)項都要發(fā)生一次 IO，那么總共需要百萬次的 IO验靡，顯然成本非常非常高倍宾。
2.2B+tree 索引
B+Tree 與 B-Tree 的區(qū)別
1）B-樹的關(guān)鍵字和記錄是放在一起的，葉子節(jié)點可以看作外部節(jié)點胜嗓，不包含任何信息高职；B+樹的非葉子節(jié)點中只有關(guān)鍵字和指向下一個節(jié)點的索引，記錄只放在葉子節(jié)點中辞州。
2）在 B-樹中怔锌，越靠近根節(jié)點的記錄查找時間越快，只要找到關(guān)鍵字即可確定記錄的存在变过；而 B+樹中每個記錄 的查找時間基本是一樣的埃元，都需要從根節(jié)點走到葉子節(jié)點，而且在葉子節(jié)點中還要再比較關(guān)鍵字媚狰。從這個角度看 B樹的性能好像要比 B+樹好岛杀，而在實際應(yīng)用中卻是 B+樹的性能要好些。因為 B+樹的非葉子節(jié)點不存放實際的數(shù)據(jù)崭孤， 這樣每個節(jié)點可容納的元素個數(shù)比 B-樹多类嗤，樹高比 B-樹小，這樣帶來的好處是減少磁盤訪問次數(shù)辨宠。盡管 B+樹找到 一個記錄所需的比較次數(shù)要比 B-樹多遗锣，但是一次磁盤訪問的時間相當(dāng)于成百上千次內(nèi)存比較的時間，因此實際中 B+樹的性能可能還會好些彭羹，而且 B+樹的葉子節(jié)點使用指針連接在一起黄伊，方便順序遍歷（例如查看一個目錄下的所有 文件，一個表中的所有記錄等）派殷，這也是很多數(shù)據(jù)庫和文件系統(tǒng)使用 B+樹的緣故还最。
2.3 聚簇索引和非聚簇索引
聚簇索引并不是一種單獨的索引類型墓阀，而是一種數(shù)據(jù)存儲方式。術(shù)語‘聚簇’表示數(shù)據(jù)行和相鄰的鍵值聚簇的存儲在一起拓轻。如下圖斯撮，左側(cè)的索引就是聚簇索引，因為數(shù)據(jù)行在磁盤的排列和索引排序保持一致扶叉。
聚簇索引的好處：
按照聚簇索引排列順序勿锅，查詢顯示一定范圍數(shù)據(jù)的時候，由于數(shù)據(jù)都是緊密相連枣氧，數(shù)據(jù)庫不不用從多 個數(shù)據(jù)塊中提取數(shù)據(jù)溢十，所以節(jié)省了大量的 io 操作。
聚簇索引的限制：
對于 mysql 數(shù)據(jù)庫目前只有 innodb 數(shù)據(jù)引擎支持聚簇索引达吞，而 Myisam 并不支持聚簇索引张弛。 由于數(shù)據(jù)物理存儲排序方式只能有一種，所以每個 Mysql 的表只能有一個聚簇索引酪劫。一般情況下就是該表的主鍵吞鸭。 為了充分利用聚簇索引的聚簇的特性，所以 innodb 表的主鍵列盡量選用有序的順序 id覆糟，而不建議用 無序的 id刻剥，比如 uuid 這種。
非聚簇索引（二級索引）：
除了主鍵以外滩字，用戶創(chuàng)建的其他索引造虏，也是B+tree結(jié)構(gòu)，鍵值就是建立了索引的那個字段麦箍，葉子節(jié)點的值（指針指向的）其實就是主鍵酗电，一般查詢會先根據(jù)二級索引（建立了二級索引的字段）查找對應(yīng)的主鍵，再根據(jù)主鍵查找數(shù)據(jù)表的B+tree内列，找到對應(yīng)的記錄行數(shù)據(jù)撵术。
3.Mysql 索引分類
3.1 單值索引
概念：即一個索引只包含單個列，一個表可以有多個單列索引
創(chuàng)建表：
CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name
VARCHAR(200),
PRIMARYKEY(id),
KEY(customer_name)
);
單獨建單值索引：
CREATE INDEXidx_customer_name ON customer(customer_name);
3.2 唯一索引
概念：索引列的值必須唯一话瞧，但允許有空值
創(chuàng)建表：
CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name
VARCHAR(200),
PRIMARYKEY(id),
KEY(customer_name),
UNIQUE(customer_no)
);
單獨建唯一索引：
CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no)
3.3 主鍵索引
概念：設(shè)定為主鍵后數(shù)據(jù)庫會自動建立索引嫩与，innodb為聚簇索引
創(chuàng)建表：
CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name
VARCHAR(200),
PRIMARYKEY(id)
);
單獨建主鍵索引：
ALTERTABLEcustomeraddPRIMARYKEYcustomer(customer_no);
刪除建主鍵索引：
ALTERTABLEcustomerdropPRIMARYKEY;
3.4 復(fù)合索引
概念：即一個索引包含多個列
創(chuàng)建表：
CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name
VARCHAR(200),
PRIMARYKEY(id),
KEY(customer_name),
UNIQUE(customer_name),
KEY(customer_no,customer_name)
);
單獨建索引：
CREATE INDEX idx_no_name ON customer(customer_no,customer_name);
3.5 基本語法
創(chuàng)建： CREATE [UNIQUE] INDEX[indexName] ON table_name(column))
刪除： DROP INDEX[indexName] ON mytable;
查看： SHOW INDEX FROM table_name\G
- 索引的創(chuàng)建時機
4.1 適合創(chuàng)建索引的情況
主鍵自動建立唯一索引；
頻繁作為查詢條件的字段應(yīng)該創(chuàng)建索引
查詢中與其它表關(guān)聯(lián)的字段交排，外鍵關(guān)系建立索引
單鍵/組合索引的選擇問題划滋， 組合索引性價比更高
查詢中排序的字段，排序字段若通過索引去訪問將大大提高排序速度
查詢中統(tǒng)計或者分組字段
4.2 不適合創(chuàng)建索引的情況
表記錄太少
經(jīng)常增刪改的表或者字段
Where 條件里用不到的字段不創(chuàng)建索引
過濾性不好的不適合建索引
