今天是新年，祝大家新年快樂，但是生活還是得繼續(xù)。

后面也會(huì)持續(xù)更新，學(xué)到新東西會(huì)在其中補(bǔ)充。
建議按順序食用，歡迎批評(píng)或者交流！
缺什么東西歡迎評(píng)論！我都會(huì)及時(shí)修改的！
大部分截圖和文章采用該書，謝謝這位大佬的文章，在這里真的很感謝讓迷茫的我找到了很好的學(xué)習(xí)文章。我只是加上了自己的拙見。我只是記錄學(xué)習(xí)沒有任何抄襲意思
MySQL 是怎樣運(yùn)行的：從根兒上理解 MySQL - 小孩子4919 - 掘金小冊(cè)

MySQL Server有一個(gè)稱為查詢優(yōu)化器的模塊，一條查詢語句進(jìn)行語法解析之后就會(huì)被交給查詢優(yōu)化器來進(jìn)行優(yōu)化，優(yōu)化的結(jié)果就是生成一個(gè)所謂的執(zhí)行計(jì)劃，這個(gè)執(zhí)行計(jì)劃表明了應(yīng)該使用哪些索引進(jìn)行查詢，表之間的連接順序是啥樣的，最后會(huì)按照?qǐng)?zhí)行計(jì)劃中的步驟調(diào)用存儲(chǔ)引擎提供的方法來真正的執(zhí)行查詢，并將查詢結(jié)果返回給用戶。

CREATE TABLE single_table (id INT NOT NULL AUTO_INCREMENT,key1 VARCHAR(100),key2 INT,key3 VARCHAR(100),key_part1 VARCHAR(100),key_part2 VARCHAR(100),key_part3 VARCHAR(100),common_field VARCHAR(100),PRIMARY KEY (id),KEY idx_key1 (key1),UNIQUE KEY idx_key2 (key2),KEY idx_key3 (key3),KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;

為這個(gè)single_table表建立了1個(gè)聚簇索引和4個(gè)二級(jí)索引，分別是：

• 為id列建立的聚簇索引。

• 為key1列建立的idx_key1二級(jí)索引。

• 為key2列建立的idx_key2二級(jí)索引，而且該索引是唯一二級(jí)索引。

• 為key3列建立的idx_key3二級(jí)索引。

• 為key_part1、key_part2、key_part3列建立的idx_key_part二級(jí)索引，這也是一個(gè)聯(lián)合索引。

#借用評(píng)論區(qū)一位大佬寫的存儲(chǔ)過程
DELIMITER //
CREATE PROCEDURE InsertRecords()
BEGIN
DECLARE i INT DEFAULT 1;WHILE i <= 10000 DO
INSERT INTO single_table (key1, key2, key3, key_part1, key_part2, key_part3, common_field) VALUES
(CONCAT('Key1_', i), i, CONCAT('Key3_', i), CONCAT('Part1_', i), CONCAT('Part2_', i), CONCAT('Part3_', i), 'CommonField');
SET i = i + 1;
END WHILE;
END //DELIMITER ;CALL InsertRecords();

訪問方法（access method）的概念

查詢的執(zhí)行方式大致分為下邊兩種：

• 使用全表掃描進(jìn)行查詢
  把表的每一行記錄都掃一遍，把符合搜索條件的記錄加入到結(jié)果集就完了。
• 使用索引進(jìn)行查詢
  因?yàn)橹苯邮褂?strong>全表掃描的方式執(zhí)行查詢要遍歷好多記錄，所以代價(jià)可能太大了。
  如果查詢語句中的搜索條件可以使用到某個(gè)索引，那直接使用索引來執(zhí)行查詢可能會(huì)加快查詢執(zhí)行的時(shí)間。
  • 針對(duì)主鍵或唯一二級(jí)索引的等值查詢
  • 針對(duì)普通二級(jí)索引的等值查詢
  • 針對(duì)索引列的范圍查詢
  • 直接掃描整個(gè)索引
    MySQL執(zhí)行查詢語句的方式稱之為訪問方法或者訪問類型。

const

SELECT * FROM single_table WHERE id = 1438;

MySQL會(huì)直接利用主鍵值在聚簇索引中定位對(duì)應(yīng)的用戶記錄

對(duì)于single_table表的聚簇索引就是id列。
B+樹葉子節(jié)點(diǎn)中的記錄是按照索引列排序的，對(duì)于聚簇索引來說，它對(duì)應(yīng)的B+樹葉子節(jié)點(diǎn)中的記錄就是按照id列排序的。

唯一二級(jí)索引列來定位一條記錄的速度也是很快的。注意這里是唯一的

SELECT * FROM single_table WHERE key2 = 3841;

第一步：先從idx_key2對(duì)應(yīng)的B+樹索引中根據(jù)key2列與常數(shù)的等值比較條件定位到一條二級(jí)索引記錄
第二步：再根據(jù)該記錄的id值到聚簇索引中獲取到完整的用戶記錄。

通過主鍵或者唯一二級(jí)索引列與常數(shù)的等值比較來定位一條記錄是像坐火箭一樣快的，所以把這種通過主鍵或者唯一二級(jí)索引列來定位一條記錄的訪問方法定義為：const，意思是常數(shù)級(jí)別的，代價(jià)是可以忽略不計(jì)的。
不過這種const訪問方法只能在主鍵列或者唯一二級(jí)索引列和一個(gè)常數(shù)進(jìn)行等值比較時(shí)才有效，如果主鍵或者唯一二級(jí)索引是由多個(gè)列構(gòu)成的話，索引中的每一個(gè)列都需要與常數(shù)進(jìn)行等值比較，這個(gè)const訪問方法才有效（這是因?yàn)橹挥性撍饕腥苛卸疾捎玫戎当容^才可以定位唯一的一條記錄）。
對(duì)于唯一二級(jí)索引來說，查詢?cè)摿袨?code>NULL值的情況比較特殊，比如這樣：

SELECT * FROM single_table WHERE key2 IS NULL;

唯一二級(jí)索引列并不限制 NULL 值的數(shù)量，所以上述語句可能訪問到多條記錄，也就是說 上邊這個(gè)語句不可以使用const訪問方法來執(zhí)行。

ref

對(duì)某個(gè)普通的二級(jí)索引列與常數(shù)進(jìn)行等值比較

SELECT * FROM single_table WHERE key1 = 'abc';

對(duì)于這個(gè)查詢，我們當(dāng)然可以選擇全表掃描來逐一對(duì)比搜索條件是否滿足要求，我們也可以先使用二級(jí)索引找到對(duì)應(yīng)記錄的id值，然后再回表到聚簇索引中查找完整的用戶記錄。由于普通二級(jí)索引并不限制索引列值的唯一性，所以可能找到多條對(duì)應(yīng)的記錄，也就是說使用二級(jí)索引來執(zhí)行查詢的代價(jià)取決于等值匹配到的二級(jí)索引記錄條數(shù)。
如果匹配的記錄較少，則回表的代價(jià)還是比較低的，所以MySQL可能選擇使用索引而不是全表掃描的方式來執(zhí)行查詢。設(shè)計(jì)MySQL的大叔就把這種搜索條件為二級(jí)索引列與常數(shù)等值比較，采用二級(jí)索引來執(zhí)行查詢的訪問方法稱為：ref。

實(shí)在不想看上面文字可以看圖

對(duì)于普通的二級(jí)索引來說，通過索引列進(jìn)行等值比較后可能匹配到多條連續(xù)的記錄，而不是像主鍵或者唯一二級(jí)索引那樣最多只能匹配1條記錄，所以這種ref訪問方法比const差了那么一丟丟，但是在二級(jí)索引等值比較時(shí)匹配的記錄數(shù)較少時(shí)的效率還是很高的。

• 二級(jí)索引列值為NULL的情況
  不論是普通的二級(jí)索引，還是唯一二級(jí)索引，它們的索引列對(duì)包含NULL值的數(shù)量并不限制，所以我們采用key IS NULL這種形式的搜索條件最多只能使用ref的訪問方法，而不是const的訪問方法。
  
• 對(duì)于某個(gè)包含多個(gè)索引列的二級(jí)索引來說，只要是最左邊的連續(xù)索引列是與常數(shù)的等值比較就可能采用ref的訪問方法，比方說下邊這幾個(gè)查詢：

SELECT * FROM single_table WHERE key_part1 = 'god like';SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 = 'legendary';SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 = 'legendary' AND key_part3 = 'penta kill';

ref_or_null

不僅想找出某個(gè)二級(jí)索引列的值等于某個(gè)常數(shù)的記錄，還想把該列的值為NULL的記錄也找出來。

SELECT * FROM single_table WHERE key1 = 'abc' OR key1 IS NULL;

range

SELECT * FROM single_table WHERE key2 IN (1438, 6328) OR (key2 >= 38 AND key2 <= 79);

當(dāng)然還可以使用全表掃描的方式來執(zhí)行這個(gè)查詢，不過也可以使用二級(jí)索引 + 回表的方式執(zhí)行
如果采用二級(jí)索引 + 回表的方式來執(zhí)行的話，那么此時(shí)的搜索條件就不只是要求索引列與常數(shù)的等值匹配了，而是索引列需要匹配某個(gè)或某些范圍的值，在本查詢中key2列的值只要匹配下列3個(gè)范圍中的任何一個(gè)就算是匹配成功了：

• key2的值是1438
• key2的值是6328
• key2的值在38和79之間。

這種利用索引進(jìn)行范圍匹配的訪問方法稱之為：range。

• 范圍1：key2 = 1438
• 范圍2：key2 = 6328
• 范圍3：key2 ∈ [38, 79]，注意這里是閉區(qū)間。

索引列等值匹配的情況稱之為單點(diǎn)區(qū)間，上邊所說的范圍1和范圍2都可以被稱為單點(diǎn)區(qū)間，像范圍3這種的我們可以稱為連續(xù)范圍區(qū)間。

index

SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key_part2 = 'abc';

由于key_part2并不是聯(lián)合索引idx_key_part最左索引列，所以無法使用ref或者range訪問方法來執(zhí)行這個(gè)語句。但是這個(gè)查詢符合下邊這兩個(gè)條件：

• 它的查詢列表只有3個(gè)列：key_part1, key_part2, key_part3，而索引idx_key_part又包含這三個(gè)列。
• 搜索條件中只有key_part2列。這個(gè)列也包含在索引idx_key_part中。

直接通過遍歷idx_key_part索引的葉子節(jié)點(diǎn)的記錄來比較key_part2 = 'abc'這個(gè)條件是否成立，把匹配成功的二級(jí)索引記錄的key_part1, key_part2, key_part3列的值直接加到結(jié)果集中就行了。（聚簇索引記錄要存儲(chǔ)所有用戶定義的列以及所謂的隱藏列，而二級(jí)索引記錄只需要存放索引列和主鍵），而且這個(gè)過程也不用進(jìn)行回表操作，所以直接遍歷二級(jí)索引比直接遍歷聚簇索引的成本要小很多，這種采用遍歷二級(jí)索引記錄的執(zhí)行方式稱之為：index。

all

對(duì)于InnoDB表來說也就是直接掃描聚簇索引，設(shè)計(jì)MySQL的大叔把這種使用全表掃描執(zhí)行查詢的方式稱之為：all。

二級(jí)索引 + 回表(不知道多少周目了)

一般情況下只能利用單個(gè)二級(jí)索引執(zhí)行查詢

SELECT * FROM single_table WHERE key1 = 'abc' AND key2 > 1000;

查詢優(yōu)化器會(huì)識(shí)別到這個(gè)查詢中的兩個(gè)搜索條件：

• key1 = 'abc'
• key2 > 1000

優(yōu)化器一般會(huì)根據(jù)single_table表的統(tǒng)計(jì)數(shù)據(jù)來判斷到底使用哪個(gè)條件到對(duì)應(yīng)的二級(jí)索引中查詢掃描的行數(shù)會(huì)更少，選擇那個(gè)掃描行數(shù)較少的條件到對(duì)應(yīng)的二級(jí)索引中查詢。將從該二級(jí)索引中查詢到的結(jié)果經(jīng)過回表得到完整的用戶記錄后再根據(jù)其余的WHERE條件過濾記錄。
一般來說，等值查找比范圍查找需要掃描的行數(shù)更少（也就是ref的訪問方法一般比range好，但這也不總是一定的，也可能采用ref訪問方法的那個(gè)索引列的值為特定值的行數(shù)特別多）
整個(gè)查詢過程可以分為兩個(gè)步驟：

• 步驟1：使用二級(jí)索引定位記錄的階段，也就是根據(jù)條件key1 = 'abc'從idx_key1索引代表的B+樹中找到對(duì)應(yīng)的二級(jí)索引記錄。
• 步驟2：回表階段，也就是根據(jù)上一步驟中找到的記錄的主鍵值進(jìn)行回表操作，也就是到聚簇索引中找到對(duì)應(yīng)的完整的用戶記錄，再根據(jù)條件key2 > 1000到完整的用戶記錄繼續(xù)過濾。將最終符合過濾條件的記錄返回給用戶。

因?yàn)?strong>二級(jí)索引的節(jié)點(diǎn)中的記錄只包含索引列和主鍵，所以在步驟1中使用idx_key1索引進(jìn)行查詢時(shí)只會(huì)用到與key1列有關(guān)的搜索條件，其余條件，比如key2 > 1000這個(gè)條件在步驟1中是用不到的，只有在步驟2完成回表操作后才能繼續(xù)針對(duì)完整的用戶記錄中繼續(xù)過濾。

明確range訪問方法使用的范圍區(qū)間

B+樹索引來說，只要索引列和常數(shù)使用=、<=>、IN、NOT IN、IS NULL、IS NOT NULL、>、<、>=、<=、BETWEEN、!=（不等于也可以寫成<>）或者LIKE操作符連接起來，就可以產(chǎn)生一個(gè)所謂的區(qū)間。

LIKE操作符比較特殊，只有在匹配完整字符串或者匹配字符串前綴時(shí)才可以利用索引

#下面兩句SQL效果相同
SELECT * FROM single_table WHERE key2 IN (1438, 6328);
SELECT * FROM single_table WHERE key2 = 1438 OR key2 = 6328;

所有搜索條件都可以使用某個(gè)索引的情況

SELECT * FROM single_table WHERE key2 > 100 AND key2 > 200;

這個(gè)查詢中的搜索條件都可以使用到key2，也就是說每個(gè)搜索條件都對(duì)應(yīng)著一個(gè)idx_key2的范圍區(qū)間。這兩個(gè)小的搜索條件使用AND連接起來，也就是要取兩個(gè)范圍區(qū)間的交集，在我們使用range訪問方法執(zhí)行查詢時(shí)，使用的idx_key2索引的范圍區(qū)間的確定過程就如下圖所示：

key2 > 100和key2 > 200交集當(dāng)然就是key2 > 200了，也就是說上邊這個(gè)查詢使用idx_key2的范圍區(qū)間就是(200, +∞)。

SELECT * FROM single_table WHERE key2 > 100 OR key2 > 200;

OR意味著需要取各個(gè)范圍區(qū)間的并集，所以上邊這個(gè)查詢?cè)谖覀兪褂?code>range訪問方法執(zhí)行查詢時(shí)，使用的idx_key2索引的范圍區(qū)間的確定過程就如下圖所示：

也就是說上邊這個(gè)查詢使用idx_key2的范圍區(qū)間就是(100， +∞)。

有的搜索條件無法使用索引的情況

SELECT * FROM single_table WHERE key2 < 100 AND common_field = 'abc';

請(qǐng)注意，這個(gè)查詢語句中能利用的索引只有idx_key2一個(gè)，而idx_key2這個(gè)二級(jí)索引的記錄中又不包含common_field這個(gè)字段，所以在使用二級(jí)索引idx_key2定位記錄的階段用不到common_field = 'abc'這個(gè)條件，這個(gè)條件是在回表獲取了完整的用戶記錄后才使用的，而范圍區(qū)間是為了到索引中取記錄中提出的概念，所以在確定范圍區(qū)間的時(shí)候不需要考慮common_field = 'abc'這個(gè)條件，我們?cè)跒槟硞€(gè)索引確定范圍區(qū)間的時(shí)候只需要把用不到相關(guān)索引的搜索條件替換為TRUE就好了。

SELECT * FROM single_table WHERE key2 > 100 AND TRUE;

SELECT * FROM single_table WHERE key2 < 100;

使用OR的情況：

SELECT * FROM single_table WHERE key2 < 100 OR common_field = 'abc';

SELECT * FROM single_table WHERE key2 < 100 OR TRUE;
#化簡(jiǎn)一下
SELECT * FROM single_table WHERE TRUE;

強(qiáng)制使用idx_key2執(zhí)行查詢的話，對(duì)應(yīng)的范圍區(qū)間就是(-∞, +∞)，也就是需要將全部二級(jí)索引的記錄進(jìn)行回表，這個(gè)代價(jià)肯定比直接全表掃描都大了。也就是說一個(gè)使用到索引的搜索條件和沒有使用該索引的搜索條件使用OR連接起來后是無法使用該索引的。

復(fù)雜搜索條件下找出范圍匹配的區(qū)間

SELECT * FROM single_table WHERE (key1 > 'xyz' AND key2 = 748 ) OR(key1 < 'abc' AND key1 > 'lmn') OR(key1 LIKE '%suf' AND key1 > 'zzz' AND (key2 < 8000 OR common_field = 'abc')) ;

可以看到他可能走的是idx_key1，idx_key2索引列
假設(shè)使用idx_key1執(zhí)行查詢
like '%suf'不會(huì)使用索引

(key1 > 'xyz' AND true) OR
(key1 < 'abc' AND key1 > 'lmn') OR
(true AND key1 > 'zzz' AND ( true OR true)) ;化簡(jiǎn)一下
(key1 > 'xyz') OR
(key1 < 'abc' AND key1 > 'lmn') OR
(key1 > 'zzz') ;
x = 78 y = 79 z = 7A
a = 61 b = 62 c = 63
l = 6C m = 6D n = 6Ekey1 < 'abc' AND key1 > 'lmn'永遠(yuǎn)為FALSE
(key1 > 'xyz') OR (key1 > 'zzz')key1 > 'xyz' 二級(jí)索引（范圍掃描） + 回表

假設(shè)使用idx_key2執(zhí)行查詢

(TRUE AND key2 = 748 ) OR
(TRUE AND TRUE) OR
(TRUE AND TRUE AND (key2 < 8000 OR TRUE))化簡(jiǎn)一下
key2 = 748 OR TRUETRUE 代表著掃描二級(jí)索引（所有記錄）+回表

索引合并

#如果看不下去就先看練習(xí)把！

MySQL在一般情況下執(zhí)行一個(gè)查詢時(shí)最多只會(huì)用到單個(gè)二級(jí)索引，也可能在一個(gè)查詢中使用到多個(gè)二級(jí)索引index merge。

Intersection合并

Intersection翻譯過來的意思是交集。

SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b';

假設(shè)這個(gè)查詢使用Intersection合并的方式執(zhí)行的話，那這個(gè)過程就是這樣的：

• 從idx_key1二級(jí)索引對(duì)應(yīng)的B+樹中取出key1 = 'a'的相關(guān)記錄。
• 從idx_key3二級(jí)索引對(duì)應(yīng)的B+樹中取出key3 = 'b'的相關(guān)記錄。
• 二級(jí)索引的記錄都是由索引列 + 主鍵構(gòu)成的，所以可以計(jì)算出這兩個(gè)結(jié)果集中id值的交集。
• 按照上一步生成的id值列表進(jìn)行回表操作，也就是從聚簇索引中把指定id值的完整用戶記錄取出來，返回給用戶。

只讀取一個(gè)二級(jí)索引的成本：

• 按照某個(gè)搜索條件讀取一個(gè)二級(jí)索引

• 根據(jù)從該二級(jí)索引得到的主鍵值進(jìn)行回表操作，然后再過濾其他的搜索條件

讀取多個(gè)二級(jí)索引之后取交集成本：

• 按照不同的搜索條件分別讀取不同的二級(jí)索引

• 將從多個(gè)二級(jí)索引得到的主鍵值取交集，然后進(jìn)行回表操作

雖然讀取多個(gè)二級(jí)索引比讀取一個(gè)二級(jí)索引消耗性能，但是讀取二級(jí)索引的操作是順序I/O，而回表操作是隨機(jī)I/O，所以如果只讀取一個(gè)二級(jí)索引時(shí)需要回表的記錄數(shù)特別多，而讀取多個(gè)二級(jí)索引之后取交集的記錄數(shù)非常少，當(dāng)節(jié)省的因?yàn)?strong>回表而造成的性能損耗比訪問多個(gè)二級(jí)索引帶來的性能損耗更高時(shí)，讀取多個(gè)二級(jí)索引后取交集比只讀取一個(gè)二級(jí)索引的成本更低。

MySQL在某些特定的情況下才可能會(huì)使用到Intersection索引合并：

• 情況一：二級(jí)索引列是等值匹配的情況，對(duì)于聯(lián)合索引來說，在聯(lián)合索引中的每個(gè)列都必須等值匹配，不能出現(xiàn)只匹配部分列的情況。

比方說下邊這個(gè)查詢可能用到idx_key1和idx_key_part這兩個(gè)二級(jí)索引進(jìn)行Intersection索引合并的操作：

#官網(wǎng)是這樣說的，實(shí)際上我并沒有測(cè)出來索引合并。
SELECT * FROM single_table WHERE key1 = 'a' AND key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c';

下邊這兩個(gè)查詢就不能進(jìn)行Intersection索引合并

#第一個(gè)查詢是因?yàn)閷?duì)key1進(jìn)行了范圍匹配
SELECT * FROM single_table WHERE key1 > 'a' AND key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c';
#第二個(gè)查詢是因?yàn)槁?lián)合索引idx_key_part中的key_part2和key_part3列并沒有出現(xiàn)在搜索條件中，
#所以這兩個(gè)查詢不能進(jìn)行Intersection索引合并。
SELECT * FROM single_table WHERE key1 = 'a' AND key_part1 = 'a';

• 情況二：主鍵列可以是范圍匹配

SELECT * FROM single_table WHERE id > 100 AND key1 = 'a';

對(duì)于InnoDB的二級(jí)索引來說，記錄先是按照索引列進(jìn)行排序，如果該二級(jí)索引是一個(gè)聯(lián)合索引，那么會(huì)按照聯(lián)合索引中的各個(gè)列依次排序。而二級(jí)索引的用戶記錄是由索引列 + 主鍵構(gòu)成的，二級(jí)索引列的值相同的記錄可能會(huì)有好多條，這些索引列的值相同的記錄又是按照主鍵的值進(jìn)行排序的。

之所以在二級(jí)索引列都是等值匹配的情況下才可能使用Intersection索引合并，是因?yàn)橹挥性谶@種情況下根據(jù)二級(jí)索引查詢出的結(jié)果集是按照主鍵值排序的。

Intersection索引合并會(huì)把從多個(gè)二級(jí)索引中查詢出的主鍵值求交集，如果從各個(gè)二級(jí)索引中查詢的到的結(jié)果集本身就是已經(jīng)按照主鍵排好序的，那么求交集的過程就很方便啦。
假設(shè)某個(gè)查詢使用Intersection索引合并的方式從idx_key1和idx_key2這兩個(gè)二級(jí)索引中獲取到的主鍵值分別是：

• 從idx_key1中獲取到已經(jīng)排好序的主鍵值：1、3、5
• 從idx_key2中獲取到已經(jīng)排好序的主鍵值：2、3、4

那么求交集的過程就是這樣：逐個(gè)取出這兩個(gè)結(jié)果集中最小的主鍵值，如果兩個(gè)值相等，則加入最后的交集結(jié)果中，否則丟棄當(dāng)前較小的主鍵值，再取該丟棄的主鍵值所在結(jié)果集的后一個(gè)主鍵值來比較，直到某個(gè)結(jié)果集中的主鍵值用完了。

• 先取出這兩個(gè)結(jié)果集中較小的主鍵值做比較，因?yàn)?code>1 < 2，所以把idx_key1的結(jié)果集的主鍵值1丟棄，取出后邊的3來比較。

• 因?yàn)?code>3 > 2，所以把idx_key2的結(jié)果集的主鍵值2丟棄，取出后邊的3來比較。

• 因?yàn)?code>3 = 3，所以把3加入到最后的交集結(jié)果中，繼續(xù)兩個(gè)結(jié)果集后邊的主鍵值來比較。

• 后邊的主鍵值也不相等，所以最后的交集結(jié)果中只包含主鍵值3。

時(shí)間復(fù)雜度是O(n)，但是如果從各個(gè)二級(jí)索引中查詢出的結(jié)果集并不是按照主鍵排序的話，那就要先把結(jié)果集中的主鍵值排序完再來做上邊的那個(gè)過程，就比較耗時(shí)了。

按照有序的主鍵值去回表取記錄有個(gè)專有名詞，叫：Rowid Ordered Retrieval，簡(jiǎn)稱ROR

SELECT * FROM single_table WHERE key1 = 'a' AND id > 100;

假設(shè)這個(gè)查詢可以采用Intersection索引合并，我們理所當(dāng)然的以為這個(gè)查詢會(huì)分別按照id > 100這個(gè)條件從聚簇索引中獲取一些記錄，在通過key1 = 'a'這個(gè)條件從idx_key1二級(jí)索引中獲取一些記錄，然后再求交集，其實(shí)這樣就把問題復(fù)雜化了，沒必要從聚簇索引中獲取一次記錄。別忘了二級(jí)索引的記錄中都帶有主鍵值的，所以可以在從idx_key1中獲取到的主鍵值上直接運(yùn)用條件id > 100過濾就行了，這樣多簡(jiǎn)單。所以涉及主鍵的搜索條件只不過是為了從別的二級(jí)索引得到的結(jié)果集中過濾記錄罷了，是不是等值匹配不重要。

當(dāng)然，上邊說的情況一和情況二只是發(fā)生Intersection索引合并的必要條件，不是充分條件。也就是說即使情況一、情況二成立，也不一定發(fā)生Intersection索引合并，這得看優(yōu)化器的心情。優(yōu)化器只有在單獨(dú)根據(jù)搜索條件從某個(gè)二級(jí)索引中獲取的記錄數(shù)太多，導(dǎo)致回表開銷太大，而通過Intersection索引合并后需要回表的記錄數(shù)大大減少時(shí)才會(huì)使用Intersection索引合并。

Union合并

SELECT * FROM single_table WHERE key1 = 'a' OR key3 = 'b';

Intersection是交集的意思，這適用于使用不同索引的搜索條件之間使用AND連接起來的情況；Union是并集的意思，適用于使用不同索引的搜索條件之間使用OR連接起來的情況。
MySQL在某些特定的情況下才可能會(huì)使用到Union索引合并：

• 情況一：二級(jí)索引列是等值匹配的情況，對(duì)于聯(lián)合索引來說，在聯(lián)合索引中的每個(gè)列都必須等值匹配，不能出現(xiàn)只出現(xiàn)匹配部分列(> <)的情況。

idx_key1和idx_key_part這兩個(gè)二級(jí)索引進(jìn)行Union索引合并的操作：

SELECT * FROM single_table WHERE key1 = 'a' 
OR ( key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c');

下邊這兩個(gè)查詢就不能進(jìn)行Union索引合并：

SELECT * FROM single_table WHERE key1 > 'a' OR (key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c');SELECT * FROM single_table WHERE key1 = 'a' OR key_part1 = 'a';

第一個(gè)查詢是因?yàn)閷?duì)key1進(jìn)行了范圍匹配，第二個(gè)查詢是因?yàn)?strong>聯(lián)合索引idx_key_part中的key_part2和key_part3列并沒有出現(xiàn)在搜索條件中，所以這兩個(gè)查詢不能進(jìn)行Union索引合并。

• 情況二：主鍵列可以是范圍匹配
• 情況三：使用Intersection索引合并的搜索條件

使用Intersection索引合并的方式得到的主鍵集合和其他方式得到的主鍵集合取交集。

explain SELECT * FROM single_table WHERE key_part1 = 'a' AND key_part2 = 'b' 
AND key_part3 = 'c' OR (key1 = 'a' AND key3 = 'b');

優(yōu)化器可能采用這樣的方式來執(zhí)行這個(gè)查詢：

• 先按照搜索條件key1 = 'a' AND key3 = 'b'從索引idx_key1和idx_key3中使用Intersection索引合并的方式得到一個(gè)主鍵集合。
• 再按照搜索條件key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c'從聯(lián)合索引idx_key_part中得到另一個(gè)主鍵集合。
• 采用Union索引合并的方式把上述兩個(gè)主鍵集合取并集，然后進(jìn)行回表操作，將結(jié)果返回給用戶。

當(dāng)然，查詢條件符合了這些情況也不一定就會(huì)采用Union索引合并，也得看優(yōu)化器的心情。優(yōu)化器只有在單獨(dú)根據(jù)搜索條件從某個(gè)二級(jí)索引中獲取的記錄數(shù)比較少，通過Union索引合并后進(jìn)行訪問的代價(jià)比全表掃描更小時(shí)才會(huì)使用Union索引合并。

Sort-Union合并

explain SELECT * FROM single_table WHERE key1 < 'a' OR key3 > 'z';

• 先根據(jù)key1 < 'a'條件從idx_key1二級(jí)索引中獲取記錄，并按照記錄的主鍵值進(jìn)行排序
• 再根據(jù)key3 > 'z'條件從idx_key3二級(jí)索引中獲取記錄，并按照記錄的主鍵值進(jìn)行排序
• 因?yàn)樯鲜龅?strong>兩個(gè)二級(jí)索引主鍵值都是排好序的，剩下的操作和Union索引合并方式就一樣了。

這種Sort-Union索引合并比單純的Union索引合并多了一步對(duì)二級(jí)索引記錄的主鍵值排序的過程。

union適用于單條索引查詢出來數(shù)據(jù)很少、intersect適用于單條查詢出來數(shù)據(jù)比較多。

索引合并注意事項(xiàng)

聯(lián)合索引替代Intersection索引合并

索引合并練習(xí)

MySQL 索引合并技巧!

explain select * from order_info 
where period = 202201 and modified = '2019-01-06 18:00:00';
A ∩ B
通過執(zhí)行計(jì)劃可以看到走的modified索引樹，拿到modified數(shù)據(jù)再回表查找period數(shù)據(jù)。

explain select * from order_info 
where period = 202201 or modified = '2019-01-06 18:00:00';
A ∪ B
這樣想象
A 走的是 period 索引
B 走的是 modified 索引
假如讀到這些數(shù)據(jù)
A ：2 1 4
B ：3 1 2
or 需要去重 兩個(gè)for循環(huán)的話是o（n次方）
排序后去重的話
排序是ologn 查找可以用二分ologn
ologn + ologn = ologn 

https://dev.mysql.com/doc/refman/8.4/en/index-merge-optimization.html

多表使用or檢索就很慢

SELECT * FROM innodb_tableWHERE primary_key#主鍵 < 10 AND key_col1#普通索引 = 20;SELECT * FROM tbl_nameWHERE key1_part1#聯(lián)合索引第一部分 = 1 AND key1_part2#聯(lián)合索引第二部分 = 2 AND key2#普通索引 = 2;

explain select * from order_info  
where period = 202201 and modified = '2019-01-06 18:00:00';explain select * from order_info  
where period < 202201 and modified = '2019-01-06 18:00:00';explain select * from order_info  
where period = 202201 and modified = '2019-01-06 18:00:00' and phone = '52197927747';

explain select * from order_info
where id < 202201 and modified = '2019-01-06 18:00:00';

SELECT * FROM t1WHERE key1 = 1 OR key2 = 2 OR key3 = 3;SELECT * FROM innodb_tableWHERE (key1 = 1 AND key2 = 2)OR (key3 = 'foo' AND key4 = 'bar') AND key5 = 5;

explain select * from order_info
where id < 202201 or modified = '2019-01-06 18:00:00';

結(jié)果集1：取出id 的主鍵值排序（本身是有序的）
結(jié)果集2：取出modified 的主鍵值排序 等值查詢的時(shí)候modified相同的時(shí)候是按照主鍵排序的。
合并union兩個(gè)結(jié)果集去重。

explain select * from order_info
where id < 202201 or modified < '2019-01-06 18:00:00';

explain select * from order_info
where id < 201901 or modified < '2019-01-06 18:00:00';

結(jié)果集1：取出id 的主鍵值排序（本身是有序的）
結(jié)果集2：取出modified 的主鍵值排序 此時(shí)范圍查詢，再modified索引樹主鍵就是無序的，因此需要sort union。

總結(jié)

所有結(jié)論都需要反復(fù)測(cè)試！如果有錯(cuò)誤歡迎指正！一起努力！
如果喜歡的話，請(qǐng)點(diǎn)個(gè)贊吧就算鼓勵(lì)我一下。