数据库原理与选型 / 系统课时

常见慢查询与 SQL 改写

常见慢查询与 SQL 改写

本课解决“已经建立索引,SQL 仍然很慢”的问题。完成后,你能识别破坏索引利用的常见写法,并通过范围条件、匹配方式、键集分页和预聚合改写查询。

学习目标

  • 识别函数包裹列、前置通配符和无效排序
  • 使用范围查询与键集分页减少扫描
  • 将重复相关子查询改写为一次聚合

先建立可验证的数据与索引

后续示例围绕订单表展开。时间使用可按字典序排序的 ISO 格式文本,因此同一时区下可直接使用半开区间筛选。

sql
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  order_no TEXT NOT NULL,
  customer_name TEXT NOT NULL,
  status TEXT NOT NULL,
  amount NUMERIC NOT NULL,
  created_at TEXT NOT NULL
);

INSERT INTO orders VALUES
  (1, 'O1001', 'Alice', 'PAID', 88.00, '2026-07-29 23:50:00'),
  (2, 'O1002', 'Bob', 'PAID', 120.00, '2026-07-30 08:00:00'),
  (3, 'O1003', 'Alice', 'PENDING', 35.00, '2026-07-30 09:30:00'),
  (4, 'O1004', 'Carol', 'PAID', 66.00, '2026-07-31 00:05:00');

CREATE INDEX idx_orders_created
ON orders(created_at);

CREATE INDEX idx_orders_status_created_id
ON orders(status, created_at DESC, id DESC);

预期结果:

text
订单表包含 4 行;两个索引创建成功。

不要用函数包裹被索引列

下面的写法先对每行执行 date(created_at),普通的 created_at 索引通常无法直接按原值形成范围。片段示例,依赖上一节创建的 orders 表:

sql
-- 较慢写法
SELECT order_no
FROM orders
WHERE date(created_at) = '2026-07-30';

-- 改写:左闭右开的时间范围
SELECT order_no
FROM orders
WHERE created_at >= '2026-07-30 00:00:00'
  AND created_at <  '2026-07-31 00:00:00'
ORDER BY order_no;

预期输出:

text
order_no
O1002
O1003

半开区间不会遗漏当天最后一秒,也不依赖小数秒精度。时间字段若混用不同时区或非固定格式,应先统一存储规则。

前置通配符无法从索引起点定位

LIKE '%alice%' 的开头未知,普通 B 树索引无法直接跳到连续前缀范围。若业务允许前缀搜索,可改为 LIKE 'Alice%',并根据列的排序规则与大小写设置验证查询计划。

若必须做任意位置包含、分词或相关性搜索,普通索引不是合适工具,应考虑 SQLite FTS 全文检索,而不是不断增加相似的 B 树索引。

让筛选与排序使用同一访问路径

查询“已支付订单,按最新时间取前 20 条”时,索引 (status, created_at DESC, id DESC) 可先按状态缩小范围,再直接按目标顺序读取。不要在子查询和外层重复无意义排序,也不要在只取任意样本时使用昂贵的 ORDER BY random()。

片段示例,依赖前面的复合索引:

sql
SELECT id, order_no, created_at
FROM orders
WHERE status = 'PAID'
ORDER BY created_at DESC, id DESC
LIMIT 20;

预期输出:

text
id | order_no | created_at
4  | O1004    | 2026-07-31 00:05:00
2  | O1002    | 2026-07-30 08:00:00
1  | O1001    | 2026-07-29 23:50:00

用键集分页替代深 OFFSET

LIMIT 20 OFFSET 100000 仍要跳过前面的十万行,页码越深,浪费越大。键集分页记住上一页最后一条记录的排序键,从该位置继续读取。

片段示例,假设上一页最后一条为 created_at='2026-07-30 08:00:00'、id=2:

sql
SELECT id, order_no, created_at
FROM orders
WHERE status = 'PAID'
  AND (created_at, id) < ('2026-07-30 08:00:00', 2)
ORDER BY created_at DESC, id DESC
LIMIT 20;

预期输出:

text
id | order_no | created_at
1  | O1001    | 2026-07-29 23:50:00

键集分页适合连续向后翻页,不适合直接跳到任意页码。排序键必须稳定,通常在时间后追加唯一主键,避免同一时间值导致漏行或重复。

合并重复相关子查询

若列表中的每一行都重复执行相似子查询,例如分别统计支付数和支付金额,数据量增大后会产生大量重复工作。可以先按客户聚合一次,再连接聚合结果。片段示例,依赖上一节的 orders 表:

sql
WITH paid_summary AS (
  SELECT
    customer_name,
    COUNT(*) AS paid_count,
    SUM(amount) AS paid_amount
  FROM orders
  WHERE status = 'PAID'
  GROUP BY customer_name
)
SELECT customer_name, paid_count, paid_amount
FROM paid_summary
ORDER BY customer_name;

预期输出:

text
customer_name | paid_count | paid_amount
Alice         | 1          | 88
Bob           | 1          | 120
Carol         | 1          | 66

改写后仍要检查查询计划和真实耗时。小数据上的“语法更复杂”不一定更快,优化目标应是减少扫描行数、临时排序、重复计算和不必要回表。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

常见慢查询与 SQL 改写

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通