数据库原理与选型 / 系统课时
常见慢查询与 SQL 改写
常见慢查询与 SQL 改写
本课解决“已经建立索引,SQL 仍然很慢”的问题。完成后,你能识别破坏索引利用的常见写法,并通过范围条件、匹配方式、键集分页和预聚合改写查询。
学习目标
- 识别函数包裹列、前置通配符和无效排序
- 使用范围查询与键集分页减少扫描
- 将重复相关子查询改写为一次聚合
先建立可验证的数据与索引
后续示例围绕订单表展开。时间使用可按字典序排序的 ISO 格式文本,因此同一时区下可直接使用半开区间筛选。
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);预期结果:
订单表包含 4 行;两个索引创建成功。不要用函数包裹被索引列
下面的写法先对每行执行 date(created_at),普通的 created_at 索引通常无法直接按原值形成范围。片段示例,依赖上一节创建的 orders 表:
-- 较慢写法
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;预期输出:
order_no
O1002
O1003半开区间不会遗漏当天最后一秒,也不依赖小数秒精度。时间字段若混用不同时区或非固定格式,应先统一存储规则。
前置通配符无法从索引起点定位
LIKE '%alice%' 的开头未知,普通 B 树索引无法直接跳到连续前缀范围。若业务允许前缀搜索,可改为 LIKE 'Alice%',并根据列的排序规则与大小写设置验证查询计划。
若必须做任意位置包含、分词或相关性搜索,普通索引不是合适工具,应考虑 SQLite FTS 全文检索,而不是不断增加相似的 B 树索引。
让筛选与排序使用同一访问路径
查询“已支付订单,按最新时间取前 20 条”时,索引 (status, created_at DESC, id DESC) 可先按状态缩小范围,再直接按目标顺序读取。不要在子查询和外层重复无意义排序,也不要在只取任意样本时使用昂贵的 ORDER BY random()。
片段示例,依赖前面的复合索引:
SELECT id, order_no, created_at
FROM orders
WHERE status = 'PAID'
ORDER BY created_at DESC, id DESC
LIMIT 20;预期输出:
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:
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;预期输出:
id | order_no | created_at
1 | O1001 | 2026-07-29 23:50:00键集分页适合连续向后翻页,不适合直接跳到任意页码。排序键必须稳定,通常在时间后追加唯一主键,避免同一时间值导致漏行或重复。
合并重复相关子查询
若列表中的每一行都重复执行相似子查询,例如分别统计支付数和支付金额,数据量增大后会产生大量重复工作。可以先按客户聚合一次,再连接聚合结果。片段示例,依赖上一节的 orders 表:
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;预期输出:
customer_name | paid_count | paid_amount
Alice | 1 | 88
Bob | 1 | 120
Carol | 1 | 66改写后仍要检查查询计划和真实耗时。小数据上的“语法更复杂”不一定更快,优化目标应是减少扫描行数、临时排序、重复计算和不必要回表。