第 5 章排序与分页

站长2026-10-22 12:00:00

内容摘要

上一篇解决了"查哪些行、查哪些列"，本篇解决"以什么顺序返回、返回前多少行"。`ORDER BY` 让结果按你想要的顺序排列，`LIMIT/OFFSET` 是 Web 后台"翻页"的核心。我们会讲单列/多列排序、升降序、字符串排序的 collation 陷阱，以及分页性能优化（深分页问题）。学完你能写出业务后台最常见的"按时间倒序、每页 20 条"查询。本示例基于 MySQL 8.0，所有 SQL 均按该版本语法书写。

📖 本篇简介：上一篇解决了"查哪些行、查哪些列"，本篇解决"以什么顺序返回、返回前多少行"。ORDER BY 让结果按你想要的顺序排列，LIMIT/OFFSET 是 Web 后台"翻页"的核心。我们会讲单列/多列排序、升降序、字符串排序的 collation 陷阱，以及分页性能优化（深分页问题）。学完你能写出业务后台最常见的"按时间倒序、每页 20 条"查询。本示例基于 MySQL 8.0，所有 SQL 均按该版本语法书写。

1 为什么需要排序

如果不显式 ORDER BY，MySQL 返回的行顺序是不保证的。你今天查出来的顺序和明天查出来的顺序可能不一样——尽管数据没变。原因是 MySQL 会根据执行计划、缓存、并发情况选择不同的扫描方式（全表扫描、索引扫描、并行扫描），行的物理顺序就会变。

-- 不写 ORDER BY：每次结果顺序可能不同（生产环境禁止依赖）
SELECT * FROM students;

-- 写 ORDER BY：顺序由你说了算
SELECT * FROM students ORDER BY id;

业务系统 99% 的查询都需要排序：后台列表按时间倒序、商品列表按价格升序、排行榜按分数降序。排序是几乎所有查询的标配。

2 ORDER BY 单列排序

2.1 升序 ASC

ORDER BY 列名 ASC（ASC 是默认值，可以省略）：

-- 按成绩从低到高排
SELECT name, score FROM students ORDER BY score ASC;

-- ASC 省略，等价
SELECT name, score FROM students ORDER BY score;

2.2 降序 DESC

DESC = descending，从高到低：

-- 按成绩从高到低（排行榜场景）
SELECT name, score FROM students ORDER BY score DESC;

排行榜、最新消息、最热商品——业务里 80% 是降序。

2.3 按表达式排序

ORDER BY 后面可以跟表达式、函数：

-- 按"姓名长度"排序
SELECT name FROM students ORDER BY LENGTH(name);

-- 按"分数 * 0.7 + 年龄 * 0.3"综合分排序
SELECT name, score, age
FROM students
ORDER BY score * 0.7 + age * 0.3 DESC;

2.4 按列别名排序

ORDER BY 是少数能引用 SELECT 别名的子句之一：

-- 给成绩起了别名 分数，然后 ORDER BY 用别名
SELECT name, score * 0.7 + age * 0.3 AS total
FROM students
ORDER BY total DESC;

这是因为执行顺序里 ORDER BY 在 SELECT 之后，别名已经存在。

3 ORDER BY 多列排序

3.1 基本用法

多列排序的规则是：先按第一列排，第一列相同再按第二列排，以此类推。

-- 先按城市升序，城市相同再按成绩降序
SELECT name, city, score
FROM students
ORDER BY city ASC, score DESC;

想象一个班的成绩单：先按班级分组，同一班里按分数高低排。这就是多列排序的典型场景。

3.2 实际例子

-- 排行榜：先按成绩降序，成绩相同再按入学日期升序（先入学的排前面）
SELECT name, score, enroll_date
FROM students
ORDER BY score DESC, enroll_date ASC;

这种"分数并列时打破平局"的排序，在竞赛、考试排名里非常常见。

3.3 按列位置排序（不推荐）

MySQL 允许用"列在 SELECT 列表中的位置"代替列名：

-- SELECT 列表第 2 列是 city，第 3 列是 score
SELECT name, city, score FROM students
ORDER BY 2 ASC, 3 DESC;

这种写法可读性差、表结构变了就崩，生产代码不推荐。

4 排序中的 NULL 与字符串

4.1 NULL 的排序位置

MySQL 中 NULL 在升序时排在最前面，降序时排在最后面：

-- 升序：NULL 最先出现
SELECT name, gender FROM students ORDER BY gender ASC;
-- 结果：NULL, F, M（NULL 在最前）

-- 降序：NULL 最后出现
SELECT name, gender FROM students ORDER BY gender DESC;
-- 结果：M, F, NULL（NULL 在最后）

如果你希望 NULL 永远排最后，8.0 可以用表达式：

-- 让 NULL 在降序时也排最后（先把 NULL 转成最大）
SELECT name, gender FROM students
ORDER BY (gender IS NULL), gender DESC;

4.2 中文排序的 Collation 陷阱

这是新手最容易踩的坑。MySQL 默认的 utf8mb4_general_ci 对中文是按"二进制编码"排的，不是按拼音：

-- 假设 cities 是：北京、上海、广州、深圳
SELECT DISTINCT city FROM students ORDER BY city;
-- 你以为会按拼音：北京(B)、广州(G)、上海(S)、深圳(S)
-- 实际按 utf8 编码字节序：北京、上海、广州、深圳

要按拼音排序，需要指定 collation：

-- 按拼音排序（需要列或表使用 utf8mb4_zh_0900_as_cs 之类的 collation）
SELECT DISTINCT city FROM students
ORDER BY city COLLATE utf8mb4_zh_0900_as_cs;

MySQL 8.0 引入了 utf8mb4_zh_0900_as_cs，对中文排序支持比 5.7 好很多。

4.3 字符串大小写

默认 _ci collation 排序时不区分大小写：

-- 'ABC' 和 'abc' 视为相同，谁在前不确定
SELECT name FROM students ORDER BY name;

要区分大小写，用 _cs collation 或 BINARY。

5 LIMIT 基本用法

5.1 LIMIT n：只返回前 n 行

-- 成绩前 10 名
SELECT name, score
FROM students
ORDER BY score DESC
LIMIT 10;

LIMIT 必须放在整个 SELECT 的最后面，语法位置：

SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT ...;

5.2 LIMIT m, n：跳过 m 行取 n 行

-- 跳过 0 行，取 20 行（第 1 页）
SELECT * FROM students ORDER BY id LIMIT 0, 20;

-- 跳过 20 行，取 20 行（第 2 页）
SELECT * FROM students ORDER BY id LIMIT 20, 20;

注意：LIMIT m, n 这种写法里 m 是"偏移量"，n 是"取多少行"。它等价于 LIMIT n OFFSET m，后者可读性更好。

5.3 LIMIT 与 WHERE/ORDER BY 配合

执行顺序：WHERE 先过滤，然后 ORDER BY 排序，最后 LIMIT 截取：

-- 先过滤出北京学生，再按成绩排，再取前 3 名
SELECT name, score
FROM students
WHERE city = '北京'
ORDER BY score DESC
LIMIT 3;

这是后台"某分类下排行榜"的标准写法。

6 OFFSET 与分页

6.1 分页公式

Web 后台每页 page_size 条，第 page 页（从 1 开始）的 SQL：

-- 假设 page = 1, page_size = 20，OFFSET = 0
-- page = 2, page_size = 20，OFFSET = 20
SELECT * FROM students
ORDER BY id
LIMIT 20 OFFSET 20;   -- 第 2 页

通用公式：OFFSET = (page - 1) * page_size。

应用层代码（Python 示意）：

# 应用层拼接分页 SQL（示意，不是真实 pymysql 代码）
page = 2
page_size = 20
offset = (page - 1) * page_size
sql = f"SELECT id, name, score FROM students ORDER BY id LIMIT {page_size} OFFSET {offset};"

6.2 分页的稳定性问题

分页要求排序字段唯一、稳定。如果用 score 排序，同一分数的行顺序每次查询可能不同——用户翻页时会看到"上一页末尾、下一页开头是同一行"或者"漏掉某行"。

解决办法：加一个唯一字段作为"打破平局"的第二排序键：

-- 先按成绩降序，成绩相同按 id 升序，保证每页稳定
SELECT id, name, score
FROM students
ORDER BY score DESC, id ASC
LIMIT 20 OFFSET 40;

7 深分页问题与优化

7.1 什么是深分页

LIMIT 1000000, 20 这种"跳过 100 万行取 20 行"的查询，就是深分页。它非常慢，原因是 MySQL 要扫描并丢弃前 100 万行，网络和 CPU 都浪费。

7.2 优化一：子查询 + 主键定位

-- 慢写法：OFFSET 100 万
SELECT * FROM students ORDER BY id LIMIT 1000000, 20;

-- 快写法：先用子查询找到第 100 万行的 id，再 JOIN 回来
SELECT s.*
FROM students s
JOIN (SELECT id FROM students ORDER BY id LIMIT 1000000, 20) AS t
  ON s.id = t.id
ORDER BY s.id;

子查询 SELECT id ... LIMIT 1000000, 20 走覆盖索引，只扫描主键列，比回表快得多。

7.3 优化二：游标分页（Keyset Pagination）

如果业务允许"只翻下一页、不跳页"，用游标分页最快：

-- 第 1 页：
SELECT id, name FROM students ORDER BY id LIMIT 20;
-- 假设最后一行 id = 20

-- 第 2 页：记住上一页最后一个 id，直接 WHERE id > 20
SELECT id, name FROM students WHERE id > 20 ORDER BY id LIMIT 20;

这种写法每次都是从某个 id 开始往后扫 20 行，完全跳过 OFFSET，性能恒定。缺点是不能直接跳到第 100 页。

7.4 优化三：禁止跳页

很多业务后台干脆不允许"跳到第 100 页"，只提供"上一页/下一页"，从根本上避免深分页。这是产品 + 技术协同的最佳实践。

8 TOP N 类需求

8.1 每个分类下的 TOP N

"求每个城市成绩最高的 3 名学生"——这种需求用单条 LIMIT 搞不定，因为 LIMIT 是对整个结果集截前 N，不是每组截 N。

解决方案有三种（本章先看最简单的，后面窗口函数章节会讲更优雅的）：

-- 思路：用变量给每个城市的学生按成绩排名，然后取排名 <= 3 的
SELECT *
FROM (
    SELECT
        s.*,
        @rn := IF(@city = s.city, @rn + 1, 1) AS rn,
        @city := s.city AS dummy
    FROM students s
    CROSS JOIN (SELECT @city := '', @rn := 0) AS vars
    ORDER BY s.city, s.score DESC
) AS ranked
WHERE rn <= 3;

这段用户变量写法在 MySQL 8.0 里仍能跑，但官方推荐用窗口函数 ROW_NUMBER() OVER (PARTITION BY city ORDER BY score DESC)，后面第 9 章附近会讲。

8.2 第 N 高的成绩

"求成绩第 3 高的分数"：

-- 思路：去重后降序排，跳过 2 行取 1 行
SELECT DISTINCT score
FROM students
ORDER BY score DESC
LIMIT 1 OFFSET 2;

注意"第 N 高"通常要求去重——不然并列第二就把第三挤掉了。

9 排序分页速查表

| 需求 | 写法 | 备注 |

|---|---|---|

| 升序 | ORDER BY c ASC | ASC 默认可省 |

| 降序 | ORDER BY c DESC | 排行榜常用 |

| 多列排序 | ORDER BY a, b DESC | 先 a 后 b |

| 按别名排序 | ORDER BY 别名 DESC | ORDER BY 可用别名 |

| 取前 N 行 | LIMIT N | 放最后 |

| 分页 | LIMIT n OFFSET m | m = (page-1)*size |

| 稳定分页 | 加唯一列做平局键 | 避免翻页重复 |

| 深分页优化 | 子查询 + 主键 JOIN | 性能提升 10 倍+ |

| 游标分页 | WHERE id > 上次最后 id | 最快但不能跳页 |

| NULL 排前 | ORDER BY c ASC | 默认行为 |

10 常见坑与注意事项

坑 1：不写 ORDER BY 依赖默认顺序。

MySQL 不保证行的物理顺序，业务代码必须显式排序。

坑 2：分页排序字段不唯一导致翻页重复/漏掉。

用"业务字段 + 唯一主键"双键排序。

坑 3：深分页 `LIMIT 1000000, 20` 拖垮数据库。

用户跳页到第 100 页就是这种 SQL，要么产品禁止跳页，要么用游标分页。

坑 4：中文按默认 collation 排序不符合预期。

按拼音排序要指定 utf8mb4_zh_0900_as_cs。

坑 5：`LIMIT m, n` 与 `LIMIT n OFFSET m` 搞混。

前者 m 是偏移，n 是行数；后者顺序反过来。建议统一用 LIMIT n OFFSET m。

坑 6：ORDER BY 对列做函数/运算导致索引失效。

ORDER BY LENGTH(name) 用不上索引，大表慎用。

坑 7：NULL 在降序时排最后，与业务预期不符。

需要 NULL 永远最后时用 ORDER BY (c IS NULL), c DESC。

坑 8：把 LIMIT 写在 WHERE 前面导致语法错误。

LIMIT 必须在 SQL 最末尾。

11 练习题

练习 1：排行榜

题目：查询 students 表中成绩最高的前 5 名学生，显示姓名、城市、成绩。

SELECT name, city, score
FROM students
ORDER BY score DESC
LIMIT 5;

</details>

练习 2：多列排序

题目：按城市升序、城市相同按成绩降序，查询所有学生。

SELECT name, city, score
FROM students
ORDER BY city ASC, score DESC;

</details>

练习 3：分页查询

题目：每页 20 条，查询第 3 页的学生（按 id 升序）。

-- OFFSET = (3 - 1) * 20 = 40
SELECT id, name
FROM students
ORDER BY id
LIMIT 20 OFFSET 40;

</details>

练习 4：稳定分页

题目：在练习 3 基础上，让分页结果稳定（不出现翻页重复），应该怎么改？

-- 按 score 降序 + id 升序双键
SELECT id, name, score
FROM students
ORDER BY score DESC, id ASC
LIMIT 20 OFFSET 40;

</details>

练习 5：第 N 高

题目：查询成绩第 3 高的分数（去重）。

SELECT DISTINCT score
FROM students
ORDER BY score DESC
LIMIT 1 OFFSET 2;

</details>

练习 6：深分页优化思路

题目：用户要跳到第 10000 页（每页 20 条），直接 LIMIT 199980, 20 会很慢。请给出两种优化方案。

方案一：子查询 + 主键 JOIN

SELECT s.* FROM students s
JOIN (SELECT id FROM students ORDER BY id LIMIT 199980, 20) t
  ON s.id = t.id
ORDER BY s.id;

方案二：游标分页（如果产品允许只翻下一页）

-- 记住上一页最后一个 id，下一页用 WHERE id > 上次 id
SELECT id, name FROM students WHERE id > ? ORDER BY id LIMIT 20;

</details>

下一篇预告：第 6 章聚合函数与GROUP BY

— 全文完 —回到顶部 ↑

下载推广海报

DISCUSSION

文章回复

0 条公开回复

还没有回复，欢迎参与讨论。

第 5 章 排序与分页

目录

1 为什么需要排序

2 ORDER BY 单列排序

2.1 升序 ASC

2.2 降序 DESC

2.3 按表达式排序

2.4 按列别名排序

3 ORDER BY 多列排序

3.1 基本用法

3.2 实际例子

3.3 按列位置排序（不推荐）

4 排序中的 NULL 与字符串

4.1 NULL 的排序位置

4.2 中文排序的 Collation 陷阱

4.3 字符串大小写

5 LIMIT 基本用法

5.1 LIMIT n：只返回前 n 行

5.2 LIMIT m, n：跳过 m 行取 n 行

5.3 LIMIT 与 WHERE/ORDER BY 配合

6 OFFSET 与分页

6.1 分页公式

6.2 分页的稳定性问题

7 深分页问题与优化

7.1 什么是深分页

7.2 优化一：子查询 + 主键定位

7.3 优化二：游标分页（Keyset Pagination）

7.4 优化三：禁止跳页

8 TOP N 类需求

8.1 每个分类下的 TOP N

8.2 第 N 高的成绩

9 排序分页速查表

10 常见坑与注意事项

11 练习题

练习 1：排行榜

练习 2：多列排序

练习 3：分页查询

练习 4：稳定分页

练习 5：第 N 高

练习 6：深分页优化思路

第 5 章排序与分页