??執行數據庫查詢時，通常查詢條件是多對個屬性進行判斷和約束，對于這種類型的查詢，如果存在多個索引則使用多個索引，或者使用建立在多屬性搜索碼上的索引，這樣能提高查詢效率。

一、使用多個單碼索引

??假設數據表 instructor 有兩個單碼索引，分別建立在 dept_name 和 salary 上。為了找到金融系中工資為 80000 的所有老師，使用以下 SQL：

select ID
from   instructor
where  dept_name = "Finance" and salary = 80000;

??可以使用以下幾種查詢策略：

1、使用 dept_name 上的索引，找出屬于金融系的所有記錄，再檢查每條記錄是否滿足 salary = 80000。
2、使用 salary 上的索引，找出所有工資為 80000 的記錄，再檢查每條記錄是否滿足 dept_name = "Finance"。
3、利用 dept_name 上的索引找出指向屬于金融系的記錄的所有指針。同樣利用 salary 上的索引找出指向工資等于 80000 的記錄的所有指針。兩個指針集合的交集，即為所有滿足查詢條件的記錄的所有指針。

??三種策略只有第三種利用了存在的多種索引的優勢，但是在以下條件下也可能是糟糕的選擇：

• 屬于金融系的記錄太多

• 工資為 80000 的記錄太多

• 屬于金融系且工資為 80000 的記錄只有幾個

  
  
  因為為了得到一個很小的結果集，必須掃描大量指針，策略的執行效果取決于索引屬性值的分布。
  
  
  

二、多碼索引（聯合索引）

1、最左前綴匹配規則

??為了解決上面的問題，一個可行的方案是在復合的搜索碼（dept_name, salary）上建立和使用索引，這就是聯合索引。

??聯合索引有個規則，叫最左前綴匹配規則，即 SQL 語句中用到了聯合索引中的最左邊的索引，那么這條 SQL 語句就可以利用這個聯合索引去進行匹配，值得注意的是，當遇到范圍查詢(>、<、between、like)就會停止匹配。

??比如對于SQL：

select ID
from   instructor
where  dept_name = "Finance" and salary = 80000;select ID
from   instructor
where  dept_name = "Finance";

??都是可以匹配索引的，查詢條件一個是（dept_name, salary），一個是（dept_name），復合最左前綴匹配規則。

??但是對下面的查詢是不匹配的：

where  salary = 80000;

??因為前面的查詢條件沒有 dept_name，聯合索引最左邊的屬性 dept_name 沒有匹配到，就不會對后面的屬性 salary 使用索引。

??對下面的查詢也是可以匹配到索引的

where salary = 80000 and dept_name = "Finance";

??因為數據庫有優化器會自動調整 salary、dept_name 的順序與索引順序一致。

??遇到范圍查詢，會停止對后面屬性的索引匹配，比如建立索引（a, b, c, d），where 后條件為

a = 1 and b = 2 and c > 3 and d = 4

??那么，a,b,c三個字段能用到索引，而d就匹配不到。因為遇到了范圍查詢，但是如果把索引改成（a, b, d, c）則又可以匹配了，因為數據庫優化器會自動把查詢條件的屬性順序調整為

a = 1 and b = 2 and d = 4 and c > 3

2、數據結構

假設，我們對(a,b)字段建立索引，那么入下圖所示

對于聯合索引（a, b），先按 a 進行排序，相同的 a 內部才按 b 進行排序，對于整個 B+ 樹來說，a 在其中是有序的，按照前序遍歷的順序，上圖中各個樹節點 a 的值分別為 1, 1, 2, 2, 2, 3, 3。

而 b 是一種全局無需，局部有序的狀態，即相同的 a 內部的有序。同樣按照前序遍歷的順序，各個結點 b 的值分別為 1, 2, 1, 4, 4, 1, 2。因此對于 b = 2 這種查詢條件是沒辦法使用索引的。

只有當 a 的值確定時，b 才是有序的。比如 a = 1 時，b 值是 1, 2 的有序狀態；當 a = 2 時，b 值是 1, 4 的有序狀態。因此，執行 a = 1 and b = 2 時 a, b 字段能用到索引，而執行 a > 1 and b = 2 時，a 字段能用到索引，b 字段用不到索引，因此此時 a 的值是一個范圍，不是固定的，在這個范圍內 b 值不是有序的，因此 b 字段用不上索引。

所以，根據最左前綴匹配原則，在遇到范圍查詢時，就會停止匹配。

3、實戰

題型一

如果sql為

SELECT * FROM table WHERE a = 1 and b = 2 and c = 3;

如何建立索引?

如果此題回答為對(a,b,c)建立索引，那都可以回去等通知了。 此題正確答法是，(a,b,c)或者(c,b,a)或者(b,a,c)都可以，重點要的是將區分度高的字段放在前面，區分度低的字段放后面。像性別、狀態這種字段區分度就很低，我們一般放后面。

例如假設區分度由大到小為b,a,c。那么我們就對(b,a,c)建立索引。在執行sql的時候，優化器會 幫我們調整where后a,b,c的順序，讓我們用上索引。

題型二

如果sql為

SELECT * FROM table WHERE a > 1 and b = 2;

如何建立索引?

如果此題回答為對(a,b)建立索引，那都可以回去等通知了。 此題正確答法是，對(b,a)建立索引。如果你建立的是(a,b)索引，那么只有a字段能用得上索引，畢竟最左匹配原則遇到范圍查詢就停止匹配。 如果對(b,a)建立索引那么兩個字段都能用上，優化器會幫我們調整where后a,b的順序，讓我們用上索引。

題型三

如果sql為

SELECT * FROM `table` WHERE a > 1 and b = 2 and c > 3;

如何建立索引? 此題回答也是不一定，(b,a)或者(b,c)都可以，要結合具體情況具體分析。

拓展一下

SELECT * FROM `table` WHERE a = 1 and b = 2 and c > 3;

怎么建索引？嗯，大家一定都懂了！

(a, b, c) 或 (b, a, c) 根據區分度決定 a 前還是 b 前。

題型四

SELECT * FROM `table` WHERE a = 1 ORDER BY b;

如何建立索引？ 這還需要想？一看就是對(a,b)建索引，當a = 1的時候，b相對有序，可以避免再次排序！ 那么

SELECT * FROM `table` WHERE a > 1 ORDER BY b;

如何建立索引？ 對(a)建立索引，因為a的值是一個范圍，這個范圍內b值是無序的，沒有必要對(a,b)建立索引。

拓展一下

SELECT * FROM `table` WHERE a = 1 AND b = 2 AND c > 3 ORDER BY c;

怎么建索引?

(a, b, c) 或 (b, a, c) 根據區分度決定 a 前還是 b 前。

題型五

SELECT * FROM `table` WHERE a IN (1,2,3) and b > 1;

如何建立索引？

還是對(a，b)建立索引，因為IN在這里可以視為等值引用，不會中止索引匹配，所以還是(a,b)!