译者 | 布加迪
审校 | 重楼
所有数据角色都需要强大的数据库和SQL技能。在实践中,你通常会在一天的工作中查询超大的数据库表(含有成千上万行)。所以,SQL查询的性能成为决定应用程序整体性能的一个重要因素。
优化不佳的查询常常导致响应时间变慢、服务器负载加大以及用户体验差强人意。因此,理解和运用SQL查询优化技术显得必不可少。
本教程介绍了优化SQL查询的实用技巧。
准备工作:获取一个示例数据库表
你在为使用的任何数据库编写SQL查询时都可以使用以下技巧。但如果你希望使用一个示例数据库表来运行这些查询,可以使用这个Python脚本。
它连接到一个SQLite数据库:employees.db,创建一个employees表,并用10000条记录填充该表。如上所述,你总是可以创建自己的示例。
1. 不要使用SELECT *,改而选择特定的列
对于初学者来说,使用SELECT *从表中检索所有列很常见。如果你只需要几列(几乎总是这样),那么这可能很低效。
因此,使用SELECT *可能导致过度的数据处理,当表有许多列或者你在处理庞大数据集时尤为如此。
不要这样:
SELECT * FROM employees;
而要这么做:
SELECT employee_id, first_name, last_name FROM employees;
只读取必要的列可以使查询更具可读性和可维护性。
2. 避免使用SELECT DISTINCT,改而使用GROUP BY
SELECT DISTINCT之所以开销很大,是由于它需要对结果进行排序和过滤以删除重复项。最好确保查询的数据在设计上是独特的,使用主键或独特的约束。
不要这样:
SELECT DISTINCT department FROM employees;
下面这个带有GROUP BY子句的查询更有帮助:
SELECT department FROM employees GROUP BY department;
GROUP BY可以更高效,特别是在适当索引的情况下(后面会讨论索引)。因此,在编写查询时,确保你在数据模型层面了解数据(不同的字段)。
3. 限制查询结果
通常你会查询有数千行的大型表,但并不总是需要处理所有行,也无法处理所有行。使用LIMIT子句(或等效子句)有助于减少返回的行数,从而提升查询性能。
你可以将结果限制为15条记录:
SELECT employee_id, first_name, last_name FROM employees LIMIT 15
使用LIMIT子句可以缩小结果集的大小,从而减少需要处理和传输的数据量。这对于在应用程序中为结果编页码也很有用。
4. 使用索引加快检索
索引允许数据库比扫描整个表更快地查找行,从而显著提高查询性能。它们对于WHERE、JOIN和ORDER BY子句中经常使用的列特别有用。
下面是针对“department”列创建的示例索引:
CREATE INDEX idx_employee_department ON employees(department);
你现在可以运行过滤' department '列的查询,并比较执行时间。使用索引后,你应该能够更快地看到使用结果。想了解有关创建索引和性能改进的更多信息,参阅《如何使用索引加速SQL查询(Python版)》。
如前所述,索引提高了对索引列进行过滤的查询的效率。但是创建太多的索引可能会成为一件坏事。这就引出了下一个技巧。
5. 谨慎使用索引
虽然索引提高了读取性能,但会降低写入性能:INSERT、UPDATE和DELETE查询,因为每次修改表时都必须更新索引。根据你经常运行的查询类型来兼顾索引的数量和类型很重要。
几条可靠的规则:
- 只索引经常查询的列。
- 避免对基数低的列(少数唯一值)进行过度索引。
- 定期检查索引,并根据需要更新和删除索引。
总之,创建索引以加快对经常查询但很少更新的列的检索速度。这确保索引的好处压倒维护成本。
结语
优化SQL查询需要了解查询的特定需求和数据结构。
如果避免SELECT *、谨慎使用SELECT DISTINCT、限制查询结果、创建适当的索引以及注意索引方面的取舍,你就可以显著提升数据库操作的性能和效率。
祝查询愉快!
原文标题:5 Tips for Improving SQL Query Performance,作者:Bala Priya C