MySQL SELECT 語法深度解析：從基礎查詢到進階優化技巧

完整語法骨架

SELECT
    [ALL | DISTINCT | DISTINCTROW]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT | SQL_BIG_RESULT]
    [SQL_BUFFER_RESULT]
    [SQL_CACHE | SQL_NO_CACHE]
    [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
      [WHERE where_condition]
      [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]]
      [HAVING where_condition]
      [ORDER BY {col_name | expr | position} [ASC | DESC], ...]
      [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    ]
    [INTO OUTFILE 'file_name' [CHARACTER SET charset_name] export_options
     | INTO DUMPFILE 'file_name'
     | INTO var_name [, var_name] ...]
    [FOR UPDATE | LOCK IN SHARE MODE];

核心子句詳細講解

1. SELECT 修飾符

2. select_expr：列、別名、表達式、函數

-- 1. 所有列
SELECT * FROM t;

-- 2. 指定列 + 別名 (AS 可省略)
SELECT id AS user_id, CONCAT(first_name, ' ', last_name) AS full_name FROM users;

-- 3. 表達式與函數
SELECT price * quantity AS total, ROUND(price * 1.1, 2) AS price_with_tax FROM orders;

-- 4. 聚合函數
SELECT COUNT(*), AVG(salary), MAX(hire_date) FROM employees;

-- 5. 窗口函數 (8.0+)
SELECT name, salary,
       ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn,
       SUM(salary) OVER (PARTITION BY dept_id) AS dept_total
FROM employees;

3. FROM 與表關聯

-- 別名 (AS 可省略)
SELECT e.name, d.name AS dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.id;

-- 子查詢作派生表 (5.6+ 物化優化)
SELECT derived.dept_name, derived.avg_sal
FROM (SELECT d.name AS dept_name, AVG(e.salary) AS avg_sal
      FROM employees e JOIN departments d ON e.dept_id = d.id
      GROUP BY d.id) derived
WHERE derived.avg_sal > 5000;

4. WHERE 條件構建

• 比較運算符：=, <>, !=, >, <, >=, <=, <=> (NULL 安全等於)。
• 範圍：BETWEEN ... AND ... (含邊界)。
• 集合：IN (...), NOT IN (...) (慎用 NOT IN 含 NULL 陷阱)。
• 模糊：LIKE 'prefix%' (走索引), LIKE '%suffix' (全掃)。
• NULL 判斷：IS NULL, IS NOT NULL。
• 邏輯組合：AND 優先於 OR，用括號明確優先級。

5. GROUP BY 與 HAVING

-- 分組聚合
SELECT dept_id, COUNT(*) AS cnt, AVG(salary) AS avg_sal
FROM employees
GROUP BY dept_id
HAVING COUNT(*) > 5;  -- 過濾聚合後的組

-- WITH ROLLUP (超級聚合行)
SELECT dept_id, COUNT(*) FROM employees GROUP BY dept_id WITH ROLLUP;
-- 最後一行 dept_id=NULL 為總計

注意：HAVING 可引用聚合函數/別名；WHERE 不可。

6. ORDER BY 排序

• 可引用：列名、別名、select_expr 位置 (1-based)、表達式。
• ASC (默認) / DESC。
• NULLS FIRST / LAST (8.0.2+)。
• 排序列若在索引中且順序匹配，可避免 filesort。

7. LIMIT 分頁

-- 前 10 行
LIMIT 10
-- 跳過 20 行取 10 行 (第 3 頁)
LIMIT 20, 10
-- 標準 SQL 寫法 (8.0+)
LIMIT 10 OFFSET 20

深分頁性能陷阱：LIMIT 1000000, 10 需掃描 100 萬行再丟棄。
優化：WHERE id > (SELECT id FROM t ORDER BY id LIMIT 1000000, 1) ORDER BY id LIMIT 10 (需唯一索引)。

8. INTO OUTFILE / DUMPFILE 導出文件

-- CSV 導出 (服務器端文件，需 FILE 權限)
SELECT * FROM orders
INTO OUTFILE '/tmp/orders.csv'
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY '\n';

進階優化技巧

覆蓋索引

SELECT a, b FROM t WHERE c = 1 → INDEX idx_c_ab (c, a, b)，Extra: Using index，零回表。

避免隱式轉換

-- ❌ 字符列比數字
WHERE phone = 13800138000
-- ✅
WHERE phone = '13800138000'

OR 拆分為 UNION

-- ❌ 兩列各有索引但 OR 導致全掃
WHERE a = 1 OR b = 2
-- ✅
SELECT * FROM t WHERE a = 1
UNION
SELECT * FROM t WHERE b = 2;

EXISTS 替代 IN (大子查詢)

-- ✅ 相關子查詢，外表驅動
SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.t1_id = t1.id);

派生表物化 (5.6+)

SELECT * FROM (SELECT ... FROM t) derived → 物化臨時表 + 索引，避免重複展開。

常見錯誤與最佳實踐清單

總結

SELECT 是 SQL 最核心、變體最豐富的語句。掌握 語法骨架 → 子句語義 → 優化修飾符 → 索引配合 → 反模式規避 五層知識，才能寫出既正確又高效的查詢。建議：每條上線 SQL 必經 EXPLAIN 驗證，慢查詢日誌定期審計，覆蓋索引作為首選優化手段。