查询过滤、排序与分页：服务真实列表场景

查询过滤、排序与分页：服务真实列表场景

真实的列表页几乎不会展示全表数据，而是「按条件筛选、按某字段排序、每页固定条数」。本节讲解如何用 WHERE、ORDER BY、LIMIT / OFFSET 实现这些需求，并介绍全文搜索与索引优化。

用 WHERE 过滤数据

WHERE 子句用于指定「只返回满足条件的行」，是查询中最常用的部分。

SELECT id, email, name FROM users
WHERE status = 'active' AND created_at > '2026-01-01';
​

常用条件运算符：

• 比较运算符：=、<>（不等于）、>、<、>=、<=
• 逻辑组合：AND、OR、NOT
• 范围：BETWEEN a AND b
• 集合：IN ('a', 'b', 'c')（属于某几个值之一）
• 模糊匹配：LIKE、ILIKE（不区分大小写的模糊匹配）
• 空值判断：IS NULL、IS NOT NULL

-- 查找昵称包含「张」且已激活的用户
SELECT id, name FROM users
WHERE name ILIKE '%张%' AND status = 'active';
​

ILIKE 是 PostgreSQL 特有的不区分大小写模糊匹配，对中文等无大小写语言也兼容良好，是前端搜索常用工具。

用 ORDER BY 排序

ORDER BY 指定结果按哪些列、以什么顺序排列。

SELECT id, name, created_at FROM users
ORDER BY created_at DESC
LIMIT 10;
​

要点：

• ASC（升序，默认）、DESC（降序）。
• 可按多列排序，如「先按状态升序，再按创建时间降序」。
• 「最新优先」「热门优先」等列表需求，本质上都是 ORDER BY 的组合。

分页：LIMIT 与 OFFSET

当结果可能很多时，不能一次性返回全部，需要分页。最基础的分页用 LIMIT（每页条数）配合 OFFSET（跳过多少条）。

-- 每页 20 条，取第 3 页
SELECT id, title FROM posts
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;
​

OFFSET 分页简单直观，但在「页码很深」时性能会下降，因为数据库仍需扫描并跳过前 N 条。对深层分页，更推荐游标分页（keyset pagination）：记住上一页最后一条的排序值，下一页从该值之后取。

-- 游标分页：已知上一页最后一篇的 created_at 与 id
SELECT id, title, created_at FROM posts
WHERE (created_at, id) < ('上一页时间', '上一页ID')
ORDER BY created_at DESC, id DESC
LIMIT 20;
​

游标分页在大数据量、无限滚动场景下明显优于 OFFSET。

全文搜索概念

LIKE / ILIKE 适合简单的子串匹配，但面对「按相关性排序、支持多词、支持中文分词」的真正搜索需求时显得力不从心。PostgreSQL 提供内置的全文搜索能力：

• 把文本预处理成可检索的「词向量」（tsvector），并建立索引。
• 用「查询词向量」（tsquery）进行匹配，并能按相关性打分排序。
• 适合文章、商品描述等较长文本的搜索。

全文搜索的完整用法较复杂，本节只建立概念认知。在文档量极大或需要语义级搜索时，还可进一步结合第 9 章的向量检索。

索引：让查询快起来

没有索引时，数据库要逐行扫描整张表来满足 WHERE 条件——数据量大时极慢。索引是一种加速查询的数据结构，类似书的目录，让数据库能快速定位到符合条件的行。

索引的几个关键点：

• 自动索引：主键与唯一约束会自动创建索引。
• 手动建索引：对常用于 WHERE、ORDER BY、JOIN 的列，应手动创建索引。
• 不是越多越好：索引会加速查询，但会拖慢写入（每次写入都要维护索引），并占用存储。只为真正需要的列建索引。

常见索引类型：

• B-tree（默认）：适合等值、范围、排序，绝大多数场景用它。
• GiST / GIN：适合全文搜索、数组、JSONB 键等复杂类型。

判断查询为何慢，可用 EXPLAIN 查看数据库的执行计划，看它是否走了索引、扫描了多少行。这是 SQL 性能调优的核心工具。

查询编写习惯

• 先写 WHERE 限定范围，再决定要哪些列，避免 SELECT *。
• 列表查询务必带 LIMIT，防止误返回海量数据。
• 排序字段尽量有索引支持。
• 深分页用游标替代 OFFSET。
• 复杂慢查询用 EXPLAIN 分析，针对性加索引。

小结

WHERE 过滤、ORDER BY 排序、LIMIT / OFFSET（或游标）分页，是支撑真实列表与搜索场景的三件套；全文搜索与索引则决定了查询能力的天花板与性能。把这些用熟，你的查询就能既灵活又高效。下一节进入关系型数据库的精华——多表联结与聚合统计。