优化SQL查询的10大技巧！

今日份知识你摄入了么？

嘿！我们来讨论一下如何优化SQL查询吧！

如果你在使用数据库，你一定知道优化查询对提高执行速度、增强数据库性能的重要性。

但你要如何做呢？

以下是我所整理的优化SQL查询的10大技巧，包括示例代码和用例。一起来看吧！

技巧1：使用EXPLAIN（解释）了解查询的执行

在开始优化之前，你需要了解查询是如何执行的。

这就是其作用所在。它向你显示查询的执行计划，包括使用的表、操作顺序和使用的任何索引。

EXPLAIN SELECT *FROM usersWHERE last_name = 'Smith';

理解了执行计划后，你就可以开始根据你学到的东西进行优化。

例如，你可能会发现在last_name列上添加索引可以提高性能。

技巧2：使用Index（索引）加速查询

索引是提高查询性能的强大工具。它们允许数据库快速找到你要查找的数据，无需扫描表格中的每一行。

CREATE INDEX last_name_index ON users (last_name);

在此示例中，我们在users表的last_name列上创建了一个索引。这可以加快按姓氏进行筛选的查询，如技巧#1中的查询。

技巧3：避免使用SELECT *

使用SELECT *很方便，但它并不总是提高性能的最佳选择。当你选择表格中的所有列时，数据库必须读取每一列，即使你不在查询中使用它们。

SELECT first_name, last_nameFROM users;

此查询仅选择first _ name和last _ name列，比选择所有列更快。

技巧4：小心使用Join（连接）

尽管性能不错，但是连接很贵——特别是你要连接大型表格的时候。在执行此操作之前，请确保你确实需要连接表格。

SELECT *FROM usersJOIN orders ON users.id = orders.user_id;

此查询分别将users表与id和user _ id列上的orders表连接起来。如果需要来自两个表的数据，我们需要用到连接，但如果只需要来自一个表的数据，就能省则省。

技巧5：尽量避免Subquery（子查询）

子查询有用，但也很慢。如果你可以通过连接或更简单的查询完成同样的事情，就不要用子查询。

SELECT *FROM usersWHERE id IN (  SELECT user_id  FROM orders);

此查询使用子查询来查找下单的所有用户。我们可以通过连接来完成同样的事情：

SELECT DISTINCT users.*FROM usersJOIN orders ON users.id = orders.user_id;

技巧6：使用UNION ALL（联合所有）代替UNION（联合）

如果需要合并两个查询的结果，联合很有用——但它比联合所有慢，后者只是将结果串联起来，不需要删除重复项。

SELECT first_name, last_nameFROM usersWHERE last_name = 'Smith'UNIONSELECT first_name, last_nameFROM usersWHERE last_name = 'Jones';

此查询使用了联合合并了两个查询的结果，而我们可以使用联合所有代替：

SELECT first_name, last_nameFROM usersWHERE last_name = 'Smith'
UNION ALL
SELECT first_name, last_nameFROM usersWHERE last_name = 'Jones';

此查询将两个查询的结果串联起来，不需要删除重复项，比联合要更快。

技巧7：使用EXISTS而不是COUNT

如果需要检查表中是否存在记录，EXISTS比COUNT更快。

SELECT *FROM usersWHERE EXISTS (  SELECT *  FROM orders  WHERE orders.user_id = users.id);

此查询使用EXISTS查找已下单的所有用户。我们可以用COUNT代替，但EXISTS通常更快。

技巧8：使用LIMIT和OFFSET控制结果集

如果只需要查询结果的子集，则可以使用LIMIT和OFFSET来控制结果集。

SELECT *FROM usersLIMIT 10OFFSET 20;

此查询选择users表格的第10至第19行。如果你要在应用程序中对结果进行分页处理，这会很有用。

技巧9：使用GROUP BY和HAVING聚合数据

如果需要在表中聚合数据，可以使用GROUP BY和HAVING。

SELECT state, COUNT(*)FROM usersGROUP BY stateHAVING COUNT(*) > 100;

此查询按州对用户进行分组，并计算每个州的用户数量。然后，它使用了HAVING过滤了用户少于100个的州。

技巧10：使用Stored Procedure（存储过程）进行复杂查询

如果你要经常进行复杂查询，可以创建一个存储过程来简化代码并提高性能。

CREATE PROCEDURE get_top_users()BEGIN  SELECT *  FROM users  ORDER BY score DESC  LIMIT 10;END;

此存储过程根据用户的得分从users表中选择前10名用户。

你可以这样称呼这个存储过程：

CALL get_top_users();

让我们来看看这些技巧的用例：

• 在第一个用例中，我们选择所有姓Smith的用户。通过使用SELECT first_name, last_name而不是SELECT *，我们只选择了我们需要的列，这可以提高性能。

• 在第二个用例中，我们为用户选择所有订单。通过添加一个ORDER BY子句和一个LIMIT子句，我们只选择了最近的订单，这可以提高性能。

• 在第三个用例中，我们得到了每个用户的订单数量。通过添加HAVING子句，我们过滤掉了订单少于5个的用户，这可以提高性能。

结语

优化SQL查询很复杂，但对于提高数据库性能来说，这也很重要。

利用10条建议来优化你的查询吧！记住，使用EXPLAIN来理解查询执行，使用索引来加速查询，避免使用SELECT *，谨慎使用连接，尽可能避免子查询，使用UNION ALL代替UNION，使用EXISTS代替COUNT，使用LIMIT和OFFSET来控制结果集，使用GROUP BY和HAVING来聚合数据，并使用存储过程来进行复杂查询。

借此，你可以优化查询，提高性能，使你的应用程序运行得更快。

希望这篇文章对你有所帮助，感谢你的阅读！

原文作者：Gabe Araujo, M.Sc.

翻译作者：高佑兮

美工编辑：过儿

校对审稿：Chuang

原文链接：https://blog.devgenius.io/here-are-my-top-10-tips-for-optimizing-your-sql-queries-5563272f3c79

本周公开课预告

往期精彩回顾

点「在看」的人都变好看了哦

点击“阅读原文”查看数据应用学院核心课程