iT邦幫忙

1

MySQL 系列文 - 索引的相關知識(4) - 複合索引

前言

我們在一張 TABLE 上除了叢集索引只能有一個之外,會有多個輔助索引,當在建立輔助索引的時候也常常會包含多個欄位,超過一個以上欄位的索引就是複合索引。

複合索引

★ 前提: 不同版本優化器是有差異的,這個版本會幫你優化語法,另一個版本可能不能幫你優化語法。所以這裡會提到一些觀念,可能在新一點的 MySQL 版本會發現不是這麼回事,所以有時間還是要自己實際測試看看。

直接舉個例子來說明:
有5個欄位
PK: Id
輔助索引 : index_name(A,B,C)

-- 建立測試 TABLE
CREATE TABLE `test_index` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `A` varchar(20) DEFAULT NULL,
  `B` varchar(20) DEFAULT NULL,
  `C` varchar(20) DEFAULT NULL,
  `D` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`Id`),
  KEY `index_name` (`A`,`B`,`C`)
) ENGINE=InnoDB

-- 插入資料
INSERT INTO `test_index`(`Id`, `A`, `B`, `C`, `D`) VALUES (1, 'a', 'b', 'c', 'd');
INSERT INTO `test_index`(`Id`, `A`, `B`, `C`, `D`) VALUES (2, 'aa', 'bb', 'cc', 'dd');
INSERT INTO `test_index`(`Id`, `A`, `B`, `C`, `D`) VALUES (3, 'aaa', 'bbb', 'ccc', 'ddd');
INSERT INTO `test_index`(`Id`, `A`, `B`, `C`, `D`) VALUES (4, 'a', 'b', 'c', 'd');
INSERT INTO `test_index`(`Id`, `A`, `B`, `C`, `D`) VALUES (5, 'aa', 'bb', 'cc', 'dd');
INSERT INTO `test_index`(`Id`, `A`, `B`, `C`, `D`) VALUES (6, 'aaa', 'bbb', 'ccc', 'ddd');
INSERT INTO `test_index`(`Id`, `A`, `B`, `C`, `D`) VALUES (7, 'a', 'b', 'c', 'd');
INSERT INTO `test_index`(`Id`, `A`, `B`, `C`, `D`) VALUES (8, 'aa', 'bb', 'cc', 'dd');
INSERT INTO `test_index`(`Id`, `A`, `B`, `C`, `D`) VALUES (9, 'aaa', 'bbb', 'ccc', 'ddd');
INSERT INTO `test_index`(`Id`, `A`, `B`, `C`, `D`) VALUES (10, 'a', 'b', 'c', 'd');
INSERT INTO `test_index`(`Id`, `A`, `B`, `C`, `D`) VALUES (11, 'aa', 'bb', 'cc', 'dd');
INSERT INTO `test_index`(`Id`, `A`, `B`, `C`, `D`) VALUES (12, 'aaa', 'bbb', 'ccc', 'ddd');

接著我們看幾個 SELECT 的語法有沒有使用到輔助索引

◎查詢1
EXPLAIN
SELECT *
FROM test_index
WHERE A='a' 

https://ithelp.ithome.com.tw/upload/images/20200306/20124671I2thsgfBNH.png
圖1
從圖1可知

  • type: ref 表示有用到非唯一性索引
  • key: index_name 表示用到我們建立的輔助索引
  • rows: 4 表示掃描了 4行
◎查詢2
EXPLAIN
SELECT *
FROM test_index
WHERE  B='b'

https://ithelp.ithome.com.tw/upload/images/20200306/20124671XLZpmfcEWC.png
圖2
從圖2可知

  • type: ALL 表示沒有用到索引,全表掃描
  • rows: 12 整張表的資料都掃了
◎查詢3
EXPLAIN
SELECT *
FROM test_index
WHERE  A='a' and B='b'

https://ithelp.ithome.com.tw/upload/images/20200306/20124671F6wDiF4wWO.png
圖3
從圖3可知

  • type: ref 表示有用到非唯一性索引
  • key: index_name 表示用到我們建立的輔助索引
  • rows: 4 表示掃描了 4行

小結

針對以上3個查詢做的小結

  • 事實上當我們輔助索引建立的是 (A,B,C),會用到索引的情況為
    (A), (A,B), (A,C), (A,B,C)
  • 不會用到索引的情況為
    (B), (C), (B,C)
  • 所以如果我們的 WHERE 條件會用到 (A) 欄位也會用到 (A,B) 欄位,表示我們只要建一個 KEY (A,B) 就好,假如此時還有另一個索引 KEY (A),那麼這個就是多建的,可以刪掉
  • 這裡我們還要了解另一件原理,也就是最左前綴原理

最左前綴(最左匹配)

MySQL 在建立複合索引的時候會符合最左前綴匹配,也就是說在建立此複合索引的 B+樹時會依照左到右的順序來建
舉一個簡單的例子

CREATE TABLE t (
a INT,
b INT,
PRIMARY KEY (a),
KEY idx_a_b (a,b)
)ENGINE=INNODB

裡面假設有一些資料如下圖4
https://ithelp.ithome.com.tw/upload/images/20200309/20124671UAaDD1sI5Q.png
圖4 參考至書籍 MySQL 技術內幕 InnoDB 存儲引擎

  • 基於最左前綴,可以發現會先依照欄位 a 來排,所以此 B+樹的順序可以看到由 1 -> 3
  • 如果不看 a 只看 b,會發現 b的數字依序是 1,2,1,4,1,2 這樣就沒有順序了
  • 所以如果 b 也要有用到索引,那麼 a 欄位也要放進去才可以,也就是當 a 欄位相同時,b欄位就有順序

為什麼需要複合索引?

  1. 減少硬碟空間占用: 如同前面提到的,假如我們的複合索引(A,B),搜尋條件有
    where A = xxx
    where A =xxx and B =xxx
    那麼不用建例兩個索引 (A) 和 (A,B)。不要忘記,一個索引就是一個B+樹
  2. 可以使用覆蓋索引: 假設有一個語法 SELECT A,B,C FROM table_name WHERE A = xxx and B = xxx ,此時我們有一個複合索引(A,B,C),表示可以不用回到叢集索引去就可以取得資料。忘記什麼是覆蓋索引可以回到我的上一篇參考 MySQL 系列文 - 索引的相關知識 - 覆蓋索引
  3. 第二個欄位已排序: 有時候我們用了 A 欄位來當 WHERE 條件,並且 order by B ,例如 SELECT * FROM table_name WHERE A = xxx ORDER BY B 此時選擇了複合索引,B 欄位本身就是排序好的,不用另外排序了。

小結

本篇介紹了什麼是複合索引,及為什麼會需要複合索引,另外也補充了最左前綴原理。這些都關係到當我們要為一張表建立索引時要去思考的部分,所以當我們在建立索引的時候要考慮搜尋的條件、使用的頻率,還有不要重複建立多餘的索引,這些都是要考慮進去的。希望以上的觀念有幫助到大家在建立索引時可能會遇到的問題。

資料庫知識相當廣泛,文中若有不正確的地方,也煩請各位大神不吝指教,謝謝


1 則留言

0
ckp6250
iT邦研究生 4 級 ‧ 2020-03-10 22:00:15

又受教了。
辛苦啦。

Stock iT邦新手 5 級 ‧ 2020-03-11 12:29:21 檢舉

不會不會,分享兼筆記!

我要留言

立即登入留言