《MySQL實戰45講》學習筆記4——MySQL中InnoDB的索引

鹿呦呦發表於2020-04-17
MySql筆記索引

索引是在儲存引擎層實現的，且在 MySQL 不同儲存引擎中的實現也不同，本篇文章介紹的是 MySQL 的 InnoDB 的索引。

下文將以這張表為例開展。

# 建立一個主鍵為 id 的表，表中有欄位 k，並且在 k 上有索引。
create table T(
  `id` int(11) AUTO_INCREMENT, 
  `k` int(11) NOT NULL, 
  `name` varchar(16),
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY (`k`),
  KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB;

# 插入多條資料
insert into T values (100, 'Bob'),(200, 'Peter'),(300,'Mary');

一、InnoDB索引模型

在 InnoDB 中，表都是根據主鍵順序以索引的形式存放的，也就是資料放在主鍵索引上，其他索引上儲存的是主鍵 id，這種儲存方式的表稱為索引組織表。

InnoDB 使用了 B+樹 索引模型，所以資料都是儲存在 B+樹 中的。每一個索引在 InnoDB 裡面對應一棵 B+樹。

二、索引的型別

2.1 主鍵約束：主鍵索引和二級索引

主鍵索引的葉子節點存的是整行資料。在 InnoDB 裡，主鍵索引也被稱為聚簇索引。

非主鍵索引的葉子節點內容是主鍵的值。在 InnoDB 裡，非主鍵索引也被稱為二級索引、輔助索引。

2.1.1 主鍵索引和非主鍵索引的區別

主鍵查詢方式：只需要搜尋 ID 這棵 B+樹；
普通索引查詢方式：先搜尋普通索引的 B+樹，得到主鍵索引 ID 的值，再到 ID 索引樹上搜尋，這個過程稱為回表。

2.1.2 主鍵索引的選取規則

從空間的角度出發：主鍵列長度儘可能短，每個二級索引的葉子節點是主鍵，主鍵過長會導致二級索引佔用空間更大。
從效能的角度出發：推薦使用自增索引，非自增主鍵在插入和刪除的操作中，會導致頁分裂和頁合併。

2.1.3 非主鍵索引的優化：覆蓋索引

先看下面這個 sql：

select * from T where k = 100;

這個 sql 語句會在 k 索引樹上找到 k=100 的記錄，取得 ID=15；
再到 ID 索引樹查到 ID=15 對應的記錄，發生了回表，如果將 sql 語句改為

select id from T where k = 100;

因為 ID 的值已經在 k 索引樹上了，因此可以直接提供查詢結果，不需要回表。

如果一個索引包含（或覆蓋）所有需要查詢的欄位的值，稱為覆蓋索引，即只需掃描索引而無須回表。

2.1.4 業務欄位做主鍵的條件

如果不使用自增 ID 做主鍵，用業務欄位直接做主鍵，則需要滿足：只有一個索引，且該索引為唯一索引。

由於沒有其他索引，所以不用考慮其他索引的葉子節點大小的問題，把這個索引設定為主鍵，避免每次查詢需要搜尋兩棵樹。

2.1.5 索引的重建
  • 主鍵索引的重建
# 正確做法
alter table T engine=InnoDB

# 錯誤做法
alter table T drop primary key;
alter table T add primary key(id);

直接刪掉主鍵索引會使得所有的二級索引都失效，並且會用 ROWID 來作主鍵索引。

  • 非主鍵索引的重建
alter table T drop index k;
alter table T add index(k);

索引可能因為刪除，或者頁分裂等原因，導致資料頁有空洞，重建索引的過程會建立一個新的索引，把資料按順序插入，這樣頁面的利用率最高，達到省空間的目的。

2.2 索引欄位數量：聯合索引和單列索引

在上面的建表語句中，可以看到有兩個索引，一個是 k 索引，一個是name_age 索引，不難看出，前者是單列索引，而後者就是聯合索引了。

為什麼會有聯合索引呢？當查詢條件為2個及以上時，比如當經常要用 name 和 age 去查詢資料時：

select * from T where name = 'Job' and age = 28;

建立一個 (name,age) 的聯合索引，相當於建立了 name 和 name、age 這兩個組合的索引，可以加速檢索。

2.2.1 最左字首原則

顧名思義是最左優先，以最左邊的為起點任何連續的索引都能匹配上。

聯合索引的示例圖如下：

索引項是按照索引定義裡的欄位順序來排序的，因此在建立聯合索引時，要根據業務需求，where子句中使用最頻繁的一列放在最左邊。

當已經有了 (a,b) 這個聯合索引後，一般就不需要單獨在 a 上建立索引了。因此，第一原則是，如果通過調整順序，可以少維護一個索引，那麼這個順序往往就是需要優先考慮採用的。

當建立 (a,b,c) 聯合索引時，相當於建立了 (a) 單列索引、(a,b) 聯合索引以及 (a,b,c) 聯合索引。
想要索引生效的話，只能使用 a 和 a,b 和 a,b,c 三種組合；a,c 組合也可以，但實際上只用到了 a 的索引，並沒有用到 c。

2.2.2 索引下推

MySQL 5.6 引入索引下推優化（index condition pushdown)，可以在索引遍歷過程中，對索引中包含的欄位先做判斷，直接過濾掉不滿足條件的記錄，減少回表次數。

比如根據（name,age）聯合索引查詢所有滿足名稱以“張”開頭的索引，然後直接再篩選出年齡小於等於10的索引，之後再回表查詢全行資料。

注意：innodb 引擎的表，索引下推只能用於二級索引。

2.3 唯一約束：唯一索引和普通索引

普通索引允許被索引的資料列包含重複的值，建立唯一索引的目的一般不是為了提高訪問速度，而只是為了避免資料重複。

2.3.1 change buffer機制

當需要更新一個資料頁時，如果資料頁在記憶體中就直接更新，而如果這個資料頁還沒有在記憶體中的話，在不影響資料一致性的前提下，InnoDB 會將這些更新操作快取在 change buffer 中，這樣就不需要從磁碟中讀入這個資料頁了。在下次查詢需要訪問這個資料頁的時候，將資料頁讀入記憶體，然後執行 change buffer 中與這個頁有關的操作。通過這種方式就能保證這個資料邏輯的正確性。

2.3.2 唯一索引和普通索引的選擇

不推薦使用唯一索引，這是因為：

從查詢的角度出發：

  • 如果查詢結果全在記憶體上：唯一索引在資料頁中查詢滿足查詢條件的第一條記錄即可返回；普通索引需要再獲取下一條記錄，由於索引項是有序的且記憶體操作，多一次判斷的時間損耗可忽略不計；
  • 如果查詢結果不在記憶體上：先把資料頁載入到記憶體中，再按照查詢結果全在記憶體的流程處理。

從更新的角度出發：

  • 如果需要更新的記錄全在記憶體上，直接更新記憶體記錄並返回；
  • 如果需要更新的記錄不在記憶體上以及部分在記憶體上：唯一索引需要先將需要更新的記錄從磁碟中載入到記憶體，更新記憶體記錄並寫 redolog；普通索引將更新操作寫入 change buffer，通知執行器更新完成；在下次讀相關記錄的時候，先把原記錄讀取到記憶體，再將 change buffer 上的操作在記憶體記錄上回放，並寫 redolog；
  • 普通索引在更新時，節省了更新時從磁碟讀取記錄的時間，而唯一索引在更新時，若記錄不在記憶體，需要從磁碟讀取記錄到記憶體。

結論：change buffer 只適用於普通索引，而不適用於唯一索引。

後記

關於索引的知識點比較多，但每看一遍專欄都會有新的收穫。fighting！?

相關文章