【建議收藏】MySQL 三萬字精華總結(jié) —索引（二）
四、索引
?說說你對(duì) MySQL 索引的理解？
數(shù)據(jù)庫索引的原理，為什么要用 B+樹，為什么不用二叉樹？
聚集索引與非聚集索引的區(qū)別？
InnoDB引擎中的索引策略，了解過嗎？
創(chuàng)建索引的方式有哪些？
聚簇索引/非聚簇索引，mysql索引底層實(shí)現(xiàn)，為什么不用B-tree，為什么不用hash，葉子結(jié)點(diǎn)存放的是數(shù)據(jù)還是指向數(shù)據(jù)的內(nèi)存地址，使用索引需要注意的幾個(gè)地方？
MYSQL官方對(duì)索引的定義為：索引（Index）是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)，所以說索引的本質(zhì)是：數(shù)據(jù)結(jié)構(gòu)
索引的目的在于提高查詢效率，可以類比字典、 火車站的車次表、圖書的目錄等 。
可以簡(jiǎn)單的理解為“排好序的快速查找數(shù)據(jù)結(jié)構(gòu)”，數(shù)據(jù)本身之外，數(shù)據(jù)庫還維護(hù)者一個(gè)滿足特定查找算法的數(shù)據(jù)結(jié)構(gòu)，這些數(shù)據(jù)結(jié)構(gòu)以某種方式引用（指向）數(shù)據(jù)，這樣就可以在這些數(shù)據(jù)結(jié)構(gòu)上實(shí)現(xiàn)高級(jí)查找算法。這種數(shù)據(jù)結(jié)構(gòu)，就是索引。下圖是一種可能的索引方式示例。
左邊的數(shù)據(jù)表，一共有兩列七條記錄，最左邊的是數(shù)據(jù)記錄的物理地址。為了加快Col2的查找，可以維護(hù)一個(gè)右邊所示的二叉查找樹，每個(gè)節(jié)點(diǎn)分別包含索引鍵值，和一個(gè)指向?qū)?yīng)數(shù)據(jù)記錄物理地址的指針，這樣就可以運(yùn)用二叉查找在一定的復(fù)雜度內(nèi)獲取到對(duì)應(yīng)的數(shù)據(jù)，從而快速檢索出符合條件的記錄。索引本身也很大，不可能全部存儲(chǔ)在內(nèi)存中，一般以索引文件的形式存儲(chǔ)在磁盤上
平常說的索引，沒有特別指明的話，就是B+樹（多路搜索樹，不一定是二叉樹）結(jié)構(gòu)組織的索引。其中聚集索引，次要索引，覆蓋索引，符合索引，前綴索引，唯一索引默認(rèn)都是使用B+樹索引，統(tǒng)稱索引。此外還有哈希索引等。
基本語法：
創(chuàng)建：
創(chuàng)建索引：
CREATE [UNIQUE] INDEX indexName ON mytable(username(length));如果是CHAR，VARCHAR類型，length可以小于字段實(shí)際長(zhǎng)度；如果是BLOB和TEXT類型，必須指定 length。
修改表結(jié)構(gòu)(添加索引)：
ALTER table tableName ADD [UNIQUE] INDEX indexName(columnName)刪除：
DROP INDEX [indexName] ON mytable;查看：
SHOW INDEX FROM table_name\G?? ? ? ? ? ? --可以通過添加 \G 來格式化輸出信息。使用ALERT命令
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):?該語句添加一個(gè)主鍵，這意味著索引值必須是唯一的，且不能為NULL。ALTER TABLE tbl_name ADD UNIQUE index_name (column_list?這條語句創(chuàng)建索引的值必須是唯一的（除了NULL外，NULL可能會(huì)出現(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 ，用于全文索引。
優(yōu)勢(shì)
提高數(shù)據(jù)檢索效率，降低數(shù)據(jù)庫IO成本
降低數(shù)據(jù)排序的成本，降低CPU的消耗
劣勢(shì)
索引也是一張表，保存了主鍵和索引字段，并指向?qū)嶓w表的記錄，所以也需要占用內(nèi)存 雖然索引大大提高了查詢速度，同時(shí)卻會(huì)降低更新表的速度，如對(duì)表進(jìn)行INSERT、UPDATE和DELETE。因?yàn)楦卤頃r(shí)，MySQL不僅要保存數(shù)據(jù)，還要保存一下索引文件每次更新添加了索引列的字段， 都會(huì)調(diào)整因?yàn)楦滤鶐淼逆I值變化后的索引信息
MySQL索引分類
數(shù)據(jù)結(jié)構(gòu)角度
B+樹索引 Hash索引 Full-Text全文索引 R-Tree索引
從物理存儲(chǔ)角度
聚集索引（clustered index）
非聚集索引（non-clustered index），也叫輔助索引（secondary index）
聚集索引和非聚集索引都是B+樹結(jié)構(gòu)
從邏輯角度
主鍵索引：主鍵索引是一種特殊的唯一索引，不允許有空值 普通索引或者單列索引：每個(gè)索引只包含單個(gè)列，一個(gè)表可以有多個(gè)單列索引 多列索引（復(fù)合索引、聯(lián)合索引）：復(fù)合索引指多個(gè)字段上創(chuàng)建的索引，只有在查詢條件中使用了創(chuàng)建索引時(shí)的第一個(gè)字段，索引才會(huì)被使用。使用復(fù)合索引時(shí)遵循最左前綴集合 唯一索引或者非唯一索引 空間索引：空間索引是對(duì)空間數(shù)據(jù)類型的字段建立的索引，MYSQL中的空間數(shù)據(jù)類型有4種，分別是GEOMETRY、POINT、LINESTRING、POLYGON。MYSQL使用SPATIAL關(guān)鍵字進(jìn)行擴(kuò)展，使得能夠用于創(chuàng)建正規(guī)索引類型的語法創(chuàng)建空間索引。創(chuàng)建空間索引的列，必須將其聲明為NOT NULL，空間索引只能在存儲(chǔ)引擎為MYISAM的表中創(chuàng)建
?為什么MySQL 索引中用B+tree，不用B-tree 或者其他樹，為什么不用 Hash 索引
聚簇索引/非聚簇索引，MySQL 索引底層實(shí)現(xiàn)，葉子結(jié)點(diǎn)存放的是數(shù)據(jù)還是指向數(shù)據(jù)的內(nèi)存地址，使用索引需要注意的幾個(gè)地方？
使用索引查詢一定能提高查詢的性能嗎？為什么?
MySQL索引結(jié)構(gòu)
首先要明白索引（index）是在存儲(chǔ)引擎（storage engine）層面實(shí)現(xiàn)的，而不是server層面。不是所有的存儲(chǔ)引擎都支持所有的索引類型。即使多個(gè)存儲(chǔ)引擎支持某一索引類型，它們的實(shí)現(xiàn)和行為也可能有所差別。
B+Tree索引
MyISAM 和 InnoDB 存儲(chǔ)引擎，都使用 B+Tree的數(shù)據(jù)結(jié)構(gòu)，它相對(duì)與 B-Tree結(jié)構(gòu)，所有的數(shù)據(jù)都存放在葉子節(jié)點(diǎn)上，且把葉子節(jié)點(diǎn)通過指針連接到一起，形成了一條數(shù)據(jù)鏈表，以加快相鄰數(shù)據(jù)的檢索效率。
先了解下 B-Tree 和 B+Tree 的區(qū)別(下節(jié)補(bǔ)充)
MyISAM主鍵索引與輔助索引的結(jié)構(gòu)
MyISAM引擎的索引文件和數(shù)據(jù)文件是分離的。MyISAM引擎索引結(jié)構(gòu)的葉子節(jié)點(diǎn)的數(shù)據(jù)域，存放的并不是實(shí)際的數(shù)據(jù)記錄，而是數(shù)據(jù)記錄的地址。索引文件與數(shù)據(jù)文件分離，這樣的索引稱為"非聚簇索引"。MyISAM的主索引與輔助索引區(qū)別并不大，只是主鍵索引不能有重復(fù)的關(guān)鍵字。
在MyISAM中，索引（含葉子節(jié)點(diǎn)）存放在單獨(dú)的.myi文件中，葉子節(jié)點(diǎn)存放的是數(shù)據(jù)的物理地址偏移量（通過偏移量訪問就是隨機(jī)訪問，速度很快）。
主索引是指主鍵索引，鍵值不可能重復(fù)；輔助索引則是普通索引，鍵值可能重復(fù)。
通過索引查找數(shù)據(jù)的流程：先從索引文件中查找到索引節(jié)點(diǎn)，從中拿到數(shù)據(jù)的文件指針，再到數(shù)據(jù)文件中通過文件指針定位了具體的數(shù)據(jù)。輔助索引類似。
InnoDB主鍵索引與輔助索引的結(jié)構(gòu)
InnoDB引擎索引結(jié)構(gòu)的葉子節(jié)點(diǎn)的數(shù)據(jù)域，存放的就是實(shí)際的數(shù)據(jù)記錄（對(duì)于主索引，此處會(huì)存放表中所有的數(shù)據(jù)記錄；對(duì)于輔助索引此處會(huì)引用主鍵，檢索的時(shí)候通過主鍵到主鍵索引中找到對(duì)應(yīng)數(shù)據(jù)行），或者說，InnoDB的數(shù)據(jù)文件本身就是主鍵索引文件，這樣的索引被稱為“聚簇索引”，一個(gè)表只能有一個(gè)聚簇索引。
主鍵索引：
我們知道InnoDB索引是聚集索引，它的索引和數(shù)據(jù)是存入同一個(gè).idb文件中的，因此它的索引結(jié)構(gòu)是在同一個(gè)樹節(jié)點(diǎn)中同時(shí)存放索引和數(shù)據(jù)，如下圖中最底層的葉子節(jié)點(diǎn)有三行數(shù)據(jù)，對(duì)應(yīng)于數(shù)據(jù)表中的id、stu_id、name數(shù)據(jù)項(xiàng)。
在Innodb中，索引分葉子節(jié)點(diǎn)和非葉子節(jié)點(diǎn)，非葉子節(jié)點(diǎn)就像新華字典的目錄，單獨(dú)存放在索引段中，葉子節(jié)點(diǎn)則是順序排列的，在數(shù)據(jù)段中。Innodb的數(shù)據(jù)文件可以按照表來切分（只需要開啟innodb_file_per_table)，切分后存放在xxx.ibd中，默認(rèn)不切分，存放在xxx.ibdata中。
輔助（非主鍵）索引：
這次我們以示例中學(xué)生表中的name列建立輔助索引，它的索引結(jié)構(gòu)跟主鍵索引的結(jié)構(gòu)有很大差別，在最底層的葉子結(jié)點(diǎn)有兩行數(shù)據(jù)，第一行的字符串是輔助索引，按照ASCII碼進(jìn)行排序，第二行的整數(shù)是主鍵的值。
這就意味著，對(duì)name列進(jìn)行條件搜索，需要兩個(gè)步驟：
① 在輔助索引上檢索name，到達(dá)其葉子節(jié)點(diǎn)獲取對(duì)應(yīng)的主鍵；
② 使用主鍵在主索引上再進(jìn)行對(duì)應(yīng)的檢索操作
這也就是所謂的“回表查詢”
InnoDB 索引結(jié)構(gòu)需要注意的點(diǎn)
數(shù)據(jù)文件本身就是索引文件
表數(shù)據(jù)文件本身就是按 B+Tree 組織的一個(gè)索引結(jié)構(gòu)文件
聚集索引中葉節(jié)點(diǎn)包含了完整的數(shù)據(jù)記錄
InnoDB 表必須要有主鍵，并且推薦使用整型自增主鍵
正如我們上面介紹 InnoDB 存儲(chǔ)結(jié)構(gòu)，索引與數(shù)據(jù)是共同存儲(chǔ)的，不管是主鍵索引還是輔助索引，在查找時(shí)都是通過先查找到索引節(jié)點(diǎn)才能拿到相對(duì)應(yīng)的數(shù)據(jù)，如果我們?cè)谠O(shè)計(jì)表結(jié)構(gòu)時(shí)沒有顯式指定索引列的話，MySQL 會(huì)從表中選擇數(shù)據(jù)不重復(fù)的列建立索引，如果沒有符合的列，則 MySQL 自動(dòng)為 InnoDB 表生成一個(gè)隱含字段作為主鍵，并且這個(gè)字段長(zhǎng)度為6個(gè)字節(jié)，類型為整型。
?那為什么推薦使用整型自增主鍵而不是選擇UUID？
UUID是字符串，比整型消耗更多的存儲(chǔ)空間；
在B+樹中進(jìn)行查找時(shí)需要跟經(jīng)過的節(jié)點(diǎn)值比較大小，整型數(shù)據(jù)的比較運(yùn)算比字符串更快速；
自增的整型索引在磁盤中會(huì)連續(xù)存儲(chǔ)，在讀取一頁數(shù)據(jù)時(shí)也是連續(xù)；UUID是隨機(jī)產(chǎn)生的，讀取的上下兩行數(shù)據(jù)存儲(chǔ)是分散的，不適合執(zhí)行where id > 5 && id < 20的條件查詢語句。
在插入或刪除數(shù)據(jù)時(shí)，整型自增主鍵會(huì)在葉子結(jié)點(diǎn)的末尾建立新的葉子節(jié)點(diǎn)，不會(huì)破壞左側(cè)子樹的結(jié)構(gòu)；UUID主鍵很容易出現(xiàn)這樣的情況，B+樹為了維持自身的特性，有可能會(huì)進(jìn)行結(jié)構(gòu)的重構(gòu)，消耗更多的時(shí)間。
?為什么非主鍵索引結(jié)構(gòu)葉子節(jié)點(diǎn)存儲(chǔ)的是主鍵值？
保證數(shù)據(jù)一致性和節(jié)省存儲(chǔ)空間，可以這么理解：商城系統(tǒng)訂單表會(huì)存儲(chǔ)一個(gè)用戶ID作為關(guān)聯(lián)外鍵，而不推薦存儲(chǔ)完整的用戶信息，因?yàn)楫?dāng)我們用戶表中的信息（真實(shí)名稱、手機(jī)號(hào)、收貨地址···）修改后，不需要再次維護(hù)訂單表的用戶數(shù)據(jù)，同時(shí)也節(jié)省了存儲(chǔ)空間。
Hash索引
主要就是通過Hash算法（常見的Hash算法有直接定址法、平方取中法、折疊法、除數(shù)取余法、隨機(jī)數(shù)法），將數(shù)據(jù)庫字段數(shù)據(jù)轉(zhuǎn)換成定長(zhǎng)的Hash值，與這條數(shù)據(jù)的行指針一并存入Hash表的對(duì)應(yīng)位置；如果發(fā)生Hash碰撞（兩個(gè)不同關(guān)鍵字的Hash值相同），則在對(duì)應(yīng)Hash鍵下以鏈表形式存儲(chǔ)。
檢索算法：在檢索查詢時(shí)，就再次對(duì)待查關(guān)鍵字再次執(zhí)行相同的Hash算法，得到Hash值，到對(duì)應(yīng)Hash表對(duì)應(yīng)位置取出數(shù)據(jù)即可，如果發(fā)生Hash碰撞，則需要在取值時(shí)進(jìn)行篩選。目前使用Hash索引的數(shù)據(jù)庫并不多，主要有Memory等。
MySQL目前有Memory引擎和NDB引擎支持Hash索引。
full-text全文索引
全文索引也是MyISAM的一種特殊索引類型，主要用于全文索引，InnoDB從MYSQL5.6版本提供對(duì)全文索引的支持。
它用于替代效率較低的LIKE模糊匹配操作，而且可以通過多字段組合的全文索引一次性全模糊匹配多個(gè)字段。
同樣使用B-Tree存放索引數(shù)據(jù)，但使用的是特定的算法，將字段數(shù)據(jù)分割后再進(jìn)行索引（一般每4個(gè)字節(jié)一次分割），索引文件存儲(chǔ)的是分割前的索引字符串集合，與分割后的索引信息，對(duì)應(yīng)Btree結(jié)構(gòu)的節(jié)點(diǎn)存儲(chǔ)的是分割后的詞信息以及它在分割前的索引字符串集合中的位置。
R-Tree空間索引
空間索引是MyISAM的一種特殊索引類型，主要用于地理空間數(shù)據(jù)類型
?為什么Mysql索引要用B+樹不是B樹？
用B+樹不用B樹考慮的是IO對(duì)性能的影響，B樹的每個(gè)節(jié)點(diǎn)都存儲(chǔ)數(shù)據(jù)，而B+樹只有葉子節(jié)點(diǎn)才存儲(chǔ)數(shù)據(jù)，所以查找相同數(shù)據(jù)量的情況下，B樹的高度更高，IO更頻繁。數(shù)據(jù)庫索引是存儲(chǔ)在磁盤上的，當(dāng)數(shù)據(jù)量大時(shí)，就不能把整個(gè)索引全部加載到內(nèi)存了，只能逐一加載每一個(gè)磁盤頁（對(duì)應(yīng)索引樹的節(jié)點(diǎn)）。其中在MySQL底層對(duì)B+樹進(jìn)行進(jìn)一步優(yōu)化：在葉子節(jié)點(diǎn)中是雙向鏈表，且在鏈表的頭結(jié)點(diǎn)和尾節(jié)點(diǎn)也是循環(huán)指向的。
?面試官：為何不采用Hash方式？
因?yàn)镠ash索引底層是哈希表，哈希表是一種以key-value存儲(chǔ)數(shù)據(jù)的結(jié)構(gòu)，所以多個(gè)數(shù)據(jù)在存儲(chǔ)關(guān)系上是完全沒有任何順序關(guān)系的，所以，對(duì)于區(qū)間查詢是無法直接通過索引查詢的，就需要全表掃描。所以，哈希索引只適用于等值查詢的場(chǎng)景。而B+ Tree是一種多路平衡查詢樹，所以他的節(jié)點(diǎn)是天然有序的（左子節(jié)點(diǎn)小于父節(jié)點(diǎn)、父節(jié)點(diǎn)小于右子節(jié)點(diǎn)），所以對(duì)于范圍查詢的時(shí)候不需要做全表掃描。
哈希索引不支持多列聯(lián)合索引的最左匹配規(guī)則，如果有大量重復(fù)鍵值得情況下，哈希索引的效率會(huì)很低，因?yàn)榇嬖诠Ｅ鲎矄栴}。
哪些情況需要?jiǎng)?chuàng)建索引
主鍵自動(dòng)建立唯一索引
頻繁作為查詢條件的字段
查詢中與其他表關(guān)聯(lián)的字段，外鍵關(guān)系建立索引
單鍵/組合索引的選擇問題，高并發(fā)下傾向創(chuàng)建組合索引
查詢中排序的字段，排序字段通過索引訪問大幅提高排序速度
查詢中統(tǒng)計(jì)或分組字段
哪些情況不要?jiǎng)?chuàng)建索引
表記錄太少 經(jīng)常增刪改的表 數(shù)據(jù)重復(fù)且分布均勻的表字段，只應(yīng)該為最經(jīng)常查詢和最經(jīng)常排序的數(shù)據(jù)列建立索引（如果某個(gè)數(shù)據(jù)類包含太多的重復(fù)數(shù)據(jù)，建立索引沒有太大意義） 頻繁更新的字段不適合創(chuàng)建索引（會(huì)加重IO負(fù)擔(dān)） where條件里用不到的字段不創(chuàng)建索引
MySQL高效索引
覆蓋索引（Covering Index）,或者叫索引覆蓋， 也就是平時(shí)所說的不需要回表操作
就是select的數(shù)據(jù)列只用從索引中就能夠取得，不必讀取數(shù)據(jù)行，MySQL可以利用索引返回select列表中的字段，而不必根據(jù)索引再次讀取數(shù)據(jù)文件，換句話說查詢列要被所建的索引覆蓋。
索引是高效找到行的一個(gè)方法，但是一般數(shù)據(jù)庫也能使用索引找到一個(gè)列的數(shù)據(jù)，因此它不必讀取整個(gè)行。畢竟索引葉子節(jié)點(diǎn)存儲(chǔ)了它們索引的數(shù)據(jù)，當(dāng)能通過讀取索引就可以得到想要的數(shù)據(jù)，那就不需要讀取行了。一個(gè)索引包含（覆蓋）滿足查詢結(jié)果的數(shù)據(jù)就叫做覆蓋索引。
判斷標(biāo)準(zhǔn)
使用explain，可以通過輸出的extra列來判斷，對(duì)于一個(gè)索引覆蓋查詢，顯示為using index，MySQL查詢優(yōu)化器在執(zhí)行查詢前會(huì)決定是否有索引覆蓋查詢