3个能瞬间提升查询速度的SQL优化技术

今日份知识你摄入了么？

已经出现了一些数据库来解决特定的软件开发和数据科学需求。然而，关系数据库仍然是许多人最常用和首选的数据库。

查询速度是人们考虑不同数据模型的最常见原因。基于文档、列族和图形数据库帮助解决速度问题。

但是关系方法对于大多数用例来说已经足够了。此外，我们可以通过优化提高速度，同时仍然享受SQL提供的所有好处。

我并不是不愿意改变。然而，在任何项目中，保持技术栈尽可能精简通常是我的主要目标。这样，SQL是大多数工程师都能理解的东西—即使是新手！

因此，切换到不同的数据模型是我最不愿意考虑的事情。相反，我将首先进行这些调整，以便使用SQL充分利用关系数据库。

我没有在这里列出所有SQL优化技术。但这些都很简单，不需要太多的精力和时间。尽管如此，最好在计划阶段就决定这些技术，而不是在行动中解决问题。

图片来自Unsplash，作者Heramb kamble

在需要的地方创建索引，在多余的地方删除索引。

提高SQL查询速度的最简单方法之一是使用索引。

在分析和软件开发项目中，您可能知道在设计阶段查询数据库的最常用方法。了解使用模式是一项宝贵的信息。

想想一本非小说类的书。要知道我们在哪一页讨论一个特定的概念并不容易。因此，在书的最后，你经常会看到一个叫做索引的部分。它会按字母顺序排列文本中的所有关键字以及它们出现的页面。这使我们更容易快速到达我们想要的地方。

类似地，在数据库中，索引类似于经常用于搜索表中的数据的关键字或主题列表。通过在一列或一组列上创建索引，数据库可以快速找到符合搜索条件的行，而无需扫描整个表。

下面是一个Postgres的例子。但是大多数关系数据库都有类似的语法。

CREATE INDEX idx_customers_email ON customers (email);

这将在“customers”表的“email”列上创建一个名为“idx_customers_email”的索引。现在，当我们运行一个按“email”列过滤或排序的查询时，数据库可以使用索引快速找到相关行，而不是扫描整个表。

部分指标

还可以创建部分索引。比如说，我们经常使用电子邮件域而不是整个电子邮件来过滤客户表。我们可以为电子邮件创建索引。

下面是如何创建部分索引：

CREATE INDEX idx_partial_email_domain ONstudents (substring(email FROM '@(.*)$'));

创建像上面这样的索引将有助于下面这样的查询：

SELECT * FROM your_table WHERE substring(email from '@(.*)$') = 'gmail.com';

多列索引

与部分索引一样，有时我们需要使用多个列来查询表。在这种情况下，多列索引将有助于提高性能。

SELECT * FROM your_table WHERE substring(email from '@(.*)$') = 'gmail.com';

现在，与索引之前的查询相比，像下面这样的查询可以更快。

SELECT * FROM your_table WHERE email = '[e-mail]' AND city = 'New York'

有时，你应该放弃索引。

正如需要在添加新信息时维护和更新图书的索引一样，数据库索引也必须更新以反映表中数据的更改。

添加索引可以提高查询性能。然而，它也减慢了数据修改操作（如插入、更新或删除行），因为索引必须更新。根据最常运行的查询仔细考虑要索引哪些列，这一点非常重要。

因此，尽管使用索引可以获得更好的查询性能，但我们必须谨慎使用索引。当不需要索引时，可以使用单个命令删除索引。

DROP INDEX idx_email_city;

明智地使用连接和子查询。

我们使用关系数据库的主要原因是为了避免冗余。SQL只对连接有用。没有连接，我们就无法得到我们需要的信息。

然而，连接也是导致查询性能下降的原因。连接越多，速度越慢。

在图数据库中，查询性能不与连接成正比。相反，它与输出的大小成正比。这正是图形数据库的优势所在。

确保为查询使用适当的连接类型——内部、外部、左侧或右侧。在合并两个大表时，交叉连接是非常糟糕的。尽量避开它们。

即使我们不想要第二个表中的列，但我们想要检查对应行是否存在，也可以使用连接。例如，如果我们有两个表，分别是课程和学生，并且想要获取修过某一组课程的学生列表。我们通常是这样做的。

SELECT DISTINCT students.student_id, students.student_nameFROM studentsINNER JOIN courses ON students.course_id = courses.course_idWHERE courses.course_id IN ('C101', 'C102', 'C103');

这是一种方法，通常是可以的。这是同一查询的另一个版本。下面的代码使用子查询而不是连接。

SELECT DISTINCT student_id, student_nameFROM studentsWHERE course_id IN (  SELECT course_id  FROM courses  WHERE course_id IN ('C101', 'C102', 'C103'));

我并不是说子查询比连接更好。我经历过一种风格在某些情况下表现得更好，而另一种风格在其他情况下表现得更好。

因此，对于我们在生产系统中经常使用的查询，最好使用不同版本来测试查询性能，并选择最佳版本。

对很少变化的数据使用物化视图。

在某些情况下，你的数据集可能以非常慢的速度变化。然而，查询是批量的，通常花费的时间比你希望的要长。

在这里，物化视图物化视图是一个救星。

视图是保存的查询，其作用类似于表。因此，如果查询的特定部分是重复的，您可以将它们存储在数据库视图中，并在任何需要的地方重用它。

实体化视图是与其最新运行结果一起存储的视图。因此，每次查询时，结果都直接从预先保存的结果中提取出来，而不是查询数据库。

下面是一个物化视图，它计算每个国家和州的客户数量。你可以使用这个物化视图来存储计算值及其查询。

CREATE MATERIALIZED VIEW customer_summary ASSELECT customer_country, customer_state, COUNT(*) AS num_customersFROM customersGROUP BY customer_country, customer_state;

现在，我们可以像查询其他表一样查询物化视图：

SELECT *FROM customer_summaryWHERE customer_country = 'United States';

这大大节省了时间。但是，它不反映对底层数据表的更新。因此，每当数据库发生变化时，我们必须刷新物化视图以显示差异。

如果大多数变化不是很大，我们可以定期刷新数据。例如，如果要为客户显示报表视图，则可以在每天午夜刷新物化视图，并让用户知道更改将在每天早上出现。

下面是刷新物化视图的方法：

REFRESH MATERIALIZED VIEW customer_summary;

结论

优化SQL查询是一个很大的主题。我不认为这在小型团队和个人运营的项目中有任何重要性。

然而，未经优化的设计可能会以服务器负载和生产系统中的查询时间的形式使您付出代价。

这篇文章并不是关于每一个SQL优化技术。但我经常使用某些具有更显著性能改进的东西。这些不需要我的所有团队成员付出大量的努力。

原文作者：Thuwarakesh Murallie

翻译作者：马薏菲

美工编辑：过儿

校对审稿：Chuang

原文链接：https://towardsdatascience.com/3-important-sql-optimization-technique-d6da3e9c8442

本周公开课预告

往期精彩回顾

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

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