PHP小码农

Appearance

条件查询与排序 ​

本节讲解如何用 WHERE 精确筛选数据、用 ORDER BY 控制结果顺序，以及 LIMIT、OFFSET、DISTINCT、IN、BETWEEN、LIKE 等高频配套子句。学完本节，你将能写出精确、高效、可读的查询条件，并理解 NULL 在条件判断中的特殊行为。

前置知识

阅读本节前，建议先了解：CRUD 增删改查

基础概念 ​

WHERE 子句接收一个布尔表达式（Boolean Expression），对 FROM 产生的每一行求值，只有结果为 true 的行才会进入后续处理。

概念说明
谓词Predicate，即返回真/假/未知的表达式，如 price > 100
三值逻辑Three-Valued Logic，SQL 的布尔结果有三种：true、false、NULL（未知）
过滤Filter，在 WHERE 中筛掉不符合条件的行
排序键Sort Key，ORDER BY 中用于比较的列或表达式
稳定性排序的确定性，即在排序键取值相同时结果是否稳定
空值排序NULLS FIRST / NULLS LAST，指定 NULL 排在最前还是最后

三值逻辑（Three-Valued Logic） 是理解 WHERE 的关键。SQL 的布尔运算不是二值而是三值：

表达式结果说明
NULL = NULLNULL未知与未知比较仍是未知
NULL <> 1NULL任何与 NULL 的比较都是未知
NULL IS NULLtrueIS 系列专门用于判空，返回确定的布尔值
true AND NULLNULL与未知做 AND 结果未知
false AND NULLfalsefalse 已经决定整体结果
true OR NULLtruetrue 已经决定整体结果

关键结论：WHERE 只保留结果为 true 的行，结果为 false 与 NULL 的行都会被丢弃。因此 WHERE price <> 100 会把 price 为 NULL 的行也一并排除——这通常是符合预期但容易被忽略的行为。

PostgreSQL 的布尔值在表中可以存储为 boolean 类型（取值 true/false/NULL），三值逻辑同样适用于列值为 NULL 的场景。

语法与用法 ​

WHERE 与 ORDER BY 的基本形态：

text
SELECT 列列表
FROM 表名
WHERE 条件表达式
ORDER BY 排序键 [ASC | DESC] [NULLS FIRST | NULLS LAST]
LIMIT 行数 OFFSET 偏移量;

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

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 CHECK (stock >= 0),
  tags       TEXT[]        NOT NULL DEFAULT '{}',
  launched   DATE,                                  -- 可能为 NULL（未上架）
  is_active  BOOLEAN       NOT NULL DEFAULT true,
  created_at TIMESTAMPTZ   NOT NULL DEFAULT now()
);

INSERT INTO products (sku, name, category, price, stock, tags, launched, is_active) VALUES
  ('SKU-1001', '机械键盘',      'peripherals', 349.00, 120, '{keyboard, wired}',  DATE '2024-03-01', true),
  ('SKU-1002', '无线鼠标',      'peripherals', 129.00, 300, '{mouse, wireless}',  DATE '2024-05-12', true),
  ('SKU-1003', 'USB-C 扩展坞',  'accessories', 199.00,  80, '{hub, usb-c}',       DATE '2024-07-20', true),
  ('SKU-1004', '显示器支架',    'accessories', 259.00,  45, '{ergonomic}',        DATE '2024-09-01', true),
  ('SKU-1005', 'HDMI 线缆',     'cable',        39.00, 500, '{hdmi, cable}',      DATE '2025-01-15', true),
  ('SKU-1006', 'USB-C 线缆',    'cable',        49.00, 420, '{usb-c, cable}',     DATE '2025-02-01', true),
  ('SKU-1007', '机械键盘 TKL',  'peripherals', 429.00,   0, '{keyboard, tkl}',    DATE '2025-06-10', false),
  ('SKU-1008', '笔记本支架',    'accessories', 159.00,  60, '{ergonomic, laptop}', NULL,             true),
  ('SKU-1009', '雷电 4 硬盘盒', 'storage',     899.00,  12, '{thunderbolt}',      NULL,             true),
  ('SKU-1010', '鼠标垫',        'accessories',  29.00, 800, '{mouse, pad}',       DATE '2025-08-01', true);

条件表达式最常用的六类：

sql
-- 1. 比较运算
SELECT sku, name, price FROM products WHERE price > 200;

-- 2. 逻辑组合
SELECT sku, name, price FROM products
WHERE category = 'peripherals' AND price < 400;

-- 3. 集合判断
SELECT sku, name, category FROM products
WHERE category IN ('cable', 'storage');

-- 4. 区间判断
SELECT sku, name, price FROM products
WHERE price BETWEEN 100 AND 300;

-- 5. 模式匹配
SELECT sku, name FROM products WHERE name LIKE '机械%';

-- 6. 空值判断
SELECT sku, name, launched FROM products WHERE launched IS NULL;

详细说明 ​

比较运算符与逻辑组合 ​

运算符含义示例
=等于price = 199.00
<> 或 !=不等于category <> 'cable'
> < >= <=大小比较stock >= 100
AND逻辑与，两侧都为真才为真is_active AND stock > 0
OR逻辑或，任一为真即为真category = 'cable' OR price < 50
NOT逻辑非NOT is_active
IS NULL / IS NOT NULL判空launched IS NULL
IS TRUE / IS FALSE / IS UNKNOWN布尔判等（含 NULL）is_active IS TRUE

AND 优先级高于 OR，混用时务必加括号，否则语义与阅读直觉不符：

sql
-- 危险！实际含义是：category = 'cable' OR (price < 100 AND is_active)
SELECT sku, name FROM products
WHERE category = 'cable' OR price < 100 AND is_active;

-- 正确：意图明确
SELECT sku, name FROM products
WHERE (category = 'cable' OR price < 100) AND is_active;

布尔列可以直接作为条件，不必写 = true：

sql
-- 简洁写法
SELECT sku, name FROM products WHERE is_active;

-- 完整写法（等价）
SELECT sku, name FROM products WHERE is_active = true;

-- 判假：不能用 = false（虽然可以，但 NULL 会被排除），推荐 IS FALSE
SELECT sku, name FROM products WHERE is_active IS FALSE;

NULL 的条件判断 ​

NULL 在条件中必须使用 IS NULL / IS NOT NULL，用 = 判断永远不成立：

sql
-- 错误！结果永远为 0 行（NULL = NULL 的结果是 NULL，不是 true）
SELECT sku, name FROM products WHERE launched = NULL;

-- 正确
SELECT sku, name FROM products WHERE launched IS NULL;

NULL 参与的算术与字符串拼接结果也是 NULL，可以用 COALESCE 提供兜底值：

sql
-- launched 为 NULL 时显示"未上架"
SELECT sku, name, COALESCE(launched::text, '未上架') AS launched_text
FROM products
WHERE launched IS NULL;

-- 注意！任何值与 NULL 拼接结果都是 NULL，整列都会变成 NULL
SELECT name, name || launched::text AS wrong_concat FROM products LIMIT 3;

-- 正确：用 COALESCE 或 concat()（concat 会忽略 NULL）
SELECT name, concat(name, ' ', launched) AS safe_concat FROM products LIMIT 3;

在排序与聚合中，NULL 的处理规则与条件判断不同，需要单独记忆：

场景NULL 的行为
ORDER BY ... ASC（默认）NULL 排在最后（最大）
ORDER BY ... DESCNULL 排在最前
NULLS FIRST / NULLS LAST显式指定，覆盖默认行为
count(*)统计所有行，包含 NULL 行
count(列)忽略该列为 NULL 的行
sum / avg / max / min忽略 NULL；若全为 NULL 则返回 NULL
GROUP BY所有 NULL 归为同一组
UNIQUE 约束默认允许多个 NULL 并存（NULL 互不相等）

模式匹配：LIKE 与正则 ​

LIKE 支持两个通配符：% 匹配任意长度（含零）字符，_ 匹配单个字符。

sql
-- 前缀匹配：能用上索引（前提是索引为普通 B-tree 且数据库 locale 为 C，或建了 text_pattern_ops 索引）
SELECT sku, name FROM products WHERE name LIKE '机械%';

-- 包含匹配：两侧都有 %，B-tree 索引失效
SELECT sku, name FROM products WHERE name LIKE '%线缆%';

-- 单字符通配
SELECT sku, name FROM products WHERE sku LIKE 'SKU-100_';

-- 大小写不敏感匹配
SELECT sku, name FROM products WHERE name ILIKE '%usb%';

-- 转义通配符本身：匹配含下划线的字符串
SELECT sku, name FROM products WHERE sku LIKE 'SKU\_10%' ESCAPE '\';

LIKE 的能力有限，复杂模式用正则表达式：

sql
-- 名称以"机械"或"无线"开头
SELECT sku, name FROM products WHERE name ~ '^(机械|无线)';

-- 大小写不敏感正则
SELECT sku, name FROM products WHERE name ~* 'usb';

-- SKU 符合 SKU-四位数字
SELECT sku, name FROM products WHERE sku ~ '^SKU-[0-9]{4}$';

三种匹配方式的对比：

方式语法大小写索引支持适用场景
LIKELIKE 'ab%'敏感前缀可走 B-tree简单前缀/后缀匹配
ILIKEILIKE 'ab%'不敏感需 pg_trgm 或表达式索引用户模糊搜索
正则~ ~* !~可选需 pg_trgm 或表达式索引复杂模式校验
相似度%（pg_trgm）—gin_trgm_ops拼写容错搜索
sql
-- 用 pg_trgm 加速模糊搜索
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_products_name_trgm ON products USING gin (name gin_trgm_ops);

-- 相似度匹配（阈值默认 0.3）
SELECT sku, name, similarity(name, '机械键盘') AS sim
FROM products
WHERE name % '机械键盘'
ORDER BY sim DESC;

集合判断：IN、ANY、EXISTS ​

IN 用于判断值是否属于一个集合；ANY/ALL 用于与数组或子查询比较。

sql
-- IN：枚举若干可能值
SELECT sku, name FROM products WHERE category IN ('cable', 'storage');

-- NOT IN 的陷阱：集合中含 NULL 时结果恒为空
SELECT sku, name FROM products WHERE category NOT IN ('cable', NULL);   -- 返回 0 行

-- 安全写法：先排除 NULL，或用 NOT EXISTS
SELECT sku, name FROM products
WHERE category NOT IN (SELECT category FROM products WHERE category IS NOT NULL);

-- = ANY：等价于 IN
SELECT sku, name FROM products WHERE category = ANY (ARRAY['cable', 'storage']);

-- <> ALL：等价于 NOT IN（同样有 NULL 陷阱）
SELECT sku, name FROM products WHERE category <> ALL (ARRAY['cable', 'storage']);

BETWEEN 是闭区间（含两端），且对日期类型容易产生"漏掉当天数据"的错误：

sql
-- 价格在 100 到 300 之间（含 100 和 300）
SELECT sku, name, price FROM products WHERE price BETWEEN 100 AND 300;

-- 等价写法
SELECT sku, name, price FROM products WHERE price >= 100 AND price <= 300;

-- 日期范围：BETWEEN 到 DATE 会漏掉当天有时间部分的数据
SELECT sku, name FROM products
WHERE launched BETWEEN DATE '2024-01-01' AND DATE '2024-12-31';   -- 仅含 2024 年

-- 推荐写法：半开区间 [起, 止)
SELECT sku, name FROM products
WHERE launched >= DATE '2024-01-01' AND launched < DATE '2025-01-01';

排序：ORDER BY ​

ORDER BY 支持多列、表达式、别名、序号，以及自定义排序规则。

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, name, price, stock FROM products ORDER BY price * stock DESC;

-- 按输出列别名排序
SELECT sku, price * stock AS inventory_value FROM products ORDER BY inventory_value DESC;

-- 按 SELECT 列表序号排序（不推荐，可读性差）
SELECT sku, name, price FROM products ORDER BY 3 DESC;

NULL 的排序位置可以用 NULLS FIRST / NULLS LAST 显式控制：

sql
-- 未上架的排在最后（与 ASC 默认一致，但显式声明更清晰）
SELECT sku, name, launched FROM products
ORDER BY launched ASC NULLS LAST;

-- 未上架的排在最前，优先处理待上架商品
SELECT sku, name, launched FROM products
ORDER BY launched ASC NULLS FIRST;

-- 更直观的写法：先按是否为空分组，再按日期
SELECT sku, name, launched FROM products
ORDER BY (launched IS NULL), launched;

用 CASE WHEN 实现自定义优先级排序，例如让缺货商品排在最前：

sql
SELECT sku, name, stock, price
FROM products
ORDER BY
  CASE
    WHEN stock = 0       THEN 1      -- 缺货最优先
    WHEN stock < 100     THEN 2      -- 库存偏低
    ELSE 3                           -- 充足
  END,
  price DESC;

排序的稳定性问题需要特别注意：当排序键存在重复值时，PostgreSQL 不保证相同键值行的相对顺序。分页查询时必须追加一个唯一列作为兜底排序键：

sql
-- 不稳定：category 有重复值，分页时可能出现行重复或遗漏
SELECT sku, category FROM products ORDER BY category LIMIT 3;

-- 稳定：追加唯一的 id 作为最终排序键
SELECT id, sku, category FROM products ORDER BY category, id LIMIT 3;

DISTINCT 与分页 ​

DISTINCT 去除结果集中的重复行；DISTINCT ON 是 PostgreSQL 扩展，用于"每组只取一行"。

sql
-- 去重：列出所有出现过的分类
SELECT DISTINCT category FROM products ORDER BY category;

-- 多列去重：组合唯一
SELECT DISTINCT category, is_active FROM products ORDER BY category, is_active;

-- DISTINCT ON：每个分类只取价格最高的一行（PostgreSQL 扩展）
SELECT DISTINCT ON (category) category, sku, name, price
FROM products
ORDER BY category, price DESC;
text
 category    | sku      | name         | price
-------------+----------+--------------+--------
 accessories | SKU-1004 | 显示器支架    | 259.00
 cable       | SKU-1006 | USB-C 线缆   |  49.00
 peripherals | SKU-1007 | 机械键盘 TKL | 429.00
 storage     | SKU-1009 | 雷电 4 硬盘盒 | 899.00

注意：DISTINCT ON 的 ORDER BY 必须以 DISTINCT ON 的列开头，否则报错。

分页使用 LIMIT + OFFSET：

sql
-- 第 1 页：每页 3 条
SELECT id, sku, name FROM products ORDER BY id LIMIT 3 OFFSET 0;

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

-- 第 3 页
SELECT id, sku, name FROM products ORDER BY id LIMIT 3 OFFSET 6;

OFFSET 在大偏移量下性能很差，因为它需要先扫描并丢弃前 N 行。深分页应改用键集分页（Keyset Pagination）：

sql
-- 深分页优化：记住上一页最后一行的 (id)，直接从该位置继续
SELECT id, sku, name
FROM products
WHERE id > 6                    -- 上一页最后一行的 id
ORDER BY id
LIMIT 3;

两种分页方式对比：

对比项LIMIT + OFFSET键集分页（Keyset）
语法OFFSET nWHERE key > 上次最后值
大偏移性能差，需扫描丢弃 n 行好，直接定位
能否跳页可以，任意页只能顺序翻页
数据变动影响可能重复或遗漏稳定
适用场景后台管理少量页信息流、无限滚动

实战示例 ​

下面用一个"商品筛选列表"场景，串起条件与排序的常用组合。

sql
-- 步骤 1：筛选"上架中、库存充足、价格在 50~500 之间"的商品
SELECT sku, name, category, price, stock
FROM products
WHERE is_active
  AND stock > 0
  AND price BETWEEN 50 AND 500
ORDER BY price DESC;
text
 sku      | name         | category    | price  | stock
----------+--------------+-------------+--------+-------
 SKU-1001 | 机械键盘      | peripherals | 349.00 |   120
 SKU-1004 | 显示器支架    | accessories | 259.00 |    45
 SKU-1003 | USB-C 扩展坞  | accessories | 199.00 |    80
 SKU-1008 | 笔记本支架    | accessories | 159.00 |    60
 SKU-1002 | 无线鼠标      | peripherals | 129.00 |   300
(5 rows)

注意 SKU-1007（429.00）虽然价格落在区间内，但它 is_active = false 且 stock = 0，因此被排除；SKU-1009（899.00）则超出价格上限。

sql
-- 步骤 2：按关键词模糊搜索（大小写不敏感）
SELECT sku, name, price
FROM products
WHERE name ILIKE '%支架%'
ORDER BY price DESC;
text
 sku      | name       | price
----------+------------+--------
 SKU-1004 | 显示器支架  | 259.00
 SKU-1008 | 笔记本支架  | 159.00
(2 rows)
sql
-- 步骤 3：按标签数组筛选（数组包含运算符 @>）
SELECT sku, name, tags
FROM products
WHERE tags @> ARRAY['ergonomic']       -- 标签中含 ergonomic
ORDER BY price DESC;
text
 sku      | name       | tags
----------+------------+------------------------
 SKU-1004 | 显示器支架  | {ergonomic}
 SKU-1008 | 笔记本支架  | {ergonomic,laptop}
(2 rows)
sql
-- 步骤 4：找出"待上架"商品（launched 为 NULL），并排在前面
SELECT sku, name, launched
FROM products
ORDER BY (launched IS NULL) DESC, launched, sku;
text
 sku      | name          | launched
----------+---------------+------------
 SKU-1008 | 笔记本支架     | (null)
 SKU-1009 | 雷电 4 硬盘盒  | (null)
 SKU-1001 | 机械键盘       | 2024-03-01
 SKU-1002 | 无线鼠标       | 2024-05-12
 ...
sql
-- 步骤 5：分页取第 2 页（每页 4 条），并用 id 保证稳定排序
SELECT id, sku, name, price
FROM products
ORDER BY price DESC, id
LIMIT 4 OFFSET 4;
text
 id | sku      | name         | price
----+----------+--------------+--------
  5 | SKU-1005 | HDMI 线缆     |  39.00
  6 | SKU-1006 | USB-C 线缆    |  49.00
 10 | SKU-1010 | 鼠标垫        |  29.00
  3 | SKU-1003 | USB-C 扩展坞  | 199.00

上例可见：OFFSET 分页结果是"跳过前 4 条后按同序取 4 条"，因此不会出现"第二页价格一定低于第一页"的直觉结果——分页结果依赖于排序键的完整性与稳定性。

sql
-- 步骤 6：统计各条件的命中行数，快速核对筛选逻辑
SELECT
  count(*)                                              AS total,
  count(*) FILTER (WHERE is_active)                     AS active_cnt,
  count(*) FILTER (WHERE stock > 0)                     AS in_stock_cnt,
  count(*) FILTER (WHERE launched IS NULL)              AS pending_cnt,
  count(*) FILTER (WHERE category IN ('cable','storage')) AS cable_storage_cnt
FROM products;
text
 total | active_cnt | in_stock_cnt | pending_cnt | cable_storage_cnt
-------+------------+--------------+-------------+-------------------
    10 |          9 |            9 |           2 |                 3
sql
-- 步骤 7：组合排序——缺货优先、其次按库存升序、最后按价格降序
SELECT sku, name, stock, price
FROM products
WHERE is_active
ORDER BY
  (stock = 0) DESC,     -- 缺货排最前
  stock ASC,
  price DESC
LIMIT 5;
text
 sku      | name       | stock | price
----------+------------+-------+--------
 SKU-1009 | 雷电 4 硬盘盒 |    12 | 899.00
 SKU-1004 | 显示器支架   |    45 | 259.00
 SKU-1008 | 笔记本支架   |    60 | 159.00
 SKU-1003 | USB-C 扩展坞 |    80 | 199.00
 SKU-1002 | 无线鼠标     |   300 | 129.00
(5 rows)

注意事项 ​

  • WHERE 中不能使用 SELECT 定义的列别名。因为 WHERE 在 SELECT 之前逻辑求值。ORDER BY 与 GROUP BY 可以使用别名（但有例外，如 GROUP BY 中别名与列名冲突时优先按列名解析）。
sql
-- 错误！WHERE 中别名不存在
SELECT price * 1.13 AS price_tax FROM products WHERE price_tax > 200;

-- 正确：重复表达式，或用子查询
SELECT price * 1.13 AS price_tax FROM products WHERE price * 1.13 > 200;

-- 也可以：子查询
SELECT * FROM (SELECT price * 1.13 AS price_tax FROM products) t WHERE price_tax > 200;
  • NOT IN 遇到 NULL 会静默返回空结果。这是最隐蔽的一类查询错误。
sql
-- 错误！子查询结果若含 NULL，整条语句返回 0 行
SELECT sku FROM products
WHERE category NOT IN (SELECT category FROM products_archive);

-- 正确：排除 NULL
SELECT sku FROM products
WHERE category NOT IN (SELECT category FROM products_archive WHERE category IS NOT NULL);

-- 更推荐：用 NOT EXISTS，天然不受 NULL 影响
SELECT p.sku FROM products p
WHERE NOT EXISTS (
  SELECT 1 FROM products_archive a WHERE a.category = p.category
);
  • LIKE '%...%' 无法使用 B-tree 索引。前导通配符会使索引失去作用，大表上会导致全表扫描。需要模糊搜索时使用 pg_trgm 的 GIN 索引，或改用全文检索。
  • 在 WHERE 中对列做运算或函数调用会导致索引失效。这称为"条件不可索引化（Non-Sargable）"。
sql
-- 错误！对列使用函数，索引失效
SELECT sku, name FROM products WHERE upper(category) = 'CABLE';

-- 正确：把函数作用在常量侧；若确需函数索引，则创建表达式索引
CREATE INDEX idx_products_category_upper ON products (upper(category));
SELECT sku, name FROM products WHERE upper(category) = 'CABLE';
  • BETWEEN 是闭区间，用日期时注意边界语义。BETWEEN '2024-01-01' AND '2024-12-31' 对 timestamptz 列会漏掉 12 月 31 日当天 00:00 之后的数据，推荐用半开区间 >= AND <。
  • ORDER BY 不带唯一列时排序不稳定。分页、LIMIT、数据导出等依赖顺序的场景，务必追加唯一键。
  • OFFSET 大偏移量性能差。OFFSET 100000 需要扫描并丢弃 10 万行。深分页改用键集分页。
  • DISTINCT 有排序开销。它需要对结果集排序或哈希去重，在对大表使用时注意代价；如果只是想判断是否存在，用 EXISTS 更高效。
  • IN 列表过长会拖慢解析与规划。几千个值的 IN 列表应改为 VALUES + JOIN，或使用临时表、= ANY(数组)。
sql
-- 不推荐：超长 IN 列表
SELECT sku FROM products WHERE sku IN ('SKU-1001', 'SKU-1002', /* ...数千个... */ 'SKU-9999');

-- 推荐：改为数组或 JOIN 临时表
SELECT p.sku FROM products p
JOIN unnest(ARRAY['SKU-1001','SKU-1002','SKU-9999']::text[]) AS t(sku)
  ON t.sku = p.sku;

最佳实践 ​

  1. 条件从左到右按"选择性强 → 弱"排列仅影响可读性，不影响性能。 优化器会自行重排条件，不必为"性能"而刻意调整 WHERE 中条件的顺序；应按业务逻辑分组、加括号提升可读性。
  2. 判空统一用 IS NULL / IS NOT NULL，避免 = NULL 与 NOT IN (含 NULL 的子查询) 两个经典陷阱。子查询排除用 NOT EXISTS。
  3. 时间范围统一用半开区间 >= 起 AND < 止。 这样无论列类型是 date 还是 timestamptz，语义都清晰且不漏数据。
  4. 为高频过滤列建索引，并保持条件"可索引化"。 不要在 WHERE 中对列做函数运算；必要时创建表达式索引或函数索引。
sql
-- 为常用过滤组合建复合索引（等值列在前，范围列在后）
CREATE INDEX idx_products_active_category_price
  ON products (is_active, category, price);

-- 部分索引：只索引活跃商品，体积更小、更高效
CREATE INDEX idx_products_active_name ON products (name) WHERE is_active;

-- 表达式索引：支持大小写不敏感等值查询
CREATE INDEX idx_products_sku_lower ON products (lower(sku));
  1. 需要"每组取一行"时用 DISTINCT ON（PostgreSQL 扩展），比 ROW_NUMBER() 窗口函数写法简洁；但要注意 ORDER BY 必须以 DISTINCT ON 列开头。
sql
-- 每个分类最贵的一件商品
SELECT DISTINCT ON (category) category, sku, name, price
FROM products
WHERE is_active
ORDER BY category, price DESC;
  1. 分页排序键必须是"唯一且稳定"的组合。 常见做法是 ORDER BY 创建时间 DESC, id DESC，这样即使创建时间重复也不会错乱。
  2. 深分页用键集分页。 用 WHERE (sort_col, id) < (上一页末值, 末 id) 的行值比较语法，可一次定位，无需扫描丢弃。
sql
-- 键集分页：行值比较写法（PostgreSQL 支持元组比较）
SELECT id, sku, name, price
FROM products
WHERE (price, id) < (199.00, 3)      -- 上一页最后一行的 (price, id)
ORDER BY price DESC, id DESC
LIMIT 4;
  1. 用 EXPLAIN (ANALYZE, BUFFERS) 验证条件是否走索引。 看到 Seq Scan 且过滤后行数远小于表行数，说明索引缺失或条件不可索引化。
sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT sku, name FROM products WHERE category = 'cable' AND price < 100;

下一节

继续学习：聚合查询与分组

参考链接 ​