快捷導(dǎo)航

盡量避免使用索引合并的場(chǎng)景問(wèn)題解析

更新時(shí)間：2023年05月15日 09:30:35 作者：江南一點(diǎn)雨

這篇文章主要為大家介紹了盡量避免使用索引合并的場(chǎng)景問(wèn)題解析，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步，早日升職加薪

引言

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

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

1. 問(wèn)題重現(xiàn)

為了方便小伙伴們理解，我先通過(guò) SQL 來(lái)把我的問(wèn)題重復(fù)一下。

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

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

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

https://github.com/datacharmer/test_db

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

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

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;

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

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

那么問(wèn)題來(lái)了，這個(gè)查詢會(huì)用到索引嗎？

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

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

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

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

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

這個(gè)其實(shí)就是一個(gè)索引合并，接下來(lái)我們就來(lái)看下到底什么是索引合并。

2. 索引合并

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

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

在官方文檔中給了四個(gè)可能會(huì)用到索引合并的例子：

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);

有的時(shí)候，我們寫(xiě)的 SQL，明明可以合并，但是系統(tǒng)卻沒(méi)有合并，此時(shí)我們對(duì)查詢條件做一些調(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í)行計(jì)劃中，如果用到了索引合并，Extra 字段的值一般分為三種情況，分別是：

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

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

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

2.1 Using intersect(...)

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

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

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

我們來(lái)看官方給出的一個(gè)例子，如下：

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

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

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

SELECT * FROM innodb_table WHERE primary_key &lt; 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 分別表示同一個(gè)復(fù)合索引的第一列和第二列（一共就兩列），此時(shí)和 key2 一起作為查詢條件，也有可能會(huì)用到索引合并。

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

舉一個(gè)簡(jiǎn)單的例子吧，還是 MySQL 官方的測(cè)試數(shù)據(jù)，sakila 庫(kù)中有一個(gè) 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;

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

select * from actor where actor_id&lt;10 and last_name='WAHLBERG'

執(zhí)行計(jì)劃如下：

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

2.2 Using union(...)

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

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

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

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

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

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

這個(gè)例子就不用舉了，文章一開(kāi)始的就是。

2.3 Using sort_union(...)

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

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

舉個(gè)例子，如下面的 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;

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

2.4 索引合并原理

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

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

例如：

二級(jí)索引必須等值匹配，等值匹配意味著最終拿到的 B+Tree 的葉子上的主鍵值就是唯一的；二級(jí)索引如果可以按照范圍查找，那么最終從二級(jí)索引的 B+Tree 的葉子結(jié)點(diǎn)上拿到的主鍵值就不是有序的了。
類似的，復(fù)合索引必須覆蓋到所有列也是相似的原因，因?yàn)槿绻麤](méi)有覆蓋到所有列，意味著最終拿到的主鍵值也是無(wú)序的。

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

3. 索引合并的問(wèn)題

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

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

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