語法結構
SELECT select_list
FROM table_name
WHERE search_condition
[ORDER BY ...] [LIMIT ...];
比較運算符
| 運算符 | 含義 | 示例 | 索引友好 |
= | 等於 | status = 1 | ✅ 最佳 |
<> / != | 不等於 | status != 1 | ❌ 通常全掃 |
>, >=, <, <= | 範圍比較 | age >= 18 | ✅ 範圍掃描 |
BETWEEN ... AND ... | 閉區間 | age BETWEEN 18 AND 60 | ✅ 等價 >= AND <= |
IN (val1, val2, ...) | 集合成員 | status IN (1,2,3) | ✅ 常量列表走索引 |
NOT IN | 非集合成員 | status NOT IN (1,2) | ⚠️ 子查詢慎用 |
IS NULL / IS NOT NULL | 空值判斷 | deleted_at IS NULL | ⚠️ 部分引擎不走索引 |
LIKE 'prefix%' | 前綴匹配 | name LIKE 'Zhang%' | ✅ 走索引 |
LIKE '%suffix' | 後綴/包含匹配 | name LIKE '%son' | ❌ 全掃 |
REGEXP / RLIKE | 正則匹配 | phone REGEXP '^1[3-9]\\d{9}$' | ❌ 8.0 前全掃 |
SARGable (Search ARGument ABLE) 原則
「讓索引能被搜索參數使用」—— 將列孤立在運算符一側,避免對列進行函數/運算/類型轉換。
❌ 非 SARGable(殺索引)
WHERE YEAR(created_at) = 2024
WHERE DATE(created_at) = '2024-01-15'
WHERE LOWER(email) = 'test@ex.com'
WHERE amount + 100 > 500
WHERE SUBSTRING(phone, 1, 3) = '138'
WHERE id + 0 = 1001 -- 隱式轉換
✅ SARGable(走索引)
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'
WHERE email = 'test@ex.com' -- 列定義同字符集/排序規則
WHERE amount > 400
WHERE phone LIKE '138%'
WHERE id = 1001
NULL 值處理陷阱
-- 錯誤:NULL = NULL 為 UNKNOWN,不為 TRUE
WHERE col = NULL -- 永遠不匹配
WHERE col != NULL -- 永遠不匹配
-- 正確
WHERE col IS NULL
WHERE col IS NOT NULL
WHERE COALESCE(col, 'default') = 'value' -- 應用層處理更好
-- NOT IN 陷阱
WHERE id NOT IN (1, 2, NULL) -- 結果永遠為空!
-- 修正
WHERE id NOT IN (SELECT id FROM t WHERE id IS NOT NULL)
-- 或
WHERE NOT EXISTS (SELECT 1 FROM t WHERE t.id = outer.id)
模式匹配最佳實踐
| 需求 | 推薦寫法 | 索引 | 備選 |
| 精確匹配 | = | ✅ | |
| 前綴匹配 | LIKE 'abc%' | ✅ | |
| 後綴匹配 | 反轉列 + 前綴 / 全文索引 | ❌ | 生成列 + 索引 |
| 包含匹配 | 全文索引 / Elasticsearch | ❌ | Trigram 索引 (pg_trgm) |
| 正則表達式 | 應用層預過濾 + REGEXP | ❌ | MySQL 8.0 功能性索引 |
動態條件構建模式
-- 應用層構建(推薦)
$where = ['1=1']; $params = [];
if ($name) { $where[] = 'name LIKE ?'; $params[] = "%$name%"; }
if ($status) { $where[] = 'status = ?'; $params[] = $status; }
$sql = "SELECT * FROM users " . implode(' AND ', $where);
-- 避免:WHERE 1=1 AND (name = '' OR 1=1) 等偽動態
多條件索引設計
-- 查詢:WHERE a=1 AND b=2 ORDER BY c
-- 最優索引:INDEX (a, b, c) -- 等值列在前,排序列在後
-- 查詢:WHERE a=1 OR b=2
-- 無完美索引 → 改寫 UNION ALL,各建單列索引
總結檢查清單
- [ ] WHERE 列是否有合適索引?
- [ ] 是否對列使用函數/運算/隱式轉換?
- [ ] OR 條件是否可改寫為 UNION ALL?
- [ ] NULL 處理是否正確(IS NULL / COALESCE)?
- [ ] LIKE 是否為前綴模式?
- [ ] 參數化查詢是否避免字符串拼接?
- [ ] EXPLAIN 確認 type=ref/range,而非 ALL/index?
copyright ©
萬盛學電腦網 all rights reserved