前情提要-我們前面為了方便解釋，建了個表還有索引

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二級索引，這也是個聯合索引

索引用於排序

我們在編寫查詢敘述時，經常需要使用order by子句對查詢出來的紀錄按規則排序。在一般情況下，我們只能把紀錄載入到記憶體中，並使用排序演算法對其排序，當空間不夠的時候會先將結果暫存在磁碟中，排序完成後再返回用戶端。
但如果order by子句中使用了索引列，有可能省去在記憶體中排序的動作。

看看幾個直接使用聯合索引排序，不需要再在記憶體排序的查詢
1.

select * from single_table order by key_part1, key_part2, key_part3 limit 10;

這個查詢的結果需要先照key_part1的值小到大排序，key_part1的值相等再照key_part2的值小到大排序，最後key_part1跟key_part2的值都相等，再照key_part3的值小到大排序。而我們都知道聯合索引idx_key_part的順序也就跟其一模一樣，因此我們不需要額外再排序，只需要從第一筆idx_key_part二級索引紀錄開始，沿著單向鏈結串列往後找10筆即可。
同理只是order by key_part1和order by key_part1,key_part2這些僅對聯合索引的索引列中左邊連續的列進行排序，也是可以直接利用B+樹索引，不需額外排序。
那如果是反過來order by key_part3, key_part2, key_part1就有所衝突，不行使用索引。

select * from single_table where key_part1 = 'a' and key_part2 = 'b' order by key_part3 limit 10;

另外當聯合索引左邊連續的列為常數時，也可以使用聯合索引對右邊的列進行排序(因為順序是相同的)。

看看幾個不能使用聯合索引進行排序的查詢

1. asc、desc混用不能用。
   很明顯的我們排序要嘛就是照asc，要嘛就是照desc，混在一起的話需要使用複雜的演算法，並無法高效率的利用索引。(但MySQL8.0引入了一種稱為Descending Index的特性，可以支援order by子句中asc、desc混用的情況。有興趣可以參考官方文件)
2. 排序列包含的列分屬於不同的索引不能用
3. 排序列屬於某個聯合索引的索引列但沒有連續(key_part1及key_part3中間還有個key_part2)不能用。
   如以下查詢

select * from single_table order by key_part1, key_part3 limit 10;

4. 用來形成掃描區間的索引列與排序列不同不能用

select * from single_table where key1 = 'a' order by key2 limit 10;

5. 排序列不是以單獨列名稱的形式出現在order by子句中不能用

select * from single_table order by upper(key1) limit 10;

索引用於分組

select key_part1,key_part2,key_part3, count(*) from single_table group by key_part1, key_part2, key_part3;

這個查詢敘述相當於執行了3次分組操作。
分組的概念跟排序是一樣的，當分組的順序也與聯合索引idx_key_part一致的話(key_part1 => key_part2 => key_part3)，那就可以直接使用聯合索引idx_key_part進行分組，不需要再建立臨時表來儲存掃描聚簇索引時的中間結果(由外至內為大中小組別，大組是key_part1，key_part1相同的紀錄再依照key_part2細分為中組，key_part1與key_part2相同的紀錄再依照key_part3細分為小組)。

回表的代價

對於下面這個查詢敘述來説

select * from single_table where key1 > 'a' and key1 < 'c';

我們可以選擇以下兩種方式來執行。

1. 全資料表掃描。直接掃描全部的聚簇索引紀錄，檢查每一筆是否符合條件，成立就發送到用戶端，否則跳過該紀錄。
2. 使用idx_key1索引執該查詢。可以根據搜索條件key1 > 'a' and key1 < 'c'得到對應的掃描區間('a','c')，然後掃描該區間中的二級索引紀錄。由於idx_key1索引的葉子節點儲存的是不完整的使用者紀錄，僅包含key1、id兩列，而查詢列表是*，表示我們每獲取一筆二級索引紀錄都要進行回表操作以獲得完整的使用者紀錄。

如果需要執行回表操作的紀錄越多，比如key1值在'a'跟'c'之間的使用者數量佔全部數量的99%以上，則使用二級索引查詢的效能也就會越差，此時倒不如直接全資料表掃描反而比較快。
至於什麼時候該全資料表掃描？什麼時候該使用二級索引+回表的方式？這就是查詢最佳化工具要做的事情。查詢最佳化工具會先針對表中的紀錄計算一些統計資料，然後再利用這些統計資料或存取表中少量紀錄來計算需要回表操作的紀錄數。這個工具之後會再進一步説明，大家先有個概念就好。

一般情況下，可以給查詢敘述指定limit子句限制返回的紀錄數，這會讓查詢最佳化工具傾向於選擇使用二級索引+回表的方式。原因是回表的紀錄越少，查詢性能越好。
上述敘述會改成像這樣

select * from single_table where key1 > 'a' and key1 < 'c' limit 10;

而對於需要對結果進行排序的查詢，如果再採用二級索引執行查詢時需要回表操作的紀錄過多。
查詢最佳化工具則傾向於使用全資料表掃描的方式來執行。
像下面的敘述

select * from single_table where order by key1;

進一步創建與使用索引

1. 只為用於搜索、排序或分組的列創建索引。
   我們只為出現在where子句中的列、連接子句中的連接列、或出現在order by或group by子句中的列創建索引。僅出現在查詢列表中的列就沒必要建立索引了。像以下的敘述

select common_field, key_part3 from single_table where key1 = 'a';

查詢列表中的common_field, key_part3就沒必要創建索引。我們只要為where子句中的key1列創建索引即可。

2. 考慮索引列中不重複值的個數。
   前面說到，在透過二級索引+回表的方式執行查詢時，某個區間的二級索引數量過多的時候，效能會越差，所以在創建索引時，需要考量到該列中不重複值的個數佔全部紀錄的比例，如果太低，表示有大量重複值，則會造成執行太多回表操作，速度太慢。

3. 索引列的類型盡量越小越好(因為越小，索引佔用的儲存空間就越小，一個資料頁就能存放更多的紀錄，磁碟I/O帶來的性能損耗也越小，讀寫效率也越高)，能使用INT就不要使用BIGINT，能使用MEDIUMINT就不要使用INT。

4. 只為索引列字首創建索引，以減少索引佔用的儲存空間。
   像這樣只用前10個字元來當索引(當字元較多的時候就可以明顯減少索引的大小)

alter table single_table drop index idx_key1;
alter table single_table add index idx_key1(key1(10));

但由於只有前十個字元資料不完整，所以下面這個查詢就不能使用索引來完成排序了

select * from single_table order by key1 limit 10;

5. 盡量使用覆蓋索引進行查詢，以避免回表帶來的性能損耗。
   為了徹底告別回表操作帶來的性能損耗，建議最好在查詢清單中只包含索引列有的欄位，如下面這樣子

select key1,id from single_table where key1 > 'a' and key1 < 'c';

由於二級索引列中的值就已包含了key1,id，因此不需要再去回表操作取得完整資料，這種索引中已包含所有需要讀取的列的查詢方式就稱為覆蓋索引。

排序操作也優先使用覆蓋索引進行查詢。如下

select key1 from single_table oredr by key1;

6. 讓索引列以列名稱的形式在搜索條件中單獨出現。

select * from single_table where key2 * 2 < 4;
select * from single_table where key2 * 4/2;

在第一個查詢key2列並不是以單獨列名稱出現，而是key2 * 2這樣的形式，MySQL並不會嘗試簡化key2 * 2 < 4這個運算式，而是會直接認爲這個搜索條件不能形成合適的掃描區間，所以會用全表搜索的方式來執行。

但在第二個查詢，MySQL可以分析出如果使用uk_key2執行查詢，對應的掃描區間就是(-無限大,2)，可以減少掃描的數量，所以會使用uk_key2索引來執行。因此請讓索引列以列名稱的形式在搜索條件中單獨出現比較好。

7. 新插入紀錄主鍵大小對效率的影響。
   假設某個資料頁儲存的聚簇索引紀錄已經滿了(總共100筆)，它儲存的主鍵值在1~100之間，此時要再插入一筆主鍵值為9的紀錄，應要插入在主鍵值為8跟10的紀錄之間，但該頁已經滿了，無法插在該頁8跟10之間，所以需要執行頁分裂的動作(新增一頁並把較大的紀錄移動到新頁)，而每次的頁分裂都是一個性能損耗。
   因此如果想降低性能損耗，最好讓插入的主鍵值依次遞增。就像single_table的主鍵id列具有auto_increment屬性那樣會自動增加。

8. 定位並刪除表中的容錯和重複索引。

alter table single_table add index idx_key_part1(key_part1);

我們針對key_part1列建立一個idx_key_part1索引
其實我們已經有一個針對key_part1、key_part2、key_part3建立的聯合索引idx_key_part。
idx_key_part索引的二級索引紀錄本身就是按照key_part1列的值排序的，所以完全不需要單獨為key_part1列再建立一個索引(也叫容錯索引)。

有時可能會對同一個列創建多個索引

alter table single_table add index unique key uk_id(id);

可是id列本身就是single_table的主鍵(不重複)，所以這個唯一二級索引uk_id就是不需要的。

以上這些容錯索引跟重複索引都要避免掉。

今天整個B+樹索引的部分就告一段落了，不知道大家吸收得如何？
如有任何問題歡迎發問唷！

接下來會開始學習MySQL的資料目錄，請大家敬請期待^^