- 索引的使用方法
- 索引的數(shù)據(jù)結(jié)構(gòu)
- 不同存儲引擎中的索引實(shí)現(xiàn)方式
MySQL數(shù)據(jù)庫表的訪問方式又兩種：順序訪問、索引訪問廉侧。
1页响、順序訪問：
直接進(jìn)行全表掃描，根據(jù)條件逐行記錄判斷段誊，指導(dǎo)找到相應(yīng)結(jié)果為止闰蚕。面對當(dāng)前互聯(lián)時代，數(shù)據(jù)量都是成百上千萬的存在连舍，這樣的訪問方式將會及其緩慢没陡，不僅消耗資源還大大影響了效率∷魃停基本不會使用此方式盼玄。
2、索引訪問：
為了解決大數(shù)據(jù)量情況下也能快速訪問潜腻，就必須采用索引的方式埃儿。索引訪問是通過遍歷索引來直接訪問表中記錄行的方式（前提是在該列上有創(chuàng)建索引）。
一融涣、MySQL數(shù)據(jù)庫索引的使用
1桥氏、創(chuàng)建索引的幾種方式：
- 創(chuàng)建表的時候同時創(chuàng)建索引：
CREATE TABLE table_name( # table_name：表名
id INT NOT NULL PRIMARY KEY, # PRIMARY KEY MySQL創(chuàng)建表之后會默認(rèn)創(chuàng)建一個主鍵索引
user_name VARCHAR(16) NOT NULL,
INDEX idx_user_name (user_name(16)) # index_name 索引名稱吹菱，user_name索引列们衙，（16）長度瞬逊，如果字段類型是BLOB或TEXT必須制定索引列長度，如果字段類型是CHAR或者VARCHAR可忽略指定長度
);
- 直接創(chuàng)建索引
CREATE UNIQUE INDEX u_idx_id ON table_name(id); # 創(chuàng)建唯一索引忽你，一般用于主鍵幼东，或者其他不能重復(fù)的常用查詢字段。
CREATE INDEX index_name ON table_name(user_name); # 普通索引 user_name->表字段名
- 修改表結(jié)構(gòu)的方式創(chuàng)建索引
ALTER TABLE table_name ADD UINDEX u_idx_id (id); # 創(chuàng)建唯一索引
ALTER TABLE table_name ADD INDEX idx_user_name (user_name); # 創(chuàng)建普通索引
2科雳、刪除索引
ALTER TABLE table_name DROP INDEX u_idx_id; # 通過ALTER命令刪除索引 table_name：表名根蟹，u_idx_id：索引名
DROP INDEX idx_user_name ON table_name; # 通過DROP命令直接刪除索引 idx_user_name：索引名，table_name：表名
3炸渡、查看索引
SHOW INDEX FROM table_name; #table_name：表名
二娜亿、MySQL數(shù)據(jù)庫索引相關(guān)數(shù)據(jù)結(jié)構(gòu)
二叉樹、紅黑樹蚌堵、B-Tree、B+Tree、Hash
1吼畏、為什么二叉樹和紅黑樹不適合作為索引的數(shù)據(jù)結(jié)構(gòu)督赤？
- 二叉樹不適合的原因：
當(dāng)某表的索引列為依次遞增序列，如此的話泻蚊，二叉樹將會變成一個鏈表形式躲舌，不利于快速查找，因此二叉樹不適合作為索引的數(shù)據(jù)結(jié)構(gòu)性雄。 在查找的過程中會多次進(jìn)行磁盤I/O没卸，將會影響系統(tǒng)性能、查詢速度較慢秒旋。 - 紅黑樹不適合的原因：
紅黑樹是一個平衡二叉樹约计，在插入數(shù)據(jù)的時候，為了保持平衡二叉樹的特性會發(fā)生自旋迁筛，相比二叉樹來效率稍微高一些煤蚌。
不適合作為索引的主要原因是當(dāng)數(shù)據(jù)量上百萬之后，表的記錄越多樹的高度越高细卧，樹的高度不可控尉桩，也會使得磁盤I/O次數(shù)增多，也是相當(dāng)耗費(fèi)資源贪庙，影響系統(tǒng)性能蜘犁。
2、B-Tree作為索引：
- 在紅黑樹上的基礎(chǔ)上進(jìn)行的改造止邮，讓每個節(jié)點(diǎn)可以橫向存儲多個元素这橙，從而使得樹的高度降低，減少的磁盤的I/O次數(shù)农尖，從而提高性能析恋，這也就是B-Tree的數(shù)據(jù)結(jié)構(gòu)。
- B-Tree特點(diǎn)：
1.葉子節(jié)點(diǎn)具有相同的深度盛卡，葉子節(jié)點(diǎn)的指針為空助隧，葉子節(jié)點(diǎn)之間沒有指針連接；
2.所有索引元素不重復(fù)滑沧；
3.節(jié)點(diǎn)中的數(shù)據(jù)索引從左至右依次遞增并村。 -
如圖下結(jié)構(gòu)：
B-Tree結(jié)構(gòu).png
其中，data可能存儲的是當(dāng)前索引列所屬行的其他字段所有數(shù)據(jù)滓技，當(dāng)表字段較多哩牍，則data占用空間就會變大，當(dāng)前大節(jié)點(diǎn)所能存儲的索引總個數(shù)就會變少令漂，從而當(dāng)表數(shù)據(jù)量過大的時候樹的高度也會變得不可控膝昆。（MySQL對大節(jié)點(diǎn)的默認(rèn)大小是16K）
- 查看MySQL文件頁大小的的SQL語句：
SHOW GLOBAL STATUS LIKE 'Innodb_page_size'; # 可修改丸边，建議不要修改
- B-Tree作為索引存在的弊端：
1.如果不是按照索引的最左列開始查找，則無法使用索引荚孵。
2.不能跳過索引中的列
3.如果查詢中有某列的范圍查詢妹窖，則其右邊所有列都無法使用索引優(yōu)化查詢。
3收叶、B+Tree
MySQL數(shù)據(jù)庫索引真正的數(shù)據(jù)結(jié)構(gòu)是采用B+Tree實(shí)現(xiàn)的（B-Tree的變種）骄呼。
- B+Tree特點(diǎn)：
1.非葉子節(jié)點(diǎn)不存儲data，只存儲索引（冗余）判没；
2.葉子節(jié)點(diǎn)包含所有索引字段蜓萄；
3.葉子節(jié)點(diǎn)從左至右依次遞增，葉子節(jié)點(diǎn)采用指針連接澄峰，提高區(qū)間訪問的性能（模糊查詢）嫉沽。 -
結(jié)構(gòu)如下圖（高度為3）：
B+Tree結(jié)構(gòu).png - 計(jì)算MySQL的索引能存儲多多少個元素：
為減少磁盤I/O，大節(jié)點(diǎn)不能存儲更多的索引摊阀。
1).第一層（根節(jié)點(diǎn)）：MySQL表主鍵一般用bigint（占用磁盤空間8B）耻蛇，指針占用6B，此時一個索引元素（默認(rèn)指針和索引是成對出現(xiàn)的）占用空間為14B胞此，大節(jié)點(diǎn)的默認(rèn)大小為16K臣咖。如此，計(jì)算一個大節(jié)點(diǎn)可以存儲的索引元素個數(shù)為：16K / (8B + 6B) ≈ 1170個索引
2).第二層（非葉子節(jié)點(diǎn)）：根據(jù)第一層可知漱牵，第二層應(yīng)該存在1170大節(jié)點(diǎn)夺蛇， 每個節(jié)點(diǎn)都存在1170個索引元素。
3).第三層（葉子節(jié)點(diǎn)）：存在1170 * 1170個大節(jié)點(diǎn)酣胀，假設(shè)data和小節(jié)點(diǎn)總共占用1K刁赦，則一個葉子節(jié)點(diǎn)可以存在16個索引元素。
綜上闻镶，高度為3的B+Tree總共存儲元素個數(shù)應(yīng)該為：1170 * 1170 * 16個索引元素（大概2000萬+）甚脉。 - 優(yōu)點(diǎn)：
1.B+Tree的高度遠(yuǎn)小于B-Tree（原因：B+Tree的非葉子節(jié)點(diǎn)不存儲data，只存儲索引铆农，每個節(jié)點(diǎn)的大小固定牺氨，所以B+Tree的節(jié)點(diǎn)可以存儲更多的索引。）墩剖。
2.磁盤I/O次數(shù)極少猴凹，一般需要2次磁盤I/O（根節(jié)點(diǎn)存在于內(nèi)存）。
3.MySQL底層對B+Tree樹葉子節(jié)點(diǎn)的指針做了優(yōu)化岭皂，改為了雙向指針郊霎。 葉子節(jié)點(diǎn)的指針就是為了便于范圍查詢，提高范圍查詢的效率爷绘，避免多次遍歷B+Tree书劝。
4进倍、Hash表作為索引
Hash索引結(jié)構(gòu)的特殊性，其檢索效率非常高庄撮，索引的檢索可以一次定位背捌，不像B-Tree索引需要從根節(jié)點(diǎn)到枝節(jié)點(diǎn)毙籽，最后才能訪問到頁節(jié)點(diǎn)這樣多次的I/O訪問洞斯，所以Hash索引的查詢效率要遠(yuǎn)高于B-Tree索引。
之所以在使用中不采用Hash索引的主要原因是Hash索引不支持范圍查詢坑赡、不支持?jǐn)?shù)據(jù)排序操作烙如。也有可能發(fā)生Hash碰撞，但發(fā)生碰撞的概率極小毅否。如果對某表的操作操作99%
都是等值查詢則可以考慮使用Hash索引亚铁，否則不建議采用此索引。
三螟加、MySQL數(shù)據(jù)庫存儲引擎索引實(shí)現(xiàn)
1徘溢、MyISAM存儲引擎(非聚簇索引)
- MyISAM將數(shù)據(jù)文件和索引文件分開存儲
- 主鍵索引和非主鍵索引結(jié)構(gòu)相似
- 建表語句
CREATE TABLE table_myisam ( # table_myisam:表名稱
id BIGINT(11) NOT NULL,
user_id VARCHAR(16) NOT NULL,
user_name VARCHAR(16) NOT NULL,
) ENGINE=MYISAM DEFAULT CHARSET=utf8 # EGINE=MYISAM 指定表的存儲引擎
-
該表對應(yīng)文件結(jié)構(gòu)（windows文件目錄在MySQL安裝目錄下的Data目錄下，Linux目錄默認(rèn)在/var/lib/mysql/目錄下）：
MyISAM存儲引擎的表對應(yīng)的文件結(jié)構(gòu).png -
索引結(jié)構(gòu)：
MyISAM索引結(jié)構(gòu).png
SQL查詢過程：
SQL語句：
SELECT * FROM table_myisam WHERE id = 49;
查詢過程：
通過B+Tree索引找到49所在的節(jié)點(diǎn)捆探，將該節(jié)點(diǎn)數(shù)據(jù)寫入內(nèi)存然爆，根據(jù)該節(jié)點(diǎn)data的信息直接去取數(shù)據(jù)文件的記錄，再將結(jié)果寫入內(nèi)存黍图。
2曾雕、InnoDB存儲引擎(聚簇索引)
表數(shù)據(jù)文件本身就是按照B+Tree組織的一個索引結(jié)構(gòu)文件
聚簇索引的葉子節(jié)點(diǎn)包含了完整的數(shù)據(jù)記錄
主鍵索引和輔助索引的主要區(qū)別：
1.主鍵索引：葉子節(jié)點(diǎn)的data存儲該記錄其他所有字段。
2.輔助索引（非主鍵索引）：葉子節(jié)點(diǎn)的data存儲該記錄的主鍵助被。建表語句
CREATE TABLE table_innodb (
id BIGINT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT, # PRIMARY KEY:主鍵剖张，AUTO_INCREMENT:自增
user_id VARCHAR(16) NOT NULL,
user_name VARCHAR(16) NOT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8; # ENGINE=INNODB：設(shè)定存儲引擎為InnoDB
-
對應(yīng)文件結(jié)構(gòu)（文件目錄同上所述MyISAM存儲引擎的文件目錄）：
InnoDB存儲引擎的表對應(yīng)的文件結(jié)構(gòu).png -
索引結(jié)構(gòu)
1.主鍵索引：
InnoDB存儲引擎主鍵索引結(jié)構(gòu).png
2.輔助索引（非主鍵索引）：
InnoDB存儲引擎非主鍵索引結(jié)構(gòu).png
面試題：
- Q : 聚簇（聚集）索引和非聚簇（稀疏、非聚集）索引的概念揩环？
A : 聚簇索引就是將索引和數(shù)據(jù)記錄存放在一起搔弄，非聚簇索引就是索引和數(shù)據(jù)記錄是分開存儲的。因此丰滑，MyISAM存儲引擎的索引就是非聚簇索引顾犹，InnoDB存儲引擎的索引就是聚簇索引。 - Q:為什么InnoDB表必須有主鍵吨枉，并且推薦使用整型的自增主鍵蹦渣？
A:
1.必須有主鍵？ InnoDB的表數(shù)據(jù)就是按照B+Tree形式存儲貌亭，所以必須要有主鍵并且默認(rèn)有主鍵索引柬唯，MySQL的InnoDB存儲引擎底層就是如此設(shè)計(jì)的。（沒有明確指定主鍵的建表語句圃庭，MySQL底層會自動建一列字段(rowid)為主鍵锄奢，數(shù)據(jù)每插入一行rowid都會生成一個唯一的整型值）失晴。
2.整型主鍵？假設(shè)主鍵采用UUID的存儲方式拘央，MySQL會將UUID進(jìn)行編碼計(jì)算（逐個比較每個字符的ASCII碼進(jìn)行比較大型科ā）得到一個值進(jìn)行大小比較建立B+Tree索引數(shù)據(jù)文件。并且UUID占用磁盤空間較大灰伟，所以也不適合作為主鍵拆又。
3.自增？如果是整型自增的話栏账，按照索引結(jié)構(gòu)一般來insert數(shù)據(jù)說都是往后面添加元素帖族，不需要過多的判斷大小，改變數(shù)據(jù)指針指向挡爵。如果不是自增竖般，會改變樹的結(jié)構(gòu)，不容易維護(hù)茶鹃，影響系統(tǒng)性能涣雕。 - Q：為什么非主鍵索引結(jié)構(gòu)葉子節(jié)點(diǎn)存儲的是主鍵值？
A:為保障數(shù)據(jù)一致性（如果一個表既存在主鍵索引也存在非主鍵索引闭翩，在insert之前需要先維護(hù)需要維護(hù)好索引結(jié)構(gòu)挣郭，再講數(shù)據(jù)寫入表中，則意味著insert就可能出現(xiàn)類似分布式事務(wù)的問題男杈，此時需要同時維護(hù)兩個索引的數(shù)據(jù)丈屹，并且要保持一致之后才能完成真正的插入操作，不僅維護(hù)量大伶棒，而且會牽扯性能問題）旺垒，并且可以節(jié)省存儲空間（非主鍵索引的data只需要存儲主鍵值，大大節(jié)省了存儲空間）肤无。
3先蒋、聯(lián)合索引
- 創(chuàng)建聯(lián)合索引：
# table_innodb : 表名， s_index：索引名宛渐， user_id竞漾、user_name：字段名
ALTER TABLE table_innodb ADD INDEX s_index (user_id, user_name, birth_date); # 通過修改表結(jié)構(gòu)的方式創(chuàng)建聯(lián)合索引
CREATE INDEX s_index ON table_innodb (user_id, user_name, birth_date); # 直接創(chuàng)建聯(lián)合索引
-
底層存儲結(jié)構(gòu)：
聯(lián)合索引.png - 解釋：
根據(jù)如上聯(lián)合索引SQL建立所得的聯(lián)合索引樹結(jié)構(gòu)圖大致如上，在實(shí)際比較過程中遵循最左匹配原則窥翩，先比較user_id业岁，從左至右依次遞增；當(dāng)user_id結(jié)果相同是再比較user_name寇蚊，根據(jù)user_name的ASCII碼值從左至右依次遞增笔时；當(dāng)user_name結(jié)果也相同的時候，再比較birth_date仗岸。如此方式建立出樹結(jié)構(gòu)允耿。 - 實(shí)際工作之中建議采用聯(lián)合索引的方式借笙，可以節(jié)省空間。
- 聯(lián)合索引就是將多個字段存儲到同一個節(jié)點(diǎn)之上较锡。
- 聯(lián)合索引排序順序根據(jù)建立索引時候的字段順序從左至右一次比較順序
不妥之處還請指正...
