聊一聊 InnoDB 引擎中的這些索引策略

  • 2020 年 2 月 20 日
  • 筆記

以下文章來源於平頭哥的技術博文，作者互聯網平頭哥

這一篇我們學習 InnoDB 的索引，聊一聊索引策略，更好的利用好索引，提升資料庫的性能，主要聊一聊覆蓋索引、最左前綴原則、索引下推。

覆蓋索引

「覆蓋索引是指在普通索引樹中可以得到查詢的結果，不需要在回到主鍵索引樹中再次搜索」。

建立如下這張表來演示覆蓋索引：

create table T (  ID int primary key,  age int NOT NULL DEFAULT 0,  name varchar(16) NOT NULL DEFAULT '',  index age(age))  engine=InnoDB;

我們執行select * from T where age between 13 and 25 語句，這條語句的執行流程大概為：

1、在 age 索引樹中查找到 age = 13 的記錄，取得 ID 的值

2、根據 id 的值在主鍵索引上查找所需要的所有資訊

3、在 age 索引樹上往下取，重複 1、2 兩步操作，直到 age 不符合條件為止。

如果我們將語句換為 select ID from T where age between 13 and 25，執行這條語句時，在 age 索引樹上就可以查詢到 ID 的值，省去了上面的回表操作，這樣就減少了搜索次數，提升了查詢效率。

這時候的 age 索引樹已經可以滿足我們的查詢需求，age 索引就稱為覆蓋索引。

覆蓋索引是常用的數據查詢優化技術，可以極大的提升資料庫性能，有以下幾個原因：

  • 「減少樹的搜索次數，顯著提升查詢性能」
  • 「索引是按照值的順序存儲，所以對於 I/O 密集型的範圍查詢比隨機從磁碟中讀取每一行的 I/O 要少很多」。
  • 「索引的條目遠小於數據的條目，在索引樹上讀取會極大的減小資料庫的訪問量」。

最左前綴原則

「最左前綴原則是建立在聯合索引之上的，如果我們建立了聯合索引，我們不需要使用索引的全部定義，只要用到了索引中的最左邊的那個欄位就可以使用這個索引，這就是 B-tree 索引支援最左前綴原則。」

建立如下這張表來解釋最左前綴原則：

create table T (  ID int primary key,  age int NOT NULL DEFAULT 0,  name varchar(16) NOT NULL DEFAULT '',  ismale tinyint(1) DEFAULT NULL,  email varchar(64),  address varchar(255),  KEY `name_age` (`name`,`age`))  engine=InnoDB;

我們建立了聯合索引 name_age，現在，假設我們有以下三種查詢情景：

  • 1、查出用戶名的第一個字是「張」開頭的人的年齡。即查詢條件子句為"where name like '張%'"
  • 2、查處用戶名中含有「張」字的人的年齡。即查詢條件子句為"where name like '%張%'"
  • 3、查出用戶名以「張」字結尾的人的年齡。即查詢條件子句為"where name like '%張'"

在這三種情況中，第一種情況可以利用到 name_age 這個聯合索引，加速查詢，可以看出，我們並沒有 使用索引的全部定義，「只要滿足最左前綴，就可以利用索引來加速檢索。這個最左前綴可以是聯合索引的最左 N 個欄位，也可以是字元串索引的最左 M 個字元。」

如果我們將索引的順序調整為KEYname_age(age,name) ，那麼上面三種情況都使用不到這個聯合索引。

「維護索引需要代價，所以有時候我們可以利用「最左前綴」原則減少索引數量」。

索引下推

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

建立如下這張表來解釋索引下推：

create table T (  ID int primary key,  age int NOT NULL DEFAULT 0,  name varchar(16) NOT NULL DEFAULT '',  ismale tinyint(1) DEFAULT NULL,  email varchar(64),  address varchar(255),  KEY `name_age` (`name`,`age`))  engine=InnoDB;

在表中建立了 name、age 的聯合索引，我們執行 select * from T where name like '張%' and age=10 and ismale=1;語句，「我們已經知道了B-tree 索引的最左前綴原則，所以將會用到 name_age 索引，因為索引下推優化，會在 name_age 索引樹上判斷 name 和 age 是否滿足」。

根據我們上面的執行語句，會在 name_age 索引樹上查找 name 以 '張' 開頭的並且 age = 10 的數據，然後在回到主鍵索引樹中查詢所需要的資訊，並不是所有 name_age 索引樹上查找 name 以 '張' 開頭的數據都回主鍵索引樹中查詢數據，這樣就減少了一些不必要的查詢。

假設我們的數據如下圖所示：

在 name_age 索引樹中有四條符合 name 以 '張'開頭的數據，如果沒有索引下推，則需要回到主鍵索引樹上判斷 age 是否等於 10 ，這樣就需要回表四次，而有了索引下推之後，在 name_age 索引樹上就判斷 age 是否等於 10 ，只需要回表兩次，這樣就減少了回表次數，提升了查詢性能。

以上就是關於 InnoDB 引擎中的索引策略，感謝您的閱讀，希望這篇文章對您的學習或者工作有所幫助。

VirMach 便宜 VPS

QNews

QNews