前言

作為一個DBA或者運維人員，有時候發(fā)現(xiàn)數(shù)據(jù)庫有慢查詢語句需要優(yōu)化，那么我們就需要先了解查詢是怎么執(zhí)行的。我們知道，MySQL Server 有一個查詢優(yōu)化器的模塊，一條查詢語句進行語法解析之后就會被交給查詢優(yōu)化器來進行優(yōu)化，優(yōu)化的結(jié)果就是生成一個執(zhí)行計劃 ，這個執(zhí)行計劃表明了應該使用哪些索引進行查詢，表之間的連接順序是怎樣的，最后會按照執(zhí)行計劃中的步驟調(diào)用存儲引擎提供的方法來真正的執(zhí)行查詢，并將查詢結(jié)果返回給用戶。接下來我們先了解一下Mysql怎么執(zhí)行單表查詢，方便后續(xù)學習優(yōu)化。

先創(chuàng)建一個測試表。

mysql> 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;
Query OK, 0 rows affected (0.15 sec)

我們?yōu)檫@個 single_table 表建立了1個聚簇索引和4個二級索引，分別是：
為 id 列建立的聚簇索引。
為 key1 列建立的 idx_key1 二級索引。
為 key2 列建立的 idx_key2 二級索引，而且是唯一二級索引。
為 key3 列建立的 idx_key3 二級索引，普通二級索引。
為 key_part1 、 key_part2 、 key_part3 列建立的 idx_key_part 二級索引，這也是一個聯(lián)合索引。
然后我們需要為這個表插入10000行記錄，除 id 列外其余的列都插入隨機值。可以使用下面的腳步（親測）：
以下是一個示例的腳本，用于向上述表中插入一萬條行記錄，其中 id 列從 1 到 10000 逐漸增長，key2 列每行的值都是唯一的，其他列都生成隨機值插入：

-- 生成隨機字符串函數(shù)
DELIMITER $$
CREATE FUNCTION random_string(length INT) RETURNS VARCHAR(100)
BEGIN
    DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
    DECLARE result VARCHAR(100) DEFAULT '';
    DECLARE i INT DEFAULT 0;
    
    WHILE i < length DO
        SET result = CONCAT(result, SUBSTRING(chars_str, FLOOR(1 + RAND() * 62), 1));
        SET i = i + 1;
    END WHILE;
    
    RETURN result;
END$$
DELIMITER ;

-- 插入一萬條行記錄
DELIMITER ;;
CREATE PROCEDURE insert_data()
BEGIN
    DECLARE i INT DEFAULT 1;
    
    WHILE i <= 10000 DO
        INSERT INTO single_table (id, key1, key2, key3, key_part1, key_part2, key_part3, common_field)
        VALUES (i, random_string(10), i, random_string(10), random_string(10), random_string(10), random_string(10), random_string(10));
        
        SET i = i + 1;
    END WHILE;
    
    COMMIT;
END;;
DELIMITER ;

-- 執(zhí)行插入數(shù)據(jù)的腳本
CALL insert_data();

訪問方法（access method）

對于單個表的查詢來說，設計MySQL的人把查詢的執(zhí)行方式大致分為下邊兩種：

• 使用全表掃描進行查詢
  這種執(zhí)行方式很好理解，就是把表的每一行記錄都掃一遍，把符合搜索條件的記錄加入到結(jié)果集就行了。所有的查詢都可以使用這種方式執(zhí)行，當然，這種也是最笨的執(zhí)行方式。
• 使用索引進行查詢
  因為直接使用全表掃描的方式執(zhí)行查詢要遍歷很多記錄，所以代價可能太大了。如果查詢語句中的搜索條件可以使用到某個索引，那直接使用索引來執(zhí)行查詢可能會加快查詢執(zhí)行的時間。使用索引來執(zhí)行查詢的方式有多種，可以細分為以下幾種：
  • 針對主鍵或唯一二級索引的等值查詢
  • 針對普通二級索引的等值查詢
  • 針對索引列的范圍查詢
  • 直接掃描整個索引
    我們把 MySQL 執(zhí)行查詢語句的方式稱之為訪問方法或者訪問類型 。同一個查詢語句可能可以使用多種不同的訪問方法來執(zhí)行，雖然最后的查詢結(jié)果都是一樣的，但是執(zhí)行的時間可能差別很大。下邊詳細說說各種訪問方法。

const（常數(shù)）

有的時候我們可以通過主鍵列來定位一條記錄，比方說這個查詢：
SELECT * FROM single_table WHERE id = 1438;
MySQL 會直接利用主鍵值在聚簇索引中定位對應的用戶記錄，就像這樣：

如上是一個簡略版的聚簇索引對應的復雜的 B+ 樹，省略了頁的結(jié)構(gòu)，直接把所有的葉子節(jié)點的記錄都放在一起展示，而且記錄中只展示了需要關注的索引列，對于 single_table 表的聚簇索引來說，索引列就是 id 列。重點就是： B+ 樹葉子節(jié)點中的記錄是按照索引列排序的，對于的聚簇索引來說，它對應的 B+ 樹葉子節(jié)點中的記錄就是按照 id 列排序的，根據(jù)主鍵值定位一條記錄的速度很快。類似的，我們根據(jù)唯一二級索引列來定位一條記錄的速度也是很快的，如下邊這個查詢（key2也是唯一索引）：
SELECT * FROM single_table WHERE key2 = 3841;
這個查詢的執(zhí)行過程的示意圖就如下：

該查詢的執(zhí)行分兩步：

• 先從 idx_key2 對應的 B+ 樹索引中根據(jù) key2 列與常數(shù)的等值比較條件定位到一條二級索引記錄，獲取到該記錄的 id 值
• 在根據(jù)獲取到的id到聚簇索引中獲取到完整的用戶記錄。

在MySQL中認為通過主鍵或者唯一二級索引列與常數(shù)的等值比較來定位一條記錄都是很快的，所以把這種通過主鍵或者唯一二級索引列來定位一條記錄的訪問方法定義為： const ，意思是常數(shù)級別的，代價很小是可以忽略不計的。不過這種 const 訪問方法只能在主鍵列或者唯一二級索引列和一個常數(shù)進行等值比較時才有效，如果主鍵或者唯一二級索引是由多個列構(gòu)成的話，索引中的每一個列都需要與常數(shù)進行等值比較，這個const 訪問方法才有效（這是因為只有該索引中全部列都采用等值比較才可以定位到唯一的一條記錄）。
對于唯一二級索引來說，查詢該列為 NULL 值的情況比較特殊，比如這樣：
SELECT * FROM single_table WHERE key2 IS NULL;
因為唯一二級索引列并不限制NULL 值的數(shù)量，所以上述語句可能訪問到多條記錄，也就是說上邊這個語句不能使用 const 訪問方法來執(zhí)行（NULL的訪問方法我們下面說）。

ref（refs 復數(shù)，理解為多個？）

有時候我們對某個普通的二級索引列與常數(shù)進行等值比較，比如這樣：
SELECT * FROM single_table WHERE key1 = ‘a(chǎn)bc’;
對于這個查詢，我們可以選擇全表掃描來逐一對比搜索條件是否滿足要求，我們也可以先使用二級索引找到對應記錄的 id 值，然后再回表到聚簇索引中查找完整的用戶記錄。由于普通二級索引并不限制索引列值的唯一性，所以很可能找到多條對應的記錄，也就是說使用二級索引來執(zhí)行查詢的代價取決于等值匹配到的二級索引條數(shù)。如果匹配的記錄較少，則回表的代價還是比較低的，所以 MySQL 可能選擇使用索引而不是全表掃描的方式來執(zhí)行查詢。在MySQL中把這種搜索條件為二級索引列與常數(shù)等值比較，采用二級索引來執(zhí)行查詢的訪問方法稱為： ref 。采用 ref 訪問方法執(zhí)行的查詢?nèi)缦聢D所示：

從上圖可以看出，對于普通的二級索引來說，通過索引列進行等值比較后可能匹配到多條連續(xù)的記錄，而不是像主鍵或者唯一二級索引那樣最多只能匹配到1條記錄，所以一般這種 ref 訪問方法比 const 訪問方法效率會差一點，但是在二級索引等值比較時匹配的記錄數(shù)較少時的效率還是很高的（但如果匹配的二級索引記錄太多那么回表的成本就很大）。不過需要注意下邊兩種情況：

• 二級索引列值為 NULL 的情況
  不論是普通的二級索引，還是唯一二級索引，它們的索引列對包含 NULL 值的數(shù)量并不限制，所以我們采用key IS NULL 這種形式的搜索條件最多只能使用 ref 的訪問方法，而不是 const 的訪問方法。

對于某個包含多個索引列的二級索引來說，只要是最左邊的連續(xù)索引列是與常數(shù)的等值比較就可能采用 ref的訪問方法，比如下邊這幾個查詢：
SELECT * FROM single_table WHERE key_part1 = ‘good’;
SELECT * FROM single_table WHERE key_part1 = ‘good’ AND key_part2 = ‘legendary’;
SELECT * FROM single_table WHERE key_part1 = ‘good’ AND key_part2 = ‘legendary’ AND key_part3 = ‘triple kill’;

但是如果最左邊的連續(xù)索引列并不全部是等值比較的話，它的訪問方法就不能稱為 ref 了，比如：
SELECT * FROM single_table WHERE key_part1 = ‘god like’ AND key_part2 > ‘legendary’;

ref_or_null

有時候我們不僅想找出某個二級索引列的值等于某個常數(shù)的記錄，還想把該列的值為 NULL 的記錄也找出來，如下面這個查詢：
SELECT * FROM single_demo WHERE key1 = ‘a(chǎn)bc’ OR key1 IS NULL;
當使用二級索引而不是全表掃描的方式執(zhí)行該查詢時，這種類型的查詢使用的訪問方法就稱為ref_or_null ， ref_or_null 訪問方法的執(zhí)行過程大致如下：

可以看到，上邊的查詢相當于先分別從 idx_key1 索引對應的 B+ 樹中找出 key1 IS NULL 和 key1 = ‘a(chǎn)bc’ 的兩個連續(xù)的記錄范圍，然后根據(jù)這些二級索引記錄中的 id 值再回表查找完整的用戶記錄。

range

上面介紹的幾種訪問方法都是在對索引列與某一個常數(shù)進行等值比較的時候才可能使用到（ ref_or_null 比較特別，還包含了值為 NULL 的情況），但是有時候我們使用的搜索條件更復雜，比如下面這個查詢：
SELECT * FROM single_table WHERE key2 IN (1438, 6328) OR (key2 >= 38 AND key2 <= 79);
我們當然可以使用全表掃描的方式來執(zhí)行這個查詢，不過也可以使用 二級索引 + 回表 的方式執(zhí)行，如果采用 二級索引 + 回表的方式來執(zhí)行的話，那么此時的搜索條件就不只是要求索引列與常數(shù)的等值匹配了，而是索引列需要匹配某個或某些范圍的值，在本查詢中 key2 列的值只要匹配下列3個范圍中的任何一個就算是匹配成功了：

• key2 的值是 1438
• key2 的值是 6328
• key2 的值在 38 和 79 之間。
  在MySQL中我們把這種利用索引進行范圍匹配的訪問方法稱之為： range 。
  注意：此處所說的使用索引進行范圍匹配中的 索引 可以是聚簇索引，也可以是二級索引。
  如果把這幾個所謂的 key2 列的值需要滿足的范圍 在數(shù)軸上體現(xiàn)出來的話，如下所示：
  
  從數(shù)學的角度看，每一個所謂的范圍都是數(shù)軸上的一個區(qū)間 ，3個范圍也就對應著3個區(qū)間：
• 范圍1： key2 = 1438
• 范圍2： key2 = 6328
• 范圍3： key2 ∈ [38, 79] ，注意這里是閉區(qū)間。
  我們可以把那種索引列等值匹配的情況稱之為單點區(qū)間 ，如范圍1和范圍2，而像范圍3這種的我們可以稱為連續(xù)范圍區(qū)間。

index

看下邊這個查詢：
SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key_part2 = ‘a(chǎn)bc’;
由于 key_part2 并不是聯(lián)合索引 idx_key_part 最左索引列，所以我們無法使用 ref 或者 range 訪問方法來執(zhí)行這個語句。但是這個查詢符合下邊這兩個條件：

• 它的查詢列表只有3個列： key_part1 , key_part2 , key_part3 ，而索引 idx_key_part 又包含這三個列。
• 搜索條件中只有 key_part2 列。這個列也包含在索引 idx_key_part 中。
  也就是說我們可以直接通過遍歷 idx_key_part 索引的葉子節(jié)點的記錄來比較 key_part2 = ‘a(chǎn)bc’ 這個條件是否成立，把匹配成功的二級索引記錄的 key_part1 , key_part2 , key_part3 列的值直接加到結(jié)果集中就行了。由于二級索引記錄比聚簇索記錄小的多（聚簇索引記錄要存儲所有用戶定義的列以及所謂的隱藏列，而二級索引記錄只需要存放索引列和主鍵），而且這個過程也不用進行回表操作，所以直接遍歷二級索引比直接遍歷聚簇索引的成本要小很多，在MySQL中就把這種采用遍歷二級索引記錄的執(zhí)行方式稱之為： index 。

all

最后一種查詢執(zhí)行方式就是全表掃描，對于 InnoDB 表來說也就是直接掃描聚簇索引，在MySQL 中我們把這種使用全表掃描執(zhí)行查詢的方式稱之為： all 。

注意事項

二級索引 + 回表

一般情況下只能選擇使用一個二級索引執(zhí)行查詢，比方說下邊的這個查詢：
SELECT * FROM single_table WHERE key1 = ‘a(chǎn)bc’ AND key2 > 1000;
注意我們是對key1和key2分別建立了索引的。查詢優(yōu)化器會識別到這個查詢中的兩個搜索條件：

• key1 = ‘a(chǎn)bc’
• key2 > 1000

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

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

需要注意的是，因為二級索引的節(jié)點中的記錄只包含索引列和主鍵，所以在步驟1中使用idx_key1 索引進行查詢時只會用到與 key1 列有關的搜索條件，其余條件，比如 key2 > 1000 這個條件在步驟1中是用不到的，只有在步驟2完成回表操作后才能繼續(xù)針對完整的用戶記錄中繼續(xù)過濾。

其次，一般情況下執(zhí)行一個查詢只會用到單個二級索引，不過還是有特殊情況的，后續(xù)詳細說明。

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

對于 B+ 樹索引來說，只要索引列和常數(shù)使用 = 、 <=> 、 IN 、 NOT IN 、 IS NULL 、 IS NOT NULL 、> 、 < 、 >= 、 <= 、 BETWEEN 、 != （不等于也可以寫成 <> ）或者 LIKE 操作符連接起來，就會產(chǎn)生一個所謂的區(qū)間 。
說明：LIKE操作符比較特殊，只有在匹配完整字符串或者匹配字符串前綴時才可以利用索引。
IN操作符的效果和若干個等值匹配操作符=之間用OR連接起來是一樣的，也就是說會產(chǎn)生多個單點區(qū)間，比如下邊這兩個語句的效果是一樣的：
SELECT * FROM single_table WHERE key2 IN (1438, 6328);
SELECT * FROM single_table WHERE key2 = 1438 OR key2 = 6328;

在日常的工作中，一個查詢的 WHERE 子句可能有很多個小的搜索條件，這些搜索條件使用 AND 或者 OR操作符連接起來，如下：
condition1 AND condition2 ：只有當 condition1 和 condition2 都為 TRUE 時整個表達式才為 TRUE 。
condition1 OR condition2 ：只要 condition1 或者 condition2 中有一個為 TRUE 整個表達式就為 TRUE 。
當我們想使用 range 訪問方法來執(zhí)行一個查詢語句時，重點就是找出該查詢可用的索引以及這些索引對應的范圍區(qū)間。下面分兩種情況看一下如何從由 AND 或 OR 組成的復雜搜索條件中正確提取出范圍區(qū)間。

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

有時候每個搜索條件都可以使用到某個索引，比如下邊這個查詢語句:
SELECT * FROM single_table WHERE key2 > 100 AND key2 > 200;
這個查詢中的搜索條件都是使用 key2 ，也就是說每個搜索條件都對應著一個 idx_key2 的范圍區(qū)間。這兩個小的搜索條件使用 AND連接起來，也就是要取兩個范圍區(qū)間的交集，在我們使用 range 訪問方法執(zhí)行查詢時，使用的idx_key2 索引的范圍區(qū)間的確定過程就如下圖所示：

key2 > 100 和 key2 > 200 交集就是 key2 > 200 了，也就是說上邊這個查詢使用 idx_key2 的范圍區(qū)間就 是 (200, +∞) 。我們再看一下使用 OR 將多個搜索條件連接在一 起的情況:
SELECT * FROM single_table WHERE key2 > 100 OR key2 > 200;
OR 意味著需要取各個范圍區(qū)間的并集，所以上邊這個查詢在我們使用 range 訪問方法執(zhí)行查詢時，使用的idx_key2 索引的范圍區(qū)間的確定過程就如下圖所示:

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

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

比如下面這個查詢:
SELECT * FROM single_table WHERE key2 > 100 AND common_field = ‘a(chǎn)bc’;
注意，這個查詢語句只能使用一個索引 idx_key2，但 idx_key2 這個二級索引的記錄中不包含 common_field 這個字段，所以在使用二級索引 idx_key2 定位記錄的階段用不到 common_field = ‘a(chǎn)bc’ 這個條件，這個條件是在回表獲取了完整的用戶記錄后比較才使用的，而范圍區(qū)間是為了到索引中取記錄中提出的概念，所以在確定范圍區(qū)間的時候不需要考慮common_field = ‘a(chǎn)bc’ 這個條件，我們在為某個索引確定范圍區(qū)間的時候只需要把用不到相關索引的搜索條件用 TRUE暫時替換就可以了。

注意：把用不到索引的搜索條件替換為TRUE，是因為我們不打算使用這些條件進行在該索引上進行過濾，所以不管索引的記錄滿不滿足這些條件，我們都會把它們選取出來，后續(xù)回表的時候再使用它們進行過濾。所以上面的查詢中用不到 common_field 這個搜索條件，替換后如下：
SELECT * FROM single_table WHERE key2 > 100 AND TRUE;
化簡之后如下：
SELECT * FROM single_table WHERE key2 > 100;
也就是總的來說上面那個查詢使用到的索引 idx_key2 的范圍區(qū)間就是: (100, +∞) 。

下面再來看一下使用 OR 的情況:
SELECT * FROM single_table WHERE key2 > 100 OR common_field = ‘a(chǎn)bc’;
同樣，我們把使用不到 idx_key2 索引的搜索條件替換為 TRUE :
SELECT * FROM single_table WHERE key2 > 100 OR TRUE;
接著化簡:
SELECT * FROM single_table WHERE TRUE;
這也就說如果我們一定要使用 idx_key2 執(zhí)行查詢的話，對應的查詢范圍區(qū)間就是 (-∞, +∞) ，也就是需要將全部二級索引的記錄進行回表，這個代價肯定比直接全表掃描都大。也就是說一個使用到索引的搜索條件和沒有使用該索引的搜索條件使用OR連接起來后是無法使用該索引的。

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

有的查詢的搜索條件可能特別復雜，看起來就比較頭疼，但其分析一下還是能找出范圍區(qū)間的。如下：
SELECT * FROM single_table WHERE
(key1 > ‘xyz’ AND key2 = 748 ) OR
(key1 < ‘a(chǎn)bc’ AND key1 > ‘lmn’) OR
(key1 LIKE ‘%suf’ AND key1 > ‘zzz’ AND (key2 < 8000 OR common_field = ‘a(chǎn)bc’)) ;
這個搜索條件看起來相當復雜了，我們需要透過現(xiàn)象看本質(zhì)，按下面這個方法進行分析：

• 首先查看 WHERE 子句中的搜索條件都涉及到了哪些列，哪些列可能使用到索引。
  這個查詢的搜索條件涉及到了 key1 、 key2 、 common_field 這3個列，然后 key1 列有普通的二級索引 idx_key1 ， key2 列有唯一二級索引 idx_key2 。
• 對于那些可能用到的索引，分析它們的范圍區(qū)間。因為一般只能使用一個索引，我們依次分析：
  • 假設我們使用 idx_key1 執(zhí)行查詢
    我們需要把那些用不到該索引的搜索條件暫時移除掉，移除方法也簡單，直接把它們替換為 TRUE 就好了。上邊的查詢中除了有關 key2 和 common_field 列不能使用到 idx_key1 索引外， key1 LIKE ‘%suf’ 也使用不到索引（不是前綴索引），所以把這些搜索條件替換為 TRUE 之后就如下：
    (key1 > ‘xyz’ AND TRUE ) OR
    (key1 < ‘a(chǎn)bc’ AND key1 > ‘lmn’) OR
    (TRUE AND key1 > ‘zzz’ AND (TRUE OR TRUE))
    化簡一下上邊的搜索條件就是下邊這樣：
    (key1 > ‘xyz’) OR
    (key1 < ‘a(chǎn)bc’ AND key1 > ‘lmn’) OR
    (key1 > ‘zzz’)
    替換掉永遠為 TRUE 或 FALSE 的條件
    因為符合 key1 < ‘a(chǎn)bc’ AND key1 > ‘lmn’ 永遠為 FALSE ，所以上邊的搜索條件繼續(xù)簡寫如下：
    (key1 > ‘xyz’) OR (key1 > ‘zzz’)
    繼續(xù)化簡區(qū)間
    key1 > ‘xyz’ 和 key1 > ‘zzz’ 之間使用 OR 操作符連接起來的，意味著要取并集，所以最終的結(jié)果化簡的到的區(qū)間就是： key1 > xyz 。也就是說：上邊那個有很多索條件的查詢語句如果使用idx_key1 索引執(zhí)行查詢的話，需要把滿足 key1 > xyz 的二級索引記錄都取出來，然后拿著這些記錄的id再進行回表，得到完整的用戶記錄之后再使用其他的搜索條件進行過濾。
  • 假設我們使用 idx_key2 執(zhí)行查詢
    我們需要把那些用不到該索引的搜索條件暫時使用 TRUE 條件替換掉，所以有關 key1 和common_field 的搜索條件都需要被替換掉，替換后結(jié)果如下：
    (TRUE AND key2 = 748 ) OR
    (TRUE AND TRUE) OR
    (TRUE AND TRUE AND (key2 < 8000 OR TRUE))
    而key2 < 8000 OR TRUE 的結(jié)果肯定是 TRUE ，也就是說化簡之后的搜索條件成這樣：
    key2 = 748 OR TRUE
    這個化簡之后的結(jié)果就更簡單了：
    TRUE
    這個結(jié)果也就說明如果我們要使用 idx_key2 索引執(zhí)行查詢語句的話，需要掃描 idx_key2 二級索引的所有記錄，然后再回表，所以這種情況下不會使用 idx_key2 索引的。

索引合并

前邊講到MySQL在一般情況下執(zhí)行一個查詢時最多只會用到一個二級索引，不過在某些特殊情況下也可能在一個查詢中使用到多個二級索引，我們把在MySQL 中這種使用到多個索引來完成一次查詢的執(zhí)行方法稱之為： index merge ，具體的索引合并方法下面三種：

Intersection合并

Intersection 翻譯過來就是交集的意思 。就是指某個查詢可以使用多個二級索引，將從多個二級索引中查詢到的結(jié)果取交集，比方說下邊這個查詢：
SELECT * FROM single_table WHERE key1 = ‘a(chǎn)’ AND key3 = ‘b’;
假設這個查詢使用 Intersection 合并的方式執(zhí)行的話，那執(zhí)行過程大致如下：

• 從 idx_key1 二級索引對應的 B+ 樹中取出 key1 = ‘a(chǎn)’ 的相關記錄。
• 從 idx_key3 二級索引對應的 B+ 樹中取出 key3 = ‘b’ 的相關記錄。
• 二級索引的記錄都是由 索引列 + 主鍵 構(gòu)成的，所以我們可以計算出這兩個結(jié)果集中 id 值的交集。
• 通過上一步獲取到id 值列表進行回表操作，也就是從聚簇索引中把指定 id 值的完整用戶記錄取出來，返回給用戶。

為啥不直接使用 idx_key1 或者 idx_key3 只根據(jù)某個搜索條件去讀取一個二級索引，然后回表后再過濾另外一個搜索條件呢？這里要分析一下兩種查詢執(zhí)行方式之間需要的成本代價。

只讀取一個二級索引的成本：

• 按照某個搜索條件讀取一個二級索引
• 根據(jù)從該二級索引得到的主鍵值進行回表操作，然后再過濾其他的搜索條件

讀取多個二級索引之后取交集成本：

• 按照不同的搜索條件分別讀取不同的二級索引
• 將從多個二級索引得到的主鍵值取交集，然后進行回表操作

雖然讀取多個二級索引比讀取一個二級索引消耗性能，但是讀取二級索引的操作是順序I/O ，而回表操作是隨機I/O ，所以如果只讀取一個二級索引時需要回表的記錄數(shù)特別多，而讀取多個二級索引之后取交集的記錄數(shù)非常少，當回表而造成的性能損耗比訪問多個二級索引帶來的性能損耗更高時，讀取多個二級索引后取交集比只讀取一個二級索引的成本更低。

MySQL 在某些特殊情況下才可能會使用到 Intersection 索引合并：

• 情況一：二級索引列是等值匹配的情況，并且如果是聯(lián)合索引的話，在聯(lián)合索引中的每個列都必須等值匹配，不能出現(xiàn)只出現(xiàn)匹配部分列的情況。
  比方說下邊這個查詢可能用到 idx_key1 和 idx_key_part （三列）這兩個二級索引進行 Intersection 索引合并的操作：
  SELECT * FROM single_table WHERE key1 = ‘a(chǎn)’ AND key_part1 = ‘a(chǎn)’ AND key_part2 = ‘b’ AND key_part3 = ‘c’;
  而下邊這兩個查詢就不能進行 Intersection 索引合并：
  SELECT * FROM single_table WHERE key1 > ‘a(chǎn)’ AND key_part1 = ‘a(chǎn)’ AND key_part2 = ‘b’ AND key_part3 = ‘c’;
  SELECT * FROM single_table WHERE key1 = ‘a(chǎn)’ AND key_part1 = ‘a(chǎn)’;
  第一個查詢是因為對 key1 進行了范圍匹配，第二個查詢是因為聯(lián)合索引 idx_key_part 中的 key_part2 、key_part3 列沒有出現(xiàn)在搜索條件中，所以這兩個查詢不能進行 Intersection 索引合并。

• 情況二：主鍵列可以是范圍匹配
  比如下面這個查詢可能用到主鍵和 idx_key1 進行 Intersection 索引合并的操作：
  SELECT * FROM single_table WHERE id > 100 AND key1 = ‘a(chǎn)’;
  原因如下：需要從InnoDB 的索引結(jié)構(gòu)說起，對于 InnoDB 的二級索引來說，記錄先是按照索引列進行排序，如果該二級索引是一個聯(lián)合索引，那么會按照聯(lián)合索引中的各個列依次排序。而二級索引的用戶記錄是由 索引列 + 主鍵 構(gòu)成的，二級索引列（不唯一）的值相同的記錄可能會有很多條，這些索引列的值相同的記錄又是按照主鍵的值進行排序的。 所以重點來了，之所以在二級索引列都是等值匹配的情況下才可能使用 Intersection 索引合并，是因為只有在這種情況下根據(jù)二級索引查詢出的結(jié)果集是按照主鍵值排序的。

Intersection 索引合并會把從多個二級索引中查詢出的主鍵值求交集，如果從各個二級索引中查詢的到的結(jié)果集本身就是已經(jīng)按照主鍵排好序的，那么求交集的過程就很簡單了。假設某個查詢使用
Intersection 索引合并的方式從 idx_key1 和 idx_key2 這兩個二級索引中獲取到的主鍵值分別是：
從 idx_key1 中獲取到已經(jīng)排好序的主鍵值：1、3、5
從 idx_key2 中獲取到已經(jīng)排好序的主鍵值：2、3、4
那么求交集的過程就是這樣：逐個取出這兩個結(jié)果集中最小的主鍵值，如果兩個值相等，則加入最后的交集結(jié)果中，否則丟棄當前較小的主鍵值，再取該丟棄的主鍵值所在結(jié)果集的后一個主鍵值來比較，直到某個結(jié)果集中的主鍵值用完了，詳細過程如下：

• 先取出這兩個結(jié)果集中較小的主鍵值做比較，因為 1 < 2 ，所以把 idx_key1 的結(jié)果集的主鍵值 1 丟棄，取出后邊的 3 來比較。
• 因為 3 > 2 ，所以把 idx_key2 的結(jié)果集的主鍵值 2 丟棄，取出后邊的 3 來比較。
• 因為 3 = 3 ，所以把 3 加入到最后的交集結(jié)果中，繼續(xù)同時取兩個結(jié)果集后邊的主鍵值進行比較（類似第一步）。
• 后邊的主鍵值也不相等，所以最后的交集結(jié)果中只包含主鍵值 3 。

這個過程其實很快，時間復雜度是 O(n) ，但是如果從各個二級索引中查詢出的結(jié)果集并不是按照主鍵排序的話，那就需要先把結(jié)果集中的主鍵值排序完再進行上面的過程，而排序過程就比較耗時了。
說明：按照有序的主鍵值去回表取記錄有個專有名詞兒，叫：Rowid Ordered Retrieval，簡稱ROR。

此外，不僅是多個二級索引之間可以采用 Intersection 索引合并，聚簇索引也可以進行索引合并，如上面的情況二 ：在搜索條件中有主鍵的范圍匹配的情況下也可以使用 Intersection 索引合并索引合并。比如下面這個查詢：
SELECT * FROM single_table WHERE key1 = ‘a(chǎn)’ AND id > 100;
假設這個查詢可以采用 Intersection 索引合并，常規(guī)想法我們會認為這個查詢會分別按照 id > 100 這個條件從聚簇索引中獲取一些記錄，在通過 key1 = ‘a(chǎn)’ 這個條件從 idx_key1 二級索引中獲取一些記錄，然后再求交集。但其實沒必要從聚簇索引中再獲取一次記錄。因為二級索引的記錄中都帶有主鍵值的，所以可以在從 idx_key1 中獲取到的主鍵值上直接運用條件 id > 100 過濾就可以了。所以涉及主鍵的搜索條件只不過是為了從別的二級索引得到的結(jié)果集中過濾記錄罷了，是不是等值匹配并不重要（因為在key1相同的情況下，是按id值進行排好序的）。

當然，上邊說的情況一和情況二只是發(fā)生Intersection 索引合并的必要條件，不是充分條件。也就是說即使情況一、情況二成立，也不一定發(fā)生 Intersection 索引合并，這得看優(yōu)化器的優(yōu)化結(jié)果。優(yōu)化器只有在單獨根據(jù)搜索條件從某個二級索引中獲取的記錄數(shù)太多，導致回表開銷太大，而通過 Intersection 索引合并后需要回表的記錄數(shù)大大減少時才會使用 Intersection 索引合并。（回表數(shù)量多而通過intersection索引合并后需要回表的記錄減少很多的時候就很可能進行索引合并）

Union合并

我們在寫查詢語句時經(jīng)常想把符合某個搜索條件的記錄取出來，也把符合另外的某個搜索條件的記錄取出來，這些不同的搜索條件之間是 OR 關系。有時候 OR 關系的不同搜索條件會使用到不同的索引，比如：
SELECT * FROM single_table WHERE key1 = ‘a(chǎn)’ OR key3 = ‘b’
上面的Intersection 是交集的意思，這適用于使用不同索引的搜索條件之間使用 AND 連接起來的情況； 而Union 是并集的意思，適用于使用不同索引的搜索條件之間使用 OR 連接起來的情況。與 Intersection 索引合并類似，MySQL 在某些特定的情況下才可能會使用到 Union 索引合并：

• 情況一：二級索引列是等值匹配的情況，對于聯(lián)合索引來說，在聯(lián)合索引中的每個列都必須等值匹配，不能出現(xiàn)只出現(xiàn)匹配部分列的情況。
  比方說下邊這個查詢可能用到 idx_key1 和 idx_key_part 這兩個二級索引進行 Union 索引合并的操作：
  SELECT * FROM single_table WHERE key1 = ‘a(chǎn)’ OR ( key_part1 = ‘a(chǎn)’ AND key_part2 = ‘b’ AND key_part3 = ‘c’);

而下面這兩個查詢就不能進行 Union 索引合并：
SELECT * FROM single_table WHERE key1 > ‘a(chǎn)’ OR (key_part1 = ‘a(chǎn)’ AND key_part2 = ‘b’ AND key_part3 = ‘c’);
SELECT * FROM single_table WHERE key1 = ‘a(chǎn)’ OR key_part1 = ‘a(chǎn)’;
第一個查詢是因為對 key1 進行了范圍匹配，第二個查詢是因為聯(lián)合索引 idx_key_part 中的 key_part2、key_part3 列并沒有出現(xiàn)在搜索條件中，所以這兩個查詢不能進行 Union 索引合并。

• 情況二：主鍵列可以是范圍匹配
• 情況三：使用 Intersection 索引合并的搜索條件
  這種情況其實也挺好理解，就是搜索條件的某些部分使用 Intersection 索引合并的方式得到的主鍵集合和其他方式得到的主鍵集合取交集，比方說這個查詢：
  SELECT * FROM single_table WHERE key_part1 = ‘a(chǎn)’ AND key_part2 = ‘b’ AND key_part3 =‘c’ OR (key1 = ‘a(chǎn)’ AND key3 = ‘b’);

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

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

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

Sort-Union合并

Union 索引合并的使用條件比較苛刻，必須保證各個二級索引列在進行等值匹配的條件下才可能被用到，比如說下邊這個查詢就無法使用到 Union 索引合并：
SELECT * FROM single_table WHERE key1 < ‘a(chǎn)’ OR key3 > ‘z’
這是因為根據(jù) key1 < ‘a(chǎn)’ 從 idx_key1 索引中獲取的二級索引記錄的主鍵值不一定是排好序的，根據(jù) key3 >‘z’ 從 idx_key3 索引中獲取的二級索引記錄的主鍵值也不一定是排好序的，但是 key1 < ‘a(chǎn)’ 和 key3 > ‘z’ 這兩個條件又很有吸引力（都有索引），所以我們可以這樣：

• 先根據(jù) key1 < ‘a(chǎn)’ 條件從 idx_key1 二級索引總獲取記錄，并按照記錄的主鍵值進行排序
• 再根據(jù) key3 > ‘z’ 條件從 idx_key3 二級索引總獲取記錄，并按照記錄的主鍵值進行排序
• 因為上述的兩個二級索引主鍵值都是排好序的，剩下的操作和 Union 索引合并方式就一樣了。

我們把上述這種先按照二級索引記錄的主鍵值進行排序，之后按照 Union 索引合并方式執(zhí)行的方式稱之為 SortUnion 索引合并，這種 Sort-Union 索引合并比單純的 Union 索引合并多了一步對二級索引記錄的主鍵值排序的過程。

注意：為啥有Sort-Union索引合并，就沒有Sort-Intersection索引合并么？是的，的確沒有Sort-Intersection索引合并這么一說， Sort-Union的適用場景是單獨根據(jù)搜索條件從某個二級索引中獲取的記錄數(shù)比較少，這樣即使對這些二級索引記錄按照主鍵值進行排序的成本也不會太高 。而Intersection索引合并的適用場景是單獨根據(jù)搜索條件從某個二級索引中獲取的記錄數(shù)太多，導致回表開銷太大，合并后可以明顯降低回表開銷，但是如果加入Sort-Intersection后，就需要為大量的二級索引記錄按照主鍵值進行排序，這個成本可能比回表查詢都高了，所以也就沒有引入Sort-Intersection。

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

SELECT * FROM single_table WHERE key1 = ‘a(chǎn)’ AND key3 = ‘b’;
這個查詢之所以可能使用 Intersection 索引合并的方式執(zhí)行，是因為 idx_key1 和 idx_key3 是兩個單獨的 B+ 樹索引，要是建立一個關于兩個列的聯(lián)合索引，那直接使用這個聯(lián)合索引就可以了。如下：
ALTER TABLE single_table drop index idx_key1, idx_key3, add index idx_key1_key3(key1, key3);

這樣我們先刪除 idx_key1 、 idx_key3 ，再添加一個聯(lián)合索引 idx_key1_key3 ，使用這個聯(lián)合索引進行查詢簡直是又快又好，既不用多讀一棵 B+ 樹，也不用合并結(jié)果。
注意：但實際使用中是否有單獨對key1、key3列進行查詢的業(yè)務場景，如果有還是需要把idx_key1 、 idx_key3這兩個索引保留。

更多關于mysql的知識分享，請前往博客主頁。編寫過程中，難免出現(xiàn)差錯，敬請指出文章來源地址http://www.zghlxwxcb.cn/news/detail-715336.html

到了這里，關于【Mysql】Mysql單表訪問方法的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關文章，希望大家以后多多支持TOY模板網(wǎng)！