SQL 審核是資料庫治理的「左移」關鍵環節，在開發階段攔截效能隱患，成本是上線後的 1/10。本文系統梳理審核體系、慢查詢分析五步法、優化器局限、Hint 使用、索引優化、常用 SQL 重寫模式。
| 維度 | 檢查項 |
|---|---|
| 命名規範 | 表/欄位小寫下劃線、無保留字、業務前綴 |
| 類型選擇 | 整數優於字串、定長優於變長、最小滿足業務長度、NOT NULL 默認 |
| 關聯設計 | 外鍵約束 (或應用層保證)、主鍵類型一致、索引覆蓋 JOIN 欄位 |
| 保留欄位 | id (PK)、created_at、updated_at、deleted_at (軟刪)、version (樂觀鎖) |
| 約束與預設 | DEFAULT 值、CHECK 約束 (8.0+)、UNIQUE 業務鍵 |
| 資料量預估 | 初始/增長/歸檔策略、是否需分區/分表 |
| 訪問模式 | 核心查詢路徑、熱點資料識別、讀寫比例 |
| 維度 | 檢查項 |
|---|---|
| 執行頻率 | 高頻 SQL 必須有索引支援、無全表掃描 |
| 索引匹配 | EXPLAIN 驗證 type ≥ ref、key 非 NULL、無 Using filesort/temporary |
| 單次成本 | rows 掃描數、臨時表大小、排序開銷 |
| 鎖影響 | 行鎖範圍、死鎖風險、長事務持有鎖時間 |
| 事務上下文 | 事務大小、隔離級別、是否混合 DDL/DML |
SELECT * → 明確列名慢查詢日誌三大指標：
| 指標 | 含義 | 健康標準 |
|---|---|---|
| Query_time | 總響應時間 | 業務 SLA 內 |
| Rows_examined | 掃描行數 | ≈ Rows_sent (理想 1:1) |
| Rows_sent | 返回行數 | 業務必要最小集 |
若 Rows_examined ≫ Rows_sent，必須優化索引或重寫 SQL。
EXPLAIN SELECT * FROM t WHERE a = 1;
| 關鍵欄位 | 目標值 | 異常處理 |
|---|---|---|
| type | ≥ ref (const/eq_ref/ref/range) | ALL/index = 全掃，加索引 |
| key | 非 NULL | NULL = 無索引可用 |
| rows | 盡可能小 | 大 = 索引選擇性差或條件不匹配 |
| Extra | 無 filesort/temporary | 有 = 需優化索引順序/覆蓋索引 |
SET profiling = 1;
-- 執行慢 SQL
SHOW PROFILES; -- 找到 Query_ID
SHOW PROFILE FOR QUERY 4; -- 詳細耗時分解
SHOW PROFILE CPU FOR QUERY 4; -- CPU 耗時
關注高耗時狀態：Sending data、Sorting result、Opening tables、Waiting for table lock。
SET optimizer_trace = 'enabled=on', end_markers_in_json=on;
SET optimizer_trace_max_mem_size = 1048576;
-- 執行查詢
SELECT * FROM information_schema.optimizer_trace;
查看為什麼選擇 Plan A 而非 Plan B，理解成本模型偏差來源。
ANALYZE TABLE 更新| Hint | 用途 | 範例 |
|---|---|---|
| FORCE INDEX | 強制使用索引 | SELECT * FROM t FORCE INDEX(idx_a) WHERE a=1 |
| IGNORE INDEX | 忽略錯誤索引 | SELECT * FROM t IGNORE INDEX(bad_idx) WHERE a=1 |
| USE INDEX | 建議使用 (優化器可拒絕) | SELECT * FROM t USE INDEX(idx_a) WHERE a=1 |
| STRAIGHT_JOIN | 強制連接順序 | SELECT * FROM t1 STRAIGHT_JOIN t2 ON ... |
| MAX_EXECUTION_TIME | 限制查詢最長時間 (ms) | SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM t |
| QB_NAME | 查詢塊命名 (配合其他 Hint) | SELECT /*+ QB_NAME(main) */ * FROM t |
⚠️ 慎用 FORCE INDEX：資料分佈變化可能導致反效果，優先修正統計資訊。
-- 慢：優化器將外表壓入子查詢
SELECT * FROM film WHERE film_id IN (SELECT film_id FROM film_actor WHERE actor_id=1);
-- 快：JOIN 重寫
SELECT f.* FROM film f INNER JOIN film_actor fa ON f.film_id=fa.film_id WHERE fa.actor_id=1;
-- 慢：先合併再 LIMIT，臨時表大
(SELECT * FROM actor ORDER BY last_name) UNION ALL (SELECT * FROM customer ORDER BY last_name) LIMIT 20;
-- 快：各子句先 LIMIT
(SELECT * FROM actor ORDER BY last_name LIMIT 20) UNION ALL (SELECT * FROM customer ORDER BY last_name LIMIT 20) LIMIT 20;
-- 慢：全表掃描
SELECT MIN(actor_id) FROM actor WHERE first_name='PENELOPE';
-- 快：LIMIT 重寫 (需索引支援)
SELECT actor_id FROM actor WHERE first_name='PENELOPE' ORDER BY actor_id LIMIT 1;
-- 錯誤：You can't specify target table for update in FROM clause
UPDATE tbl SET cnt = (SELECT COUNT(*) FROM tbl WHERE type = outer_tbl.type);
-- 正確：多表 UPDATE + 衍生表
UPDATE tbl INNER JOIN (SELECT type, COUNT(*) AS cnt FROM tbl GROUP BY type) d USING(type) SET tbl.cnt = d.cnt;
ANALYZE TABLE 更新統計資訊CHECK TABLE 檢測損壞OPTIMIZE TABLE / ALTER TABLE ... ENGINE=InnoDB 整理碎片| 場景 | 優化前 | 優化後 | 原理 |
|---|---|---|---|
| 大批量導入 (MyISAM) | INSERT 逐條 | 禁用索引 → LOAD DATA → 啟用索引 | 避免索引維護開銷 |
| 大批量導入 (InnoDB) | INSERT 逐條 | 關閉唯一檢查 → 批次 INSERT → 開啟檢查 | SET unique_checks=0 |
| INSERT 批次 | 單條 INSERT | 多值表 INSERT (100-1000 行/批) | 減少網路往返、解析開銷 |
| ORDER BY 索引排序 | filesort | 複合索引 (WHERE 欄位, ORDER BY 欄位) + 同序 ASC/DESC | 索引天然有序 |
| GROUP BY 禁止排序 | Using temporary; Using filesort | ORDER BY NULL | 跳過隱式排序 |
| 子查詢轉 JOIN | IN/EXISTS 子查詢 | INNER JOIN + 條件下推 | 避免臨時表、優化器更好優化 |
| OR 條件索引 | OR 導致全掃 | UNION ALL 分拆 (各側有索引) | 各分支走索引 |
| 深分頁 | LIMIT 100000, 20 | 延遲關聯 / 書籤分頁 | 避免掃描大量 OFFSET 行 |
| 大查詢拆解 | 單條複雜 SQL | 多條簡單 SQL + 應用層組裝 | 降低優化器複雜度、利用快取 |
每日：慢查詢 TOP 20 分析 → 索引優化/ SQL 重寫/ 審核補漏
每週：SHOW GLOBAL STATUS 基線對比 → 參數微調/ 架構評估
每月：pt-index-usage 未用索引清理、pt-duplicate-key-checker 重複索引合併
每季：版本升級評估、效能基線重測、災備演練
SQL 審核與優化非一次性專案，而是持續的工程實踐。建立「審核→監控→分析→優化→驗證」閉環，讓資料庫效能隨業務演進持續優化。
