組合索引使用場景分析-愛可生

建立在多個列上的索引即組合索引（聯合索引），適用在多個列必須一起使用或者是從左到右方向部分連續列一起使用的業務場景。

組合索引和單值索引類似，索引上的每個鍵值按照一定的大小排序。比如針對三個欄位的組合索引有以下組合：

（f1， f2， f3）

（f1， f2， f3 desc）

（f1， f2 desc， f3）

（f1 desc， f2， f3）

（f1 desc， f2 desc， f3 desc）

。。。

今天討論的組合索引只基於預設排序方式，也就是 （f1，f2，f3），等價於 （f1 asc， f2 asc， f3 asc）。

組合索引的語法：

alter table t1 add key idx_multi（f1 ［asc/desc］，f2 ［asc/desc］，f3 ［asc/desc］） ［using btree/using hash］

MySQL 裡，組合索引最大支援 16 個列。可以基於 B+ 樹，也可以基於雜湊，這篇主要討論基於 B 樹，並且索引順序預設升序，基於 HASH 只有一種用法，就是所有列的都必須等值過濾【僅限於下面 SQL 3】。

使用組合索引的必備條件為：列 f1 必須存在於 SQL 語句過濾條件中！也就是說組合索引的第一個列（最左列）在過濾條件中必須存在，而且最好是等值過濾。

考慮以下 15 條 SQL 語句， 分別對錶 t1 欄位 f1、f2、f3 有不同的組合過濾，並且都包含了列 f1，也就是說滿足了組合索引使用的必備條件。

# SQL 1select * from t1 where f1 = 1；# SQL 2select * from t1 where f1 = 1 and f2 = 1；# SQL 3select * from t1 where f1 = 1 and f2 = 1 and f3 = 1 ；# SQL 4select f1，f2 from t1 where 1 order by f1，f2；# SQL 5select f1，f2，f3 from t1 where 1 order by f1，f2，f3；# SQL 6select f1，f2，count（*） from t1 group by f1，f2；# SQL 7select f1，f2，f3，count（*） from t1 group by f1，f2，f3；# SQL 8select * from t1 where f1 = 10 and f2 = 5 and f3 > 10# SQL 9select * from t1 where f1 = 10 and f2 > 5；# SQL 10select * from t1 where f1 < 10；# SQL 11select * from t1 where f1 < 10 and f2 > 5；# SQL 12select * from t1 where f1 < 10 and f2 > 5 and f3 < 10；# SQL 13select * from t1 where f1 < 10 and f2 = 5 and f3 < 10；# SQL 14select * from t1 where f1 < 10 and f2 = 5 and f3 = 10；# SQL 15select * from t1 where f1 = 1 and f3 = 1；

SQL 1、SQL 2、 SQL 3 三條 SQL 分別基於組合索引 idx_multi 過濾後回表；其中 SQL 3 是組合索引中每個欄位都能過濾到的最完美查詢。來看看 SQL 3的執行計劃：

（127。0。0。1：3400）|（ytt）>explain select * from t1 where f1 = 1 and f2 = 1 and f3 = 1\G*************************** 1。 row *************************** id： 1 select_type： SIMPLE table： t1 partitions： NULL type： refpossible_keys： idx_multi key： idx_multi key_len： 15 ref： const，const，const rows： 1 filtered： 100。00 Extra： NULL1 row in set， 1 warning （0。00 sec）

SQL 3 的執行計劃趨近完美，當然最完美的是索引 idx_multi 是主鍵或者唯一索引。比如下面對錶 t3 查詢，t3 的索引 udx_multi 是一個唯一索引。

（127。0。0。1：3400）|（ytt）>explain select * from t3 where f1 = 9 and f2 = 52 and f3 = 35\G*************************** 1。 row *************************** id： 1 select_type： SIMPLE table： t3 partitions： NULL type： constpossible_keys： udx_multi key： udx_multi key_len： 15 ref： const，const，const rows： 1 filtered： 100。00 Extra： NULL1 row in set， 1 warning （0。00 sec）

SQL 4、SQL 5、SQL 6、SQL 7 四條 SQL 走覆蓋索引掃描，不用回表，利用索引 idx_multi 升序輸出結果。隨便列印下其中 SQL 7 的執行計劃看看：

127。0。0。1：3400）|（ytt）>explain -> select f1，f2，f3，count（*） from t1 group by f1，f2，f3\G*************************** 1。 row *************************** id： 1 select_type： SIMPLE table： t1 partitions： NULL type： indexpossible_keys： idx_multi key： idx_multi key_len： 15 ref： NULL rows： 32194 filtered： 100。00 Extra： Using index1 row in set， 1 warning （0。00 sec）

結果 type 列 為 index，直接索引返回結果。

其實這裡遇到一個非常常見的疑問：SQL 1 過濾條件裡只有欄位 f1， SQL 2 過濾欄位裡只有 f1，f2，針對這兩種場景可否應該建立如下單值索引讓查詢執行的更加高效？

alter table t1 add key idx_f1（f1）；alter table t1 add key idx_multi_sub（f1，f2）；

其實針對列 f1 單獨建立索引沒有必要，因為 f1 為索引 idx_multi 的第一個欄位，查詢時如果僅僅包含欄位 f1，那 MySQL 也僅僅只使用 f1 的索引資料，不會讓索引 idx_multi 的所有列都被使用；

同理，基於欄位 （f1，f2） 再建立一個組合索引也沒有必要，（f1， f2） 可以看作以這樣的方式存在與組合索引中：（（f1， f2）， f3）

那分別看下 SQL 2 用索引 idx_multi 和 idx_multi_sub 的執行計劃來證實以上的說法：

（127。0。0。1：3400）|（ytt）>explain select * from t1 where f1 = 1 and f2 = 1\G*************************** 1。 row *************************** id： 1 select_type： SIMPLE table： t1 partitions： NULL type： refpossible_keys： idx_multi，idx_f1，idx_multi_sub key： idx_multi key_len： 10 ref： const，const rows： 3 filtered： 100。00 Extra： NULL1 row in set， 1 warning （0。00 sec）（127。0。0。1：3400）|（ytt）>explain select * from t1 force index （idx_multi_sub） where f1 = 1 and f2 = 1\G*************************** 1。 row *************************** id： 1 select_type： SIMPLE table： t1 partitions： NULL type： refpossible_keys： idx_multi_sub key： idx_multi_sub key_len： 10 ref： const，const rows： 3 filtered： 100。00 Extra： NULL1 row in set， 1 warning （0。00 sec）

從以上結果來看，執行計劃沒有任何不同，所以如果查詢過濾條件為組合索引裡第一個列或者是包含第一個列的連續字首列，不需要單獨再建立部分欄位的組合索引，保留原來組合索引即可。

（127。0。0。1：3400）|（ytt）>alter table t1 drop key idx_multi_sub， drop key idx_f1；Query OK， 0 rows affected （0。06 sec）Records： 0 Duplicates： 0 Warnings： 0

那接下來看看 SQL 8。SQL 8 裡前兩列也是連續的，不同的是後面列 f3 的過濾條件是一個範圍，那看下 SQL 8 執行計劃：

（127。0。0。1：3400）|（ytt）>explain select * from t1 where f1 = 10 and f2 = 5 and f3 > 10\G*************************** 1。 row *************************** id： 1 select_type： SIMPLE table： t1 partitions： NULL type： rangepossible_keys： idx_multi key： idx_multi key_len： 15 ref： NULL rows： 154 filtered： 100。00 Extra： Using index condition1 row in set， 1 warning （0。00 sec）

SQL 8 的執行計劃顯示：由於列f3是過濾條件是一個範圍，並不影響使用組合索引，因為前兩列 f1，f2 是連續的。

SQL 9 和 SQL 8 一樣，因為列 f1 等值過濾，之後是 f2 範圍過濾。

SQL 10 只用到了列 f1， 這點類似於單值索引 （f1）。

SQL 11、 SQL 12 這兩條 SQL 和 SQL 10 是類似的，雖然過濾欄位順序和索引欄位順序一樣，但是由於第一個列是一個範圍，只能用到組合索引的第一列。

SQL 13 、SQL 14 有點不一樣，雖然列f1是範圍過濾，但是 SQL 13 裡 f2 是等值過濾，SQL 14 裡 f2，f3 是等值過濾。

所以如果 SQL 13、SQL 14 也執行較為頻繁的話，可以另外加一個組合索引 （f2， f3）。

（127。0。0。1：3400）|（ytt）>alter table t1 add key idx_multi2（f2，f3）；Query OK， 0 rows affected （0。38 sec）Records： 0 Duplicates： 0 Warnings： 0

看下 SQL 13 和 SQL 14 的執行計劃：

（127。0。0。1：3400）|（ytt）>explain select * from t1 where f1 < 10 and f2 = 5 and f3 < 10\G*************************** 1。 row *************************** id： 1 select_type： SIMPLE table： t1 partitions： NULL type： rangepossible_keys： idx_multi，idx_multi2 key： idx_multi2 key_len： 10 ref： NULL rows： 174 filtered： 7。99 Extra： Using index condition； Using where1 row in set， 1 warning （0。00 sec）（127。0。0。1：3400）|（ytt）>explain select * from t1 where f1 < 10 and f2 = 5 and f3 = 10\G*************************** 1。 row *************************** id： 1 select_type： SIMPLE table： t1 partitions： NULL type： refpossible_keys： idx_multi，idx_multi2 key： idx_multi2 key_len： 10 ref： const，const rows： 14 filtered： 7。99 Extra： Using where1 row in set， 1 warning （0。00 sec）（127。0。0。1：3400）|（ytt）>

那此時上面兩條 SQL 又回到了組合索引的經典使用場景。

再看下最後一條 SQL，SQL 15。SQL 15 過濾條件只有 （f1=1 and f3=1），也就是不匹配組合索引的過濾連續性特徵，但是由於列 f1 是等值過濾，所以也可以使用組合索引 idx_multi， 看下執行計劃：

（127。0。0。1：3400）|（ytt）>explain select * from t1 where f1 = 1 and f3 = 1\G*************************** 1。 row *************************** id： 1 select_type： SIMPLE table： t1 partitions： NULL type： refpossible_keys： idx_multi key： idx_multi key_len： 5 ref： const rows： 312 filtered： 10。00 Extra： Using index condition1 row in set， 1 warning （0。00 sec）

從執行計劃結果看出，也只用到了列 f1，因為 f2 不存在。所以這條 SQL 如果執行也很頻繁，可以再次建立一個新的組合索引 （f1， f3）。

（127。0。0。1：3400）|（ytt）>alter table t1 add key idx_multi3（f1，f3）；Query OK， 0 rows affected （0。36 sec）Records： 0 Duplicates： 0 Warnings： 0

再看下執行計劃：

（127。0。0。1：3400）|（ytt）>explain select * from t1 where f1 = 1 and f3 = 1\G*************************** 1。 row *************************** id： 1 select_type： SIMPLE table： t1 partitions： NULL type： refpossible_keys： idx_multi，idx_multi3 key： idx_multi3 key_len： 10 ref： const，const rows： 1 filtered： 100。00 Extra： NULL1 row in set， 1 warning （0。00 sec）

那此時又回到了組合索引的過濾連續性特徵場景。

文中示例用到的表結構：

（127。0。0。1：3400）|（ytt）>show create table t1\G*************************** 1。 row *************************** Table： t1Create Table： CREATE TABLE `t1` （ `id` int NOT NULL， `f1` int DEFAULT NULL， `f2` int DEFAULT NULL， `f3` int DEFAULT NULL， `f4` int DEFAULT NULL， PRIMARY KEY （`id`）， KEY `idx_multi` （`f1`，`f2`，`f3`）） ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set （0。00 sec）（127。0。0。1：3400）|（ytt）>show create table t3\G*************************** 1。 row *************************** Table： t3Create Table： CREATE TABLE `t3` （ `id` int NOT NULL， `f1` int DEFAULT NULL， `f2` int DEFAULT NULL， `f3` int DEFAULT NULL， `f4` int DEFAULT NULL， UNIQUE KEY `udx_multi` （`f1`，`f2`，`f3`）） ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set （0。00 sec）

這裡討論了組合索引的種種使用點，日常業務中，如果一個列已經在組合索引，並且在第一位，應當避免建立額外的單個索引。

關鍵字：MySQL資料庫、資料查詢、資料庫運維、資料管理、愛可生