MySQL 性能優化是系統穩定性的基石。本文提煉 8 大核心優化原則，涵蓋索引策略、SQL 重寫、架構設計，每條原則附帶反面教材與最佳實踐，助你構建高性能數據庫。
無索引 = 全表掃描。大表無索引查詢是性能殺手。
-- 1000 萬行表，無索引查詢耗時 5 秒+
SELECT * FROM orders WHERE user_id = 12345;單列索引查詢只能用一個索引，復合索引可同時滿足多列過濾。
-- 查詢：北京 22 歲用戶
SELECT * FROM users WHERE area = 'beijing' AND age = 22;
-- ❌ 單列索引：只能用 area 或 age 其中一個
-- ✅ 復合索引 (area, age)：一次索引查找完成
-- 進階：(area, age, salary) 等價於三個索引
-- (area, age, salary) 、 (area, age) 、 (area)
-- 這就是「最左前綴特性」單列索引不存 NULL；復合索引任一列為 NULL，該行不進入索引。
WHERE col IS NULL 無法用索引，改用 WHERE col = '' 或增加虛擬列長字符串列（VARCHAR(255)、TEXT、BLOB）建全列索引佔用空間大、I/O 高。
-- 假設 email 前 20 字符區分度已 > 90%
ALTER TABLE users ADD INDEX idx_email_prefix (email(20));MySQL 單表查詢只能使用一個索引。WHERE 用了索引，ORDER BY 就無法再用索引。
| 場景 | 建議 | |
|---|---|---|
| WHERE + ORDER BY 同列 | 單列索引即可 | |
| WHERE 列 + ORDER BY 列不同 | 建立復合索引 (where_col, order_col) | |
| 多列排序 | 建立復合索引匹配排序順序 | |
| 無 WHERE 純排序 | 索引可用於排序（如 idx_created_at） |
| ❌ 悪例（索引失效） | ✅ 佳例（索引生效） |
|---|---|
WHERE YEAR(create_time) = 2024 | WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01' |
WHERE DATE_FORMAT(t, '%Y-%m') = '2024-01' | WHERE t >= '2024-01-01' AND t < '2024-02-01' |
WHERE SUBSTRING(phone, 1, 3) = '138' | WHERE phone LIKE '138%' |
WHERE id + 1 = 100 | WHERE id = 99 |
WHERE UPPER(name) = 'JOHN' | WHERE name = 'JOHN' (或建函數索引 8.0+) |
NOT IN、<>、!= 通常導致全表掃描，不走索引。
| 原寫法 | 改寫 | 原理 |
|---|---|---|
WHERE id NOT IN (1,2,3) | WHERE id NOT EXISTS (SELECT 1 FROM t2 WHERE t2.id = t1.id AND t2.id IN (1,2,3)) | NOT EXISTS 可用索引 |
WHERE status <> 1 | WHERE status = 0 OR status = 2 | IN/OR 可用索引合併 |
WHERE id != 3 | WHERE id > 3 OR id < 3 | 範圍查詢可用索引 |
□ 所有查詢 EXPLAIN 過，無 ALL、無 Using temporary、無 Using filesort
□ 高頻 SQL 都有對應索引（含復合索引最左前綴）
□ 無 SELECT *，按需選列（減少網絡、內存、覆蓋索引機會）
□ 大表有歸檔/分區策略
□ 寫入峰值評估過，innodb_buffer_pool_size 設為內存 70-80%
□ 慢查詢日志開啟，long_query_time = 1s
□ 定期 ANALYZE TABLE 更新統計信息
□ 備份恢復演練每季度一次MySQL 優化無銀彈，但遵循這 8 大原則能解決 80%+ 性能問題：索引是核心武器，SQL 重寫是戰術手段，架構設計是戰略保障。建立「慢查詢→分析→優化→驗證」閉環，讓數據庫性能持續在線。
