处理缓慢的SQL查询？让 "EXPLAIN "语句来处理吧
了解EXPLAIN查询计划（第二部分）。
你有没有遇到过一个运行缓慢的SQL查询，但你不知道从哪里开始寻找优化的方法？如果有某种方法可以准确地指出你的查询的哪些部分导致整个事情运行缓慢，这样你就可以把你的努力集中在那里，而不是到处进行调整了。
这正是EXPLAIN 语句所允许我们做的。EXPLAIN 语句会给你由数据库的查询计划器产生的查询计划，也会给你估计你的查询中最昂贵的部分是什么。然而，解读查询计划的每一部分告诉我们什么，一开始会有点棘手。
在这篇文章中，我们将介绍如何理解查询计划告诉我们的内容，以及如何使用这些信息来提高查询的性能。
本文是两篇文章中的第一部分，主要是通过EXPLAIN 语句来理解查询计划器的输出。
在这里，我们将使用Postgres作为我们的数据库，所以只要注意，如果你使用不同的RDBMS，可能会有一些小的差异，特别是在Postgres相关的功能方面。如果你使用的是数据仓库，你实际上可能有一套单独的工具来分析你的查询性能，因为RDBMS和数据仓库以不同的方式去存储和检索数据。
设置
为了开始工作，让我们创建一个基本的表，我们可以在整个过程中使用它来写查询和分析性能。
CREATE TABLE user_event_log ( id SERIAL PRIMARY KEY, event_id INTEGER, event_metadata JSONB, user_id INTEGER, created_at TIMESTAMP DEFAULT NOW(), updated_at TIMESTAMP DEFAULT NOW());
虽然我们不会创建它们，但我们将假设event_id和user_id是对一个单独的event和user表的外键引用。
接下来，让我们插入一些数据。
INSERT INTO user_event_log(event_id, event_metadata, user_id) VALUES(1, ‘{“field”: “some value”}’, 1),(2, ‘{“field”: “some value”}’, 1);
基本的EXPLAIN和相关术语
虽然我们目前只有两行，但实际上已经有很多有趣的信息，我们可以从这里得到，所以让我们开始使用我们的EXPLAIN查询。
EXPLAIN SELECT *FROM user_event_logWHERE user_id = 1;
这就是我们的输出。
Seq Scan on user_event_log (cost=0.00..21.50 rows=5 width=60) Filter: (user_id = 1)
好的，那么这意味着什么呢？让我们从顶部的EXPLAIN关键字开始，一点一点地看。
EXPLAIN关键字将给我们提供查询计划，即查询计划员认为是最佳的方法，如果执行这个查询，将采取这种方法。此外，我们也会得到一些估计的成本指标。
在更复杂的计划中，我们将在后面看到，我们可以得到多个节点作为输出，但在这个查询中我们只得到一个节点。
我们首先看到的是Seq Scan 关键字，换句话说，我们有一个全表扫描。这意味着我们的查询在执行这个查询时将会浏览这个表中的每一条记录。呀!
现在，当我们只有两条记录时，这显然不是一个问题，但想象一下，有一个有1000万条记录的表，而你现在每秒钟多次运行这个查询。这意味着成吨的数据将被从磁盘读入内存，而只有相关的位被保留。
这也是Filter: (user_id = 1)语句的作用所在。
这里发生的事情的全貌是，我们的查询正在遍历我们的整个数据，从磁盘上读取数据，它发现的任何符合这个条件的记录将被保留下来，与查询一起返回，其余的被丢弃。
我们稍后会看一下如何解决这个问题。你们中的一些人可能已经知道这里的解决方案是索引，但让我们一步步来，看看括号里的其他输出。
我们还看到这个：cost=0.00...21.50 。这意味着什么呢？
这是Postgres对查询的这一部分的成本的估计。这个成本是以任意单位计算的，本身并没有什么意义，但是它对于比较查询的不同部分的相对成本是非常有用的。在我们的例子中，我们还没有多个部分，但这在以后可能会很有用。
0.00 是估计的启动成本。启动成本是对查询的这一部分产生第一行所需时间的衡量。在这种情况下，它是0，但我们稍后会看到，它并不总是从0开始。
21.50 是查询的这一部分的总成本，从开始到结束的时间。
需要注意的是，这两个成本部分都是累积的，也就是说，较高节点的成本包括较低部分的成本。
最后，Postgres估计它将返回5行，以字节为单位的大小（它称之为宽度）是每行60字节。行的估计显然是错误的，但这很好，我们将在稍后讨论行的估计，以及你应该担心的时候。
基本优化
现在我们已经看到了我们的查询计划，让我们把事情优化一下。当你看到一个全表扫描时，你通常会考虑通过在列上创建索引来优化它。
CREATE INDEX event_log_user_id_idx ON user_event_log(user_id);
我们的查询计划现在看起来像这样。
Seq Scan on user_event_log (cost=0.00..1.02 rows=1 width=60) Filter: (user_id = 1)
你现在可能在想，等等，这不就是我们之前的计划吗？
是的，而且我们实际上已经达到了一个有趣的边缘情况。
Postgres现在必须在使用索引和使用全表扫描之间做出决定，而且它决定在这种情况下全表扫描实际上是更有效的操作。通常，当你最终返回表中至少10%的数据时，全表扫描会比索引扫描更有效率。
这是因为使用索引需要几个IO操作--查找索引，然后查找从索引中确定的相关记录。而全表扫描只是直接读入记录。
如果我们用一个简单的python脚本在数据库中加入更多的数据
然后再次尝试我们的EXPLAIN 查询，这就是我们的查询计划的样子。
Index Scan using event_log_user_id_idx on user_event_log (cost=0.28..8.37 rows=5 width=117) Index Cond: (user_id = 1)
我们可以看到，我们从一个全表扫描和一个过滤器换成了一个索引扫描和一个索引条件。
在我们继续快速讨论这个节点之前，你们中的一些人可能已经注意到，我们现在总共有219个用户。难道不是应该有10个用户就足以让我们达到大约10%的门槛吗？嗯，是的，但也不是。原因是我们正在处理一个非常小的表，而且在这种规模下我们实际上也受到了缓冲区的影响。我们将在第二部分回到缓冲区，以避免在这里出现太大的分歧。
回到我们的新输出--我们看到我们在上面创建的索引event_log_user_id_idx 上有一个Index Scan ，我们可以看到我们的索引是使用过滤器(user_id = 1) 中的条件进行扫描的。这意味着我们通过我们的索引，并通过从索引中查找我们需要的相关记录来找到我们需要从磁盘中读取的行。
如果我们为额外的用户改变我们的过滤条件，我们会看到Index Scan ，但是我们的索引条件会被更新。这是因为我们可以为所有这些过滤条件使用同一个索引。
EXPLAIN SELECT *FROM user_event_logWHERE user_id in (1, 12, 87)
这给了。
Index Scan using event_log_user_id_idx on user_event_log (cost=0.28..17.09 rows=15 width=117) Index Cond: (user_id = ANY (‘{1,12,87}’::integer[]))”
然而，改变我们的过滤条件，使其也包括，例如，事件_id，我们就会增加一个额外的过滤操作，因为我们没有一个关于（user_id, event_id）的索引。
EXPLAIN SELECT *FROM user_event_logWHERE user_id = 1 and event_id = 2
给予。
Index Scan using event_log_user_id_idx on user_event_log (cost=0.28..8.38 rows=1 width=117) Index Cond: (user_id = 1) Filter: (event_id = 2)
这里发生的事情是，我们根据我们的索引查询加载所有相关的记录user_id = 1，然后用过滤条件event_id = 2 来过滤这些行。
如果我们在event_id和user_id上都有一个索引，我们将恢复到只有一个索引查询。
CREATE INDEX event_log_user_id_event_id_idx ON user_event_log(user_id, event_id);
结果是。
Index Scan using event_log_user_id_event_id_idx on user_event_log (cost=0.28..8.30 rows=1 width=117) Index Cond: ((user_id = 1) AND (event_id = 2))
但是要注意，这并不总是最好的步骤。一般来说，你应该尽量减少你的表的索引数量。这是因为每次你的数据发生变化时，所有的索引都需要更新。如果你的数据经常变化，这会给你的数据库增加很多开销，因为它需要维护你的所有索引。因此，要确保你只拥有你真正使用的索引。
同样重要的是，要记住把你所处的情况考虑在内。查询计划提供了关于计划如何执行查询的信息，但是接下来要由你来决定这是否有潜在的问题，或者计划是否真的没问题。
例如，在你的应用程序中，你可能有上面的非常简单的选择查询。
SELECT *FROM user_event_logWHERE user_id = 1 and event_id = 2
而你注意到，随着负载的增加，性能在下降。你运行EXPLAIN 计划，发现你实际上只有一个关于user_id的索引，而没有关于user_id和event_id的索引。天真地添加（user_id, event_id）索引实际上会导致额外的性能问题，因为实际问题可能是你在元数据列上也有一个索引，而增加的负载导致你的数据库不得不做大量的后台工作来维护这个索引。添加一个额外的索引并不总是能神奇地解决你的问题，有时它实际上可能只是使情况变得更糟。
事实上，我记得有一次，我们把一个分析性ETL工作的结果批量导入到我们的Postgres数据库中，我们正好遇到这些问题。虽然批处理upsert本身在15分钟内就完成了，但由于索引更新和自动吸尘过程被触发，我们数据库的CPU负载在整整一个小时内都在80%以上。在这种情况下，从upsert转为insert，对陈旧的数据进行延迟删除作业，并删除大量未使用的索引，为我们解决了这个问题。
所以请记住，一定要考虑你工作的全部环境。
更复杂的查询
现在我们已经花时间理解和破译了节点结果中的每个元素告诉我们什么，让我们看看一些更复杂的查询。
为了帮助我们，我们将在这里创建第二个表，其中包含每个事件_id到它们各自名称的映射。
CREATE TABLE IF NOT EXISTS event ( id SERIAL PRIMARY KEY, name TEXT);
并插入一些数据
INSERT INTO event(id, name) VALUES(1, ‘login’), (2, ‘like’), (3, ‘comment’), (4, ‘follow’), (5, ‘subscribe’);
现在让我们对我们的user_event_log表进行连接，并过滤掉所有事件，除了like 事件。
EXPLAIN SELECT uel.*FROM user_event_log uelLEFT JOIN event e on e.id = uel.event_idWHERE e.name = ‘like’;
给予。
Hash Join (cost=25.95..79.75 rows=5 width=117) Hash Cond: (uel.event_id = e.id) -> Seq Scan on user_event_log uel (cost=0.00..50.92 rows=1092 width=117) -> Hash (cost=25.88..25.88 rows=6 width=4) -> Seq Scan on event e (cost=0.00..25.88 rows=6 width=4) Filter: (name = ‘like’::text)
这里跳出来的第一件事是，我们现在有几个节点，我们也有嵌套（或子+父）组件。
我们阅读这些查询计划的方式是由内向外的。因此，首先发生的步骤将是对事件表进行序列扫描，并应用过滤器。
下一层很有意思，因为我们有两个节点在同一层次上。这里首先发生的是我们从下层节点（也叫内层子节点）开始，我们将事件表上的序列扫描结果和过滤器加载到内存哈希表中。
然后，对于上层节点（也称为外层子节点）的每一条记录，我们使用连接条件在哈希表中寻找匹配的记录。
SQL显然不仅仅是由SELECT、WHERE和JOIN语句组成，但我们在这里学到的原则也可以应用到你将要使用的其他类型的语句中。
总结
在这篇文章中，我们看到了如何使用EXPLAIN 语句来获取和理解查询计划，它告诉我们数据库计划如何执行这个查询，以及它认为每一步的成本有多高。
从查询计划中，我们已经能够得到一些有洞察力的信息，比如数据库认为哪个节点的工作量最大，以及它打算在哪里使用索引。
然而，了解我们查询的实际结果也是非常有用的，并且能够将查询计划器的成本估计与实际数字进行比较。这就是EXPLAIN ANALYZE 语句允许我们做的事情，你可以在本文的第二部分中阅读。
