引言

在前面的文章中，松哥和小伙伴們分享了 MySQL 中，InnoDB 存儲引擎的數(shù)據(jù)結(jié)構(gòu)，小伙伴們知道，當(dāng)我們使用索引進行搜索的時候，每一次的搜索都是在某一棵 B+Tree 中搜索的，如果使用了二級索引的話，可能還會涉及到回表。

那么現(xiàn)在問題來了，如果我們的搜索條件中包含兩個字段，且這兩個字段都有獨立的索引，那么 MySQL 會怎么處理？今天我們就來討論下這個話題。

1. 問題重現(xiàn)

為了方便小伙伴們理解，我先通過 SQL 來把我的問題重復(fù)一下。

我使用的測試數(shù)據(jù)是 MySQL 官網(wǎng)提供的測試數(shù)據(jù)，相關(guān)的介紹文檔在：

• https://dev.mysql.com/doc/employee/en/

相應(yīng)的數(shù)據(jù)庫腳本在：

• https://github.com/datacharmer/test_db

小伙伴們可以自行下載這個數(shù)據(jù)庫腳本并導(dǎo)入到自己的數(shù)據(jù)庫之中。

在官方提供的案例中，有一個這樣的表：

CREATE TABLE `film_actor` (
  `actor_id` smallint unsigned NOT NULL,
  `film_id` smallint unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`,`film_id`),
  KEY `idx_fk_film_id` (`film_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

在這個表中有兩個索引，其中一個是主鍵索引，主鍵索引是一個聯(lián)合索引，還有一個是根據(jù) film_id 建立的普通索引?，F(xiàn)在假設(shè)我有如下 SQL 需要執(zhí)行：

select * from film_actor where film_id=1 or actor_id=1;

那么問題來了，這個查詢會用到索引嗎？

想知道有沒有用到索引，用 explain 關(guān)鍵字看一下就知道了：

explain select * from film_actor where film_id=1 or actor_id=1;

執(zhí)行結(jié)果如下：

小伙伴們看到，此時 type 是 index_merge，possible_keys 和 key 中，都給出來了兩個索引，Extra 中的值為 Using union(idx_fk_film_id,PRIMARY); Using where。

看起來是用了索引，但是具體是怎么用的，這個執(zhí)行計劃該如何解讀呢？

這個其實就是一個索引合并，接下來我們就來看下到底什么是索引合并。

2. 索引合并

index_merge 表示索引合并，當(dāng)同一個表中的搜索條件中同時存在多個索引的時候，MySQL 會分別對這些索引進行掃描，然后將掃描結(jié)果進行合并，合并分三種情況：

• 對各自掃描結(jié)果求并集（unions）。
• 對各自掃描結(jié)果求交集（intersections）。
• 前兩者的組合。

在官方文檔中給了四個可能會用到索引合并的例子：

SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20;
SELECT * FROM tbl_name
  WHERE (key1 = 10 OR key2 = 20) AND non_key = 30;
SELECT * FROM t1, t2
  WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%')
  AND t2.key1 = t1.some_col;
SELECT * FROM t1, t2
  WHERE t1.key1 = 1
  AND (t2.key1 = t1.some_col OR t2.key2 = t1.some_col2);

有的時候，我們寫的 SQL，明明可以合并，但是系統(tǒng)卻沒有合并，此時我們對查詢條件做一些調(diào)整，例如：

• (x AND y) OR z => (x OR z) AND (y OR z)
• (x OR y) AND z => (x AND z) OR (y AND z)

另外需要注意的是，索引合并不適用于全文索引。

在 explain 執(zhí)行計劃中，如果用到了索引合并，Extra 字段的值一般分為三種情況，分別是：

• Using intersect(...)
• Using union(...)
• Using sort_union(...)

上文案例屬于第二種情況。

那么接下來把這三種情況都來和小伙伴們聊一下。

2.1 Using intersect(...)

這個就是對多個掃描結(jié)果求交集。

并不是只要涉及到多個索引，且是 AND，就會觸發(fā) Using intersect，有兩個條件：

• 如果是二級索引，則必須是等值查詢。如果二級索引是復(fù)合索引，則復(fù)合索引的每一列都必須覆蓋到，不能只是其中的某幾列。
• 主鍵索引可以是范圍查詢。

我們來看官方給出的一個例子，如下：

key_part1 = const1 AND key_part2 = const2 ... AND key_partN = constN

key_part1 - key_partN 就是復(fù)合索引中的所有列（必須是所有列）。

對于第 2 點，如果涉及到主鍵索引，則主鍵索引可以是范圍查詢，例如下面這樣（但是二級索引依然只能是等值查詢）：

SELECT * FROM innodb_table WHERE primary_key < 10 AND key_col1 = 20;

如果是復(fù)合索引和普通索引，那么復(fù)合索引必須覆蓋到所有列且復(fù)合索引和普通索引都要是等值匹配才可以，例如下面這樣：

SELECT * FROM tbl_name WHERE key1_part1 = 1 AND key1_part2 = 2 AND key2 = 2;

key1_part1 和 key1_part2 分別表示同一個復(fù)合索引的第一列和第二列（一共就兩列），此時和 key2 一起作為查詢條件，也有可能會用到索引合并。

上面這些情況都是在各自搜索完成之后求交集。

舉一個簡單的例子吧，還是 MySQL 官方的測試數(shù)據(jù)，sakila 庫中有一個 actor 表，該表結(jié)構(gòu)如下：

CREATE TABLE `actor` (
  `actor_id` smallint unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8mb3;

可以看到，有一個主鍵，有一個普通索引，我執(zhí)行如下 SQL：

select * from actor where actor_id<10 and last_name='WAHLBERG'

執(zhí)行計劃如下：

可以看到，用到了索引合并，且是 Using intersect。

2.2 Using union(...)

求并集的跟求交集的比較像，就是 AND 變成了 OR。

當(dāng)二級索引是等值查詢，或者是組合索引，但是要求組合索引的每一列都必須覆蓋到，不能只是覆蓋到部分列，例如下面這個查詢條件：

key_part1 = const1 OR key_part2 = const2 ... OR key_partN = constN

key_part1~key_partN 就是同一個復(fù)合索引的不同列，同時在該復(fù)合索引中，也一共就只有這 N 個字段，這種情況就會用到 Using union。

InnoBD 表上的主鍵范圍查詢也有可能會觸發(fā) Using union。

符合 2.1 小節(jié)的情況，將 AND 換成 OR 之后，也有可能會觸發(fā) Using union。

這個例子就不用舉了，文章一開始的就是。

2.3 Using sort_union(...)

很明顯，2.2 小節(jié)的條件比較苛刻，二級索引必須是等值查詢才能觸發(fā) Using union，而我們?nèi)粘Ｊ褂玫臅r候，范圍查詢也是非常常見的，所以又有了 Using sort_union，這個的要求就寬松一些了：

• 二級索引也可以按照范圍匹配
• 復(fù)合索引也不用覆蓋所有列

舉個例子，如下面的 SQL：

SELECT * FROM tbl_name
  WHERE key_col1 < 10 OR key_col2 < 20;
SELECT * FROM tbl_name
  WHERE (key_col1 > 10 OR key_col2 = 20) AND nonkey_col = 30;

二級索引范圍搜索，也有可能觸發(fā) Using sort_union 的。

2.4 索引合并原理

在 2.1 小節(jié)和 2.2 小節(jié)，分別是求交集和求并集，為了 intersect 和 union 操作方便，在各個單獨的索引掃描的時候，都是要獲取到有序的主鍵值的合集，各個索引都獲取到有序的主鍵，然后求交集或者并集就會比較方便。

因此，在 2.1 和 2.2 小節(jié)，都是主鍵索引可以范圍搜索，因為主鍵索引本身主鍵就是有序的；二級索引則有諸多限制，這諸多限制的最終目的都是為了做到最終拿到的主鍵值是有序的。

例如：

• 二級索引必須等值匹配，等值匹配意味著最終拿到的 B+Tree 的葉子上的主鍵值就是唯一的；二級索引如果可以按照范圍查找，那么最終從二級索引的 B+Tree 的葉子結(jié)點上拿到的主鍵值就不是有序的了。
• 類似的，復(fù)合索引必須覆蓋到所有列也是相似的原因，因為如果沒有覆蓋到所有列，意味著最終拿到的主鍵值也是無序的。

2.3 小節(jié)允許二級索引按照范圍搜索，這是因為在 Using sort_union 中，會先對拿到的主鍵值進行排序，然后才會去求交集或者并集，當(dāng)然，相比于 2.1 和 2.2 小節(jié)，2.3 小節(jié)的性能也會降低一些。

3. 索引合并的問題

索引合并看著似乎提升了 MySQL 搜索的性能，然而，一般出現(xiàn)索引合并，大概率都是因為索引創(chuàng)建的不合理，我們需要重新審視自己的索引。

如上面 2.3 小節(jié)所述，這種方式在查詢的過程中需要緩存臨時數(shù)據(jù)、需要排序然后才能求交集或者并集，這些操作都會消耗掉大部分的 CPU 和內(nèi)存資源。并且這些消耗不會被計算到查詢成本中，因為 MySQL 優(yōu)化器只關(guān)心隨機頁面的讀取問題，并不會關(guān)心這里涉及到的這些額外計算問題，所以，在一些極端情況下，索引合并的性能可能還不如全表掃描。

因此，有時候如果我們確定自己不需要索引合并，那么可以通過 ignore index 來忽略掉一些索引，如下（對比 2.1 小節(jié)截圖）：

也可以通過 optimizer_switch 來關(guān)閉索引合并功能，如下：

好啦，索引合并就和小伙伴們聊這么多吧～感興趣的小伙伴也可以嘗試下哦！

以上就是盡量避免使用索引合并的場景問題解析的詳細內(nèi)容，更多關(guān)于避免使用索引合并分析的資料請關(guān)注腳本之家其它相關(guān)文章！

最新評論