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

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

B樹 和 B+ 樹

B樹：

B+樹：

區(qū)別：

B樹的關(guān)鍵字和記錄是放在一起的，葉子節(jié)點可以看作外部節(jié)點，不包含任何信息；B+樹的非葉子節(jié)點中只有關(guān)鍵字和指向下一個節(jié)點的索引，記錄只放在葉子節(jié)點中 在 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+樹的緣故

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

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

為什么不用紅黑樹？

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

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

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

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

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

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

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

1.4 全文索引

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

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

InnoDB 存儲引擎在 MySQL 5.6.4 版本中也開始支持全文索引。

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

優(yōu)點

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

缺點

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

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

例如

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

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

2. 前綴索引

有時候需要索引很長的字符列，這會讓索引變得大且慢

比如對于 BLOB、TEXT 和 VARCHAR 類型的列，必須使用前綴索引，只索引開始的部分字符。

前綴長度的選取需要根據(jù)索引選擇性來確定

3. 多列索引

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

在多個列上建立獨立的單列索引大部分情況下并不能提高MySQL的查詢性能，所以引入“索引合并”的策略，一定程度上可以使用表上的多個單列索引來定位指定的行。

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

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

讓選擇性最強的索引列放在前面。

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

5. 聚簇索引

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

InnoDB 通過主鍵聚集數(shù)據(jù)，如果沒有定義主鍵，InnoDB會選擇一個唯一的非空索引來代替，如果沒有這樣的索引，InnoDB會隱式的定義一個主鍵來作為聚簇索引。

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

優(yōu)點：

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

缺點：

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

非聚簇索引

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

6. 覆蓋索引

索引覆蓋所有需要查詢的字段的值

好處：

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

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

舉例：

1.1 id：表的讀取順序

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

id相同：執(zhí)行順序為 從上至下執(zhí)行

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

id不同：執(zhí)行順序為 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：查詢操作類型

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

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

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

1.4 type：訪問類型

type 是查詢的訪問類型。是較為重要的一個指標(biāo)，結(jié)果值從最好到最壞依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all--常見的順序為system > const > eq_ref > ref > range > index > all

一般來說，得保證查詢至少達到 range 級別，最好能達到 ref

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

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

1.6 key：實際使用的索引

實際使用的索引。如果為NULL，則沒有使用索引

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

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

ken_len 越長，說明索引使用的越充分

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

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

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

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

1.10 Extra：額外重要信息

其他的額外重要的信息

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

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