前綴索引優化#
在字串欄位的前幾個字元上建立索引。
局限性：
- order by 無法使用前綴索引
- 無法把 [前綴索引] 用作 [覆蓋索引]
覆蓋索引優化#
含義：指 SQL 中 select 的所有欄位，在二級索引 B+Tree 的葉子節點上都能找得到，即從二級索引的 B+Tree 中查詢得到記錄，而不需要通過聚簇索引查詢獲得，可以避免回表的操作。
比如只需商品的名稱、價格時，建立聯合索引即 [商品 ID、名稱、價格] 作為一個聯合索引。如果索引中存在這些資料，查詢將不會再次檢索主鍵索引，從而避免回表（減少 I/O 操作）。
主鍵索引最好自增#
InnoDB 建立主鍵索引默認為聚簇索引，資料被存放在了 B+Tree 的葉子節點上（葉子節點自動排序）。
使用自增主鍵：每次插入一條新記錄，都是追加操作，不需要重新移動資料。
使用非自增主鍵：由於每次插入主鍵的索引值都是隨機的，因此每次插入新的資料時，就可能會插入到現有資料頁面中間的某個位置，這將不得不移動其他資料來滿足新資料的插入，甚至需要從一個頁面複製資料到另外一個頁面，我們通常將這種情況稱為頁面分裂。頁面分裂還有可能會造成大量的記憶體碎片，導致索引結構不緊湊，從而影響查詢效率。
舉個例子，假設某個資料頁面中的資料是 1、3、5、9，且資料頁面滿了，現在準備插入一個資料 7，則需要把資料頁面分割為兩個資料頁面：
出現頁面分裂時，需要將一個頁面的記錄移動到另外一個頁面，效能會受到影響，同時頁面空間的利用率下降，造成儲存空間的浪費。
而如果記錄是順序插入的，例如插入資料 11，則只需開闢新的資料頁面，也就不會發生頁面分裂：
主鍵欄位的長度不要太大，因為主鍵欄位長度越小，意味著二級索引的葉子節點越小（二級索引的葉子節點存放的資料是主鍵值），這樣二級索引佔用的空間也就越小。
索引列設置 NOT NULL#
為了更好的利用索引，索引列要設置為 NOT NULL 約束。有兩個原因：
- 索引列存在 NULL 就會導致優化器在做索引選擇的時候更加複雜，更加難以優化，因為可為 NULL 的列會使索引、索引統計和值比較都更複雜，比如進行索引統計時，count 會省略值為 NULL 的行
- NULL 值是一個沒意義的值，但是它會佔用物理空間，所以會帶來的儲存空間的問題，因為 InnoDB 儲存記錄的時候，如果表中存在允許為 NULL 的欄位，那麼 **行格式 (opens new window)中至少會用 1 位元組空間儲存 NULL 值列表 **，如下圖的紫色部分：
防止索引失效#
用上了索引並不意味著查詢的時候會使用到索引。索引失效的常見情況：
- 使用左或左右模糊匹配，即
like '%xx'或like '%xx%' - where 中對索引列做了計算、函數、類型轉換操作
- 不遵守聯合索引最左匹配原則
- 在 where 子句中，
where 索引列 or 非索引列
實際過程中，需要查看執行計劃 (explain) 來判斷是否使用了索引，如下圖就是全表掃描沒有使用索引的查詢語句：
對於執行計劃的結果：
- possible_keys 欄位表示可能用到的索引
- key 欄位表示實際用的索引，如果這一項為 NULL，說明沒有使用索引
- key_len 表示索引的長度
- rows 表示掃描的資料行數
- 重點！！type 表示資料掃描類型，執行效率從低到高的順序為：
- All（全表掃描）！！儘量避免
- index（全索引掃描）！！儘量避免
- range（索引範圍掃描）：至少要到這一級別
- ref（非唯一索引掃描）：存在重複值
- eq_ref（唯一索引掃描）：eq_ref 通常用於多表聯查中
- const（結果只有一條的主鍵或唯一索引掃描）：const 是與常量進行比較，查詢效率會更快
- Extra：
- ！！避免 Using filesort：查詢語句中包含 group by，且無法利用索引排序時
- ！！避免 Using temporary：使用了臨時表保存的中間結果，MySQL 在對查詢結果排序時使用臨時表，常見於排序 order by 和分組查詢 group by
- ！！不錯 Using index：所需資料在二級索引的 B+Tree 中即可全部獲得，不需要再一一回表獲取資料，也就是使用了覆蓋索引，避免回表操作