前綴索引優化#
在字符串字段的前幾位字符上建立索引。
局限性：
- 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 中即可全部獲得，不需要再逐一回表獲取數據，也就是使用了覆蓋索引，避免回表操作