MySQL的優化主要分為結構優化（Scheme optimization）和查詢優化（Query optimization）。
本章討論的高效能索引策略主要屬於結構優化範疇。本章的內容完全基於上文的理論基礎，實際上一旦理解了索引背後的機制，那麼選擇高效能的策略就變成了純粹的推理，並且可以理解這些策略背後的邏輯。
一、示例資料庫
為了討論索引策略，需要一個資料量不算小的資料庫作為示例。本文選用MySQL官方文件中提供的示例資料庫之一：employees。這個資料庫關係複雜度適中，且資料量較大。下圖是這個資料庫的E-R關係圖（引用自MySQL官方手冊）：
可以從https://github.com/datacharmer/test_db倒入employees.sql檔案
二、最左字首原理與相關優化
高效使用索引的首要條件是知道什麼樣的查詢會使用到索引，這個問題和B+Tree中的“最左字首原理”有關，下面通過例子說明最左字首原理。
這裡先說一下聯合索引的概念。在上文中，我們都是假設索引只引用了單個的列，實際上，MySQL中的索引可以以一定順序引用多個列，這種索引叫做聯合索引。
一般的，一個聯合索引是一個有序元組<a1, a2, …, an>，其中各個元素均為資料表的一列，實際上要嚴格定義索引需要用到關係代數，但是這裡我不想討論太多關係代數的話題，因為那樣會顯得很枯燥，所以這裡就不再做嚴格定義。另外，單列索引可以看成聯合索引元素數為1的特例。
以employees.titles表為例，下面先檢視其上都有哪些索引：
三、EXPLAIN
在日常工作中，我們會有時會開慢查詢去記錄一些執行時間比較久的SQL語句，找出這些SQL語句並不意味著完事了，些時我們常常用到explain這個命令來檢視一個這些SQL語句的執行計劃，檢視該SQL語句有沒有使用上了索引，有沒有做全表掃描，這都可以通過explain命令來檢視。
所以我們深入瞭解MySQL的基於開銷的優化器，還可以獲得很多可能被優化器考慮到的訪問策略的細節，以及當執行SQL語句時哪種策略預計會被優化器採用。
EXPLAIN出來的資訊有10列，分別是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra
概要描述：
- id:選擇識別符號
- select_type: 表示查詢的型別。
- table: 輸出結果集的表
- type: 表示表的連線型別
all（全表掃描） 、index（按照索引順序的全表掃描）、range(有範圍的索引掃描)
req (查詢條件列使用了索引而且不為主鍵和unique,使用該索引列的值並不唯一)、ref_eq（使用了主鍵或者唯一性索引進行查詢的情況）、
const（主鍵放置到where後面作為條件查詢，mysql優化器就能把這次查詢優化轉化為一個常量） - possible_keys: 表示查詢時，可能使用的索引
- key: 表示實際使用的索引
- key_len: 索引欄位的長度
- ref: 列與索引的比較
- rows: 掃描出的行數(估算的行數)
- Extra:執行情況的描述和說明
四、具體內容
情況一：全列匹配
explain SELECT * FROM employees.titles WHERE emp_no='10001' AND title = 'Senior Engineer' AND from_date='1986-06-26';
很明顯，當按照索引中所有列進行精確匹配（這裡精確匹配指“=”或“IN”匹配）時，索引可以被用到。這裡有一點需要注意，理論上索引對順序是敏感的，但是由於MySQL的查詢優化器會自動調整where子句的條件順序以使用適合的索引，例如我們將where中的條件順序顛倒效果是一樣的。
情況二：最左字首匹配
EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001';
當查詢條件精確匹配索引的左邊連續一個或幾個列時，如或<emp_no, title>，所以可以被用到，但是隻能用到一部分，即條件所組成的最左字首。上面的查詢從分析結果看用到了PRIMARY索引，但是key_len為4，說明只用到了索引的第一列字首。
情況三：查詢條件用到了索引中列的精確匹配，但是中間某個條件未提供
EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND from_date='1986-0626'; 
此時索引使用情況和情況二相同，因為title未提供，所以查詢只用到了索引的第一列，而後面的from_date雖然也在索引中，但是由於title不存在而無法和左字首連線，因此需要對結果進行掃描過濾from_date（這裡由於emp_no唯一，所以不存在掃描）。
如果想讓from_date也使用索引而不是where過濾，可以增加一個輔助索引<emp_no, from_date>，此時上面的查詢會使用這個索引。除此之外，還可以使用一種稱之為“隔離列”的優化方法，將emp_no與from_date之間的“坑”填上。
首先我們看下title一共有幾種不同的值：
只有7種。在這種成為“坑”的列值比較少的情況下，可以考慮用“IN”來填補這個“坑”從而形成最左字首：
這次key_len為56，說明索引被用全了，但是從type和rows看出IN實際上執行了一個range查詢，這裡檢查了7個key。
“填坑”後效能提升了一點。如果經過emp_no篩選後餘下很多資料，則後者效能優勢會更加明顯。當然，如果title的值很多，用填坑就不合適了，必須建立輔助索引。
情況四：查詢條件沒有指定索引第一列
由於不是最左字首，索引這樣的查詢顯然用不到索引。
情況五：匹配某列的字首字串
此時可以用到索引，但是如果萬用字元不是隻出現在末尾，則無法使用索引。（原文表述有誤，如果萬用字元%不出現在開頭，則可以用到索引，但根據具體情況不同可能只會用其中一個字首）
情況六：範圍查詢
範圍列可以用到索引（必須是最左字首），但是範圍列後面的列無法用到索引。同時，索引最多用於一個範圍列，因此如果查詢條件中有兩個範圍列則無法全用到索引。
可以看到索引對第二個範圍索引無能為力。這裡特別要說明MySQL一個有意思的地方，那就是僅用explain可能無法區分範圍索引和多值匹配，因為在type中這兩者都顯示為range。同時，用了“between”並不意味著就是範圍查詢，例如下面的查詢：
看起來是用了兩個範圍查詢，但作用於emp_no上的“BETWEEN”實際上相當於“IN”，也就是說emp_no實際是多值精確匹配。可以看到這個查詢用到了索引全部三個列。因此在MySQL中要謹慎地區分多值匹配和範圍匹配，否則會對MySQL的行為產生困惑。
情況七、索引選擇性與字首索引
既然索引可以加快查詢速度，那麼是不是隻要是查詢語句需要，就建上索引？答案是否定的。因為索引雖然加快了查詢速度，但索引也是有代價的：索引檔案本身要消耗儲存空間，同時索引會加重插入、刪除和修改記錄時的負擔，另外，MySQL在執行時也要消耗資源維護索引，因此索引並不是越多越好。一般兩種情況下不建議建索引。
第一種情況是表記錄比較少，例如一兩千條甚至只有幾百條記錄的表，沒必要建索引，讓查詢做全表掃描就好了。至於多少條記錄才算多，這個個人有個人的看法，我個人的經驗是以2000作為分界線，記錄數不超過 2000可以考慮不建索引，超過2000條可以酌情考慮索引。
另一種不建議建索引的情況是索引的選擇性較低。所謂索引的選擇性（Selectivity），是指不重複的索引值（也叫基數，Cardinality）與表記錄數（#T）的比值：
Index Selectivity = Cardinality / #T顯然選擇性的取值範圍為(0, 1]，選擇性越高的索引價值越大，這是由B+Tree的性質決定的。例如，上文用到的employees.titles表，如果title欄位經常被單獨查詢，是否需要建索引，我們看一下它的選擇性：
title的選擇性不足0.0001（精確值為0.00001579），所以實在沒有什麼必要為其單獨建索引。
有一種與索引選擇性有關的索引優化策略叫做字首索引，就是用列的字首代替整個列作為索引key，當字首長度合適時，可以做到既使得字首索引的選擇性接近全列索引，同時因為索引key變短而減少了索引檔案的大小和維護開銷。下面以employees.employees表為例介紹字首索引的選擇和使用。
從示例資料庫圖可以看到employees表只有一個索引，那麼如果我們想按名字搜尋一個人，就只能全表掃描了,如果頻繁按名字搜尋員工，這樣顯然效率很低，因此我們可以考慮建索引。有兩種選擇，建或<first_name, last_name>，看下兩個索引的選擇性：
顯然選擇性太低，選擇性很好，但是first_name和last_name加起來長度為30，有沒有兼顧長度和選擇性的辦法？ >> 可以考慮用first_name和last_name的前幾個字元建立索引，例如， 看看其選擇性： 
這時選擇性已經很理想了，而這個索引的長度只有18，比短了接近一半，我們把這個字首索引 建上： ``` ALTER TABLE employees.employees ADD INDEX `first_name_last_name4` (first_name, last_name(4)); ``` 此時再執行一遍按名字查詢，比較分析一下與建索引前的結果： 效能的提升是顯著的，查詢速度提高了120多倍。
>> 字首索引兼顧索引大小和查詢速度，但是其缺點是不能用於ORDER BY和GROUP BY操作，也不能用於Covering index（即當索引本身包含查詢所需全部資料時，不再訪問資料檔案本身）。