前言

對于我們這些MySQL的使用者來說，MySQL其實就是一個軟件，平時用的最多的就是查詢功能。DBA時不時丟過來一些慢查詢語句讓優(yōu)化，我們?nèi)绻B查詢是怎么執(zhí)行的都不清楚還優(yōu)化個毛線，所以是時候掌握真正的技術了。

一、數(shù)據(jù)準備

我們在前面的文章的時候就曾說過，MySQL Server有一個稱為查詢優(yōu)化器的模塊，一條查詢語句進行語法解析之后就會被交給查詢優(yōu)化器來進行優(yōu)化，優(yōu)化的結(jié)果就是生成一個所謂的執(zhí)行計劃，這個執(zhí)行計劃表明了應該使用哪些索引進行查詢，表之間的連接順序是啥樣的，最后會按照執(zhí)行計劃中的步驟調(diào)用存儲引擎提供的方法來真正的執(zhí)行查詢，并將查詢結(jié)果返回給用戶。不過查詢優(yōu)化這個主題有點兒大，在學會跑之前還得先學會用，所以本章先來瞅瞅MySQL怎么執(zhí)行單表查詢（就是from語句后邊只有一個表，最簡單的那種查詢～）。不過需要強調(diào)的一點是，在學習本章前務必看過前邊關于記錄結(jié)構(gòu)、數(shù)據(jù)頁結(jié)構(gòu)以及索引的部分，如果你不能保證這些東西已經(jīng)完全掌握，那么本章不適合你

為了我們能順利的學習，我們先創(chuàng)建一張表：

mysql> USE testdb;

mysql> create table demo8 (    
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));

demo8 表一共創(chuàng)建了1個聚簇（主鍵）索引和4個二級索引：

• 為id列創(chuàng)建的聚簇索引；
• 為key1列創(chuàng)建的二級索引；
• 為key2列創(chuàng)建的唯一二級索引；
• 為key3列創(chuàng)建的二級索引；
• 為key_part1、key_part2、key_part3列創(chuàng)建的復合（聯(lián)合）二級索引。

然后我們需要為這個表插入20000條記錄，除id列外其余的列都插入隨機值就好了

mysql> delimiter //
create procedure demo8data()
begin    
	declare i int;    
	set i=0;    
	while i<20000 do        
		insert into demo8(key1,key2,key3,key_part1,key_part2,key_part3,common_field) values(substring(md5(rand()),1,2),i+1,substring(md5(rand()),1,3),substring(md5(rand()),1,4),substring(md5(rand()),1,5),substring(md5(rand()),1,6),substring(md5(rand()),1,7));        
		set i=i+1;    
	end while;
end;
//
delimiter ;

mysql> call demo8data();

二、單表訪問方法

2.1 訪問方法（access method）的概念

大家上小學時都有過查字典的經(jīng)歷，當我們詞匯量還沒有達到一定程度時，如果我們想知道某個沒見過字的釋義，就需要查字典。一般情況下，我們都會先去字典目錄找對應的拼音或者漢字部首、定位所在字的頁碼，然后直接翻到對應頁碼查看對應釋義即可。還有一種情況，閑的厲害，上百頁的字典，可以從首頁翻到尾頁一頁一頁去查找，最終也能找到。兩種方式，都可以得到我們想要的結(jié)果，但是花費的時間和精力卻是天差地別。MySQL也一樣，為了加快查詢數(shù)據(jù)的速度，提出了B+樹索引的概念。但是有的時候，我們要查詢所有的數(shù)據(jù)，那么就不得不一行一行，一頁一頁的去遍歷所有的數(shù)據(jù)頁去獲取結(jié)果。

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

2.1.1 使用全表掃描進行查詢

這種方式就是上面說的，一行一行記錄，一頁一頁記錄去遍歷，把符合搜索條件的記錄加入到結(jié)果集就完了。這種方式雖然可以滿足我們的需求，但效率無疑是最低的。

2.1.2 使用索引進行查詢

直接使用全表掃描的方式執(zhí)行查詢要遍歷好多記錄，代價可能非常大。就像我們通過字典目錄查找漢字一樣，如果MySQL查詢語句中的搜索條件可以使用到某個索引，那直接使用索引來執(zhí)行查詢可能會加快查詢執(zhí)行的時間。使用索引來執(zhí)行查詢的方式可以細分為許多種類：

• 針對主鍵或唯一二級索引的等值查詢
• 針對普通二級索引的等值查詢
• 通過索引列的范圍查詢
• 通過直接掃描整個索引

小結(jié)

MySQL執(zhí)行查詢語句的方式稱之為訪問方法或者訪問類型。同一個查詢語句可能使用多種不同的訪問方法來執(zhí)行，雖然最后的查詢結(jié)果都是一樣的，但是執(zhí)行效率卻千差萬別。

2.2 const

通過主鍵列來定位一條記錄：

mysql> select * from demo8 where id=9999;
+------+------+------+------+-----------+-----------+-----------+--------------+
| id   | key1 | key2 | key3 | key_part1 | key_part2 | key_part3 | common_field |
+------+------+------+------+-----------+-----------+-----------+--------------+
| 9999 | 34   | 9999 | 6c7  | 1823      | 24955     | 5deed4    | 3aebe82      |
+------+------+------+------+-----------+-----------+-----------+--------------+
1 row in set (0.00 sec)

還記得MySQL之B+樹索引中聚簇索引B+樹的結(jié)構(gòu)嗎，葉子（Leaf）節(jié)點存儲的是完整的記錄，B+樹葉子（Leaf）節(jié)點中的記錄是按照主鍵id列的值從小到大排序的。B+樹本來就是一個矮胖子，加上唯一、有序、非空的屬性，根據(jù)主鍵值定位一條記錄的速度非?？?。同樣，根據(jù)唯一二級索引列來定位一條記錄的速度也是非常快的：

mysql> select * from demo8 where key2=8888;
+------+------+------+------+-----------+-----------+-----------+--------------+
| id   | key1 | key2 | key3 | key_part1 | key_part2 | key_part3 | common_field |
+------+------+------+------+-----------+-----------+-----------+--------------+
| 8888 | 12   | 8888 | fc9  | 7810      | 1c7ed     | 5d6dbc    | adbea8c      |
+------+------+------+------+-----------+-----------+-----------+--------------+
1 row in set (0.00 sec)

根據(jù)之前文章的知識鋪墊，這個查詢的執(zhí)行分兩步：

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

MySQL通過主鍵或者唯一二級索引列與常數(shù)的等值比較來定位一條記錄是非常非常快的，所以把這種通過主鍵或者唯一二級索引列來定位一條記錄的訪問方法定義為：const，意思是常數(shù)級別，代價可以忽略不計。

不過這種const訪問方法只能在主鍵列或者唯一二級索引列和一個常數(shù)進行等值比較時才有效，如果主鍵或者唯一二級索引是由多個列構(gòu)成的話，索引中的每一個列都需要與常數(shù)進行等值比較，這個const訪問方法才有效（這是因為只有該索引中全部列都采用等值比較才可以定位唯一的一條記錄）。

對于唯一二級索引來說，查詢該列為NULL值的情況比較特殊：

mysql> select * from demo8 where key2 is null;

唯一二級索引列并不限制NULL值的數(shù)量，所以上述語句可能訪問到多條記錄，上邊這個語句不可以使用const訪問方法來執(zhí)行，至于什么訪問方法我們下邊馬上說

2.3 ref

普通的二級索引列與常數(shù)進行等值比較：

mysql> select * from demo8 where key1 = 'd9';
+-------+------+-------+------+-----------+-----------+-----------+--------------+
| id    | key1 | key2  | key3 | key_part1 | key_part2 | key_part3 | common_field |
+-------+------+-------+------+-----------+-----------+-----------+--------------+
|    59 | d9   |    59 | b7a  | 041b      | 5b1cf     | e342ac    | e103738      |
|   182 | d9   |   182 | 8bf  | 2b3c      | 08b7c     | a63ed0    | 40f2c52      |
|   401 | d9   |   401 | 137  | adbd      | dafba     | 581313    | ba72bf5      |
|  2114 | d9   |  2114 | d8b  | bf2a      | 117ae     | 69de3d    | f5467a5      |
|  2758 | d9   |  2758 | 2f7  | a159      | e3707     | f60f38    | 795ec06      |
|  3823 | d9   |  3823 | 682  | 347e      | f6195     | 6faa0d    | 5e55f78      |
|  4351 | d9   |  4351 | e92  | f3b4      | a159e     | d3e013    | e28ca48      |
|  5138 | d9   |  5138 | b41  | 1b10      | b9605     | cbe517    | b267144      |
|  5539 | d9   |  5539 | da1  | d30d      | c59b1     | 0c5d79    | ae57b7d      |
|  5604 | d9   |  5604 | ddc  | f0b0      | 00dbf     | f93c0e    | 3218cff      |
|  6050 | d9   |  6050 | 64e  | 22a4      | 69e69     | 7284ba    | 4a5b7d5      |
|  6147 | d9   |  6147 | 529  | cd38      | 71855     | 434168    | a426cbe      |
|  6428 | d9   |  6428 | a38  | c00f      | 9f710     | 9fb7c5    | a722b51      |
|  6456 | d9   |  6456 | eb7  | d208      | 30539     | ee3ae4    | a6c4870      |
|  6473 | d9   |  6473 | c15  | da29      | cc897     | 1e35c3    | 4b5f135      |
|  6860 | d9   |  6860 | b01  | bad8      | 4e3a9     | e83331    | 0cd3b9d      |
|  7257 | d9   |  7257 | fa6  | d537      | a4afe     | bbc5d8    | e11e937      |
|  7333 | d9   |  7333 | a97  | 5532      | 64097     | cb5d16    | 7e43077      |
|  7867 | d9   |  7867 | 3d7  | b341      | 0b0bb     | 7df721    | 8c64142      |
|  8265 | d9   |  8265 | a16  | 120b      | 9d372     | c17ce4    | c481ace      |
|  8371 | d9   |  8371 | 5be  | 7924      | 313f7     | 293487    | cb52072      |
|  8738 | d9   |  8738 | c05  | 7123      | b61b1     | c8d819    | e310cbf      |
|  9005 | d9   |  9005 | 44e  | e857      | 4075c     | 8460a0    | 409cb1d      |
|  9006 | d9   |  9006 | d8c  | 8c2b      | ed54f     | 3b8bfd    | 268fcce      |
|  9362 | d9   |  9362 | 5cb  | 9d70      | 05937     | 1b70d2    | a866a32      |
|  9449 | d9   |  9449 | ab8  | f9c6      | c1917     | 5ffe25    | ff88471      |
| 10146 | d9   | 10146 | 07f  | 31a7      | c30c4     | 7c2e48    | 6c5c562      |
| 10197 | d9   | 10197 | 85a  | 4796      | e5ff9     | d12af4    | 20be699      |
| 10223 | d9   | 10223 | 94b  | c57e      | adfb6     | b93c19    | a7c944b      |
| 10285 | d9   | 10285 | 9ab  | f33d      | 69e5c     | 35a651    | 0953db7      |
| 10621 | d9   | 10621 | a29  | a92b      | fbf80     | 83f1e2    | d167770      |
| 11133 | d9   | 11133 | 560  | 97af      | 35f38     | ceb1b9    | 6e89ca8      |
| 11265 | d9   | 11265 | fcc  | e7d7      | 0243e     | b52571    | 89ea417      |
| 11557 | d9   | 11557 | 1c0  | 7f66      | 0898f     | d41cfc    | e759975      |
| 11614 | d9   | 11614 | de5  | 00f7      | fb3b3     | 93dc1a    | bbe8993      |
| 11672 | d9   | 11672 | f3f  | 9fe4      | da2dd     | 82f711    | 436f3d4      |
| 12477 | d9   | 12477 | d58  | 0613      | 1df6d     | 40999a    | b748cd2      |
| 13789 | d9   | 13789 | 67b  | 5b30      | ab2f3     | 89f0ec    | 9e2d255      |
| 14050 | d9   | 14050 | 537  | bbdc      | 5e87e     | 4ac153    | 0346558      |
| 14363 | d9   | 14363 | 2ac  | 33f3      | e2b82     | 7e55c1    | 45ee579      |
| 14444 | d9   | 14444 | e47  | 6319      | 851b7     | 1d4c57    | e17a95b      |
| 14635 | d9   | 14635 | 16a  | 4d83      | 52b33     | 376017    | c853bc0      |
| 14646 | d9   | 14646 | 202  | 6fdd      | f2486     | 9900f3    | c29d0d6      |
| 15298 | d9   | 15298 | 074  | a7ee      | 6bc1d     | e96458    | 723b0f8      |
| 15489 | d9   | 15489 | 514  | 0bdc      | fb94c     | db5ce8    | 63797e8      |
| 16895 | d9   | 16895 | 4aa  | 921c      | 00b9e     | f07907    | bce779f      |
| 17587 | d9   | 17587 | 6aa  | 621b      | d521f     | a6c5ad    | 45fac89      |
| 18151 | d9   | 18151 | 87d  | cd74      | f7135     | 47d900    | 211303e      |
| 18255 | d9   | 18255 | 4dc  | b9e7      | 99bf2     | 55d0eb    | 3e6ce6c      |
| 18490 | d9   | 18490 | 6a2  | f0ff      | 85e86     | ed9bb8    | dca2cb4      |
| 18872 | d9   | 18872 | 404  | eeee      | 001c7     | 0e846d    | fae0876      |
| 19018 | d9   | 19018 | 142  | 80d7      | 2b9fd     | 77be32    | d6d8398      |
| 19228 | d9   | 19228 | a5b  | a125      | 795fa     | 108159    | 65acbf5      |
| 19537 | d9   | 19537 | 1ca  | 016a      | 3df13     | 3f5b9c    | 720de00      |
| 19940 | d9   | 19940 | 95c  | 6150      | 2696d     | 3f89b8    | d37d43a      |
| 19945 | d9   | 19945 | 538  | 6378      | a20a9     | 2b7b00    | 1865f1c      |
+-------+------+-------+------+-----------+-----------+-----------+--------------+
56 rows in set (0.00 sec)

普通二級索引并不限制索引列值的唯一性，所以可能找到多條對應的記錄（就像我們本例中共匹配出56行滿足條件的記錄），也就是說使用二級索引來執(zhí)行查詢的代價取決于等值匹配到的二級索引記錄條數(shù)。如果匹配的記錄較少，則回表的代價還是比較低的，所以MySQL可能選擇使用索引而不是全表掃描的方式來執(zhí)行查詢。MySQL把這種搜索條件為二級索引列與常數(shù)等值比較，采用二級索引來執(zhí)行查詢的訪問方法稱為：ref。
對于普通的二級索引來說，通過索引列進行等值比較后可能匹配到多條連續(xù)的記錄，而不是像主鍵或者唯一二級索引那樣最多只能匹配1條記錄，所以這種ref訪問方法比const差了一點，但是在二級索引等值比較時匹配的記錄數(shù)較少時的效率還是很高的（如果匹配的二級索引記錄太多那么回表的成本就太大了）。

需要注意下邊兩種情況：

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

mysql> select * from demo8 where key_part1='6378';
+-------+------+-------+------+-----------+-----------+-----------+--------------+
| id    | key1 | key2  | key3 | key_part1 | key_part2 | key_part3 | common_field |
+-------+------+-------+------+-----------+-----------+-----------+--------------+
| 19945 | d9   | 19945 | 538  | 6378      | a20a9     | 2b7b00    | 1865f1c      |
+-------+------+-------+------+-----------+-----------+-----------+--------------+
1 row in set (0.00 sec)
mysql> select * from demo8 where key_part1='6378' and key_part2='a20a9';
+-------+------+-------+------+-----------+-----------+-----------+--------------+
| id    | key1 | key2  | key3 | key_part1 | key_part2 | key_part3 | common_field |
+-------+------+-------+------+-----------+-----------+-----------+--------------+
| 19945 | d9   | 19945 | 538  | 6378      | a20a9     | 2b7b00    | 1865f1c      |
+-------+------+-------+------+-----------+-----------+-----------+--------------+
1 row in set (0.00 sec)
mysql> select * from demo8 where key_part1='6378' and key_part2='a20a9' and key_part3='2b7b00';
+-------+------+-------+------+-----------+-----------+-----------+--------------+
| id    | key1 | key2  | key3 | key_part1 | key_part2 | key_part3 | common_field |
+-------+------+-------+------+-----------+-----------+-----------+--------------+
| 19945 | d9   | 19945 | 538  | 6378      | a20a9     | 2b7b00    | 1865f1c      |
+-------+------+-------+------+-----------+-----------+-----------+--------------+
1 row in set (0.00 sec)

但是如果最左邊的連續(xù)索引列并不全部是等值比較的話，它的訪問方法就不能稱為ref：

mysql> select * from demo8 where key_part1='6378' and key_part2>'a20a9';
Empty set (0.00 sec)

2.4 ref_or_null

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

mysql> select * from demo8 where key1 = 'd9' or key is null;

當使用二級索引而不是全表掃描的方式執(zhí)行該查詢時，這種類型的查詢使用的訪問方法就稱為ref_or_null。相當于先分別從idx_key1索引對應的B+樹中找出key1 IS NULL和key1 = 'ef’的兩個連續(xù)的記錄范圍，然后根據(jù)這些二級索引記錄中的id值再回表查找完整的用戶記錄。

2.5 range

之前介紹的幾種訪問方法都是在對索引列與某一個常數(shù)進行等值比較的時候才可能使用到（ref_or_null比較奇特，還計算了值為NULL的情況），有時候我們面對的搜索條件更復雜：

mysql> select * from demo8 where key2 in (5555,6666) or (key2>=1234 and key2<=1235);
+------+------+------+------+-----------+-----------+-----------+--------------+
| id   | key1 | key2 | key3 | key_part1 | key_part2 | key_part3 | common_field |
+------+------+------+------+-----------+-----------+-----------+--------------+
| 1234 | 67   | 1234 | a2d  | 2779      | 18191     | 96a5b2    | 86c5afa      |
| 1235 | 6b   | 1235 | c48  | 43b4      | 0e1d1     | 27f9a0    | 1c17810      |
| 5555 | 0b   | 5555 | dc2  | 6b61      | dac52     | a0451f    | 187011e      |
| 6666 | 00   | 6666 | 9c3  | 99f8      | d22de     | 283e92    | 656f2f1      |
+------+------+------+------+-----------+-----------+-----------+--------------+
4 rows in set (0.00 sec)

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

• key2的值是5555
• key2的值是6666
• key2的值在1234和1235之間

MySQL把這種利用索引進行范圍匹配的訪問方法稱之為：range

2.6 index

mysql> select key_part1,key_part2,key_part3 from demo8  where key_part2 = 'd22de';
+-----------+-----------+-----------+
| key_part1 | key_part2 | key_part3 |
+-----------+-----------+-----------+
| 99f8      | d22de     | 283e92    |
+-----------+-----------+-----------+
1 row in set (0.01 sec)

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 = 'd22de’這個條件是否成立，把匹配成功的二級索引記錄的key_part1, key_part2, key_part3列的值直接加到結(jié)果集中就行了。由于二級索引記錄比聚簇索記錄小的多（聚簇索引記錄要存儲所有用戶定義的列以及所謂的隱藏列，而二級索引記錄只需要存放索引列和主鍵），而且這個過程也不用進行回表操作，所以直接遍歷二級索引比直接遍歷聚簇索引的成本要小很多，把這種采用遍歷二級索引記錄的執(zhí)行方式稱之為：index

2.7 all

mysql> select * from demo8;

就像這個SQL語句，查詢所有數(shù)據(jù)。這種查詢執(zhí)行方式就是全表掃描，對于InnoDB表來說也就是直接掃描聚簇索引，把這種使用全表掃描執(zhí)行查詢的方式稱之為：all

三、注意事項

3.1 重溫 二級索引+回表

一般情況下只能利用單個二級索引執(zhí)行查詢，比如下邊的語句：

mysql> select * from demo8 where key1='00' and key2>15544;

查詢優(yōu)化器會識別到這個查詢中兩個搜索條件

• key1=‘00’
• key2>15544

優(yōu)化器一般會根據(jù)demo8表的統(tǒng)計數(shù)據(jù)來判斷到底使用哪個條件到對應的二級索引中查詢掃描的行數(shù)會更少，選擇那個掃描行數(shù)較少的條件到對應的二級索引中查詢。然后將從該二級索引中查詢到的結(jié)果經(jīng)過回表得到完整的用戶記錄后再根據(jù)其余的where條件過濾記錄。假設優(yōu)化器決定使用idx_key1索引進行查詢，那么整個查詢過程可以分為兩個步驟：

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

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

小提示：
需要注意的是注意，這里我們所說的是一般情況，一般情況下執(zhí)行一個查詢只會用到單個二級索引

3.2 明確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 demo8 where key2 in (1222, 1333);
select * from demo8 where key2 = 1222 or key2 = 1333;

不過在經(jīng)常工作中，一個查詢的where子句可能有很多個小的搜索條件，這些搜索條件需要使用and或者or操作符連接起來：

A and B，A和B都為true，整個表達式才為trueA or B，A或B任意一個為true，整個表達式就為true

當我們想使用range訪問方法來執(zhí)行一個查詢語句時，重點就是找出該查詢可用的索引以及這些索引對應的范圍區(qū)間。下邊分兩種情況看一下怎么從由and或or組成的復雜搜索條件中提取出正確的范圍區(qū)間：

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

mysql> select * from demo8 where key2 > 2222 and key2 > 3333;

這個查詢中的搜索條件都可以使用到key2，也就是說每個搜索條件都對應著一個idx_key2的范圍區(qū)間。這兩個小的搜索條件使用AND連接起來，也就是要取兩個范圍區(qū)間的交集。key2 > 2222和key2 > 3333交集當然就是key2 > 3333了，上邊這個查詢使用idx_key2的范圍區(qū)間就是(3333, +∞)

如果是or

mysql> select * from demo8 where key2 > 2222 or key2 > 3333;

or意味著需要取各個范圍區(qū)間的并集，key2 > 2222和key2 > 3333并集就是key2 > 2222，上邊這個查詢使用idx_key2的范圍區(qū)間就是(2222， +∞)

3.2.2 部分搜索條件無法使用索引的情況

mysql> select * from demo8 where key2 > 2222 AND common_field = '039cb00';

這個查詢語句中能利用的索引只有idx_key2一個，而idx_key2這個二級索引的記錄中又不包含common_field這個字段，所以在使用二級索引idx_key2定位記錄的階段用不到common_field = '039cb00’這個條件，這個條件是在回表獲取了完整的用戶記錄后才使用的，而范圍區(qū)間是為了到索引中取記錄中提出的概念，所以在確定范圍區(qū)間的時候不需要考慮common_field = '039cb00’這個條件，我們在為某個索引確定范圍區(qū)間的時候只需要把用不到相關索引的搜索條件替換為true（把用不到索引的搜索條件替換為true，是因為我們不打算使用這些條件進行在該索引上進行過濾，所以不管索引的記錄滿不滿足這些條件，我們都把它們選取出來，待到之后回表的時候再使用它們過濾）。

mysql> select * from demo8 where key2 > 2222 AND true;

簡化后

mysql> select * from demo8 where key2 > 2222;

由此可得，使用idx_key2的范圍區(qū)間就是：(2222, +∞)。同理，可得使用or的情況：

mysql> select * from demo8 where key2 > 2222 or common_field = '039cb00';
mysql> select * from demo8 where key2 > 2222 or true;
mysql> select * from demo8 where true;

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

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

mysql> select * from demo8 
	where (key1 > 'ed' and key2 = 66 ) 
	or (key1 < 'zc' and key1 > 'zz') 
	or (key1 like '%33' and key1 > 'fa' and (key2 < 7777 or common_field = '97d435e')) ;

看到這種復雜條件的SQL，不要驚慌，一起來慢慢分析一下

步驟一：首先查看where子句中的搜索條件都涉及到了哪些列，哪些列可能使用到索引

這個查詢的搜索條件涉及到了key1、key2、common_field這3個列，然后key1列有普通的二級索引idx_key1，key2列有唯一二級索引idx_key2

步驟二：對于那些可能用到的索引，分析它們的范圍區(qū)間

步驟三：假設我們使用idx_key1執(zhí)行查詢 ，還是用上面的方法，那些用不到該索引的搜索條件暫時移除掉

(key1 > 'ed' and true ) or(key1 < 'zc' and key1 > 'zz') or(true and key1 > 'fa' and (true or true))

繼續(xù)簡化

(key1 > 'ed') OR(key1 < 'zc' AND key1 > 'zz') OR(key1 > 'fa')

步驟四：替換掉永遠為true或false的條件

mysql> select 'zc' > 'zz';
+-------------+
| 'zc' > 'zz' |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

結(jié)果為0，即為false，‘zc’小于’zz’。符合key1 < ‘zc’ AND key1 > 'zz’永遠為false，所以上邊的搜索條件可以被寫成這樣:
(key1 > 'ed') OR (key1 > 'fa')

繼續(xù)化簡區(qū)間

mysql> select 'ed' > 'fa';
+-------------+
| 'ed' > 'fa' |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

key1 > 'ed’和key1 > 'fa’之間使用or操作符連接起來的，意味著要取并集，所以最終的結(jié)果化簡的到的區(qū)間就是：key1 > ‘ed’。也就是說：上述復雜搜索條件的查詢語句如果使用 idx_key1索引執(zhí)行查詢的話，需要把滿足key1 > 'ed’的二級索引記錄都取出來，然后拿著這些記錄的id再進行回表，得到完整的用戶記錄之后再使用其他的搜索條件進行過濾。

步驟五： 假設我們使用idx_key2執(zhí)行查詢，需要把那些用不到該索引的搜索條件暫時使用trueE條件替換掉，其中有關key1和common_field的搜索條件都需要被替換掉

(true and key2 = 66 ) or(true and true) or(true and true and (key2 < 7777 or true))

key2 < 7777 OR true 的結(jié)果是true ，繼續(xù)簡化

key2 = 66 or true

繼續(xù)簡化：true

這個結(jié)果意味著如果我們要使用idx_key2索引執(zhí)行查詢語句的話，需要掃描idx_key2二級索引的所有記錄，然后再回表，和使用idx_key1二級索引相比，得不償失，兩種方法對比結(jié)果下不會使用idx_key2索引。

四、索引合并

在MySQL中，官方為我們內(nèi)置了很多優(yōu)化特性，是否需要啟用需要我們手動設置：

mysql> show variables like 'optimizer_switch'\g
*************************** 1. row ***************************
variable_name: optimizer_switch
        value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on
1 row in set (0.01 sec)

下面我們簡單說明一下：

• index_merge=on（索引合并）
• index_merge_union=on（Union索引合并—非聚簇索引獲取到的id值取并集）
• index_merge_sort_union=on（Sort-Union索引合并—非聚簇索引獲取到的id值先排序-取并集）
• index_merge_intersection=on（Intersection索引合并—非聚簇索引獲取到的id值取交集）
• engine_condition_pushdown=on（引擎條件下推—只用于NDB引擎，開啟后時按照WHERE條件過濾后的數(shù)據(jù)發(fā)送到SQL節(jié)點來處理，不開啟所有數(shù)據(jù)節(jié)點的數(shù)據(jù)都發(fā)送到SQL節(jié)點來處理。）
• index_condition_pushdown=on（索引條件下推—ICP）
• mrr=on（Multi-Range Read-MRR—這個優(yōu)化的主要目的是盡量使用順序讀盤）
• mrr_cost_based=on（cost-based choice—是否計算基于使用MRR的成本計算/判斷消耗）
• block_nested_loop=on（基于塊的嵌套循環(huán)連接—BNL）
• batched_key_access=off（BKA—針對Index Nested-Loop Join(NLJ) 算法的優(yōu)化）
• materialization=on（物化）
• subquery_materialization_cost_based=on（是否開啟子查詢物化的成本計算）
• semijoin=on（半連接）
• loosescan=on（半連接—松散掃描）
• firstmatch=on（半連接—首次匹配）
• duplicateweedout=on（半連接—重復值消除）
• use_index_extensions=on（使用索引擴展）
• condition_fanout_filter=on（條件(扇出)過濾）
• derived_merge=on（視圖/派生表合并，需要配合好Auto_key）

這些優(yōu)化特性會在后面的文章中慢慢鋪開詳解，回到今天的內(nèi)容，我們前邊說過MySQL在一般情況下執(zhí)行一個查詢時最多只會用到單個二級索引，但在某些特殊情況下也可能在一個查詢中使用到多個二級索引，MySQL把這種使用到多個索引來完成一次查詢的執(zhí)行方法稱之為：index merge，就是位置排在最前面的優(yōu)化特性，具體的索引合并算法有下邊三種

4.1 Intersection合并

Intersection直譯就是交集。這里是說某個查詢可以使用多個二級索引，將從多個二級索引中查詢到的結(jié)果取交集。

mysql> select * from demo8 where key1='4a' AND key3='c84';

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

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

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

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

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

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

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

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

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

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

比方說下邊這個查詢可能用到idx_key1和idx_key_part這兩個二級索引進行Intersection索引合并的操作：

mysql> select * from demo8 where key1 = 'a' and key_part1 = 'a' and key_part2 = 'b' and key_part3 = 'c';

而下邊這兩個查詢就不能進行Intersection索引合并：

mysql> select * from demo8 where key1 > 'a' and key_part1 = 'a' and key_part2 = 'b' and key_part3 = 'c';
mysql> select * from demo8 where key1 = 'a' and key_part1 = 'a';

第一個查詢是因為對key1進行了范圍匹配，第二個查詢是因為聯(lián)合索引idx_key_part中的key_part2列并沒有出現(xiàn)在搜索條件中，所以這兩個查詢不能進行Intersection索引合并。

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

比方說下邊這個查詢可能用到主鍵和idx_key1進行Intersection索引合并的操作：

mysql> select * from demo8 where id > 100 and key1 = 'a';

對于InnoDB的二級索引來說，記錄先是按照索引列進行排序，如果該二級索引是一個聯(lián)合索引，那么會按照聯(lián)合索引中的各個列依次排序。而二級索引的用戶記錄是由索引列 + 主鍵構(gòu)成的，二級索引列的值相同的記錄可能會有好多條，這些索引列的值相同的記錄又是按照主鍵的值進行排序的。在二級索引列都是等值匹配的情況下才可能使用Intersection索引合并，是因為只有在這種情況下根據(jù)二級索引查詢出的結(jié)果集是按照主鍵值排序的。

so？還是沒看懂根據(jù)二級索引查詢出的結(jié)果集是按照主鍵值排序的對使用Intersection索引合并有啥好處？小伙子，別忘了Intersection索引合并會把從多個二級索引中查詢出的主鍵值求交集，如果從各個二級索引中查詢的到的結(jié)果集本身就是已經(jīng)按照主鍵排好序的，那么求交集的過程就很easy啦。假設某個查詢使用Intersection索引合并的方式從idx_key1和idx_key2這兩個二級索引中獲取到的主鍵值分別是：

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

那么求交集的過程就是這樣：逐個取出這兩個結(jié)果集中最小的主鍵值，如果兩個值相等，則加入最后的交集結(jié)果中，否則丟棄當前較小的主鍵值，再取該丟棄的主鍵值所在結(jié)果集的后一個主鍵值來比較，直到某個結(jié)果集中的主鍵值用完了，如果還是覺得不太明白那繼續(xù)往下看：

• 先取出這兩個結(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索引合并索引合并。為啥主鍵這就可以范圍匹配了？還是得回到應用場景里，比如看下邊這個查詢：

mysql> select * from demo8 where key1 = 'a' and id > 100;

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

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

4.2 Union合并

我們在寫查詢語句時經(jīng)常想把既符合某個搜索條件的記錄取出來，也把符合另外的某個搜索條件的記錄取出來，我們說這些不同的搜索條件之間是OR關系。有時候OR關系的不同搜索條件會使用到不同的索引，比如說這樣：

mysql> select * from demo8 where key1 < 'a' or key3 > 'z';

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

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

比如說下邊這個查詢可能用到idx_key1和idx_key_part這兩個二級索引進行Union索引合并的操作：

mysql> select * from demo8 where key1 = 'a' or ( key_part1 = 'a' and key_part2 = 'b' and key_part3 = 'c');

而下邊這兩個查詢就不能進?Union索引合并：

mysql> select * from demo8 where key1 > 'a' or (key_part1 = 'a' and key_part2 = 'b' and key_part3 = 'c');
mysql> select * from demo8 where key1 = 'a' or key_part1 = 'a';

第一個查詢是因為對key1進行了范圍匹配，第二個查詢是因為聯(lián)合索引idx_key_part中的key_part2列并沒有出現(xiàn)在搜索條件中，所以這兩個查詢不能進行Union索引合并。

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

情況三： 使用Intersection索引合并的搜索條件（這種情況其實也挺好理解，就是搜索條件的某些部分使用Intersection索引合并的方式得到的主鍵集合和其他方式得到的主鍵集合取交集）。

這種情況其實也挺好理解，就是搜索條件的某些部分使用Intersection索引合并的方式得到的主鍵集合和其他方式得到的主鍵集合取交集，比如說這個查詢：

mysql> 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í)行這個查詢：

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

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

4.3 Sort-Union合并

Union索引合并的使用條件太苛刻，必須保證各個二級索引列在進行等值匹配的條件下才可能被用到。比方說下邊這個查詢就無法用到Union索引合并：

mysql> select * from demo8 where key1 < 'a' and key3 > 'z';

這是因為根據(jù)是因為根據(jù)key1 < 'a’從idx_key1索引中獲取的二級索引記錄的主鍵值不是排好序的，根據(jù)key3 > 'z’從idx_key3索引中獲取的二級索引記錄的主鍵值也不是排好序的，但是key1 < 'a’和key3 > 'z’這兩個條件又特別讓我們動心，所以我們可以這樣：

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

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

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

4.4 聯(lián)合索引合并注意事項

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

mysql> select * from demo8 where key1 = 'a' and key3 = 'b';

這個查詢之所以可能使用Intersection索引合并的方式執(zhí)行，還不是因為idx_key1和idx_key3是兩個單獨的B+樹索引，你要是把這兩個列搞一個聯(lián)合索引，那直接使用這個聯(lián)合索引就把事情搞定了，何必用啥索引合并呢，就像這樣：

mysql> alter table demo8 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é)果，何樂而不為？

小提示：
不過小心有單獨對key3列進行查詢的業(yè)務場景，這樣不得不再把key3列的單獨索引給加上

總結(jié)

今天學寫了MySQL針對單表的訪問方式（access method）以及優(yōu)化特性-索引合并（index merge）的知識點，下面我們來總結(jié)一下：

針對單表的訪問方式（access method）：

• const：通過主鍵或者唯一二級索引列來定位一條記錄的訪問方法定義為：const，意思是常數(shù)級別的，代價是可以忽略不計的。
• ref：搜索條件為二級索引列與常數(shù)等值比較，采用二級索引來執(zhí)行查詢的訪問方法稱為：ref。
• ref_or_null：使用二級索引而不是全表掃描的方式執(zhí)行該查詢時，這種類型的查詢使用的訪問方法就稱為：ref_or_null。
• range：利用索引進行范圍匹配的訪問方法稱之為：range。
• index：遍歷二級索引記錄的執(zhí)行方式稱之為：index。
• all：使用全表掃描執(zhí)行查詢的方式稱之為：all。

索引合并（index merge）的三種算法：

• Intersection合并（index_merge_intersection）。
• Union合并（index_merge_union）。
• Sort-Union合并（index_merge_sort_union）。

我們也學習了索引合并三種算法的觸發(fā)條件都是必要且不充分的，最終是否會使用到相關算法，仍需通過優(yōu)化器做出最終判斷。同時還學習了range訪問方法使用的范圍區(qū)間，通過人為分析的方式預估出使用不同索引執(zhí)行SQL時所花費的成本高低。今天的內(nèi)容偏原理，但理解起來很簡單，大家多結(jié)合前面MySQL之B+樹索引知識來感受MySQL設計的精妙之處。

總結(jié)

到此這篇關于MySQL單表訪問方法的文章就介紹到這了,更多相關MySQL單表訪問方法內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！