小白也能懂的Mysql資料庫索引詳解

程序员世杰發表於2024-07-03
MySql資料庫索引

核心概念

  • 主鍵索引/二級索引
  • 聚簇索引/非聚簇索引
  • 回表/索引覆蓋
  • 索引下推
  • 聯合索引/最左聯合匹配
  • 字首索引
  • explain

一、[索引定義]

1.索引定義

在資料之外，資料庫系統還維護著滿足特定查詢演算法的資料結構，這些資料結構以某種方式引用（指向）資料，這樣就可以在這些資料結構上實現高階查詢演算法。這種資料結構，就是索引。

2.索引的資料結構

  • B樹 / B+樹 (mysql的innodb引擎預設選擇B+樹作為索引的資料結構)
  • HASH表
  • 有序陣列

3.選用B+樹而不選用B樹作為索引

  • B樹的資料結構：record記錄存放在樹的節點中

img

  • B+樹的資料結構: record記錄只存放在樹的葉子節點中

img

  • 假設一條資料大小1KB，索引大小16B，資料庫採用磁碟資料頁儲存，磁碟頁預設大小是16K。同樣三次IO:
    1. B樹能獲取16*16*16=4096條資料
    2. B+樹能夠獲取1000*1000*1000=10億條資料

二、[索引型別]

1.主鍵索引和二級索引

  • 主鍵索引：索引的葉子節點是資料行

  • 二級索引：索引的葉子節點是KEY欄位加主鍵索引，因此，透過二級索引詢首先查到是主鍵值，然後InnoDB再根據查到的主鍵值透過主鍵索引找到相應的資料塊。

  • innodb的主索引檔案上 直接存放該行資料,稱為聚簇索引,次索引指向對主鍵的引用

  • myisam中, 主索引和次索引,都指向物理行(磁碟位置).

image-20240624223031827

2.聚簇索引和非聚簇索引

  • 聚簇索引是對磁碟上實際資料重新組織以按指定的一個或多個列的值排序的演算法。特點是儲存資料的順序和索引順序一致。一般情況下主鍵會預設建立聚簇索引，且一張表只允許存在一個聚簇索引（理由：資料一旦儲存，順序只能有一種）上圖可以看出innodb的一級索引和二級索引均是聚簇索引
  • 相較於聚簇索引的葉子點選是資料記錄，非聚簇索引的葉子節點是指向資料記錄的指標。非聚簇索引與聚簇索引最大的不同就是資料記錄的順序跟索引是不一致的，因此在資料

3.聚簇索引優劣

  • 優勢: 根據主鍵查詢條目比較少時，不用回行(資料就在主鍵節點下)

  • 劣勢: 如果碰到不規則資料插入時，造成頻繁的頁分裂。

三、[索引概念引申]

1.回表

回表的概念涉及到主鍵索引和非主鍵索引的查詢區別

  • 如果語句是 select * from T where ID=500，即主鍵查詢，則只需要搜尋 ID 這棵樹。
  • 如果語句是 select * from T where k=5，即非主鍵索引查詢，則需要先搜尋 k 索引樹，得到 ID 的值為 500，再到 ID 索引樹搜尋一次。
  • 從非主鍵索引回到主鍵索引的過程稱為回表。

基於非主鍵索引的查詢需要多掃描一棵索引樹。因此，我們在應用中應該儘量使用主鍵查詢。而從儲存空間的角度講，因為非主鍵索引樹的葉結點存放的是主鍵的值，那麼，應該考慮讓主鍵的欄位儘量短，這樣非主鍵索引的葉子結點就越小，非主鍵索引佔用的空間也就越小。一般情況下，建議建立一個自增主鍵，這樣非主鍵索引佔用的空間最小。

2.索引覆蓋

  • 如果where子句中的一個條件是非主鍵索引，那麼查詢的時候，先透過非主鍵索引定位到主鍵索引（主鍵位於非主鍵索引搜尋樹的葉子節點）；然後透過主鍵索引定位到查詢的內容。在這個過程中，回到主鍵索引樹的過程，稱為回表。
  • 但是當我們的查詢內容是主鍵值，那麼可以直接提供查詢結果，不需要回表。也就是說，在這個查詢裡，非主鍵索引 已經 “覆蓋了” 我們的查詢需求，故稱為覆蓋索引。
  • 覆蓋索引就是從輔助索引中就能直接得到查詢結果，而不需要回表到一級索引中進行再次查詢，所以可以減少搜尋次數（不需要從輔助索引樹回表到聚簇索引樹），或者說減少 IO 操作（透過輔助索引樹可以一次性從磁碟載入更多節點），從而提升效能。

3.聯合索引

聯合索引是指對錶上的多個列進行索引。

場景一：

聯合索引 (a, b) 是根據 a, b 進行排序（先根據 a 排序，如果 a 相同則根據 b 排序）。因此，下列語句可以直接使用聯合索引得到結果（事實上，也就是用到了最左字首原則）

  • select … from xxx where a=xxx;
  • select … from xxx where a=xxx order by b;

而下列語句則不能使用聯合查詢：

  • select … from xxx where b=xxx;

場景二：

對於聯合索引 (a, b, c)，下列語句同樣可以直接透過聯合索引得到結果：

  • select … from xxx where a=xxx order by b;
  • select … from xxx where a=xxx and b=xxx order by c;

而下列語句則不行，需要執行一次 filesort 排序操作。

  • select … from xxx where a=xxx order by c;

總結：

以聯合索引(a,b,c)為例，建立這樣的索引相當於建立了索引a、ab、abc三個索引。一個索引頂三個索引當然是好事，畢竟每多一個索引，都會增加寫操作的開銷和磁碟空間的開銷。

4.最左匹配原則

  • 從上面聯合索引的例子，可以體會到最左字首原則。
  • 不只是索引的全部定義，只要滿足最左字首，就可以利用索引來加速檢索。這個最左字首可以是聯合索引的最左 N 個欄位，也可以是字串索引的最左 M 個字元。利用索引的 “最左字首” 原則來定位記錄，避免重複定義索引。
  • 因此，基於最左字首原則，我們在定義聯合索引的時候，考慮如何安排索引內的欄位順序就至關重要了！評估的標準就是索引的複用能力，比如，當已經有了 (a,b) 欄位的索引，一般就不需要再單獨在 a 上建立索引了。

5.索引下推

MySQL 5.6 引入了索引下推最佳化，可以在索引遍歷過程中，對索引中包含的欄位先做判斷，過濾掉不符合條件的記錄，減少回表字數。

  • 建表
 CREATE TABLE `test` (
   `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增主鍵',
   `age` int(11) NOT NULL DEFAULT '0',
   `name` varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
   PRIMARY KEY (`id`),
   KEY `idx_name_age` (`name`,`age`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

  • SELECT * from user where name like '陳%' 最左匹配原則，命中idx_name_age索引

  • SELECT * from user where name like '陳%' and age=20

    • 5.6版本之前，先根據name索引(此時是忽略age=20這個條件的)，匹配2條記錄，然後找到對應的2個id。回表之後，在根據age=20進行過濾
    • 5.6版本之後，會加入索引下推，在根據name匹配到2條資料之後，此時不會忽略age=20條件，在回表之前就會根據age進行過濾。此即索引下推，可以減少回表的資料量，增加查詢效能

6.字首索引

當索引是很長的字元序列時，這個索引將會很佔記憶體，而且會很慢，這時候就會用到字首索引了。通常可以索引開始的幾個字元，而不是全部值，以節約空間並得到好的效能。所謂的字首索引就是使用索引的前面幾個字母作為索引，但是要降低索引的重複率，索引我們還必須要判斷字首索引的重複率。

  • 先計算當前字串欄位的唯一性佔比：select 1.0*count(distinct name)/count(*) from test
  • 在計算不同字首的唯一性佔比：
    • select 1.0*count(distinct left(name,1))/count(*) from test 取name字串第一個作為字首索引的佔比
    • select 1.0*count(distinct left(name,2))/count(*) from test 取name字串前兩個作為字首索引的佔比
    • ...
  • 當left(str, n)的n不在顯著增加時，此時可以選取n作為字首索引的擷取數
  • 建立索引alter table test add key(name(n));

四、[檢視索引]

當我們新增索引之後，如何去檢視索引呢？又或者執行語句的時候執行的特別慢，我們如何去排查呢？

explain 通常用於檢視索引是否生效。

當我們獲得慢查詢的日誌之後，檢視日誌，觀察那些語句執行是慢查詢，在該語句之前加上 explain 再次執行，explain 會在查詢上設定一個標誌，當執行查詢時，這個標誌會使其返回關於在執行計劃中每一步的資訊，而不是執行該語句。它會返回一行或多行資訊，顯示出執行該計劃中的每一部分和執行次序.

explain 執行語句返回的重要欄位：

  • type：顯示是搜尋方式（全表掃描或者索引掃描）
  • key：使用的索引欄位，未使用則是 null

explain的 type欄位：

  • ALL: 全表掃描
  • index: 索引全掃描
  • range: 索引範圍掃描
  • ref: 使用非唯一索引掃描
  • eq_ref: 使用唯一索引掃描

相關文章