MySQL 依兩欄位差值降序排序：正確寫法與效能優化

錯誤示範

原回答建議 `ALTER TABLE ... ADD c3 INT IDENTITY` 並 `SELECT c3=c1-c2`，這在 MySQL 中**語法錯誤**（無 `IDENTITY` 關鍵字、賦值語句不能放在 SELECT 選項）且需額外儲存空間、維護觸發器，屬反模式。

標準 SQL 寫法（MySQL 5.7+ / 8.0 通用）

SELECT *, (c1 - c2) AS diff
FROM `table`
ORDER BY diff DESC;

• 直接在 `ORDER BY` 使用運算式，無需新增欄位、無額外 I/O。
• 別名 `diff` 可在 `ORDER BY` 直接引用，符合 SQL 標準。

進階：加速大資料量排序

1. 虛擬欄位 + 索引

ALTER TABLE `table`
ADD COLUMN `diff` INT AS (c1 - c2) STORED,
ADD INDEX `idx_diff` (`diff`);

• `STORED` 虛擬欄位在寫入時計算並持久化，佔用磁碟但查詢極快（走索引）。
• MySQL 5.7.6+ 支援；8.0 更優化。

2. 覆蓋索引

CREATE INDEX `idx_c1_c2_cover` ON `table` (c1, c2);

若只需 `c1, c2, diff` 三欄，覆蓋索引可避免回表。

3. 分頁查詢優化

SELECT * FROM `table`
WHERE id > (SELECT id FROM `table` ORDER BY (c1-c2) DESC LIMIT 10000, 1)
ORDER BY (c1-c2) DESC LIMIT 20;

避免 `OFFSET` 深分頁掃描大量行。

避坑指南

• 若 `c1`、`c2` 為 `NULL`，差值結果為 `NULL` 會排在最前/最後，建議 `COALESCE(c1,0) - COALESCE(c2,0)`。
• 浮點數欄位直接相減可能產生精度問題，考慮 `ROUND(c1-c2, 2)`。
• 不要在應用層撈全表再排序，資料量大時 OOM 風險極高。