去年需要几毫秒的查询现在可能需要几分钟，仅仅是因为表增长了。 SQL 查询优化 与其说是巧妙的技巧，不如说是一个可重复的过程：找出数据库正在做什么，消除浪费的工作，并检查它是否有帮助。

我们在 SQLite 3.50 中生成的一百万行上测试了下面的每种技术。计时是使用内存数据库在一台笔记本电脑上运行的几次运行中最好的。它们与您的系统不匹配，并且 PostgreSQL、MySQL 和 SQL Server 都有自己的优化器和计划格式。关注每项改进的规模及其背后的原则。

结果一目了然

测试设置

CREATE TABLE customers (
  id INTEGER PRIMARY KEY, name TEXT, city TEXT
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY, customer_id INT, status TEXT,
  created TEXT, total INT, note TEXT
);
-- 50,000 customers and 1,000,000 orders with random values

一个简单的优化过程

1. 找到重要的查询。使用慢速查询日志或监控。每分钟运行 1000 次、时长为 200 毫秒的查询可能比 10 秒的月度报告更重要。
2. 阅读计划。了解数据库实际上在做什么。
3. 改变一件事。添加索引或重写部分查询。
4. 根据实际数据再次测量。针对 100 个测试行的计划只能告诉您关于 100 万行的信息。
5. 随着时间的推移进行观察。 计划可能会随着数据的增长和统计数据的变化而改变。

阅读执行计划

执行计划是数据库运行查询的逐步策略，包括它使用哪些索引以及它连接表的顺序。为查询添加前缀以查看它：

请注意，PostgreSQL 中的 EXPLAIN ANALYZE 确实运行查询，因此与 UPDATE 或 DELETE 一起使用时要小心。以下是首先要寻找的内容：

完整扫描不会自动变坏。如果查询需要大部分小表，扫描可能是最快的选择。在 PostgreSQL 的 EXPLAIN ANALYZE 中，还将估计行与实际行进行比较。巨大的差异通常意味着表统计数据已经过时。

添加正确的索引

此查询查找一位客户的订单：

SELECT id, total FROM orders WHERE customer_id = 123;

在添加索引之前，计划是全盘扫描：

SCAN orders
-- 20 rows in 73 ms

然后我们在过滤列上添加索引：

CREATE INDEX idx_orders_customer ON orders(customer_id);

SEARCH orders USING INDEX idx_orders_customer (customer_id=?)
-- 20 rows in 0.01 ms

同样的 20 行返回速度快了数千倍，因为数据库不再读取一百万行来查找它们。本文中没有其他技术给我们带来了更大的胜利。好的第一个候选列是 WHERE、JOIN 和 ORDER BY 中使用的列，特别是当它们将结果缩小到几行时。我们的 SQL 索引 指南解释了它们的工作原理。

不要将索引列包装在函数中

以下是查找 3 月订单的两种方法，索引位于 created：

-- Function on the column
SELECT id FROM orders WHERE substr(created, 1, 7) = '2026-03';
-- SCAN orders USING COVERING INDEX idx_orders_created
-- 84,961 rows in 96 ms

-- Plain range
SELECT id FROM orders
WHERE created >= '2026-03-01' AND created < '2026-04-01';
-- SEARCH orders USING COVERING INDEX idx_orders_created (created>? AND created<?)
-- 84,961 rows in 33 ms

两者都返回相同的行，但第一个花费的时间大约是前者的三倍。该计划说明了原因：使用列上的函数，数据库无法跳转到排序索引中的位置，因此它必须 SCAN 遍历每个条目。范围版本可以SEARCH直接到达正确的位置。能够以这种方式使用索引的条件称为sargable。

这些常见模式也会出现同样的问题：

我们也检查了最后一项。产生了前导通配符 SCAN customers，因为从第一个字母排序的索引无助于末尾的匹配。

综合指数

当查询过滤两列时，单列索引仅执行部分工作：

SELECT id FROM orders
WHERE status = 'paid'
  AND created >= '2026-03-01' AND created < '2026-03-08';

-- Using only the created index: 6,198 rows in 19 ms

CREATE INDEX idx_status_created ON orders(status, created);

-- SEARCH orders USING COVERING INDEX idx_status_created
--   (status=? AND created>? AND created<?)
-- 6,198 rows in 2.2 ms

列顺序非常重要。 (status, created) 上的索引就像电话簿一样，先按姓氏排序，然后按名字排序。您可以单独查找姓氏，但不能单独查找名字。我们对 status 的查询单独可以使用这个索引。单独对created进行过滤通常无法有效地使用它。根据经验，首先放置经过相等性测试的列，然后放置范围列。

该计划还提到覆盖索引，这意味着索引保存了查询所需的所有内容，因此不必读取表本身。仅选择您需要的列会使这种情况更有可能发生，这是要避免的另一个原因 SELECT *。

更快的分页

对深层页面使用 OFFSET 是一种隐藏成本，因为数据库仍然必须遍历所有跳过的行：

-- Page deep into the results
SELECT id FROM orders ORDER BY id LIMIT 20 OFFSET 500000;
-- 8.1 ms

-- Keyset pagination: continue from the last id seen
SELECT id FROM orders WHERE id > 500000 ORDER BY id LIMIT 20;
-- 0.01 ms

无论您走多深，键集分页都保持快速。当添加新数据时，它还可以避免页面之间的行移动。代价是用户无法直接跳转到第 5,000 页，而这很少是真正的需要。在具有宽行或要跳过更多行的表上，间隙可能比我们的测试中大得多。

编写更精简的查询

• 仅选择您需要的列。 SELECT * 发送更多数据，并且可以防止索引覆盖查询。
• 尽早过滤。 使用 WHERE 在分组之前删除行，HAVING 仅适用于聚合条件。请参阅SQL GROUP BY 和 HAVING。
• 当您只需要几行时，请使用 LIMIT。
• 当您不需要删除重复项时，首选 UNION ALL。我们的 UNION 与 UNION ALL 测试显示了这样可以节省什么。
• 避免不必要的 DISTINCT。 它通常隐藏产生重复的连接。
• 使用 EXISTS 进行存在性检查。它可以在第一个匹配处停止，正如我们的 SQL 子查询指南所解释的那样。
• 注意 N+1 查询。 循环中每行一个查询（通常由应用程序代码引起）通常是真正的问题。单个连接或批量查询可以替代数百次往返。

加入

为您加入的列建立索引。我们为一个城市的客户提供了一份总支出报告，该计划正是您所希望的：

SELECT c.name, SUM(o.total)
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.city = 'Goa'
GROUP BY c.id;

-- SCAN c
-- SEARCH o USING COVERING INDEX idx_cover (customer_id=?)
-- 9,900 rows in 34 ms

它扫描客户，然后使用索引来查找每个客户的订单。如果没有 orders.customer_id 上的索引，数据库将不得不为每个客户做更多的工作。外键索引是您可以进行的最常见和最有价值的添加之一。要刷新您对基础知识的理解，请参阅SQL 连接解释。

当索引让你付出代价时

索引不是免费的，因此不要为每一列添加一个：

• 写入速度较慢。 每次插入、更新和删除也必须更新每个索引。
• 更多存储空间和内存。 大表上的索引可能会变大。
• 对非选择性列没有什么好处。 如果大多数行匹配，则 true 或 false 列上的索引可能没有帮助。
• 冗余。 如果 (a, b) 上已有索引，则 (a) 上的索引通常是不必要的。

定期检查使用情况统计信息，并删除不使用的索引。并使用 ANALYZE 或数据库的等效项保持统计信息最新，因为优化器依赖它们来选择一个好的计划。有时缓慢的部分是争用而不是查询。长时间运行的事务持有锁可能会使快速查询看起来很慢。

常见错误

• 优化而不测量。许多自信的猜测最终被证明是错误的。
• 使用微小数据进行测试。随着表的增长，计划和时间安排也会发生巨大变化。
• 信任另一个数据库的提示。针对一个引擎的建议可能不适用于另一个引擎。
• 盲目加索引。每加一个都会减慢写入速度。
• 忽略第一次运行的影响。缓存使第二次运行更快，因此可以运行多次。

最后的话

大多数缓慢的查询都有常见的原因：缺少索引、列上的函数或获取的数据过多。以本周最慢的实际查询为例，对其运行 EXPLAIN，并在大型表上查找完整扫描。修复一件事，再次测量，并记录您所做的更改。

常见问题

继续学习