PHP小码农

Appearance

排序（ORDER BY） ​

ORDER BY 决定结果集的输出顺序。本节讲解单列与多列排序、ASC / DESC 与每列独立方向、NULLS FIRST / NULLS LAST 与默认行为、按表达式与别名排序、COLLATE 排序规则、中文与多语言排序、排序稳定性与分页的关系，以及排序的性能问题。学完本节，你将能写出结果确定、性能可控的排序查询。

前置知识

阅读本节前，建议先了解：WHERE 条件筛选

基础概念 ​

ORDER BY 在逻辑执行顺序中位于 SELECT 与 DISTINCT 之后、LIMIT 之前，因此它能引用输出列别名，但不能影响 WHERE 的过滤结果。

术语英文说明
排序键Sort KeyORDER BY 中用于比较的列或表达式
排序规则Collation字符串比较依据的语言文化规则
稳定性Determinism排序键取值相同时结果顺序是否确定
空值排序Null OrderingNULL 排在前面还是后面
排序节点Sort Node执行计划中真正做排序的算子
增量排序Incremental SortPostgreSQL 13+ 在部分有序输入上只排序剩余键

NULL 的默认排序位置是理解 ORDER BY 的关键：PostgreSQL 把 NULL 视为最大值。

排序方向默认 NULL 位置等价显式写法
ASC（默认）最后ASC NULLS LAST
DESC最前DESC NULLS FIRST

注意这与部分数据库（如 MySQL 把 NULL 视为最小值）行为相反，迁移时需特别留意。

ORDER BY 的稳定性（Determinism） 决定了分页是否可靠：当排序键存在重复值时，PostgreSQL 不保证这些行的相对顺序，因此分页可能重复或遗漏。

语法与用法 ​

text
SELECT 列列表
FROM 表名
[WHERE 条件]
ORDER BY 排序表达式 [ASC | DESC] [NULLS FIRST | NULLS LAST] [, ...];

准备本节贯穿使用的示例表：

sql
-- 商品表：含可空字段与重复分类，便于演示排序细节
CREATE TABLE products (
  id         INTEGER       GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku        TEXT          NOT NULL UNIQUE,
  name       TEXT          NOT NULL,
  category   TEXT          NOT NULL,
  price      NUMERIC(10, 2) NOT NULL CHECK (price >= 0),
  stock      INTEGER       NOT NULL DEFAULT 0,
  rating     NUMERIC(3, 1),                      -- 可为 NULL（暂无评分）
  launched   DATE,                               -- 可为 NULL（未上架）
  created_at TIMESTAMPTZ   NOT NULL DEFAULT now()
);

INSERT INTO products (sku, name, category, price, stock, rating, launched) VALUES
  ('SKU-1001', '机械键盘',      'peripherals', 349.00, 120, 4.7, DATE '2024-03-01'),
  ('SKU-1002', '无线鼠标',      'peripherals', 129.00, 300, 4.5, DATE '2024-05-12'),
  ('SKU-1003', 'USB-C 扩展坞',  'accessories', 199.00,  80, 4.2, DATE '2024-07-20'),
  ('SKU-1004', '显示器支架',    'accessories', 259.00,  45, 4.8, DATE '2024-09-01'),
  ('SKU-1005', 'HDMI 线缆',     'cable',        39.00, 500, 3.9, DATE '2025-01-15'),
  ('SKU-1006', 'USB-C 线缆',    'cable',        49.00, 420, 4.1, DATE '2025-02-01'),
  ('SKU-1007', '机械键盘 TKL',  'peripherals', 429.00,   0, NULL, DATE '2025-06-10'),
  ('SKU-1008', '笔记本支架',    'accessories', 159.00,  60, NULL, NULL),
  ('SKU-1009', '雷电 4 硬盘盒', 'storage',     899.00,  12, 4.9, NULL),
  ('SKU-1010', '鼠标垫',        'accessories',  29.00, 800, 4.0, DATE '2025-08-01');

常见排序写法：

sql
-- 单列升序（ASC 可省略）
SELECT sku, name, price FROM products ORDER BY price;

-- 单列降序
SELECT sku, name, price FROM products ORDER BY price DESC;

-- 多列：分类升序、同分类内价格降序
SELECT sku, category, price FROM products ORDER BY category ASC, price DESC;

-- 按表达式排序
SELECT sku, price, stock, price * stock AS inventory_value
FROM products ORDER BY price * stock DESC;

详细说明 ​

单列与多列排序 ​

多列排序时每一列可以独立指定方向，比较方式是从左到右依次比较，前一个键相等时才看后一个键。

sql
-- 分类升序，同分类按库存降序，库存相同再按 SKU 升序
SELECT sku, category, stock
FROM products
ORDER BY category ASC, stock DESC, sku ASC;
text
 sku      | category    | stock
----------+-------------+-------
 SKU-1010 | accessories |   800
 SKU-1003 | accessories |    80
 SKU-1008 | accessories |    60
 SKU-1004 | accessories |    45
 SKU-1005 | cable       |   500
 SKU-1006 | cable       |   420
 SKU-1002 | peripherals |   300
 SKU-1001 | peripherals |   120
 SKU-1007 | peripherals |     0
 SKU-1009 | storage     |    12
(10 rows)

ASC / DESC 与每列独立方向 ​

sql
-- 全列同向
SELECT sku, price FROM products ORDER BY price ASC;

-- 混合方向：分类升序、价格降序
SELECT sku, category, price FROM products ORDER BY category ASC, price DESC;

-- 用表达式取反实现反向排序（不推荐，可读性差）
SELECT sku, price FROM products ORDER BY -price ASC;

-- 推荐：直接用 DESC
SELECT sku, price FROM products ORDER BY price DESC;

NULLS FIRST / NULLS LAST ​

NULL 默认被视为最大值，因此 ASC 排最后、DESC 排最前。用 NULLS FIRST / NULLS LAST 显式覆盖：

sql
-- 默认行为：rating 为 NULL 的排最后
SELECT sku, name, rating FROM products ORDER BY rating;

-- 显式声明（与 ASC 默认一致）
SELECT sku, name, rating FROM products ORDER BY rating ASC NULLS LAST;

-- 让未评分的商品排最前，优先处理待评价
SELECT sku, name, rating FROM products ORDER BY rating ASC NULLS FIRST;

-- DESC 默认 NULL 在前；显式让其排最后
SELECT sku, name, rating FROM products ORDER BY rating DESC NULLS LAST;
text
 sku      | name         | rating
----------+--------------+--------
 SKU-1007 | 机械键盘 TKL  |
 SKU-1008 | 笔记本支架   |
 SKU-1005 | HDMI 线缆    |  3.9
 SKU-1010 | 鼠标垫       |  4.0
 SKU-1006 | USB-C 线缆   |  4.1
 ...

另一种控制 NULL 位置的技巧：先按"是否为空"布尔值排序，false 在前：

sql
-- 先排非空（false < true），再按 rating 降序
SELECT sku, name, rating
FROM products
ORDER BY (rating IS NULL) ASC, rating DESC NULLS LAST;

按表达式与别名排序 ​

ORDER BY 可以使用任意表达式，也可以引用 SELECT 中定义的别名（因为它在 SELECT 之后执行）。

sql
-- 按表达式排序
SELECT sku, price, stock FROM products ORDER BY price * stock DESC;

-- 按别名排序（推荐，语义清晰）
SELECT sku, price * stock AS inventory_value
FROM products
ORDER BY inventory_value DESC;

-- 按函数结果排序
SELECT sku, name FROM products ORDER BY length(name) DESC, name;

-- 按 CASE 表达式排序
SELECT sku, name, stock FROM products
ORDER BY CASE WHEN stock = 0 THEN 0 ELSE 1 END, stock;

按列序号排序（不推荐） ​

ORDER BY 2 表示按 SELECT 列表的第 2 项排序。它写起来短，但可读性差且容易在修改 SELECT 列表后出错：

sql
-- 不推荐：序号含义不直观，调整 SELECT 列表后极易出错
SELECT sku, name, price FROM products ORDER BY 3 DESC;

-- 推荐：显式写列名或别名
SELECT sku, name, price FROM products ORDER BY price DESC;

按序号排序唯一有价值的场景是快速调试（例如配合 SELECT * 探查列位置），生产 SQL 中应避免。

COLLATE 指定排序规则 ​

字符串比较受排序规则（Collation） 影响，它定义了字符的排序顺序、大小写敏感性等。PostgreSQL 支持在列定义、数据库、表达式（COLLATE 子句）三个层级指定。

sql
-- 查看数据库默认排序规则
SELECT datcollate, datctype FROM pg_database WHERE datname = current_database();
text
  datcollate   |   datctype
---------------+---------------
 en_US.UTF-8   | en_US.UTF-8
(1 row)
sql
-- 用 COLLATE 在排序时临时切换规则
SELECT name FROM products ORDER BY name COLLATE "C";

-- 大小写不敏感排序：先转小写再排（通用做法）
SELECT username FROM users ORDER BY lower(username);

-- 或用 ICU 排序规则（PostgreSQL 10+，需数据库启用 ICU）
SELECT name FROM products ORDER BY name COLLATE "zh-x-icu";

常用排序规则对比：

排序规则大小写中文顺序适用场景
C大写在前（按字节）按 UTF-8 码点追求确定性、可移植
en_US.UTF-8大小写混排按本地化规则英文为主
zh-x-icu按本地化拼音顺序中文排序
zh_CN.UTF-8按本地化按本地化规则中文（glibc）
und-x-icu通用ICU 默认多语言混合
sql
-- 用 C 规则排序：数字与字母按字节序，中文按码点
SELECT name FROM products ORDER BY name COLLATE "C" LIMIT 5;

-- 用 ICU 中文规则排序：中文按拼音
SELECT name FROM products ORDER BY name COLLATE "zh-x-icu" LIMIT 5;

排序规则会直接影响索引能否被用于排序：索引必须与 ORDER BY 使用相同排序规则，否则仍需额外 Sort 节点。

排序稳定性与分页的关系 ​

当排序键存在重复值时，相同键值行的相对顺序不确定。分页查询必须追加唯一列作为兜底排序键，否则可能出现行重复或遗漏。

sql
-- 不稳定：accessories 分类有 4 行，顺序不确定
SELECT sku, category FROM products ORDER BY category LIMIT 3 OFFSET 0;

-- 稳定：追加唯一的 id 作为最终排序键
SELECT id, sku, category FROM products ORDER BY category, id LIMIT 3 OFFSET 0;
text
 id | sku      | category
----+----------+-------------
  3 | SKU-1003 | accessories
  4 | SKU-1004 | accessories
  8 | SKU-1008 | accessories
(3 rows)
sql
-- 深分页：不稳定排序会导致第 2 页出现重复行
-- 错误！只有 category 一个排序键
SELECT id, sku, category FROM products ORDER BY category LIMIT 3 OFFSET 3;

-- 正确：追加唯一键
SELECT id, sku, category FROM products ORDER BY category, id LIMIT 3 OFFSET 3;
text
 id | sku      | category
----+----------+-------------
 10 | SKU-1010 | accessories
  5 | SKU-1005 | cable
  6 | SKU-1006 | cable
(3 rows)

排序的性能 ​

排序主要有两种实现路径：

路径触发条件代价特征
索引有序读取ORDER BY 与索引列顺序、方向、排序规则一致无需排序，直接按索引顺序读
Sort 节点无匹配索引需在内存或磁盘排序
Incremental Sort输入已按排序键前缀有序PostgreSQL 13+，只排剩余键
Top-N Sort配合 LIMIT只维护前 N 个元素，内存占用小
sql
-- 建索引
CREATE INDEX idx_products_category_price ON products (category, price DESC);

-- 排序键与索引完全匹配：可走索引有序读取，无 Sort 节点
EXPLAIN (ANALYZE, BUFFERS)
SELECT sku, category, price FROM products
ORDER BY category, price DESC
LIMIT 5;
text
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Limit  (cost=0.14..1.63 rows=5 width=23) (actual time=0.015..0.019 rows=5 loops=1)
   Buffers: shared hit=2
   ->  Index Scan using idx_products_category_price on products
         (cost=0.14..2.99 rows=10 width=23) (actual time=0.013..0.016 rows=5 loops=1)
         Buffers: shared hit=2
 Planning Time: 0.112 ms
 Execution Time: 0.035 ms
(6 rows)
sql
-- 排序键与索引顺序不符：需要 Sort 节点
EXPLAIN (ANALYZE, BUFFERS)
SELECT sku, category, price FROM products
ORDER BY category, price ASC;      -- 索引是 price DESC
text
                                     QUERY PLAN
-------------------------------------------------------------------------------------
 Sort  (cost=1.26..1.28 rows=10 width=23) (actual time=0.035..0.036 rows=10 loops=1)
   Sort Key: category, price
   Sort Method: quicksort  Memory: 25kB
   Buffers: shared hit=1
   ->  Seq Scan on products  (cost=0.00..1.10 rows=10 width=23)
         (actual time=0.008..0.011 rows=10 loops=1)
 Planning Time: 0.098 ms
 Execution Time: 0.055 ms
(7 rows)

小表上 Sort 代价可忽略，但大表排序可能超出 work_mem 而落盘（执行计划中会显示 Sort Method: external merge Disk: xxxx）。

sql
-- Top-N 排序：配合 LIMIT，只在内存中维护前 N 个候选
EXPLAIN (ANALYZE, BUFFERS)
SELECT sku, price FROM products ORDER BY price DESC LIMIT 3;
text
                             QUERY PLAN
---------------------------------------------------------------------
 Limit  (cost=1.28..1.28 rows=3 width=12) (actual time=0.026..0.027 rows=3 loops=1)
   ->  Sort  (cost=1.26..1.28 rows=10 width=12) (actual time=0.024..0.025 rows=3 loops=1)
         Sort Key: price DESC
         Sort Method: top-N heapsort  Memory: 25kB
         ->  Seq Scan on products  (cost=0.00..1.10 rows=10 width=12)
(5 rows)

ORDER BY 与 LIMIT 配合 ​

LIMIT 不带 ORDER BY 时，返回的行是任意的（取决于扫描顺序），因此分页必须配 ORDER BY：

sql
-- 错误认知：以为 LIMIT 10 返回"前 10 条"
SELECT sku, name FROM products LIMIT 10;   -- 顺序不确定

-- 正确：分页必须指定确定的排序
SELECT sku, name FROM products ORDER BY id LIMIT 10;
sql
-- 分页三要素：稳定的排序键 + LIMIT + OFFSET
SELECT id, sku, name, price
FROM products
ORDER BY price DESC, id DESC
LIMIT 5 OFFSET 0;

-- 第 2 页
SELECT id, sku, name, price
FROM products
ORDER BY price DESC, id DESC
LIMIT 5 OFFSET 5;

实战示例 ​

下面用"商品列表的多维排序需求"串联本节要点。

sql
-- 步骤 1：默认排序——价格升序，未上架商品排最后
SELECT sku, name, price, launched
FROM products
ORDER BY launched ASC NULLS LAST, price ASC;
text
 sku      | name         | price  |  launched
----------+--------------+--------+------------
 SKU-1001 | 机械键盘      | 349.00 | 2024-03-01
 SKU-1002 | 无线鼠标      | 129.00 | 2024-05-12
 SKU-1003 | USB-C 扩展坞  | 199.00 | 2024-07-20
 SKU-1004 | 显示器支架    | 259.00 | 2024-09-01
 SKU-1005 | HDMI 线缆     |  39.00 | 2025-01-15
 SKU-1006 | USB-C 线缆    |  49.00 | 2025-02-01
 SKU-1007 | 机械键盘 TKL  | 429.00 | 2025-06-10
 SKU-1010 | 鼠标垫        |  29.00 | 2025-08-01
 SKU-1008 | 笔记本支架    | 159.00 |
 SKU-1009 | 雷电 4 硬盘盒 | 899.00 |
(10 rows)
sql
-- 步骤 2：业务优先级排序——缺货最优先，其次库存偏低，最后按评分降序
SELECT sku, name, stock, rating
FROM products
ORDER BY
  CASE
    WHEN stock = 0   THEN 1     -- 缺货最紧急
    WHEN stock < 50  THEN 2
    WHEN stock < 200 THEN 3
    ELSE 4
  END,
  rating DESC NULLS LAST,
  sku;
text
 sku      | name         | stock | rating
----------+--------------+-------+--------
 SKU-1007 | 机械键盘 TKL  |     0 |
 SKU-1009 | 雷电 4 硬盘盒 |    12 |  4.9
 SKU-1004 | 显示器支架    |    45 |  4.8
 SKU-1003 | USB-C 扩展坞  |    80 |  4.2
 SKU-1008 | 笔记本支架    |    60 |
 SKU-1001 | 机械键盘      |   120 |  4.7
 SKU-1002 | 无线鼠标      |   300 |  4.5
 SKU-1006 | USB-C 线缆    |   420 |  4.1
 SKU-1010 | 鼠标垫        |   800 |  4.0
 SKU-1005 | HDMI 线缆     |   500 |  3.9
(10 rows)

注意 SKU-1009 与 SKU-1004 同属"库存偏低"档，因此按评分降序时 4.9 排在 4.8 之前；SKU-1003 与 SKU-1008 同属"库存中等"档，SKU-1008 因 rating 为 NULL 排在档内最后。

sql
-- 步骤 3：中文排序规则对比
SELECT name FROM products ORDER BY name COLLATE "C" LIMIT 4;
text
     name
---------------
 HDMI 线缆
 USB-C 扩展坞
 USB-C 线缆
 显示器支架
(4 rows)
sql
-- 步骤 4：稳定分页——按价格降序，id 作为兜底键
SELECT id, sku, price
FROM products
ORDER BY price DESC, id DESC
LIMIT 4 OFFSET 4;
text
 id | sku      | price
----+----------+--------
  4 | SKU-1004 | 259.00
  3 | SKU-1003 | 199.00
  8 | SKU-1008 | 159.00
  2 | SKU-1002 | 129.00
(4 rows)
sql
-- 步骤 5：验证排序是否走索引
CREATE INDEX idx_products_category_price ON products (category, price DESC);

EXPLAIN (ANALYZE, BUFFERS)
SELECT sku, category, price
FROM products
ORDER BY category, price DESC
LIMIT 5;
text
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Limit  (cost=0.14..1.63 rows=5 width=23) (actual time=0.018..0.022 rows=5 loops=1)
   Buffers: shared hit=2
   ->  Index Scan using idx_products_category_price on products
         (cost=0.14..2.99 rows=10 width=23) (actual time=0.016..0.019 rows=5 loops=1)
         Buffers: shared hit=2
 Planning Time: 0.121 ms
 Execution Time: 0.039 ms
(6 rows)

计划中没有 Sort 节点，说明排序由索引有序读取完成，这正是我们想要的结果。

注意事项 ​

  • ORDER BY 不带唯一列时排序不稳定。分页、LIMIT、数据导出等依赖顺序的场景，务必追加唯一键。
sql
-- 不稳定
SELECT id, sku, category FROM products ORDER BY category LIMIT 3 OFFSET 3;

-- 稳定
SELECT id, sku, category FROM products ORDER BY category, id LIMIT 3 OFFSET 3;
  • NULL 默认被视为最大值。这与 MySQL 相反，迁移 SQL 时务必检查 ORDER BY 的 NULL 位置是否符合业务预期。
sql
-- 未评分的排最后（ASC 默认）
SELECT sku, rating FROM products ORDER BY rating;

-- 未评分的排最前
SELECT sku, rating FROM products ORDER BY rating NULLS FIRST;
  • LIMIT 不带 ORDER BY 时结果顺序任意。分页查询必须同时写 ORDER BY 与 LIMIT。
  • 按列序号排序（ORDER BY 2）不推荐。修改 SELECT 列表后极易产生错误，且阅读时无法直接看出排序键。
  • ORDER BY 中的别名可能被同名列覆盖。当别名与输入列同名时，PostgreSQL 优先按输入列解析。
sql
-- 危险：别名与输入列同名
SELECT sku, price / 100 AS price FROM products ORDER BY price DESC;
-- 实际按 products.price 排序，而不是 price / 100

-- 安全：别名不要与列名重名
SELECT sku, price / 100 AS price_in_hundreds FROM products ORDER BY price_in_hundreds DESC;
  • 排序规则（Collation）不一致会导致索引无法用于排序。索引建立时的排序规则必须与 ORDER BY 的规则匹配。
sql
-- 索引默认使用列排序规则
CREATE INDEX idx_products_name ON products (name);

-- 以下查询无法用该索引排序（排序规则不同）
SELECT name FROM products ORDER BY name COLLATE "C";

-- 需要单独建对应排序规则的索引
CREATE INDEX idx_products_name_c ON products (name COLLATE "C");
  • DISTINCT 与 ORDER BY 组合有限制。ORDER BY 的表达式必须出现在 SELECT 列表中。
sql
-- 错误！
SELECT DISTINCT category FROM products ORDER BY price;

-- 正确
SELECT DISTINCT category FROM products ORDER BY category;
  • 大表排序可能落盘。执行计划中出现 Sort Method: external merge Disk: NNNkB 表示超出 work_mem，应调大 work_mem 或改用索引有序读取。
sql
-- 会话级临时调大，仅对当前连接生效
SET work_mem = '64MB';

-- 查看当前设置
SHOW work_mem;
  • ORDER BY 与 LIMIT 组合时优化器会用 Top-N 排序，内存占用与 N 成正比，而非与表行数成正比。

最佳实践 ​

  1. 分页排序键必须是"唯一且稳定"的组合。推荐 ORDER BY created_at DESC, id DESC，即使时间重复也不会错乱。
sql
-- 稳定的分页排序
SELECT id, sku, name
FROM products
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 0;
  1. 为高频排序建匹配的索引，索引列顺序与方向都要与 ORDER BY 一致。
sql
-- 支撑 ORDER BY category, price DESC
CREATE INDEX idx_products_category_price ON products (category, price DESC);
  1. 多列排序时方向要与索引保持一致。混合方向（a ASC, b DESC）在 PostgreSQL 中可以用 (a ASC, b DESC) 索引支持，但 (a ASC, b ASC) 索引则无法支持 b DESC。
sql
-- 匹配混合方向排序的索引
CREATE INDEX idx_products_cat_asc_price_desc ON products (category ASC, price DESC);
  1. 用 NULLS FIRST / NULLS LAST 显式声明 NULL 位置。即使与默认行为一致，显式写出也能让意图清晰、跨数据库迁移更安全。
sql
SELECT sku, name, rating FROM products ORDER BY rating DESC NULLS LAST;
  1. 优先让排序走索引有序读取，避免 Sort 节点。用 EXPLAIN 确认是否存在 Sort；若存在且数据量大，考虑调整索引或 work_mem。
sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT sku, category, price FROM products ORDER BY category, price DESC LIMIT 10;
  1. 深分页改用键集分页，避免 OFFSET 扫描丢弃大量行，同时天然稳定。
sql
-- 键集分页：从上一次的最后一行继续
SELECT id, sku, price
FROM products
WHERE (price, id) < (259.00, 4)
ORDER BY price DESC, id DESC
LIMIT 4;
  1. 中文排序需要明确的排序规则。若数据库使用 C 规则，中文按码点排序（近似按 UTF-8 字节序）；需要拼音顺序时使用 ICU 规则或 COLLATE "zh-x-icu"。
sql
-- 拼音顺序（需 ICU 支持）
SELECT name FROM products ORDER BY name COLLATE "zh-x-icu";
  1. 导出数据时始终指定 ORDER BY。否则导出的行顺序不确定，跨环境对比会出现"数据不一致"的假象。

下一节

继续学习：分页（LIMIT / OFFSET）

参考链接 ​