建站
咨詢
售后
建站咨詢
新聞中心
在前面的文章中，松哥和小伙伴們分享了 MySQL 中，InnoDB 存儲(chǔ)引擎的數(shù)據(jù)結(jié)構(gòu)，小伙伴們知道，當(dāng)我們使用索引進(jìn)行搜索的時(shí)候，每一次的搜索都是在某一棵 B+Tree 中搜索的，如果使用了二級(jí)索引的話，可能還會(huì)涉及到回表。
成都創(chuàng)新互聯(lián)公司-專業(yè)網(wǎng)站定制、快速模板網(wǎng)站建設(shè)、高性價(jià)比橋西網(wǎng)站開(kāi)發(fā)、企業(yè)建站全套包干低至880元,成熟完善的模板庫(kù),直接使用。一站式橋西網(wǎng)站制作公司更省心,省錢,快速模板網(wǎng)站建設(shè)找我們，業(yè)務(wù)覆蓋橋西地區(qū)。費(fèi)用合理售后完善，十年實(shí)體公司更值得信賴。
那么現(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í)候，我們寫的 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 = constNkey_part1 - key_partN 就是復(fù)合索引中的所有列（必須是所有列）。
對(duì)于第 2 點(diǎn)，如果涉及到主鍵索引，則主鍵索引可以是范圍查詢，例如下面這樣（但是二級(jí)索引依然只能是等值查詢）：
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 分別表示同一個(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<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 = constNkey_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é)截圖）：
也可以通過(guò) optimizer_switch 來(lái)關(guān)閉索引合并功能，如下：
好啦，索引合并就和小伙伴們聊這么多吧～感興趣的小伙伴也可以嘗試下哦！
新聞標(biāo)題：索引合并，能不用就不要用吧！
本文路徑：http://www.5511xx.com/article/ccdcsos.html
