MySQL創建高性能索引的全步驟

一、索引基礎

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高性能索引內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!

推薦閱讀: