MySQL 多字段排序與置頂功能實現：ORDER BY 進階用法詳解

一、多字段排序語法

MySQL `ORDER BY` 支持多字段，按聲明順序依次排序：先按第一字段，相同則按第二字段，以此類推。

基本語法

SELECT * FROM table_name ORDER BY field1, field2;

指定排序方向

-- field1 降序，field2 升序 (默認)
SELECT * FROM table_name ORDER BY field1 DESC, field2 ASC;

-- 等價簡寫 (ASC 可省略)
SELECT * FROM table_name ORDER BY field1 DESC, field2;

實戰示例：電商商品排序

-- 銷量降序、價格升序 (銷量同則價格低者優先)
SELECT * FROM products ORDER BY sales_volume DESC, price ASC;

二、置頂功能實現方案

BBS/文章列表常見需求：置頂文章始終展示在最前，其次按時間倒序。

方案：權重字段 + 時間字段聯合索引排序

1. 表結構設計

CREATE TABLE posts (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    priority TINYINT NOT NULL DEFAULT 0 COMMENT '置頂權重：0=普通, 1=置頂, 2=全局置頂...',
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    -- 聯合索引支持 ORDER BY priority DESC, create_time DESC
    INDEX idx_priority_time (priority DESC, create_time DESC)
) ENGINE=InnoDB;

2. 查詢語句

SELECT * FROM posts 
ORDER BY priority DESC, create_time DESC;

3. 業務操作

• 置頂：`UPDATE posts SET priority = 1 WHERE id = 123;`
• 取消置頂：`UPDATE posts SET priority = 0 WHERE id = 123;`
• 多級置頂：`priority=2` (全局置頂) > `priority=1` (版塊置頂) > `priority=0` (普通)。

三、進階場景：分頁查詢優化

深分頁 `LIMIT 10000, 20` 配合 `ORDER BY` 會掃描大量行。利用覆蓋索引 + 子查詢優化：

-- 1. 覆蓋索引只掃描索引獲取 ID
SELECT id FROM posts ORDER BY priority DESC, create_time DESC LIMIT 10000, 20;

-- 2. 回表查詳細信息
SELECT * FROM posts WHERE id IN (上述子查詢) ORDER BY priority DESC, create_time DESC;

四、避坑指南

總結

• 多字段排序：`ORDER BY f1 DESC, f2 ASC`，順序決定優先級。
• 置頂功能：**權重字段 `priority` + 時間字段 `create_time` + 聯合索引** 是標準解法。
• 索引順序**必須與 ORDER BY 順序一致** (含方向)，才能避免 filesort。
• 深分頁優化：覆蓋索引查 ID → 回表查詳情。