一、索引基礎

1. 索引的類型

1.1 B-Tree 索引

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

B樹 和 B+ 樹

B樹：

B+樹：

區(qū)別：

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

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

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

為什么不用紅黑樹？

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

1.2 哈希索引

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

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

InnoDB 存儲引擎有一個特殊的功能叫“自適應哈希索引”，當某個索引值被使用的非常頻繁時，會在 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 相關的函數(shù)來維護數(shù)據(jù)。

1.4 全文索引

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

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

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

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

優(yōu)點

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

缺點

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

三、高性能索引策略

1. 獨立的列

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

例如

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

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

2. 前綴索引

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

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

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

3. 多列索引

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

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

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

SELECT username, password FROM t_user WHERE username = 'Aiguodala' AND password = 'Aiguodala';

4. 合適的索引列順序

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

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

5. 聚簇索引

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

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

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

優(yōu)點：

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

缺點：

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

非聚簇索引

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

6. 覆蓋索引

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

好處：

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

三、查詢性能優(yōu)化

1. Explain 性能分析

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

舉例：

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)合查詢、子查詢等的復雜查詢

1.3 table:表的來源

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

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

1.5 possible_key：可能用到的索引

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

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ù)使用一個外部的索引排序，而不是按照表內的索引順序進行讀取。MySQL 中無法利用索引 完成的排序操作稱為“文件排序”
  • 出現(xiàn) Using filesort 說明SQL語句設計的不好，沒有按照創(chuàng)建的索引進行排序，或者未按照索引指定的順序進行排序
• Using temporary
  • 使了用臨時表保存中間結果,MySQL 在對查詢結果排序時使用臨時表。常見于排序 order by 和分組查詢 group by
  • 出現(xiàn) Using temporary 說明SQL語句設計的非常不好，可能是因為沒有按照順序使用復合索引
• Using index
  • Using index 代表表示相應的 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)化

總結

到此這篇關于MySQL創(chuàng)建高性能索引的文章就介紹到這了,更多相關MySQL高性能索引內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論