【MySQL（2）| MySQL索引機制】

程式猿雜貨鋪發表於2019-02-16
MySql索引

什麼是索引？

索引是儲存引擎用於快速找到記錄資料行的一種分散儲存的資料結構。

索引對於良好的效能非常關鍵，尤其是當表中的資料量越來越大時，索引對效能的影響愈發重要。

在資料量較小且負載較低時，不恰當的索引對效能的影響可能還不明顯，但是當資料量逐漸增大時，效能則會急劇下降。

所以 正確的建立合適的索引是提升資料庫查詢效能的基礎。

為什麼要使用索引？

  • 索引可以把隨機IO程式設計順序IO
  • 索引能極大的減少儲存殷勤需要掃描的資料量
  • 索引可以幫助我們在進行分組、排序等操作時，避免使用臨時表

索引有哪些型別？

索引有很多型別，可以為不同的場景提供更好的效能。

MySQL中，索引是在儲存引擎層面實現的，所以，並沒有統一的索引標準，一般來說，不同儲存引擎的工作方式是不一樣的，也不是所有的儲存引擎都支援所有型別的索引

雜湊索引

雜湊索引基於雜湊表實現，只有精確匹配索引所有列的查詢才有效。

對於每一個資料行，儲存引擎都會對所有的索引列根據一定的計算規則計算出一個雜湊碼，然後雜湊索引將所有的雜湊碼儲存在索引中，同時在雜湊表中會儲存一個指向對應資料行的指標。

MySQL中，Memory引擎是顯式支援雜湊索引的，他也是該引擎預設的索引型別，值得注意的一點是：Memory引擎是支援非唯一雜湊索引的，也就是說如果多個列的雜湊值相同，索引會以連結串列的方式存放多個記錄指標到同一個雜湊表中。

雜湊索引的應用場景

根據本人的理解，這種直接通過雜湊索引的儲存引擎，因為索引自身只需要儲存對應的雜湊值，所以索引的結構十分緊湊，這會讓雜湊索引查詢的速度非常快

雜湊索引的一些限制
  • 雜湊索引只包含雜湊值和行指標，而不儲存欄位值，所以不能使用索引中的值來避免讀取行（即不能使用雜湊索引來做覆蓋索引掃描），不過，訪問記憶體中的行的速度很快（因為memory引擎的資料都儲存在記憶體裡），所以大部分情況下這一點對效能的影響並不明顯。
  • 雜湊索引資料並不是按照索引列的值順序儲存的，所以也就無法用於排序
  • 雜湊索引也不支援部分索引列匹配查詢，因為雜湊索引始終是使用索引的全部列值內容來計算雜湊值的。如：資料列（a,b）上建立雜湊索引，如果只查詢資料列a，則無法使用該索引。
  • 雜湊索引只支援等值比較查詢，如：=,in(),<=>(注意，<>和<=>是不同的操作)，不支援任何範圍查詢（必須給定具體的where條件值來計算hash值，所以不支援範圍查詢）。
  • 訪問雜湊索引的資料非常快，除非有很多雜湊衝突，當出現雜湊衝突的時候，儲存引擎必須遍歷連結串列中所有的行指標，逐行進行比較，直到找到所有符合條件的行。
  • 如果雜湊衝突很多的話，一些索引維護操作的代價也很高，如：如果在某個選擇性很低的列上建立雜湊索引（即很多重複值的列），那麼當從表中刪除一行時，儲存引擎需要遍歷對應雜湊值的連結串列中的每一行，找到並刪除對應的引用，衝突越多，代價越大。

B-Tree索引

B-Tree索引使用B-Tree樹資料結構儲存資料，大多數MySQL引擎都支援這種索引（Archive引擎是個例外）

在這裡插入圖片描述

詳細的B-Tree和B+Tree可以參考這篇文章

B樹被作為實現索引的資料結構被創造出來，是因為它能夠完美的利用“區域性性原理”。

什麼是區域性性原理與磁碟預讀？

由於儲存介質的特性，磁碟本身存取就比主存慢很多，再加上機械運動耗費，磁碟的存取速度往往是主存的幾百分分之一，因此為了提高效率，要儘量減少磁碟I/O。為了達到這個目的，磁碟往往不是嚴格按需讀取，而是每次都會預讀，即使只需要一個位元組，磁碟也會從這個位置開始，順序向後讀取一定長度的資料放入記憶體。這樣做的理論依據是電腦科學中著名的區域性性原理：當一個資料被用到時，其附近的資料也通常會馬上被使用。程式執行期間所需要的資料通常比較集中。

由於磁碟順序讀取的效率很高（不需要尋道時間，只需很少的旋轉時間），因此對於具有區域性性的程式來說，預讀可以提高I/O效率。

預讀的長度一般為**頁（page）**的整倍數。頁是計算機管理儲存器的邏輯塊，硬體及作業系統往往將主存和磁碟儲存區分割為連續的大小相等的塊，每個儲存塊稱為一頁（在許多作業系統中，頁得大小通常為4k），主存和磁碟以頁為單位交換資料。當程式要讀取的資料不在主存中時，會觸發一個缺頁異常，此時系統會向磁碟發出讀盤訊號，磁碟會找到資料的起始位置並向後連續讀取一頁或幾頁載入記憶體中，然後異常返回，程式繼續執行。

B樹為何適合做索引？

(1)由於是m分叉的，高度能夠大大降低；

(2)每個節點可以儲存j個記錄，如果將節點大小設定為頁大小，例如4K，能夠充分的利用預讀的特性，極大減少磁碟IO；

注意：高度降低的原因在於：

  • 在利用了區域性性原理前提下，我們把一個節點的大小設為一頁，一頁4K，假設一個KEY有8byte，一個節點可以儲存500個KEY，即j=500

  • m叉樹，大概m/2<= j <=m，即可以差不多是1000叉樹

  • 一層樹：1個節點，1*500個KEY，大小4K

​ 二層樹：1000個節點，1000500=50W個KEY，大小10004K=4M

​ 三層樹：10001000個節點，10001000500=5億個KEY，大小10001000*4K=4G

所以：《高效能Mysql第三版》這本書也說了，一般的B+樹都不會超過三層，也就意味著絕大數資料通過三次IO就可以找到

B樹，它的特點是：

(1)不再是二叉搜尋，而是m叉搜尋；

(2)葉子節點，非葉子節點，都儲存資料；

(3)中序遍歷，可以獲得所有節點；

B+樹的特點是：

B+樹，是在B樹的基礎上，做了一些改進：

  • 非葉子節點不再儲存資料，資料只儲存在同一層的葉子節點上；

  • 葉子之間，增加了連結串列，獲取所有節點，不再需要中序遍歷；

以上改進讓B+樹比B樹有更優的特性：

  • 範圍查詢，定位min與max之後，中間葉子節點，就是結果集，不用中序回溯（範圍查詢在SQL中用得很多，這是B+樹比B樹最大的優勢）；

  • 葉子節點儲存實際記錄行，記錄行相對比較緊密的儲存，適合大資料量磁碟儲存；非葉子節點儲存記錄的PK，用於查詢加速，適合記憶體儲存；

  • 非葉子節點，不儲存實際記錄，而只儲存記錄的KEY的話，那麼在相同記憶體的情況下，B+樹能夠儲存更多索引；

索引體現形式

Myisam引擎

使用Myisam引擎的表在資料庫中會存在三個檔案

以user表為例：

一個是表定義檔案 user.frm

一個是索引儲存檔案 user.MYI

還有一個是資料儲存檔案 user.MYD

因為Myisam引擎的索引和資料是分開儲存的，叫做非聚集索引（UnClustered Index）。並且在B+tree樹的葉子節點儲存的是資料行的地址，在檢索資料時，以此從根節點開始檢索，直到找到對應的關鍵字，然後到資料區獲取資料行地址，最後根據這個資料行地址返回檢索的資料行

Innodb引擎

Innodb和Myisam最大的不同是他是以主鍵為索引來組織資料的儲存，叫做聚簇索引（Clustered Index），也叫作聚集索引。

可能你會說，如果我的表沒有主鍵怎麼辦？你也儘可放心:

  • 如果你沒有主鍵，innodb會選擇一個唯一的非空索引代替;

  • 如果沒有這樣的唯一索引可用，Innodb會自己建立一個隱式的row-id索引用於組織儲存資料，

使用Innodb引擎的表在資料庫中會存在三個檔案

以teacher表為例：

一個是表定義檔案 teacher.frm

一個是資料和索引儲存檔案 teacher.IBD

此處引入一個聚簇索引（也叫聚集索引）：資料庫錶行中資料的物理順序與鍵值得邏輯順序（也就是索引）相同，聚集索引並不是一種單獨的索引型別，而是一種資料儲存技術。

當有聚簇索引時，它的所有資料行實際上存放在索引的葉子頁中，此處應該注意的是，因為無法同時把資料行存放在兩個不同的地方，所以一個表只能有一個聚簇索引。

聚簇索引的優點
  • 可以把相關資料儲存在一起
  • 資料訪問更快。聚簇索引將索引和資料儲存在同一個b-tree中，因此從聚簇索引中查詢資料通常比在非聚簇索引中查詢要快
  • 使用覆蓋索引（後文會有介紹）掃描的查詢可以直接使用頁節點中主鍵值
聚簇索引的缺點
  • 插入速度嚴重依賴於插入順序。按照主鍵的順序插入是載入資料到Innodb表中速度最快的方式。
  • 更新聚簇索引列的代價很高，因為Innodb會強制將每個被更新的行移動到新的位置
  • 聚簇索引可能導致全表掃描變慢，尤其是行比較稀疏，或者由於頁分裂（當行的主鍵值要求必須將這一行插入到某個已滿的頁時，儲存引擎會將該頁分裂成兩個頁面來儲存該行，這就是一頁分裂操作，頁分裂會佔用更多的磁碟空間）導致資料儲存不連續的時候
  • 二級索引訪問需要兩次索引查詢

關於最後一點，是因為，二級索引葉子節點儲存的並不是指向行的物理位置的指標，而是儲存的是主鍵值，這意味著通過二級索引查詢行的時候，儲存引擎首先需要找到二級索引所對應的主鍵值，然後通過主鍵值再去聚簇索引找到對應的行。

小結

MyISAM和InnoDB都使用B+樹來實現索引：

  • MyISAM的索引與資料分開儲存
  • MyISAM的索引葉子儲存指標，主鍵索引與普通索引無太大區別
  • InnoDB的聚集索引和資料行統一儲存
  • InnoDB的聚集索引儲存資料行本身，普通索引儲存主鍵
  • InnoDB一定有且只有一個聚集索引
  • InnoDB建議使用趨勢遞增整數作為PK，而不宜使用較長的列作為PK

其他索引策略

覆蓋索引

如果一個索引包含所有需要查詢的欄位的值，我們就稱之為“覆蓋索引”

換言之，如果查詢列可以通過索引節點中的關鍵字直接返回，則該索引稱之為覆蓋索引

覆蓋索引的優點
  • 索引條目通常遠小於資料行大小，因為只需要讀取索引，自然極大減少了資料訪問量，減少資料庫IO
  • 將隨機IO變成順序IO：因為索引是按照列值順序儲存的。

聯合索引

單列索引可以理解成是一種特殊的聯合索引

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

記得之前看過一個部落格說 建議把 where條件裡邊的列都加上索引，實際上這個建議是非常錯誤的。

在多個列上建立獨立的單列索引大部分情況下並不能提高MySQL的查詢效能

聯合索引有幾個選擇原則：

  • 經常用的列優先【最左匹配原則】
  • 選擇性（離散度）高的列優先【離散度越高 選擇性越好】
  • 寬度小的列優先【最少空間原則】

哪麼如何選擇合適的索引列順序？

選擇合適的索引列順序

正確的順序依賴於使用該索引的查詢，並且同時需要考慮如何更好的滿足排序和分組的需要

在B-Tree索引中，索引列的順序意味著索引首先按照最左列進行排序，其次是第二列，也就是最左匹配原則，所以多列索引的列順序至關重要。

有一個經驗法則：

將選擇性最高的列放到索引最前列

在不需要考慮排序和分組時，這個法則是很好的。因為此時索引的作用只是用於優化where條件的查詢。這種情況下這樣設計的索引能夠最快的過濾出需要的行。但是查詢效能還和查詢條件的具體值以及值得分佈有關

判斷哪個列的選擇性更高

select * from paymen where staff_id = 2 and customer_id = 584;

select sum(staff_id = 2),sum(customer_id = 584) from payment \G

select count(distinct staff_id)/count(*) as staff_id_selectivity,
count(distinct customer_id)/count(*) as customer_id_selectivity,
count(*)
from payment \G

值越大 選擇性更高
複製程式碼

比你優秀的人比你還努力，你有什麼資格不去奮鬥！

選用B+Tree的原因

  • B+樹是B-樹的變種（PLUS版）多路絕對平衡查詢樹，他擁有B-樹的優勢
  • B+樹掃庫、表能力更強
  • B+樹的磁碟讀寫能力更強
  • B+樹的排序能力更強
  • B+樹的查詢效率更加穩定

總結

最後在網上看到一個順口溜，如下：

全值匹配我最愛，最左字首要遵守；

帶頭大哥不能死，中間兄弟不能斷；

索引列上少計算，範圍之後全失效；

LIKE百分寫最右，覆蓋索引不寫星；

不等空值還有or，索引失效要少用。

可謂是很精闢

歡迎關注微信公眾號 程式猿雜貨鋪 ID zhoudl_l

微信公眾號

在這裡插入圖片描述

相關文章