一、為什麼需要索引？

無索引時，任何查詢都會觸發全表掃描。數據量大、結果集小時，性能災難性下降。但索引非越多越好：

• 過度索引：如「性別」只有男/女，建索引不提升查詢效率，反而拖慢寫入（INSERT/UPDATE/DELETE 需維護索引）。
• 權衡：讀多寫少的表多建索引，寫入頻繁的表謹慎建立。

二、複合索引與最佳左前綴原則

查詢：SELECT * FROM users WHERE area='beijing' AND age=22;

• 單列索引：MySQL 單次查詢只能用一個索引，效率有限。
• 複合索引 (area, age, salary)：等同於擁有 (area,age,salary)、(area,age)、(area) 三個索引 —— 這就是最佳左前綴特性。

建立原則： 將區分度高、常用於 WHERE/JOIN/ORDER BY 的列放在最左，依次遞減。

三、索引不包含 NULL 值

• 單列含 NULL → 不進索引。
• 複合索引任一列含 NULL → 該列對複合索引無效。
• 建議： 設計時設 NOT NULL DEFAULT '' 或 0，避免 NULL。

四、短索引（前綴索引）

對長字符串列（如 CHAR(255)），若前 10-20 字符區分度已高，可建前綴索引：

ALTER TABLE t ADD INDEX idx_name (name(10));

優勢：節省磁盤空間、減少 I/O、提升查詢速度。

五、排序與索引的關係

• MySQL 單查詢只用一個索引。若 WHERE 已用索引，ORDER BY 列通常無法再用索引（除非是覆蓋索引）。
• 避免多列排序；必須多列排序時，建立對應複合索引（順序同 ORDER BY）。

六、LIKE 查詢與索引

• LIKE '%aaa%'：不走索引（前導通配符）。
• LIKE 'aaa%'：可走索引（後導通配符）。
• 全文搜尋需求請用 FULLTEXT 索引或 Elasticsearch。

七、避免在索引列上做運算/函數

錯誤： WHERE YEAR(adddate) < 2007 → 全表掃描。

正確： WHERE adddate < '2007-01-01' → 走索引範圍掃描。

八、拒絕 NOT IN / <> / !=

• 這些操作符永不使用索引，導致全表掃描。
• 替代方案：
  • NOT IN → NOT EXISTS（子查詢可用索引）。
  • id <> 3 → id > 3 OR id < 3（可走範圍索引）。

九、覆蓋索引

查詢只需讀取索引即可滿足（不回表），EXPLAIN 顯示 Using index。設計複合索引時可包含 SELECT 列，實現覆蓋。

十、總結：索引設計清單

1. 分析慢查詢（EXPLAIN、SHOW PROFILE）。
2. 為高頻 WHERE/JOIN/ORDER BY 列建複合索引，遵循最佳左前綴。
3. 避免 NULL、函數運算、前導通配符、不等於操作。
4. 定期 ANALYZE TABLE 更新統計信息，必要時 OPTIMIZE TABLE 整理碎片。