千萬不要看到 WHERE 條件中出現的字段就直接創建索引，因爲創建太多的單列索引，反而會造成性能上一個的下降和缺陷。
無需一開始就創建索引，可以等到業務場景明確後，或者是數據量超過 1 萬、查詢變慢後，再針對需要查詢、排序或分組的字段創建索引。我們可以把所有的請求記錄到 general log 裡面，或者我們把 long_query_time 設置爲 0 把所有的 sql 都當成慢查詢 sql，記錄所有的 sql，然後在針對這些慢查詢 sql 進行分析，看看哪些 sql 出現的頻率最高，或者是哪些 sql 的執行耗時更高，然後我們針對這些 sql，再進行有針對性的去創建合適的索引。
盡量索引輕量級的字段，比如能索引 int 字段就不要索引 varchar 字段。索引字段可以是部分前綴，在創建的時候指定字段索引長度。
盡量不要再 sql 語句中使用 SELECT *，而是 SELECT 必要的字段，甚至可以考慮使用聯合索引來包含我們要搜索的字段，既能實現索引加速，又可以避免回表的開銷。
索引定義
索引是輔助存儲引擎高效獲取數據的一種數據結構，索引是數據的目錄，便於存儲引擎快速地定位數據，從而加快數據查詢的效率。
索引類型
數據結構
B+tree
Hash
Full-text
MySQL 常見的存儲引擎，InnoDB、MyISAM 和 Memory 分別支持的索引類型
| InnoDB | MyISAM | Memory | |
| B+tree | Yes | Yes | Yes |
| Hash | No | No | Yes |
| Full-text | Yes | Yes | No |
在 MySQL5.5 及以上版本中，InnoDB 存儲引擎是 MySQL 關係型數據庫默認使用的存儲引擎，B+tree索引是 InnoDB存儲引擎的默認索引類型。
B+tree 和 B-tree 的區別：
B+tree 只在葉子節點存儲數據，而 B-tree 的非葉子節點也存儲數據，所以 B+tree 的單個節點的數據量更小，在相同的磁盤 I/O 次數下能查詢更多的節點，存儲更高效，另外 B+tree 葉子節點在節點内部（也就是頁結構的内部）記錄之間是一個單鏈表，更適合 MySQL 中常見的基於範圍的順序檢索場景，而 B-tree 無法做到這一點。
B+tree 和紅黑樹的區別：
對於有 N 個節點的 B+tree，其查詢複雜度爲 O(logdN)，d 爲 B+tree 的度，表示節點允許的最大子節點個數爲 d 個，在實際的運用中 d 值是大於 100 的，即使數據達到千萬級別時 B+tree 的高度依然維持在 3-4 左右，保证了 3-4 次磁盤 I/O 操作就能查詢到目標數據；而紅黑樹是二叉樹，節點的子節點數爲兩個，意味著其查詢複雜度爲 O(logN)，比 B+tree 高出不少，因此紅黑樹檢索到目標數據所需經歷的磁盤 I/O 次數更多。
B+tree 和 Hash 的區別：
範圍查詢時 MySQL 常見的查詢場景，Hash 表不適合做範圍查詢，更適合做等值查詢，另外 Hash 表還存在 Hash 函數選擇和 Hash 值沖突等問題。因爲這些原因，B+tree 要比 Hash 有更廣的適用場景。
物理存儲
聚簇索引
二級索引（輔助索引）
InnoDB
InnoDB 表的索引按葉子節點存儲的是否爲完整表數據分爲聚簇索引和二級索引，全表數據就是存儲在聚簇索引中的，聚簇索引以外的其他索引叫做二級索引。InnDB 默認創建的主鍵索引就是聚簇索引，其他索引都是二級索引，也叫輔助索引或非聚簇索引。
InnoDB 表要求必須有聚簇索引，默認在主鍵字段上建立聚簇索引，在沒有主鍵字段情況下，表的第一個 NOT NULL 的唯一索引將被建立爲聚簇索引，在前兩者都沒有的情況下，InnoDB 將自動生成一個隱式自增 id 列，並在此列上創建聚簇索引。
二級索引的葉子節點並不存儲一行完整的表數據，而是存儲了聚簇索引所在列的值，由於二級索引的葉子節點不存儲完整的表數據，所以當通過二級索引查詢到聚簇索引所在的列值後，還需要回到聚簇索引，也就是表數據本身進一步獲取數據，也就是回表查詢，回表查詢需要額外的 B+tree 查詢過程，必然增大查詢耗時。
需要注意的是，通過二級索引查詢時，回表查詢也不是必須的過程，當查詢的所有字段在二級索引中就能找到時，就不需要回表，不需要回表查詢的二級索引被稱爲覆蓋索引，也稱爲觸發了索引覆蓋。通常可以使用 Explain 查看索引計劃的 Extra 列的值是否爲 Using index，如果是Using index則觸發了索引覆蓋，不需要回表查詢，如果是 NUll，則未觸發索引覆蓋，需要回表查詢。
MyISAM
MyISAM 存儲引擎的表不存在聚簇索引，而使用的是二級索引，MyISAM 表中的主鍵索引和非主鍵索引的結構是一樣的，它們的葉子節點不存放表數據，存放的是表數據的地址，即行指針，所以 MyISAM 表可以沒有主鍵，MyISAM 表的數據和索引是分開的，是單獨存放的，MyISAM 表中的主鍵索引和非主鍵索引的區別僅在與主鍵索引 B+tree 上的 key 必須符合主鍵的限制，非主鍵索引 B+tree 上的 key 只要符合相應字段的特性就可以了。
字段特性
主鍵索引
唯一索引
普通索引
前綴索引
主鍵索引是建立在主鍵字段上的索引，一張表最多只有一個主鍵索引，索引列值不允許有空值，通常在創建表的時候一起創建。
唯一索引是建立在 UNIQUE 字段上的索引，一張表可以有多個唯一索引，索引列值允許爲空。
普通索引是建立在普通字段上的索引，即不要求字段是主鍵，也不就要求字段是 UNIQUE。
前綴索引是指對字符類型的前幾個字符或對二進制類型字段的前幾個 bytes 建立的索引，而不是在整個字段上建立索引，前綴索引可以建立在類型爲 char、varchar、binary、varbinary 的列上，可以大大減少索引佔用的存儲空間，也能提升索引的查詢效率。但是，前綴索引也具有局限性，比如 order by 就無法使用前綴索引，無法把前綴索引用作覆蓋索引。
字段個數
單列索引
聯合索引（複合索引）
建立在單個列上的索引稱爲單列索引，建立在多個列上的索引稱爲聯合索引，也叫複合索引。聯合索引的非葉子節點保存了兩個字段的值作爲 B+tree 的 key 值，當 B+tree 上插入數據時，先按字段 id 比較，在 id 相同的情況下按 name 字段比較。
創建索引
建立前綴索引
通常字符類型的字段只需要對它前面幾位字符建立索引就能滿足性能要求了，過長的索引會使用更多的空間，也影響查找效率。
建立覆蓋索引
覆蓋索引是指sql 中查詢的所有字段，在索引 B+tree 的葉子節點上都能找得到的那些索引，使用覆蓋索引查詢時無需回表查詢。
如果 sql 中查詢的字段無法觸發已有索引的索引覆蓋，就可以考慮去掉查詢中不必要的字段，或者查詢的字段數不多的話，可以再對這些字段建立新的聯合索引，以便使用新的聯合索引的索引覆蓋。
聯合索引具有最左匹配原則，這個原則指明聯合索引中的某個字段，如果想要被用到聯合索引的過濾當中，除了這個字段不會使聯合索引失效以外，另外一個必要的前提是這個字段的前一個字段也被用到了聯合索引的過濾中，所以建立聯合索引時的字段順序對索引效率是有很大影響的，越靠前的字段被用於索引過濾的概率越高。
通常會把區分度大的字段排在前面，這樣區分度大的字段越有可能被更多的 sql 使用到，所謂區分度就是某個字段中不同值得個數與表的總行數的比值，比如性別字段，區分度就很小，不適合建立索引或不適合排在聯合索引列的靠前的位置，相反像 uuid 這類字段就比較適合做索引或排在聯合索引列的靠前的位置。
依據聯合索引的最左匹配原則，當聯合索引中的某個字段使得索引失效時，這個字段就不能被聯合索引用於索引過濾了。MySQL 從 5.6 版本開始對這種情況引入了索引下推機制，索引下推機制能減少二級索引的回表次數，也能減少查詢引擎和 MySQL Server 層之間的數據傳遞量。
json、group by、order by 語句的查詢條件，要使用索引。
參與 join 的字段上一般需要加上索引，盡量結合現有的索引字段進行 order by 操作，盡量避免低效的文档排序，group by 操作中，分組的字段一般需要加上索引，盡量避免創建臨時表。
盡量將數據表的字段設置爲 NOT NULL 約束，這樣做的好處是可以更好地使用索引，節省空間，甚至加速 sql 的運行。因爲判斷索引列是否爲 NOT NULL，往往需要走全表掃描，因此我們最好在設計數據表的時候就將字段設置爲 NOT NULL 約束，比如你可以將 INT 類型的字段，默認值設置爲 0，將字符串類型的字段的默認值設置爲空字符串。
「MySQL 索引創建與刪除」
索引的缺點
索引會帶來數據寫入延遲
索引會引入額外的空間損耗
索引能提升查詢效率的場景是有限的，大數據量時，需要使用其他的，如讀寫分離、分庫分表等方案
sql 中使用了索引，但是查詢仍然很慢的原因：
sql 查詢是事務中依賴多版本並發控制（MVCC）的快照讀，需要多次版本回退
索引失效
鎖等待，觸發表鎖，或行鎖升級爲表鎖，或死鎖
不恰當的 sql 語句，比如，SELECT *、SELECT COUNT(*)、 在大數據表中使用LIMIT M,N 分頁查詢，以及對非索引字段進行排序等。
索引優化
使用 EXPLAIN 查看 sql 執行計劃
我們通過 EXPLAIN 查看 sql 語句的執行計劃，explain + sql 語句。
EXPLAIN SELECT id,name FROM blog_tag WHERE id > 2; +----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | blog_tag | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 16 | 100.00 | Using where | +----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
id：每個執行計劃都有一個 id，如果是一個聯合查詢，這裡還將有多個 id。
select_type：表示 SELECT 查詢類型，常見的有 SIMPLE（普通查詢、即沒有聯合查詢、子查詢）、PRIMARY（主查詢）、UNION（UNION 中後面的查詢）、SUBQUERT（子查詢）等。
table：當前執行計劃查詢的表，如果給表起別名了，則顯示別名信息。
partitions：訪問的分區表信息。
type：數據掃描類型，常見掃描類型的效率從低到高依次爲 ALL（全表掃描）、index（全索引掃描）、range（索引範圍掃描）、ref（非唯一索引掃描）、eq_ref（唯一索引掃描）、const（結果只有一條的主鍵或唯一索引掃描）。其中，ALL（全表掃描）和 index（全索引掃描）是要盡量避免的。
possible_keys：可能使用的字段
key：實際使用的字段
key_len：索引的長度
ref：關聯 id 等信息。
rows：掃描數據的行數
filtered：查找到所需記錄佔總掃描記錄數的比例
Extra：額外的信息
其中重點介紹一下 type 列的值：
system/const：表示只有一行數據匹配，此時根據索引查詢一次就能找到對應的數據。
eq_ref：使用唯一索引掃描，常見於多表連接中使用主鍵和唯一索引作爲關聯條件。
ref：非唯一索引掃描，還可見於唯一索引最左前綴原則匹配掃描。
range：索引範圍掃描，比如 <，>，between 等操作。
index：索引全表掃描，此時遍歷整個索引樹。
ALL：表示全表掃描，需要遍歷全表來找到對應的行。
使用 Show Profile 分析 sql 執行性能
MySQL 在 v5.0.37版本之後支持 SHow Profile 功能，可以分析執行線程的狀態和時間，還支持進一步選擇 ALL、CPU、MEMORY、BLOCK IO、CONTEXT SWITCHES 等類型來查詢 sql 語句在不同系統資源上所消耗的時間。
SHOW PROFILE [type [, type] ... ] [FOR QUERY n] [LIMIT row_count [OFFSET offset]]
type 參數：
ALL：顯示所有開銷信息
BLOCK IO：阻塞的輸入輸出次數
CONTEXT SWITCHES：上下文切換相關開銷信息
CPU：顯示CPU的相關開銷信息
IPC：接收和發送消息的相關開銷信息
MEMORY ：顯示内存相關的開銷，目前無用
PAGE FAULTS ：顯示頁面錯誤相關開銷信息
SOURCE ：列出相應操作對應的函數名及其在源碼中的調用位置(行數)
SWAPS：顯示swap交換次數的相關開銷信息
MySQL 最新版本是默認開啓 Show Profile 功能的，舊版本默認是關閉該功能的。
索引失效
必定失效
被使用的索引列上有表達式計算
被使用的索引列上有函數操作
被使用的索引列上有隱式類型轉換操作
失效原因：
索引的使用時依賴於整個 B-tree 索引樹的遍歷，而索引樹的遍歷依賴於索引樹底層葉子節點的有序性，當被索引字段做了表達式計算，函數，隱式類型轉換時，有可能這個字段新的排列順序和原來在索引樹的葉子節點層的排列順序不一樣了，這就破壞了索引樹葉子節點層的有序性，當 sql 語句被執行時，MySQL 數據庫的 sql 語句執行器無法判斷原來的索引樹還能否還能被檢索使用，所以最後的結果就是 sql 語句執行器不使用該索引了。
like 匹配使用了左模糊匹配符’%abc’ 和使用了左右模糊匹配符’%abc%’，都會造成索引失效，只有 like 關鍵字的右模糊匹配’abc%’，能夠依舊使用到索引
被使用的索引字段，不是聯合索引的最左字段
失效原因：
因爲 MySQL 中的索引樹檢索遵循最左匹配原則，B-tree 索引樹的葉子節點的有序性也是建立在最左匹配的基礎上的，如果直接使用索引鍵的中部或者後部進行 sql 查詢，由於違背了最左匹配原則。MySQL 的 sql 語句執行器無法利用這個索引樹進行檢索。
如果查詢條件中使用 or，並且 or 的前後條件中有一個列沒有索引，那麽涉及的索引都不會被使用到。
需要注意的是，如果查詢過程中發生了索引覆蓋，也就是不需要回表時，索引樹還是可以被使用的。
可能失效
索引列上用了!=、>、>=、<、<=、or、in 等
索引列上用了 is null，is not null
之所以說可能失效，是因爲以上這些條件有比較大的概率會造成要掃描更多的數據，數據庫一旦要掃描的數據量超過 20% 到 30% 範圍的時候，會直接把執行計劃變成一個全表掃描，不管有沒有索引，它都會直接默認變成全表掃描。因爲 MySQL 認爲隨機掃描的數據量太大了，還不如直接來一次全表掃描。
需要注意的是，並不是所有情況下，都會使索引失效，都一定會變成全表掃描，應該是要根據實際掃描的數據庫來決定是否會導致索引失效。也就是說如果 MySQL 查詢優化器預估走索引的代價比全表掃描的代價還要大，則不走響應的索引，直接全表掃描，如果走索引比全表掃描的代價小，則使用索引。
MySQL 查詢優化器的預估並不是精確的，當優化器判定索引失效的情況下，我們可以通過 force index 的方式強制查詢引擎走特定的索引。
