一、索引基礎1. 索引的類型1.1 B-Tree 索引

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

B樹 和 B+ 樹

B樹：

B+樹：

區別：

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

為什么說 B+樹比 B-樹更適合實際應用中操作系統的文件索引和數據庫索引？

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

為什么不用紅黑樹？

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

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

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

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

1.3 空間數據索引（R-Tree）

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

必須使用 GIS 相關的函數來維護數據。

1.4 全文索引

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

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

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

二、索引的優缺點

優點

索引大大減少了服務器需要掃描的數據量 通過索引可以幫助服務器避免排序和臨時表，降低CPU消耗 可以將隨機IO變為順序IO，加快IO速度

缺點

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

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

例如

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

MySQL無法解析這個 id + 1 方程式，我們應該養成簡化WHERE條件的習慣

2. 前綴索引

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

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

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

3. 多列索引

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

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

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

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

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

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

5. 聚簇索引

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

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

聚集的數據的優缺點

優點：

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

缺點：

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

非聚簇索引

將數據存儲于索引分開結構，索引結構的葉子節點指向了數據的對應行，myisam通過key_buffer把索引先緩存到內存中，當需要訪問數據時（通過索引訪問數據），在內存中直接搜索索引，然后通過索引找到磁盤相應數據，這也就是為什么索引不在key buffer命中時，速度慢的原因

6. 覆蓋索引

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

好處：

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

使用 EXPLAIN 關鍵字可以模擬優化器執行 SQL 查詢語句，從而知道 MySQL 是如何處理你的 SQL 語句的。分析你的查詢語句或是表結構的性能瓶頸

舉例：

1.1 id：表的讀取順序

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

id相同：執行順序為 從上至下執行

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

id不同：執行順序為 id大的先執行

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代表查詢的類型，主要是用于區別普通查詢、聯合查詢、子查詢等的復雜查詢

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

table表示這個數據是基于哪張表的

1.4 type：訪問類型

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

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

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

1.6 key：實際使用的索引

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

1.7 key_len：索引使用字節數

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

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

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

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

1.9 rows：被查詢的行數

rows 列顯示 MySQL 認為它執行查詢時必須檢查的行數。越少越好！

1.10 Extra：額外重要信息

其他的額外重要的信息

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

到此這篇關于MySQL創建高性能索引的文章就介紹到這了,更多相關MySQL高性能索引內容請搜索好吧啦網以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持好吧啦網！