MySQL 數據庫優化技術：索引使用技巧深度總結（進階篇）

表優化基礎

• 引擎選型：MyISAM 適合讀多，InnoDB 適合讀寫混合。
• 列類型優化：PROCEDURE ANALYSE() 獲取建議。
• 非空列 NOT NULL，利於索引。
• 定長字段（CHAR）比變長（VARCHAR）略快，但空間換時間需權衡。

建立索引八大原則

1. 合理使用：單查詢只能用一個索引，EXPLAIN 驗證，ANALYZE TABLE 更新統計信息。
2. 索引建在 WHERE/ORDER BY/GROUP BY/JOIN 涉及列。
3. 高區分度列優先（唯一索引最好），低基數列（性別）不建。
4. 短類型/前綴索引：減少 I/O、提高緩存命中率。
5. 多列索引應對組合查詢（單查詢只能用一個索引）。
6. 最左前綴：將高區分、高頻列放最左。
7. 不濫建：僅高頻條件列建索引。
8. 慢查詢日誌（log-slow-queries、long_query_time）發現優化目標。

充分利用索引七條軍規

1. 同類型比較：避免隱式轉換導致索引失效。
2. 索引列獨立：WHERE col < 4/2 用索引，WHERE col*2 < 4 失效。
3. 不對索引列加函數：YEAR(date_col)<1990 → date_col<'1990-01-01'。
4. LIKE 禁用前導通配符：LIKE 'abc%' 用索引，LIKE '%abc' 全掃。
5. STRAIGHT_JOIN 強制聯結順序。
6. FORCE INDEX 強制指定索引。
7. 避免隱式類型轉換：WHERE int_col='5' 導致索引失效。

SQL 改寫優化

• 中間結果表降低大表掃描。
• 子查詢改 JOIN：
  SELECT a.id, (SELECT MAX(created) FROM posts WHERE author_id=a.id) FROM authors a
  →
  SELECT a.id, MAX(p.created) FROM authors a JOIN posts p ON a.id=p.author_id GROUP BY a.id
• ON DUPLICATE KEY UPDATE 替換「查重再插/更新」。
• 禁用游標，用臨時表/多表更新替代。

EXPLAIN 關鍵列解讀

總結

索引優化是「建對索引 + 用對索引 + 改寫 SQL + 監控慢查詢」的系統工程，需持續迭代。