一月 7, 2025
摘要：在本教程中，您将学习如何通过改进筛选条件，来优化 SELECT 类的 SQL 查询。
目录
有时，我们需要评估和改进查询中的WHERE子句部分，对筛选条件的细微更改都可能会对查询性能产生巨大影响。
避免在筛选条件中使用函数
原因
在筛选阶段将函数应用于列会降低性能。数据库需要在筛选之前将函数应用于数据集。让我们来看一个对 timestamp 字段进行筛选的简单示例：
SELECT count(*)
FROM orders
WHERE CAST(order_timestamp AS DATE) >= '2024-02-01';
上述对 100000000 行数据集的查询，运行了01 min 53 sec，因为它需要在应用筛选条件之前，将order_timestamp列的数据类型从 timestamp 更改为 date。但是，那不是必需的！请注意，对于上面的查询，根据表的元数据，可以将其重写为：
SELECT count(*)
FROM orders
WHERE order_timestamp >= '2024-02-01 00:00:00';
重写的查询使用了原始的时间戳字段，而不进行强制转换。在完成如此微小的更改之后，现在查询可以在20 sec内运行完，速度比原始查询快了近 6 倍。
警告
并非所有的函数都可以避免掉，因为可能需要一些函数，来检索列值中的某部分（考虑下substring的示例）或者重构它。不过，每次要在筛选条件中添加函数时，请考虑是否可以使用原始的数据类型运算符进行替代。
最佳实践
将筛选条件应用于一个列时，请尝试调整筛选条件的格式，而不是列的格式。
以上是一个完美的示例：将过滤条件的格式，从日期2024-02-01调整成时间戳2024-02-01 00:00:00，这样就允许我们使用原始的时间戳数据格式和运算符。
专业建议
如果必须要应用函数，您可以尝试以下两个方法：
- 在表达式上创建一个索引，这在 PostgreSQL 和 MySQL 中都是可行的
- 使用数据库触发器，来填充要进行转换的附加列
改进子查询
原因
子查询通常用于筛选条件中，以检索出一个数值集合，来应用到筛选条件。一个常见的示例是，需要检索出最近活动过的用户列表时。
SELECT *
FROM users
WHERE id IN (
SELECT DISTINCT user_id
FROM sessions
WHERE session_date = '2024-02-01');
上面的查询从SESSIONS表中检索出不同的用户列表，然后对USERS表应用筛选条件。但是，有几种性能更高的方法，可以实现相同的结果。一个例子是使用EXISTS：
SELECT *
FROM users
WHERE EXISTS (
SELECT user_id
FROM sessions
WHERE user_id = id and session_date = '2024-02-01'
);
EXISTS的速度更快，因为它不需要从SESSION表中检索不同用户的列表，而是只验证对于特定的用户在表中是否存在至少一行。上面的用例只是更改了子查询部分，性能就从02 min 08 sec变为了18 sec。
警告
在极端情况下，子查询中的细微变化可能会提供不同的结果。
最佳实践
当需要使用子查询时，请花点时间学习和了解有哪些方法，以及它们允许您实现的目标。很多时候有多个方法，某些功能会提供更好的响应时间。
对结果进行分页
原因
当需要显示一长串的行时，对从数据库中检索到的结果进行分页，会非常有用。PostgreSQL 和 MySQL 都提供了对输出进行LIMIT的功能，可以只检索一定数量的行，并通过基于排序仅检索在特定范围内的行，来为结果集进行OFFSET的功能。要将发送到客户端的数据最小化为仅需要显示的数据，使用LIMIT和OFFSET是一个好方法。
警告
使用LIMIT和OFFSET的缺点是，您需要对数据库要发送和执行的每个 “分页” 进行查询。如果总行数与页面大小相差不大，这可能会很不方便。例如，如果您以10行的分页形式显示结果，但平均要显示15行的结果，则最好一次检索出整个数据集。
最佳实践
如果结果集的大小比页面大小要大一个数量级，则使用分页可能是一个实现更好性能的有效方法，因为只要从数据库中检索可见的数据集。
专业建议
LIMIT和OFFSET子句是大多数数据库中缺省的分页方法。但是，通过将当前页面的开始和结束偏移量存储在客户端，并在 SQL 语句的WHERE子句中推移对下一页的筛选条件，可以实现更高效的分页实现。分页查询教程中提供了这类实现的几个示例。
将筛选条件从 HAVING 子句移动到 WHERE 子句
原因
运行一个查询时，在WHERE和HAVING子句中定义的筛选条件，会在不同的时间进行应用：
- 在应用
SELECT语句中定义的任何数据转换之前，会对原始数据集应用WHERE筛选条件 HAVING筛选条件是在聚合后应用的，因此发生在检索、转换和汇总所有行之后。
因此，应优先考虑移动在WHERE子句中的筛选条件，因为它允许您处理较小的数据集。例如，如果我们要尝试获取一个日期列表，其中有跟踪的user_id的会话（user_id字段不为空）
SELECT session_date, count(user_id) nr_sessions
FROM sessions
GROUP BY session_date
HAVING count(user_id) > 0;
我们可以通过将筛选条件推送到WHERE子句中，来重写上面的查询：
SELECT session_date, count(user_id) nr_sessions
FROM sessions
WHERE user_id is not null
GROUP BY session_date
对100,000,000行数据集的查询性能，从21 sec提升到了18 sec，这只是对筛选语句做了下更改。
警告
只有当我们可以确定有一个类似的条件，可以应用于行级别，而不是聚合的级别时，才能将筛选条件从HAVING移动到WHERE子句。
最佳实践
应该始终尝试在WHERE子句中进行筛选，因为筛选会在SELECT语句转换/聚合数据之前应用。