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

然後我們需要為這個表插入10000行記錄

訪問方法

• 使用全表掃描進行查詢
• 是所有索引進行查詢

const

設計MySQL的大叔認為通過主鍵或者唯一二級索引列與常數的等值比較來定位一條記錄是像坐火箭一樣快的，所以他們把這種通過主鍵或者唯一二級索引列來定位一條記錄的訪問方法定義為：const，意思是常數級別的，代價是可以忽略不計的。

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

ref

SELECT * FROM single_table WHERE key1 = 'abc';

由於普通二級索引並不限制索引列值的唯一性，所以可能找到多條對應的記錄，也就是說使用二級索引來執行查詢的代價取決於等值匹配到的二級索引記錄條數。如果匹配的記錄較少，則回表的代價還是比較低的，所以MySQL可能選擇使用索引而不是全表掃描的方式來執行查詢。

需要注意的兩點:

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

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了，比方說這樣：

SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 > 'legendary';

ref_or_null

有時候我們不僅想找出某個二級索引列的值等於某個常數的記錄，還想把該列的值為NULL的記錄也找出來，就像下邊這個查詢：

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

range

設計MySQL的大叔把這種利用索引進行範圍匹配的訪問方法稱之為：range。

此處所說的使用索引進行範圍匹配中的 索引 可以是聚簇索引，也可以是二級索引。

index

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

也就是說我們可以直接通過遍歷idx_key_part索引的葉子節點的記錄來比較key_part2 = 'abc'這個條件是否成立，把匹配成功的二級索引記錄的key_part1, key_part2, key_part3列的值直接加到結果集中就行了。

設計MySQL的大叔就把這種採用遍歷二級索引記錄的執行方式稱之為：index。

all

注意事項

二級索引 + 回表

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

優化器一般會根據single_table表的統計數據來判斷到底使用哪個條件到對應的二級索引中查詢掃描的行數會更少，選擇那個掃描行數較少的條件到對應的二級索引中查詢（關於如何比較的細節我們後邊的章節中會嘮叨）。

一般來說，等值查找比範圍查找需要掃描的行數更少（也就是ref的訪問方法一般比range好，但這也不總是一定的，也可能採用ref訪問方法的那個索引列的值為特定值的行數特別多），所以這裡假設優化器決定使用idx_key1索引進行查詢，那麼整個查詢過程可以分為兩個步驟：

步驟1：使用二級索引定位記錄的階段，也就是根據條件key1 = 'abc'從idx_key1索引代表的B+樹中找到對應的二級索引記錄。

步驟2：回表階段，也就是根據上一步驟中找到的記錄的主鍵值進行回表操作，也就是到聚簇索引中找到對應的完整的用戶記錄，再根據條件key2 > 1000到完整的用戶記錄繼續過濾。

明確range訪問方法使用的範圍區間

其實對於B+樹索引來說，只要索引列和常數使用=、<=>、IN、NOT IN、IS NULL、IS NOT NULL、>、<、>=、<=、BETWEEN、!=（不等於也可以寫成<>）或者LIKE操作符連接起來，就可以產生一個所謂的區間。

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

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

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

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

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

這個查詢中,索引只有idx_key2一個,所以在使用二級索引idx_key2定位記錄的階段用不到common_field = 'abc'這個條件，這個條件是在回表獲取了完整的用戶記錄後才使用的，所以在確定範圍區間的時候不需要考慮common_field = 'abc'這個條件 ,

我們在為某個索引確定範圍區間的時候只需要把用不到相關索引的搜索條件替換為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';

把使用不到idx_key2索引的搜索條件替換為TRUE：

SELECT * FROM single_table WHERE key2 > 100 OR TRUE;

簡化

SELECT * FROM single_table WHERE TRUE;

也就是說走索引的代價大於直接全表掃描的代價,所以就用不到索引

#### 索引合併 index merge

Intersection合併

Intersection翻譯過來的意思是交集。這裡是說某個查詢可以使用多個二級索引，將從多個二級索引中查詢到的結果取交集，比方說下邊這個查詢：

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

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

按照某個搜索條件讀取一個二級索引

根據從該二級索引得到的主鍵值進行回表操作，然後再過濾其他的搜索條件

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

按照不同的搜索條件分別讀取不同的二級索引

將從多個二級索引得到的主鍵值取交集，然後進行回表操作

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

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

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

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

二級索引的用戶記錄是由索引列 + 主鍵構成的，二級索引列的值相同的記錄可能會有好多條，這些索引列的值相同的記錄又是按照主鍵的值進行排序的。所以重點來了，之所以在二級索引列都是等值匹配的情況下才可能使用Intersection索引合併，是因為只有在這種情況下根據二級索引查詢出的結果集是按照主鍵值排序的。

Union合併

Union是並集的意思，適用於使用不同索引的搜索條件之間使用OR連接起來的情況。

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

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

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

而下邊這兩個查詢就不能進行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';

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

情況三：使用Intersection索引合併的搜索條件

搜索條件的某些部分使用Intersection索引合併的方式得到的主鍵集合和其他方式得到的主鍵集合取交集，比方說這個查詢：

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

優化器可能採用這樣的方式來執行這個查詢：

先按照搜索條件key1 = 'a' AND key3 = 'b'從索引idx_key1和idx_key3中使用Intersection索引合併的方式得到一個主鍵集合。

再按照搜索條件key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c'從聯合索引idx_key_part中得到另一個主鍵集合。

採用Union索引合併的方式把上述兩個主鍵集合取並集，然後進行回表操作，將結果返回給用戶。

"

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;

然後我們需要為這個表插入10000行記錄

訪問方法

• 使用全表掃描進行查詢
• 是所有索引進行查詢

const

設計MySQL的大叔認為通過主鍵或者唯一二級索引列與常數的等值比較來定位一條記錄是像坐火箭一樣快的，所以他們把這種通過主鍵或者唯一二級索引列來定位一條記錄的訪問方法定義為：const，意思是常數級別的，代價是可以忽略不計的。

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

ref

SELECT * FROM single_table WHERE key1 = 'abc';

由於普通二級索引並不限制索引列值的唯一性，所以可能找到多條對應的記錄，也就是說使用二級索引來執行查詢的代價取決於等值匹配到的二級索引記錄條數。如果匹配的記錄較少，則回表的代價還是比較低的，所以MySQL可能選擇使用索引而不是全表掃描的方式來執行查詢。

需要注意的兩點:

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

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了，比方說這樣：

SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 > 'legendary';

ref_or_null

有時候我們不僅想找出某個二級索引列的值等於某個常數的記錄，還想把該列的值為NULL的記錄也找出來，就像下邊這個查詢：

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

range

設計MySQL的大叔把這種利用索引進行範圍匹配的訪問方法稱之為：range。

此處所說的使用索引進行範圍匹配中的 索引 可以是聚簇索引，也可以是二級索引。

index

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

也就是說我們可以直接通過遍歷idx_key_part索引的葉子節點的記錄來比較key_part2 = 'abc'這個條件是否成立，把匹配成功的二級索引記錄的key_part1, key_part2, key_part3列的值直接加到結果集中就行了。

設計MySQL的大叔就把這種採用遍歷二級索引記錄的執行方式稱之為：index。

all

注意事項

二級索引 + 回表

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

優化器一般會根據single_table表的統計數據來判斷到底使用哪個條件到對應的二級索引中查詢掃描的行數會更少，選擇那個掃描行數較少的條件到對應的二級索引中查詢（關於如何比較的細節我們後邊的章節中會嘮叨）。

一般來說，等值查找比範圍查找需要掃描的行數更少（也就是ref的訪問方法一般比range好，但這也不總是一定的，也可能採用ref訪問方法的那個索引列的值為特定值的行數特別多），所以這裡假設優化器決定使用idx_key1索引進行查詢，那麼整個查詢過程可以分為兩個步驟：

步驟1：使用二級索引定位記錄的階段，也就是根據條件key1 = 'abc'從idx_key1索引代表的B+樹中找到對應的二級索引記錄。

步驟2：回表階段，也就是根據上一步驟中找到的記錄的主鍵值進行回表操作，也就是到聚簇索引中找到對應的完整的用戶記錄，再根據條件key2 > 1000到完整的用戶記錄繼續過濾。

明確range訪問方法使用的範圍區間

其實對於B+樹索引來說，只要索引列和常數使用=、<=>、IN、NOT IN、IS NULL、IS NOT NULL、>、<、>=、<=、BETWEEN、!=（不等於也可以寫成<>）或者LIKE操作符連接起來，就可以產生一個所謂的區間。

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

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

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

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

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

這個查詢中,索引只有idx_key2一個,所以在使用二級索引idx_key2定位記錄的階段用不到common_field = 'abc'這個條件，這個條件是在回表獲取了完整的用戶記錄後才使用的，所以在確定範圍區間的時候不需要考慮common_field = 'abc'這個條件 ,

我們在為某個索引確定範圍區間的時候只需要把用不到相關索引的搜索條件替換為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';

把使用不到idx_key2索引的搜索條件替換為TRUE：

SELECT * FROM single_table WHERE key2 > 100 OR TRUE;

簡化

SELECT * FROM single_table WHERE TRUE;

也就是說走索引的代價大於直接全表掃描的代價,所以就用不到索引

#### 索引合併 index merge

Intersection合併

Intersection翻譯過來的意思是交集。這裡是說某個查詢可以使用多個二級索引，將從多個二級索引中查詢到的結果取交集，比方說下邊這個查詢：

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

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

按照某個搜索條件讀取一個二級索引

根據從該二級索引得到的主鍵值進行回表操作，然後再過濾其他的搜索條件

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

按照不同的搜索條件分別讀取不同的二級索引

將從多個二級索引得到的主鍵值取交集，然後進行回表操作

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

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

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

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

二級索引的用戶記錄是由索引列 + 主鍵構成的，二級索引列的值相同的記錄可能會有好多條，這些索引列的值相同的記錄又是按照主鍵的值進行排序的。所以重點來了，之所以在二級索引列都是等值匹配的情況下才可能使用Intersection索引合併，是因為只有在這種情況下根據二級索引查詢出的結果集是按照主鍵值排序的。

Union合併

Union是並集的意思，適用於使用不同索引的搜索條件之間使用OR連接起來的情況。

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

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

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

而下邊這兩個查詢就不能進行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';

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

情況三：使用Intersection索引合併的搜索條件

搜索條件的某些部分使用Intersection索引合併的方式得到的主鍵集合和其他方式得到的主鍵集合取交集，比方說這個查詢：

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

優化器可能採用這樣的方式來執行這個查詢：

先按照搜索條件key1 = 'a' AND key3 = 'b'從索引idx_key1和idx_key3中使用Intersection索引合併的方式得到一個主鍵集合。

再按照搜索條件key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c'從聯合索引idx_key_part中得到另一個主鍵集合。

採用Union索引合併的方式把上述兩個主鍵集合取並集，然後進行回表操作，將結果返回給用戶。

"