《跬步千里系列》高效能MySql之索引到底是個啥

sumu蘇沐發表於2019-12-05
MySql索引

首發：www.leroyling.com/archives/my…

生活中，如果你想要快速的在一本書中找到某個你感興趣的內容，一般來講是會先看書的“索引”部分，也就是書的目錄，找到對應的頁碼之後，再翻去看你感興趣的具體內容。

在MySQL中，儲存引擎也用的類似的方法使用索引，即現在索引中找到對應的值，然後根據匹配到的索引的記錄找到對應的資料行。索引可以包含一個或多個列的值，如果索引含有多個列，那麼如何放置列的順序也是相當重要的，因為在MySQL中只能高效的使用索引的最左字首列。所以在MySQL中建立一個包含兩個列的索引，和單獨建立兩個各自只包含一個列的索引效果也會大不一樣。

索引的型別

1.1 B-Tree索引

一般來說，當我們說到索引的時候，如果沒有特別的指明型別，那麼多半來說，預設說的就是B-Tree索引了（不同的儲存引擎中，可能使用了不同的儲存結構，如InnoDB引擎中使用的是B+Tree）。

使用B-Tree索引，意味著所儲存的值是按照順序儲存的。使用索引之所以能加快訪問資料的速度，是因為儲存引擎不在需要對資料進行全表掃描了，而是從索引的根節點開始搜尋，通過比較節點頁的值和實際要查詢的值，從而找到合適的指標進入下層的子節點進行查詢。 以一張簡單的表為例：

CREATE TABLE ORDER(
order_id varchar(32) not null,
product_code varchar(20) not null,
order_time datetime not null，
order_price decimal(10,2) not null default 0,
address varchar(100) not null
key(order_id,product_code,order_time)
)
複製程式碼

對於上面表中的每一條資料，索引中都包含了 order_id, product_code, order_time這三列的值。對於多個列的值進行排序的時候，排序規則是根據create語句中定義索引時使用的列順序來進行的，以上表為例，即排序的順序為order_id, product_code, order_time。

能夠命中索引的查詢方式

  • 全值匹配 指的是查詢語句中where條件的列和索引中的所有列都匹配，比如前面表中的
select * from order where order_id ='o001' and product_code='p001' and order_time='2019-12-05';
複製程式碼
  • 最左字首匹配 指的是查詢條件命中了索引從左到右的部分順序
select * from order where order_id='o001';

select * from order where order_id='o001' and product_code='p001'
複製程式碼
  • 列字首匹配 只匹配到了索引中列的開頭部分
select * from order where order_id like 'o001%';

select * from order where order_id ='o0001' and product_code like 'p001%';
複製程式碼
  • 列範圍值匹配
select * from order where order_id >='o001';

select * from order where order_id = 'o001' and product_code >'p001';

select * from order where order_id = 'o001' and product_code ='p001' and order_time >'2019-12-01';
複製程式碼

  • 只訪問索引的查詢

    即查詢只需要訪問索引而無需訪問資料行，此時這種查詢進化為一種名為“覆蓋索引”查詢。所謂的“覆蓋索引”指的是如果一個索引包含（或者說覆蓋）所有需要查詢返回的欄位的值，那麼這個索引就稱之為“覆蓋索引”。此時查詢結果可以使用索引來直接獲取列的資料，不再需要回表讀取資料行。

1.2 雜湊索引

雜湊索引是基於雜湊表實現的，只能精準匹配索引的所有列的查詢時才會生效。在Mysql中，只有Memory引擎顯式的支援雜湊索引，這也是Memory引擎的預設索引型別，同時Memory引擎也支援B-Tree索引。

因為雜湊索引自身只需要儲存對應的雜湊值，所以索引的結構會十分的緊湊，這也讓雜湊索引的查詢速度變的非常快。但是雜湊索引在使用時也有它的一些限制：

  • 雜湊索引只包含雜湊值和行指標，不儲存欄位值，因此不能使用索引中的值來避免讀取行資料。不過由於訪問內容中的行資料的速度很快，所以大部分的情況下這一點對效能的影響並不明顯。
  • 因為雜湊索引的資料並不是按照索引值的順序儲存的，所以雜湊索引無法用於排序。
  • 因為雜湊索引時所有列的雜湊，因為不支援部分索引列的匹配查詢。比如對於Index(key1,key2)的雜湊索引而言，如果查詢條件中只有key1，那麼查詢的時候將不會使用索引查詢。
  • 雜湊索引只支援等值比較查詢，包括=、in()、<=>，不支援熱呢範圍查詢，比如 where order_time >'2019-12-01'。
  • 訪問雜湊索引的資料速度非常快，除非有很多的雜湊衝突（不同的索引列值卻有著相同的雜湊值）。當出現雜湊衝突的時候，儲存引擎必須要遍歷連結串列中的所有行指標，對資料進行逐行比較，直到找到所有符合條件的資料。
  • 如果雜湊衝突很多的話，一些索引的維護操作代價會變得很高昂。比如刪除表中的一條資料時，儲存引擎需要遍歷對應雜湊表的連結串列中的每一行，找到並刪除對應行的引用，此時，雜湊衝突越多，代價越高昂。

在InnoDB引擎中有個特殊的功能叫“自適應雜湊索引（adaptive hash index）”。說的是當InnoDB注意到某些索引值被應用的非常頻繁的時候，它會在記憶體中基於B-Tree索引之上再建一個雜湊索引，這樣會讓B-Tree索引也具有一部分的雜湊索引的優點，比如快速的雜湊查詢。不過這是一個完全自動的引擎內部的行為，使用者無法控制或配置。

1.3 空間資料索引（R-Tree）

MyISAM表支援空間索引，可以用作地理資料儲存。這類索引無需字首查詢，空間索引會從所有維度來索引資料。查詢時，可以有效的使用任意維度來組合查詢。但是必須要使用MYySQL的GIS相關函式來維護資料。MySQL的GIS支援的並不完善，因為大部分人不會去使用這個特性。

1.4 全文索引

全文索引時一種特殊型別的額索引，它查詢的是文字中的關鍵詞，而不是直接比較索引中的值。全文搜尋和其他幾類索引的匹配方式完全不一樣。全文索引更類似於搜尋引擎做的事情，而不是簡單的where查詢條件匹配。

在相同的列上同時建立全文索引和基於值的B-Tree索引不會有衝突。

索引優化策略

  • 建立索引的列不要參與數學計算或者函式計算。
  • 需要索引很長字元的列時，通過驗證索引的選擇性，對該列進行字首索引。
  • 由於MySQL原生不支援反向索引，當遇到需要進行字尾資料索引查詢時，可以通過將字元反轉後儲存，並基於此建立字首索引。通過觸發器來維護此類索引（自定義索引）。
  • 當有多個列欄位需要索引時，優先考慮聯合索引，而不是將每個列單獨的建立索引
  • 多列索引中需要正確選擇索引列的順序，以便更好地滿足排序和分組的需要。

相關文章