MySQL SQL 優化 10 條核心建議（從索引設計到架構層面）

建議 1：高頻查詢欄位必建索引

`WHERE`、`JOIN`、`ORDER BY`、`GROUP BY` 出現的欄位，優先建立**複合索引**（最左前綴原則）。單表索引數建議 ≤ 5 個，權衡寫入開銷。

建議 2：優先使用整數類型，拒絕不必要的 NULL

• `INT`/`BIGINT` 比 `VARCHAR`/`CHAR` 比較快、佔空間小、索引更緊湊。
• 所有欄位 `NOT NULL DEFAULT <值>`，`NULL` 會增加索引儲存、複雜化統計、導致 `COUNT()`/`ORDER BY` 行為差異。

建議 3：大字段（TEXT/BLOB/長 VARCHAR）垂直拆分

-- 原表（含大字段）
CREATE TABLE article (
  id INT PRIMARY KEY,
  title VARCHAR(200),
  content LONGTEXT,        -- 大字段
  author_id INT,
  created_at DATETIME
) ENGINE=InnoDB;

-- 拆分後
CREATE TABLE article (
  id INT PRIMARY KEY,
  title VARCHAR(200),
  author_id INT,
  created_at DATETIME
) ENGINE=InnoDB;

CREATE TABLE article_content (
  article_id INT PRIMARY KEY,
  content LONGTEXT
) ENGINE=InnoDB;

效益：主表行長大幅縮短，Buffer Pool 可緩存更多熱數據，查詢列表/統計不再讀取大字段頁面。

建議 4：用 PROCEDURE ANALYSE() / phpMyAdmin 結構建議優化欄位型別

SELECT * FROM `table` PROCEDURE ANALYSE();
-- phpMyAdmin：結構 → Propose table structure
-- 輸出建議：VARCHAR(255) → VARCHAR(32)、INT → SMALLINT UNSIGNED 等

根據實際資料分佈自動推薦最小充足型別，定期執行可持續收窄定義。

建議 5：拒絕 SELECT *，精準投影；單行查詢加 LIMIT 1

-- 差
SELECT * FROM user WHERE id = 100;

-- 優
SELECT id, name, email FROM user WHERE id = 100 LIMIT 1;

減少網路傳輸、解析開銷、緩存污染；`LIMIT 1` 讓優化器停止掃描（主鍵查詢除外）。

建議 6：嚴禁 ORDER BY RAND()

-- 災難寫法：產生臨時表 + 排序 + 全表掃描
SELECT * FROM article ORDER BY RAND() LIMIT 10;

-- 替代方案：應用層取隨機 ID 或主鍵範圍隨機
SELECT * FROM article 
WHERE id >= (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM article))) 
ORDER BY id LIMIT 10;

建議 7：每表必有單調遞增整數主鍵

• `BIGINT UNSIGNED AUTO_INCREMENT` 或雪花 ID。
• 避免 UUID/字串主鍵導致聚簇索引頁分裂、二級索引膨脹。
• 業務唯一鍵（如訂單號）建**唯一二級索引**，不做主鍵。

建議 8：大批量 DELETE/INSERT 分批執行

-- 差：單事務刪 100 萬行，鎖表、Undo Log 爆漲、主從延遲數小時
DELETE FROM log WHERE created_at < '2022-01-01';

-- 優：分批 5000 行，間隔睡眠
WHILE (SELECT COUNT(*) FROM log WHERE created_at < '2022-01-01') > 0 DO
  DELETE FROM log WHERE created_at < '2022-01-01' LIMIT 5000;
  DO SLEEP(0.1);
END WHILE;

建議 9：慎用持久連線 (mysql_pconnect / PDO::ATTR_PERSISTENT)

• PHP-FPM / Java 連線池已管理連線生命週期，**不需要**持久連線。
• 持久連線易導致「僵屍連線」佔滿 `max_connections`、事務殘留、鎖不釋放。
• 短連線 + 連線池（HikariCP/Druid/PDO pool）是標準模式。

建議 10：拒絕過度複雜的大 SQL，拆分為多條簡單查詢

「看到關聯 3~4 張表的巨型 SQL，只會覺得不靠譜。」

• 複雜 SQL 難維護、難優化、鎖範圍大、優化器易誤判。
• 拆分為：應用層組裝、多次簡單查詢、或引入物化視圖/彙總表預計算。
• 微服務架構下，跨庫 JOIN 更應避免，改用 API 組裝或異步同步冗餘字段。

進階補充：架構層面優化清單

總結

SQL 優化不是背誦技巧，而是**建立「測量 → 分析 → 優化 → 驗證」閉環習慣**。上述 10 條建議覆蓋「索引、型別、結構、查詢寫法、事務、連線、架構」全鏈路。落地關鍵：將其內化為 Code Review 標準、CI 靜態掃描規則、新人入職必考知識點。