前文非常詳細的說明了Innodb儲存引擎的B+樹索引，我們必須熟悉下面這些觀念。

• 每個索引都對應一棵B+樹。B+樹分為好多層，最下面一層是葉子節點(所有使用者紀錄儲存在這)，其餘是內節點(所有目錄項紀錄儲存在這)。
• Innodb儲存引擎會自動為主鍵建立聚簇索引(如果沒有顯性指定主鍵及沒有不允許儲存NULL的UNIQUE值，那Innodb就會自動建立)，聚簇索引的葉子節點包含完整的使用者紀錄。
• 我們可以為感興趣的列建立二級索引，二級索引的葉子節點包含的使用者紀錄由索引列和主鍵組成。如果想透過二級索引找到完整的使用者紀錄，則需要再拿主鍵去聚簇索引內查詢，這動作也叫回表。
• B+樹每層節點都按照索引列的值小到大的順序排序組成了雙向鏈結串列，且每個頁內的紀錄(無論是使用者紀錄或是目錄項紀錄)都按照索引列的值小到大的順序行成單向鏈結串列。如果是聯合索引，則頁面和紀錄先按照索引列中前列的值排序，如果值相同，再按照索引列後列的值排序。
• 透過索引尋找紀錄時，是從B+樹的根節點開始一層一層向下搜索的。由於每個頁面(無論是內節點頁面還是葉子節點頁面)中的紀錄都劃分成了許多組，每組中索引列值最大的紀錄(當然，規定Supermum紀錄比任何使用者紀錄都大)在頁內的偏移量會被當作槽依次存放在頁目錄中，因此可以在頁目錄中透過二分法快速定位到索引列等於某個值的紀錄。

這些觀念那怕只有一丁點不熟悉，那麼今天的內容就不適合你，請蛙友們回去我前面的文章複習再來～

正文開始

索引的代價
索引是個好東西，但不能肆意創建。在更進一步介紹索引之前，要先了解它在時間及空間上所要付出的代價。
空間上的代價
每建立一個索引都要為其建立一棵B+樹。樹內每個節點都是一頁(佔16kb)，所以當有很多節點時是相當佔空間的。

時間上的代價
每當有新增、刪除、修改的動作，都需要修改索引。為了讓索引符合每層節點都按照小到大排序及節點內的紀錄一樣按照小到大排序，都需要引擎付出額外的時間進行頁分裂、頁回收等操作。

為了方便說明接下來的實際查詢語法與索引的關係，先建個table

mysql> create table single_table(
    -> id int not null auto_increment,
    -> key1 varchar(10),
    -> 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 uk_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, 1 warning (0.05 sec)

為id列建立的聚簇索引
為key1列建立的idx_key1二級索引
為key2列建立的uk_key2二級索引，且是唯一
為key3列建立的idx_key3二級索引
為key_part1、key_part2、key_part3列建立的idx_key_part二級索引，這也是個聯合索引

然後我們建立10000筆測試資料

mysql> delimiter //
mysql> create procedure insert_test_data_to_single_table()
    -> begin
    -> declare num int;
    -> set num=1;
    -> while num <= 10000 do
    -> insert into single_table(key1,key2,key3,key_part1,key_part2,key_part3,common_field) values (concat("key1", num),num,concat("key3", num),concat("key_part1", num),concat("key_part2", num),concat("key_part3", num),concat("common_field", num));
    -> set num=num+1;
    -> end while;
    -> end
    -> //
Query OK, 0 rows affected (0.02 sec)

mysql> call insert_test_data_to_single_table();
    -> //
Query OK, 1 row affected (8.85 sec)

表內就有一萬筆資料辣，我們先來看看一些重要觀念

掃描區間與邊界條件
我們舉幾個例子來看
1.

select * from single_table where id >=2 and id <=100;

這個敘述是想找尋id值在[2,100]區間中的所有聚簇索引紀錄。我們把待掃描id值所在的區間稱為掃描區間，把這個搜索條件稱為形成這個掃描區間的邊界條件。

select * from single_table where key2 in (1438, 6328) or (key2 >= 38 and key2 <= 79);

該搜索條件為key2列，我們有為key2列建立uk_key2索引。如果使用uk_key2索引執行這個查詢，相當於從下面三個掃描區間中獲取二級索引紀錄。
[1438,1438] => 對應的邊界條件為key2 in (1438)。
[6328,6328] => 對應的邊界條件為key2 in (6328)。
[38,79] => 對應的邊界條件為key2 >=38 and key2 <= 79。
我們把[1438,1438],[6328,6328]這樣只包含一個值的掃描區間稱為單點掃描區間。
把[38,79]這樣包含多個值的掃描區間稱為範圍掃描區間。
另外由於我們的查詢清單是(*)，也就是需要完整的使用者紀錄，所以從上述掃描區間中每獲取一筆二級索引紀錄，就需要做一次回表的動作。

有的搜索條件無法生成合適的掃描區間，如下：

select * from single_table where key2 > 100 or common_field = 'abc';

在使用key2的索引uk_key2查詢時，掃描區間為(100,+無限大)，由於uk_key2二級索引並不按照common_field列進行排序(照key2列排序)，而且其實索引內根本就不包含common_field列，所以common_field='abc'這個條件並無法減少所需掃描的二級索引紀錄，沒有任何作用，其掃描區間為(-無限大,+無限大)。
由於是or的關係，聯集後的掃描區間就是(-無限大,+無限大)，也就是需要掃描uk_key2的全部二級索引紀錄，大家別忘了，每一筆二級索引紀錄還要去執行回表操作，這個速度比全資料表掃描還更費時。所以這樣的情況不如不用uk_key2索引。

如何從複雜的搜索條件中找出掃描區間：

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

首先查看where子句中的搜索條件有那些列，以及我們為那些列建立了索引。
這個查詢包含key1, key2, common_field三列，key1對應二級索引idx_key1，key2對應唯一二級索引uk_key2。

然後針對可能會用到的索引，一一分析掃描區間。
第一個先使用idx_key1來看看
在搜索條件中我們可以先把對於key1沒有發揮作用的條件替換為true(該條件沒有任何作用跟true並無區別)，所以可以把此搜索條件看成

select * from single_table where
        (key1 > 'xyz' and true) or
        (key1 < 'abc' and key1 > 'lmn') or
        (true and key1 > 'zzz' and (true or true));

其中key1 like '%suf'變為true，補充說明一下，like敘述只有在匹配完整的字串或匹配字串字首時才產生合適的掃描區間。舉個例：對於二級索引列來說，所有字串字首為'a'的紀錄肯定是相鄰的(因為它會照著字串大小排列，字串比較大小會先比較第一個字母，第一個字母小就是小，第一個字母相同再去比較第二個字母，依此類推)，所以只要定位到字首為'a'的第一筆紀錄，沿著單向鏈結往後掃描，直到字串字首不為'a'為止。因此'%suf'並沒有匹配字串字首，所以沒有起到任何作用。

可以簡化為

select * from single_table where
        (key1 > 'xyz') or (key1 < 'abc' and key1 > 'lmn') or (key1 > 'zzz'));

除了true可以替換，也可以替換掉false條件。
由於 key1 < 'abc' and key1 > 'lmn'永遠為false['a'在編碼中比'l'小，所以'abc'比'lmn'小(先比對第一個字元)，因此key1比'abc'小的話就不可能比'lmn'大]
替換掉false如下

select * from single_table where
        (key1 > 'xyz') or (key1 > 'zzz'));

在編碼中x比z小，所以'xyz'比'zzz'小(先比對第一個字元)，這邊是or運算元，所以要取聯集，只要比'xyz'還大就可以符合所有條件，搜索條件就是key1 > 'xyz'。也就是說使用idx_key1來執行查詢，對應的掃描區間就是('xyz',+無限大)。

最後的結果就是把滿足key1 > 'xyz'條件的所有二級索引紀錄取出來，針對獲取的每筆紀錄用主鍵值去回表操作，然後得到完整的使用者紀錄後，再使用其他搜索條件過濾。

第二個使用uk_key2來查詢
一樣把不能形成合適掃描區間的條件用true換掉

select * from single_table where
        (true and key2 = 748) or
        (true and true) or
        (true and true and (key2 < 8000 or true));

簡化如下(key2 < 8000 or true)的結果肯定是true囉(符合其一即可)

select * from single_table where
        key2 = 748 or true;

也就是

select * from single_table where
        true;

因此用uk_key2當索引查詢，掃描區間就是沒有任何限制的(-無限大,+無限大)，也就是最慢的結果，因此不考慮。

耶～take a break