MySQL 性能優化八大核心策略：從字段設計到查詢重寫

系統總結 MySQL 優化八大方向，覆蓋 Schema 設計、SQL 改寫、索引、架構、配置，附反模式對比。

1. 選最小足夠字段類型

• CHAR(6) 存郵編，而非 VARCHAR(255)。
• 整數優先 TINYINT/SMALLINT/MEDIUMINT，避免 BIGINT。
• 能 NOT NULL 就 NOT NULL，省 1 字節 NULL 位圖、利於索引統計。
• 枚舉值（省份、性別）用 ENUM，內部存數值，比較快。

2. JOIN 代替子查詢

-- 子查詢：建臨時表、不可用索引
SELECT * FROM customer WHERE id NOT IN (SELECT customer_id FROM orders);
-- JOIN：可用索引、無臨時表
SELECT c.* FROM customer c LEFT JOIN orders o ON c.id=o.customer_id WHERE o.customer_id IS NULL;

3. UNION 代替手工臨時表

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

會話結束自動清理，比 CREATE TEMPORARY TABLE 簡潔。

4. 事務保證一致性

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

高併發下鎖爭用大，考慮樂觀鎖或分庫分表降級。

5. 表鎖替代事務（低併發、批量導入）

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

鎖粒度粗，僅適合單線程批處理窗口。

6. 外鍵維護關聯完整性

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

僅 InnoDB 支持；高寫入場景外鍵開銷大，常在應用層維護。

7. 索引策略

• 建在 JOIN/WHERE/ORDER BY 列上。
• 高選擇性列優先；低選擇性（性別、狀態）單列索引無效，考慮組合索引。
• 避免索引列上用函數/運算：WHERE YEAR(dt)<'2001' → WHERE dt<'2001-01-01'。
• LIKE 'prefix%' 走索引，LIKE '%suffix' 全掃；改用 col>='prefix' AND col<'prefiy' 區間查詢。
• 防隱式轉換：字符串比數字、字符集不一致均導致索引失效。

8. 查詢重寫清單

配置微調清單

• innodb_buffer_pool_size = 物理內存 70%
• max_connections = 1000+（配合線程池）
• query_cache_type=OFF（高寫入場景緩存失效開銷大）
• innodb_flush_log_at_trx_commit=2（可接受 1s 丟失換吞吐）