一、索引基礎(chǔ)1. 索引的類(lèi)型1.1 B-Tree 索引

大多數(shù)MySQL存儲(chǔ)引擎默認(rèn)使用的是B+樹(shù)的索引，不同的存儲(chǔ)引擎用不同的方式使用B+樹(shù)索引，MyISAM使用前綴壓縮技術(shù)使得索引更小，但是InnoDB則按照元數(shù)據(jù)格式進(jìn)行存儲(chǔ)；MyISAM索引通過(guò)數(shù)據(jù)的物理位置引用被索引的行，而InnoDB則根據(jù)主鍵引用被索引的行。

B樹(shù) 和 B+ 樹(shù)

B樹(shù)：

B+樹(shù)：

區(qū)別：

B樹(shù)的關(guān)鍵字和記錄是放在一起的，葉子節(jié)點(diǎn)可以看作外部節(jié)點(diǎn)，不包含任何信息；B+樹(shù)的非葉子節(jié)點(diǎn)中只有關(guān)鍵字和指向下一個(gè)節(jié)點(diǎn)的索引，記錄只放在葉子節(jié)點(diǎn)中 在 B樹(shù)中，越靠近根節(jié)點(diǎn)的記錄查找時(shí)間越快，只要找到關(guān)鍵字即可確定記錄的存在；而 B+樹(shù)中每個(gè)記錄 的查找時(shí)間基本是一樣的，都需要從根節(jié)點(diǎn)走到葉子節(jié)點(diǎn)，而且在葉子節(jié)點(diǎn)中還要再比較關(guān)鍵字。從這個(gè)角度看 B樹(shù)的性能好像要比 B+樹(shù)好，而在實(shí)際應(yīng)用中卻是 B+樹(shù)的性能要好些。因?yàn)?B+樹(shù)的非葉子節(jié)點(diǎn)不存放實(shí)際的數(shù)據(jù)， 這樣每個(gè)節(jié)點(diǎn)可容納的元素個(gè)數(shù)比 B樹(shù)多，樹(shù)高比 B樹(shù)小，這樣帶來(lái)的好處是減少磁盤(pán)訪(fǎng)問(wèn)次數(shù)。盡管 B+樹(shù)找到 一個(gè)記錄所需的比較次數(shù)要比 B樹(shù)多，但是一次磁盤(pán)訪(fǎng)問(wèn)的時(shí)間相當(dāng)于成百上千次內(nèi)存比較的時(shí)間，因此實(shí)際中 B+樹(shù)的性能可能還會(huì)好些，而且 B+樹(shù)的葉子節(jié)點(diǎn)使用指針連接在一起，方便順序遍歷（例如查看一個(gè)目錄下的所有 文件，一個(gè)表中的所有記錄等），這也是很多數(shù)據(jù)庫(kù)和文件系統(tǒng)使用 B+樹(shù)的緣故

為什么說(shuō) B+樹(shù)比 B-樹(shù)更適合實(shí)際應(yīng)用中操作系統(tǒng)的文件索引和數(shù)據(jù)庫(kù)索引？

B+樹(shù)的磁盤(pán)讀寫(xiě)代價(jià)更低 B+樹(shù)的內(nèi)部結(jié)點(diǎn)并沒(méi)有指向關(guān)鍵字具體信息的指針。因此其內(nèi)部結(jié)點(diǎn)相對(duì) B 樹(shù)更小。如果把所有同一內(nèi)部結(jié)點(diǎn) 的關(guān)鍵字存放在同一盤(pán)塊中，那么盤(pán)塊所能容納的關(guān)鍵字?jǐn)?shù)量也越多。一次性讀入內(nèi)存中的需要查找的關(guān)鍵字也就越多。相對(duì)來(lái)說(shuō) IO 讀寫(xiě)次數(shù)也就降低了 B+樹(shù)的查詢(xún)效率更加穩(wěn)定 由于非終結(jié)點(diǎn)并不是最終指向文件內(nèi)容的結(jié)點(diǎn)，而只是葉子結(jié)點(diǎn)中關(guān)鍵字的索引。所以任何關(guān)鍵字的查找必須走一條從根結(jié)點(diǎn)到葉子結(jié)點(diǎn)的路。所有關(guān)鍵字查詢(xún)的路徑長(zhǎng)度相同，導(dǎo)致每一個(gè)數(shù)據(jù)的查詢(xún)效率相當(dāng)

為什么不用紅黑樹(shù)？

B+樹(shù)更少的查找次數(shù) 平衡樹(shù)查找操作的時(shí)間復(fù)雜度和樹(shù)高 h 相關(guān)，O(h)=O(logdN)，其中 d 為每個(gè)節(jié)點(diǎn)的出度。紅黑樹(shù)的出度為 2，而 B+樹(shù) 的出度一般都非常大，所以紅黑樹(shù)的樹(shù)高 h 很明顯比 B+樹(shù) 大非常多，查找的次數(shù)也就更多。 B+樹(shù)利用磁盤(pán)預(yù)讀特性 為了減少磁盤(pán) I/O 操作，磁盤(pán)往往不是嚴(yán)格按需讀取，而是每次都會(huì)預(yù)讀。預(yù)讀過(guò)程中，磁盤(pán)進(jìn)行順序讀取，順序讀取不需要進(jìn)行磁盤(pán)尋道，并且只需要很短的磁盤(pán)旋轉(zhuǎn)時(shí)間，速度會(huì)非常快。操作系統(tǒng)一般將內(nèi)存和磁盤(pán)分割成固定大小的塊，每一塊稱(chēng)為一頁(yè)，內(nèi)存與磁盤(pán)以頁(yè)為單位交換數(shù)據(jù)。數(shù)據(jù)庫(kù)系統(tǒng)將索引的一個(gè)節(jié)點(diǎn)的大小設(shè)置為頁(yè)的大小，使得一次 I/O 就能完全載入一個(gè)節(jié)點(diǎn)。并且可以利用預(yù)讀特性，相鄰的節(jié)點(diǎn)也能夠被預(yù)先載入1.2 哈希索引

哈希索引基于哈希表實(shí)現(xiàn)，對(duì)于每一行數(shù)據(jù)，存儲(chǔ)引擎會(huì)對(duì)所有的索引列計(jì)算一個(gè)哈希碼，通過(guò)哈希碼能以 O(1) 時(shí)間進(jìn)行查找，但是無(wú)法用于排序與分組，并且只支持精確查找，無(wú)法用于部分查找和范圍查找。

在MySQL 中，只有Memory引擎顯式支持哈希索引

InnoDB 存儲(chǔ)引擎有一個(gè)特殊的功能叫“自適應(yīng)哈希索引”，當(dāng)某個(gè)索引值被使用的非常頻繁時(shí)，會(huì)在 B+Tree 索引之上再創(chuàng)建一個(gè)哈希索引，這樣就讓 B+Tree 索引具有哈希索引的一些優(yōu)點(diǎn)，比如快速的哈希查找。

1.3 空間數(shù)據(jù)索引（R-Tree）

MyISAM 存儲(chǔ)引擎支持空間數(shù)據(jù)索引（R-Tree），可以用于地理數(shù)據(jù)存儲(chǔ)。空間數(shù)據(jù)索引會(huì)從所有維度來(lái)索引數(shù)據(jù)，可以有效地使用任意維度來(lái)進(jìn)行組合查詢(xún)。

必須使用 GIS 相關(guān)的函數(shù)來(lái)維護(hù)數(shù)據(jù)。

1.4 全文索引

MyISAM 存儲(chǔ)引擎支持全文索引，用于查找文本中的關(guān)鍵詞，而不是直接比較是否相等。

查找條件使用 MATCH AGAINST，而不是普通的 WHERE。全文索引使用倒排索引實(shí)現(xiàn)，它記錄著關(guān)鍵詞到其所在文檔的映射。

InnoDB 存儲(chǔ)引擎在 MySQL 5.6.4 版本中也開(kāi)始支持全文索引。

二、索引的優(yōu)缺點(diǎn)

優(yōu)點(diǎn)

索引大大減少了服務(wù)器需要掃描的數(shù)據(jù)量 通過(guò)索引可以幫助服務(wù)器避免排序和臨時(shí)表，降低CPU消耗 可以將隨機(jī)IO變?yōu)轫樞騃O，加快IO速度

缺點(diǎn)

雖然索引大大提高了查詢(xún)速度，同時(shí)卻會(huì)降低更新表的速度，如對(duì)表進(jìn)行INSERT、UPDATE和DELETE。因?yàn)楦卤頃r(shí)，MySQL不僅要保存數(shù)據(jù)，還要保存一下索引文件每次更新添加了索引列的字段，都會(huì)調(diào)整因?yàn)楦滤鶐?lái)的鍵值變化后的索引信息 實(shí)際上索引也是一張表，該表保存了主鍵與索引字段，并指向?qū)嶓w表的記錄，所以索引列也是要占用空間的 三、高性能索引策略1. 獨(dú)立的列

如果MySQL查詢(xún)的列不是獨(dú)立的，就不會(huì)使用索引，“獨(dú)立的列”指的是，索引列不能是表達(dá)式的一部分，也不能是函數(shù)的參數(shù)

例如

mysql> SELECT id, name FROM t_user WHERE id + 1 = 5;

MySQL無(wú)法解析這個(gè) id + 1 方程式，我們應(yīng)該養(yǎng)成簡(jiǎn)化WHERE條件的習(xí)慣

2. 前綴索引

有時(shí)候需要索引很長(zhǎng)的字符列，這會(huì)讓索引變得大且慢

比如對(duì)于 BLOB、TEXT 和 VARCHAR 類(lèi)型的列，必須使用前綴索引，只索引開(kāi)始的部分字符。

前綴長(zhǎng)度的選取需要根據(jù)索引選擇性來(lái)確定

3. 多列索引

很多人對(duì)于多列索引的理解都不夠，一個(gè)常見(jiàn)的錯(cuò)誤就是，為每個(gè)列創(chuàng)建獨(dú)立的索引，或者按照錯(cuò)誤的順序創(chuàng)建多列索引

在多個(gè)列上建立獨(dú)立的單列索引大部分情況下并不能提高M(jìn)ySQL的查詢(xún)性能，所以引入“索引合并”的策略，一定程度上可以使用表上的多個(gè)單列索引來(lái)定位指定的行。

例如下面的語(yǔ)句中，最好把 username 和 password 設(shè)置為多列索引。

SELECT username, password FROM t_user WHERE username = ’Aiguodala’ AND password = ’Aiguodala’;4. 合適的索引列順序

讓選擇性最強(qiáng)的索引列放在前面。

索引的選擇性是指：不重復(fù)的索引值和記錄總數(shù)的比值。最大值為 1，此時(shí)每個(gè)記錄都有唯一的索引與其對(duì)應(yīng)。選擇性越高，每個(gè)記錄的區(qū)分度越高，查詢(xún)效率也越高。

5. 聚簇索引

聚簇索引并不是一種單獨(dú)的索引類(lèi)型，而是一種數(shù)據(jù)存儲(chǔ)方式，術(shù)語(yǔ)“聚簇”表示數(shù)據(jù)行和相鄰的鍵值緊湊地存儲(chǔ)在一起。

InnoDB 通過(guò)主鍵聚集數(shù)據(jù)，如果沒(méi)有定義主鍵，InnoDB會(huì)選擇一個(gè)唯一的非空索引來(lái)代替，如果沒(méi)有這樣的索引，InnoDB會(huì)隱式的定義一個(gè)主鍵來(lái)作為聚簇索引。

聚集的數(shù)據(jù)的優(yōu)缺點(diǎn)

優(yōu)點(diǎn)：

可以把相關(guān)的數(shù)據(jù)保存在一起 例如實(shí)現(xiàn)電子郵箱時(shí)，根據(jù)用戶(hù)ID來(lái)聚集數(shù)據(jù)，這樣只需要從磁盤(pán)讀取少量的數(shù)據(jù)就可以獲取某個(gè)用戶(hù)的全部郵件，如果沒(méi)有聚簇索引，獲取每封郵件都會(huì)導(dǎo)致一次磁盤(pán)IO 數(shù)據(jù)訪(fǎng)問(wèn)更快，聚簇索引將索引和數(shù)據(jù)保存在同一個(gè)B+樹(shù)中，能更快的查找數(shù)據(jù) 使用覆蓋索引掃描的查詢(xún)可以直接使用頁(yè)節(jié)點(diǎn)中的主鍵值

缺點(diǎn)：

聚簇?cái)?shù)據(jù)最大限度提高了IO密集型應(yīng)用的性能，但是如果數(shù)據(jù)全部放在內(nèi)存中，則訪(fǎng)問(wèn)的順序就不重要，聚簇索引也沒(méi)有優(yōu)勢(shì) 插入速度嚴(yán)重依賴(lài)于插入順序，如果不是按照主鍵的順序加載數(shù)據(jù)，那么加載完成后最好使用OPTIMIZE TABLE命令重新組織一下表，所以建議選擇自增的主鍵 更新聚簇索引列的代價(jià)很高，因?yàn)闀?huì)強(qiáng)制InnoDB將每個(gè)被更新的行移動(dòng)到新的位置。 基于聚簇索引的表在插入新行，或者主鍵被更新導(dǎo)致需要移動(dòng)行的時(shí)候，可能面臨“頁(yè)分裂”的問(wèn)題。當(dāng)行的主鍵值要求必須將這一行插入到某個(gè)已滿(mǎn)的頁(yè)中時(shí)，存儲(chǔ)引擎會(huì)將該頁(yè)分裂成兩個(gè)頁(yè)面來(lái)容納該行，這就是一次分裂操作。頁(yè)分裂會(huì)導(dǎo)致表占用更多的磁盤(pán)空間。 聚簇索引可能導(dǎo)致全表掃描變慢，尤其是行比較稀疏，或者由于頁(yè)分裂導(dǎo)致數(shù)據(jù)存儲(chǔ)不連續(xù)的時(shí)候。

非聚簇索引

將數(shù)據(jù)存儲(chǔ)于索引分開(kāi)結(jié)構(gòu)，索引結(jié)構(gòu)的葉子節(jié)點(diǎn)指向了數(shù)據(jù)的對(duì)應(yīng)行，myisam通過(guò)key_buffer把索引先緩存到內(nèi)存中，當(dāng)需要訪(fǎng)問(wèn)數(shù)據(jù)時(shí)（通過(guò)索引訪(fǎng)問(wèn)數(shù)據(jù)），在內(nèi)存中直接搜索索引，然后通過(guò)索引找到磁盤(pán)相應(yīng)數(shù)據(jù)，這也就是為什么索引不在key buffer命中時(shí)，速度慢的原因

6. 覆蓋索引

索引覆蓋所有需要查詢(xún)的字段的值

好處：

索引條目遠(yuǎn)小于數(shù)據(jù)行大小，所以可以幾大減少數(shù)據(jù)訪(fǎng)問(wèn)量以及更容易全部放到內(nèi)存 索引是按照列值順序存儲(chǔ)，對(duì)于IO密級(jí)型的范圍查詢(xún)會(huì)比隨機(jī)從磁盤(pán)讀取每一行數(shù)據(jù)的IO要少得多 一些存儲(chǔ)引擎（例如 MyISAM）在內(nèi)存中只緩存索引，而數(shù)據(jù)依賴(lài)于操作系統(tǒng)來(lái)緩存。因此，只訪(fǎng)問(wèn)索引可以不使用系統(tǒng)調(diào)用（通常比較費(fèi)時(shí)）。 InnoDB 的二級(jí)索引（非聚簇索引）在葉子結(jié)點(diǎn)保存了行的主鍵值，如果二級(jí)主鍵能夠覆蓋查詢(xún)，則可以避免對(duì)主鍵索引的二次查詢(xún)?nèi)⒉樵?xún)性能優(yōu)化1. Explain 性能分析

使用 EXPLAIN 關(guān)鍵字可以模擬優(yōu)化器執(zhí)行 SQL 查詢(xún)語(yǔ)句，從而知道 MySQL 是如何處理你的 SQL 語(yǔ)句的。分析你的查詢(xún)語(yǔ)句或是表結(jié)構(gòu)的性能瓶頸

舉例：

1.1 id：表的讀取順序

id是select查詢(xún)的序列號(hào)，包含一組數(shù)字，表示查詢(xún)中執(zhí)行select子句或操作表的順序

id相同：執(zhí)行順序?yàn)?從上至下執(zhí)行

EXPLAIN SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.id = t3.id;

id不同：執(zhí)行順序?yàn)?id大的先執(zhí)行

EXPLAIN SELECT t2.id FROM t2 WHERE t2.id = (SELECT t1.id FROM t1 WHERE t1.id = (SELECT t3.id FROM t3));

1.2 select_type：查詢(xún)操作類(lèi)型

select_type代表查詢(xún)的類(lèi)型，主要是用于區(qū)別普通查詢(xún)、聯(lián)合查詢(xún)、子查詢(xún)等的復(fù)雜查詢(xún)

select_type 屬性 含義 SIMPLE 簡(jiǎn)單的 select 查詢(xún),查詢(xún)中不包含子查詢(xún)或者 UNION PRIMARY 查詢(xún)中若包含任何復(fù)雜的子部分，最外層查詢(xún)則被標(biāo)記為 Primary DERIVED 在 FROM 列表中包含的子查詢(xún)被標(biāo)記為 DERIVED(衍生) MySQL 會(huì)遞歸執(zhí)行這些子查詢(xún), 把結(jié)果放在臨時(shí)表里 SUBQUERY 在SELECT或WHERE列表中包含了子查詢(xún)，WHERE 后面是單個(gè)值（=） DEPEDENT SUBQUERY 在SELECT或WHERE列表中包含了子查詢(xún),子查詢(xún)基于外層，WHERE 后面是一組值（IN） UNCACHEABLE SUBQUERY 無(wú)法使用緩存的子查詢(xún) UNION 若第二個(gè)SELECT出現(xiàn)在UNION之后，則被標(biāo)記為UNION； 若UNION包含在FROM子句的子查詢(xún)中,外層SELECT將被標(biāo)記為：DERIVED UNION RESULT 從UNION表獲取結(jié)果的SELECT 1.3 table:表的來(lái)源

table表示這個(gè)數(shù)據(jù)是基于哪張表的

1.4 type：訪(fǎng)問(wèn)類(lèi)型

type 是查詢(xún)的訪(fǎng)問(wèn)類(lèi)型。是較為重要的一個(gè)指標(biāo)，結(jié)果值從最好到最壞依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all--常見(jiàn)的順序?yàn)閟ystem > const > eq_ref > ref > range > index > all

一般來(lái)說(shuō)，得保證查詢(xún)至少達(dá)到 range 級(jí)別，最好能達(dá)到 ref

類(lèi)型名 含義 SYSTEM 表只有一行記錄（等于系統(tǒng)表），這是 const 類(lèi)型的特列，平時(shí)不會(huì)出現(xiàn)，這個(gè)也可以忽略不計(jì) CONST 表示通過(guò)索引一次就找到了,const 用于比較 primary key 或者 unique 索引。因?yàn)橹黄ヅ湟恍袛?shù)據(jù)，所以很快。如將主鍵置于 where 列表中，MySQL 就能將該查詢(xún)轉(zhuǎn)換為一個(gè)常量 EQ_REF 唯一性索引掃描，對(duì)于每個(gè)索引鍵，表中只有一條記錄與之匹配。常見(jiàn)于主鍵或唯一索引掃描 REF 非唯一性索引掃描，返回匹配某個(gè)單獨(dú)值的所有行。本質(zhì)上也是一種索引訪(fǎng)問(wèn)，它返回所有匹配某個(gè)單獨(dú)值的行， 然而，它可能會(huì)找到多個(gè)符合條件的行，所以他應(yīng)該屬于查找和掃描的混合體 RANGE 只檢索給定范圍的行,使用一個(gè)索引來(lái)選擇行。key 列顯示使用了哪個(gè)索引一般就是在你的 where 語(yǔ)句中出現(xiàn) 了 between、<、>、in 等的查詢(xún)這種范圍掃描索引掃描比全表掃描要好，因?yàn)樗恍枰_(kāi)始于索引的某一點(diǎn)，而 結(jié)束語(yǔ)另一點(diǎn)，不用掃描全部索引 INDEX 出現(xiàn)index是sql使用了索引但是沒(méi)用通過(guò)索引進(jìn)行過(guò)濾，一般是使用了覆蓋索引或者是利用索引進(jìn)行了排序分組 ALL Full Table Scan，將遍歷全表以找到匹配的行 1.5 possible_key：可能用到的索引

顯示可能應(yīng)用在這張表中的索引，一個(gè)或多個(gè)。查詢(xún)涉及到的字段上若存在索引，則該索引將被列出，但不一 定被查詢(xún)實(shí)際使用

1.6 key：實(shí)際使用的索引

實(shí)際使用的索引。如果為NULL，則沒(méi)有使用索引

1.7 key_len：索引使用字節(jié)數(shù)

表示索引中使用的字節(jié)數(shù)，可通過(guò)該列計(jì)算查詢(xún)中使用的索引的長(zhǎng)度。 key_len 字段能夠幫你檢查是否充分的利用上了索引

ken_len 越長(zhǎng)，說(shuō)明索引使用的越充分

1.8 ref：顯示被使用的索引的具體信息

ref顯示索引的哪一列被使用了，如果可能的話(huà)，可以是一個(gè)常數(shù)。哪些列或常量被用于查找索引列上的值

1.9 rows：被查詢(xún)的行數(shù)

rows 列顯示 MySQL 認(rèn)為它執(zhí)行查詢(xún)時(shí)必須檢查的行數(shù)。越少越好！

1.10 Extra：額外重要信息

其他的額外重要的信息

Using filesort：使用外部索引排序（未使用用戶(hù)創(chuàng)建的索引） 說(shuō)明 mysql 會(huì)對(duì)數(shù)據(jù)使用一個(gè)外部的索引排序，而不是按照表內(nèi)的索引順序進(jìn)行讀取。MySQL 中無(wú)法利用索引 完成的排序操作稱(chēng)為“文件排序”出現(xiàn) Using filesort 說(shuō)明SQL語(yǔ)句設(shè)計(jì)的不好，沒(méi)有按照創(chuàng)建的索引進(jìn)行排序，或者未按照索引指定的順序進(jìn)行排序 Using temporary 使了用臨時(shí)表保存中間結(jié)果,MySQL 在對(duì)查詢(xún)結(jié)果排序時(shí)使用臨時(shí)表。常見(jiàn)于排序 order by 和分組查詢(xún) group by出現(xiàn) Using temporary 說(shuō)明SQL語(yǔ)句設(shè)計(jì)的非常不好，可能是因?yàn)闆](méi)有按照順序使用復(fù)合索引 Using index Using index 代表表示相應(yīng)的 select 操作中使用了覆蓋索引(Covering Index)，避免訪(fǎng)問(wèn)了表的數(shù)據(jù)行，效率不錯(cuò)！如果同時(shí)出現(xiàn) using where，表明索引被用來(lái)執(zhí)行索引鍵值的查找如果沒(méi)有同時(shí)出現(xiàn) using where，表明索引只是用來(lái)讀取數(shù)據(jù)而非利用索引執(zhí)行查找。 Using where 表明使用了 where 過(guò)濾 Using join buffer 使用了連接緩存 impossible where where 子句的值總是 false，不能用來(lái)獲取任何元組 select tables optimized away 在沒(méi)有 GROUP BY 子句的情況下，基于索引優(yōu)化 MIN/MAX 操作或者對(duì)于 MyISAM 存儲(chǔ)引擎優(yōu)化 COUNT(*)操 作，不必等到執(zhí)行階段再進(jìn)行計(jì)算，查詢(xún)執(zhí)行計(jì)劃生成的階段即完成優(yōu)化總結(jié)

到此這篇關(guān)于MySQL創(chuàng)建高性能索引的文章就介紹到這了,更多相關(guān)MySQL高性能索引內(nèi)容請(qǐng)搜索好吧啦網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持好吧啦網(wǎng)！