MySQL 效能優化八大實戰法則：欄位設計、JOIN、UNION、事務、鎖表、外鍵、索引、SQL 重寫

法則 1：選取最適用的欄位屬性（小而精）

• 整數： TINYINT(1 byte) > SMALLINT(2) > MEDIUMINT(3) > INT(4) > BIGINT(8)。能用 MEDIUMINT 別用 INT。
• 字串： 固定長度用 CHAR，可變用 VARCHAR，郵遞區號 CHAR(6) 而非 VARCHAR(255)。
• NOT NULL： 預設加 NOT NULL，省 1 bit 判斷、索引更緊湊、統計更準。
• ENUM： 固定集合（性別、狀態、省份）用 ENUM，內部存整數，比較快、空間小。

法則 2：用 JOIN 取代子查詢

-- 子查詢（建立臨時表，慢）
SELECT * FROM customer WHERE id NOT IN (SELECT customer_id FROM sales);

-- LEFT JOIN + IS NULL（無臨時表，快）
SELECT c.* FROM customer c
LEFT JOIN sales s ON c.id = s.customer_id
WHERE s.customer_id IS NULL;

原理： MySQL 子查詢常物化成臨時表，JOIN 直接走索引巢狀迴圈。

法則 3：用 UNION 取代手動臨時表

SELECT name, phone FROM client
UNION
SELECT name, birth FROM author
UNION
SELECT name, supplier FROM product;

• 自動合併去重（UNION ALL 保留重複、更快）。
• 會話結束自動清理，無殘留垃圾表。

法則 4：事務保證一致性

BEGIN;
INSERT INTO sales SET customer_id=14;
UPDATE inventory SET qty=11 WHERE item='book';
COMMIT;
-- 任一失敗 → ROLLBACK 回滾

• ACID：原子性、一致性、隔離性、持久性。
• 高併發下防止髒寫、不可重複讀、幻讀。

法則 5：鎖表替代事務（低併發、批次作業）

LOCK TABLE inventory WRITE;
SELECT qty FROM inventory WHERE item='book';
UPDATE inventory SET qty=11 WHERE item='book';
UNLOCK TABLES;

• WRITE 鎖：阻擋其他讀寫，獨佔。
• 適用於夜間批次、資料修正、單用戶維護窗口。
• 高併發網站嚴禁長時間鎖表。

法則 6：外鍵維護關聯完整性

CREATE TABLE sales (
  id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customer(id) ON DELETE CASCADE
) ENGINE=InnoDB;

• 必須用 InnoDB。
• ON DELETE CASCADE 級聯刪除，防孤兒資料。
• 效能代價：寫入額外檢查、鎖父表索引，高寫入場景權衡使用。

法則 7：索引是查詢加速器

• 建立對象： WHERE、JOIN、ORDER BY、GROUP BY 欄位。
• 避免： 低選擇性欄位（性別、布林）、頻繁更新的長字串。
• 複合索引最左前綴： INDEX (a,b,c) 支援 a、a,b、a,b,c。
• 全文索引： FULLTEXT 僅 MyISAM/InnoDB 5.6+，大量文字搜尋考慮 Elasticsearch。
• 載入大量資料： 先載資料再建索引（ALTER TABLE ... ADD INDEX），比邊載邊建快數倍。

法則 8：優化查詢語句（寫法決定索引生死）

總結清單

1. 欄位：小、定長、NOT NULL、ENUM。
2. 查詢：JOIN > 子查詢，UNION > 臨時表。
3. 寫入：事務 > 鎖表（高併發），外鍵視寫入量權衡。
4. 索引：高選擇性、最左前綴、覆蓋索引、大量載入後建。
5. SQL：無函數、無運算、無左%、無隱式轉換、無 SELECT *。

將這八條法則內化為 Code Review 標準，效能問題可減少 80% 以上。

1. 上一篇文章： MySQL 遠程連線慢？關閉 DNS 反向解析（skip-name-resolve）立竿見影
2. 下一篇文章： PHP 靜態頁面快取類：極光快取降低 CPU 與 MySQL 負載

MySQL 資料庫相關知識

MySQL 可用 API 全景：C API、Perl DBI、PHP API 架構與選擇

MySQL 三大主流 API 架構解析：C API 基石、D

MySQL SELECT 查詢語法全解：從基礎語法到無 FROM 子句的運算式評估

SELECT 語句核心語法 SELECT 是 SQL 最常用

MySQL 慢查詢日誌配置完整指南：從啟用到監控實戰

隨著 MySQL 資料庫在生產環境中穩定運行，前端業務逐步遷

Windows 下 MySQL 徹底卸載指南：從服務停止到註冊表清理

為什麼需要「徹底」卸載？ Windows 控制面板的「程式和