SQL开发中改善查询性能的N种写法

在SQL开发中，某些写法可能会显著影响查询性能，甚至让系统变得非常缓慢。这里列出一些常见的“坑”，并解释它们为什么会影响性能，以及如何避免这些坑。

1. 使用 SELECT *

问题:

SELECT * FROM employees;

影响: - 返回所有列，可能导致网络传输大量不必要的数据。 - 如果表结构发生变化，查询结果也可能随之变化，导致客户端代码出错。

改进:

SELECT id, name, position FROM employees;

只选择需要的列。

2. 在 WHERE 子句中使用函数或计算

问题:

SELECT * FROM orders WHERE YEAR(order_date) = 2023;

影响: - 阻止数据库使用索引。 - 每次查询都需要对每一行进行函数计算。

改进:

SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

或者使用日期函数在查询外部计算日期范围。

3. 使用隐式类型转换

问题:

SELECT * FROM users WHERE user_id = '123'; -- user_id 是整数类型

影响: - 可能导致索引失效。 - 数据库需要执行类型转换。

改进:

SELECT * FROM users WHERE user_id = 123;

确保类型匹配。

4. 不使用索引的列进行连接（JOIN）或过滤

问题:

SELECT * FROM orders o JOIN customers c ON o.customer_name = c.name;

影响: - 如果 customer_name 和 name 不是索引列，性能会很差。

改进:

-- 假设 customer_id 是外键
SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id;

确保连接条件中的列有索引。

5. 使用 OR 代替 IN

问题:

SELECT * FROM employees WHERE department = 'HR' OR department = 'Finance';

影响: - 通常比使用 IN 更慢。

改进:

SELECT * FROM employees WHERE department IN ('HR', 'Finance');

6. 在子查询中使用 SELECT *

问题:

SELECT * FROM employees WHERE id IN (SELECT id FROM employees_backup WHERE status = 'active');

影响: - 可能导致大量数据传输和内存消耗。

改进:

SELECT * FROM employees WHERE id IN (SELECT id FROM employees_backup WHERE status = 'active' AND id IS NOT NULL);
-- 或者只选择必要的列
SELECT e.* FROM employees e WHERE e.id IN (SELECT id FROM employees_backup WHERE status = 'active');

7. 忽略索引统计信息

问题: 数据库统计信息过时，导致优化器选择错误的执行计划。

影响: - 查询性能下降。

改进: 定期更新统计信息，例如在 PostgreSQL 中：

ANALYZE employees;

8. 嵌套子查询过多

问题:

SELECT * FROM (SELECT * FROM (SELECT * FROM employees WHERE status = 'active') AS subquery1 WHERE department = 'HR') AS subquery2;

影响: - 每层子查询都会消耗资源。

改进:

SELECT * FROM employees WHERE status = 'active' AND department = 'HR';

9. 过度使用 DISTINCT

问题:

SELECT DISTINCT column1, column2 FROM large_table;

影响: - 排序和去重操作非常耗时。

改进: - 尽量避免使用 DISTINCT，或者通过其他方式（如 GROUP BY）实现。

10. 使用不当的 JOIN 类型

问题:

SELECT * FROM employees e LEFT JOIN departments d ON e.department_id = d.id WHERE d.name IS NULL;

影响: - 使用 LEFT JOIN 但过滤掉右表的数据，等效于 INNER JOIN 加过滤条件，但性能更差。

改进:

SELECT * FROM employees e WHERE e.department_id NOT IN (SELECT id FROM departments);

或者使用 NOT EXISTS：

SELECT * FROM employees e WHERE NOT EXISTS (SELECT 1 FROM departments d WHERE e.department_id = d.id);

总结

• 选择必要的列
• 避免在 WHERE 子句中使用函数
• 确保类型匹配
• 使用索引列进行连接和过滤
• 优先使用 IN 而非 OR
• 定期更新统计信息
• 减少嵌套子查询
• 谨慎使用 DISTINCT
• 选择适当的 JOIN 类型

遵循这些原则，可以显著提升 SQL 查询的性能。