SQL查询优化：7个简单技巧提升数据库性能

大家好！在这篇文章中，我将分享一些简单的方法来加速您的SQL查询并提高其效率。我们将看一些实际示例（主要针对PostgreSQL，但这些概念也适用于其他SQL数据库）。到最后，您将能够优化查询以获得更好的性能和更快的响应时间。

1. 使用虚拟表上的JOIN替换IN子句

问题：在IN子句中使用大量值可能导致对每一行进行顺序检查，从而加重CPU负担。

解决方案：使用虚拟表（带VALUES）并将其与您的主表进行JOIN。这通常能让数据库更有效地利用索引。

示例：

-- 优化前：

SELECT order_id, city

FROM orders

WHERE city IN ('Berlin', 'Paris', 'Rome');

-- 优化后：

SELECT o.order_id, o.city

FROM orders AS o

JOIN (VALUES ('Berlin'), ('Paris'), ('Rome')) AS v(city_name) -- 虚拟表v，列名为city_name

ON o.city = v.city_name; -- 连接条件

为什么更快：通过将值列表视为一个表，PostgreSQL有时能创建出比使用长IN子句更优的执行计划。

2. 使用 ANY(ARRAY [ ]) 替代 IN (仅限PostgreSQL)

问题：与第一种情况类似，大型IN列表会减慢查询速度，因为必须逐一检查每个可能的匹配项。

解决方案：尝试使用PostgreSQL特有的语法 = ANY(ARRAY[...])，它可以在找到匹配项后立即停止检查（短路）。

示例：

-- 优化前：

SELECT product_id, quantity

FROM order_items

WHERE product_id IN (101, 202, 303, 404);

-- 优化后：

SELECT product_id, quantity

FROM order_items

WHERE product_id = ANY(ARRAY[101, 202, 303, 404]); -- 使用ANY(ARRAY)

为什么更快：ANY 在遇到匹配项后可以停止检查，从而可能减少比较次数。

3. 使用 JOIN 替代关联子查询 (Correlated Subquery)

问题：关联子查询会对外部查询的每一行重复执行，导致对相同数据进行多次扫描。

解决方案：尽可能用常规的 JOIN 或非关联子查询替换关联子查询。

示例：

-- 优化前：

SELECT c.customer_id, c.name

FROM customers AS c

WHERE EXISTS ( -- 关联子查询

SELECT 1

FROM orders AS o

WHERE o.customer_id = c.customer_id -- 关联条件

AND o.amount > 1000

);

-- 优化后 (使用 JOIN)：

SELECT DISTINCT c.customer_id, c.name -- 可能需要 DISTINCT 因为 JOIN 可能产生重复

FROM customers AS c

JOIN orders AS o

ON c.customer_id = o.customer_id

WHERE o.amount > 1000;

为什么更快：JOIN 允许PostgreSQL使用索引并避免多次执行相同的子查询

4. 使用 BETWEEN 替代日期函数

问题：像EXTRACT(YEAR FROM order_date) = 2023 这样的表达式会阻止数据库使用日期索引。对列应用函数会使PostgreSQL无法识别索引范围。

解决方案：使用 BETWEEN 和明确的日期范围，以便直接在 order_date 字段上使用索引。

示例：

-- 优化前：

SELECT *

FROM orders

WHERE EXTRACT(YEAR FROM order_date) = 2023 -- 对列应用函数

AND EXTRACT(MONTH FROM order_date) = 5;

-- 优化后：

SELECT *

FROM orders

WHERE order_date BETWEEN '2023-05-01'::DATE -- 使用 BETWEEN 和字面日期

AND '2023-05-31'::DATE;

为什么更快：在索引日期列上使用 BETWEEN 可以直接进行索引范围扫描，跳过每行的函数调用。

5. 检查存在性时，依赖 EXISTS 而非 JOIN

问题：如果您只需要确认另一个表中是否存在至少一个相关的行，使用 JOIN 最终可能会获取比所需更多的数据。

解决方案：使用 EXISTS，一旦找到匹配项就立即停止。

示例：

-- 优化前：

SELECT COUNT(DISTINCT o.order_id) -- 计算有订单项的订单数

FROM orders AS o

JOIN order_items AS i -- 使用 JOIN

ON o.order_id = i.order_id; -- 连接所有订单项

-- 优化后：

SELECT COUNT(DISTINCT o.order_id) -- 计算有订单项的订单数

FROM orders AS o

WHERE EXISTS ( -- 使用 EXISTS 检查存在性

SELECT 1

FROM order_items AS i

WHERE i.order_id = o.order_id -- 关联条件

);

为什么更快：该查询不会检索不必要的行。一旦找到匹配记录，它就知道条件已满足。

6. DISTINCT 的替代方案：ROW_NUMBER()

目标：高效地提取唯一值，尤其是在大型数据集中。

解决方案：有时使用 ROW_NUMBER()（并按关键列分区）比 DISTINCT 更快，特别是如果这些列已建立索引。

示例：

-- 优化前 (使用 DISTINCT)：

SELECT COUNT(DISTINCT user_id) -- 计算唯一用户数

FROM logins

WHERE login_date BETWEEN '2023-01-01'::DATE

AND '2023-01-31'::DATE;

-- 优化后 (使用 ROW_NUMBER)：

SELECT COUNT(user_id) -- 计算 user_id (rn=1 的行)

FROM (

SELECT user_id,

ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY user_id) AS rn -- 按 user_id 分区并排序，为每个组内的行编号

FROM logins

WHERE login_date BETWEEN '2023-01-01'::DATE

AND '2023-01-31'::DATE

) AS tmp

WHERE rn = 1; -- 只取每个用户的第一行 (即代表该用户的唯一行)

为什么更快：ROW_NUMBER() 通过为每个组内的行分配行号，避免了繁重的去重排序操作。

7. 其他有用技巧

1. 只选择您需要的列• SELECT * 会加载每一列，如果您只需要几个字段，这会减慢查询速度。
2. 添加 LIMIT• 如果您只需要一小部分行，请指定 LIMIT。这可以让数据库跳过扫描所有内容。
3. 避免在条件中使用函数• 对于字符串操作，优先使用 LIKE 'ABC%' 而不是 SUBSTRING(field, 1, 3) = 'ABC'，以便索引仍然可用。
4. 优化聚合操作• 使用 FILTER (在PostgreSQL中) 或 CASE 进行条件计数，这比多次使用 UNION 或重复的子查询更高效。
5. 对于简单的布尔检查，使用逻辑表达式替代 CASE• 例如，(table1.is_deleted OR table2.is_deleted) 比庞大的 CASE 表达式更清晰且更快。

示例

-- 优化前 (使用 CASE 聚合)：

SELECT SUM(CASE WHEN status = 'NEW' THEN 1 END) AS new_orders, -- 使用 SUM(CASE)

SUM(CASE WHEN status = 'CLOSED' THEN 1 END) AS closed_orders

FROM orders;

-- 优化后 (使用 FILTER)：

SELECT COUNT() FILTER (WHERE status = 'NEW') AS new_orders, -- 使用 COUNT FILTER

COUNT() FILTER (WHERE status = 'CLOSED') AS closed_orders

FROM orders;

总结

SQL查询优化是任何数据库“支持”项目的主要步骤。通过使用诸如用虚拟表或 ANY(ARRAY[]) 替换 IN、利用 BETWEEN 处理日期、选择 EXISTS 而非完整的 JOIN、以及使用 FILTER 进行条件聚合等策略，您很可能会看到性能的显著提升。

请记住：

• 避免 SELECT *

• 明智地使用索引

• 注意WHERE子句中的函数调用

• 尽早过滤数据

• 保持查询的可读性和高效性

---

我是钱德勒（chandler_is_dreaming），拥有超10年全球顶尖企业数据运营与商业分析实战经验，曾任职于多家头部互联网及国际知名企业，历任商业运营总监、商业智能负责人、数据分析高级经理等职。具备丰富的数据分析实战经验，曾成功从0搭建团队、优化流程、推动数字化转型，最多管理60余人的数据团队，累计面试超300人，尤其擅长数据相关岗位（如数据分析师、商业分析师、运营分析师、数据产品经理等）的职业规划、简历优化、技能提升、业务思维、面试技巧等。