MySQL 分頁排序查詢：正確使用 LIMIT 與 ORDER BY

想從表 `a` 倒序輸出 `id` 從 20 到 11 的記錄 (即第 2 大到第 11 大)，寫成 `SELECT * FROM a WHERE id LIMIT 10,20 ORDER BY id DESC` 會報錯。原因是 MySQL 語法規定 `ORDER BY` 必須寫在 `LIMIT` 之前。

正確語法

SELECT * FROM a ORDER BY id DESC LIMIT 10, 10;

• `ORDER BY id DESC`：先按 `id` 降序排列 (最大的在前)。
• `LIMIT 10, 10`：跳過前 10 條 (即 id 最大的 10 條)，取接下來 10 條 (第 11~20 大)。

注意：`LIMIT offset, row_count` 中 offset 從 0 開始。若要「id 從 20 到 11」，假設 id 連續且無縺隙，上述語句即可；若 id 不連續，應改用 `WHERE id BETWEEN 11 AND 20 ORDER BY id DESC`。

子查詢寫法 (兼容舊版/特殊需求)

若必須先篩選再排序 (如先 `WHERE` 後 `ORDER BY` 再 `LIMIT`)，可用子查詢：

SELECT t.* FROM (SELECT * FROM a WHERE id > 100) t ORDER BY t.id DESC LIMIT 10;

但標準單表查詢**無需子查詢**，直接 `WHERE → ORDER BY → LIMIT` 順序即可。

常見誤區對照表

大數據量分頁優化提示

當 `offset` 很大 (如 `LIMIT 1000000, 20`) 時，`ORDER BY ... LIMIT` 效能會急劇下降。優化策略：

1. 游標分頁：記住上一頁最大/最小 `id`，改用 `WHERE id > last_max_id ORDER BY id LIMIT 20`。
2. 覆蓋索引：確保 `ORDER BY` 欄位有索引，且 `SELECT` 只取索引欄位 (避免回表)。
3. 延遲關聯：`SELECT * FROM a INNER JOIN (SELECT id FROM a ORDER BY id LIMIT 1000000, 20) t USING (id)`。

掌握標準語法順序 `SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT`，分頁排序不再出錯。