🚰 🛡️ 🔎 一次SQL调查的历史记录 🚥 🤪 🤶🏾

去年12月，我收到了VWO支持团队的有趣的错误报告。对于大型企业客户而言，一份分析报告的加载时间似乎令人望而却步。由于这是我的责任范围，因此我立即专注于解决问题。

背景知识

为了弄清楚我在说什么，我将简单介绍一下VWO。这个平台可让您在网站上执行各种针对性的广告系列：进行A / B实验，跟踪访问者和转化，分析销售渠道，显示热图并播放访问记录。

但是平台中最重要的是报告。以上所有功能都是相互关联的。而对于企业客户而言，如果没有强大的平台以分析的形式呈现它们，那么大量信息将毫无用处。

使用该平台，您可以对大型数据集进行任意请求。这是一个简单的示例：

 显示abc.com上的所有点击
从<日期d1>到<日期d2>
对于那些谁
二手Chrome或
 （在欧洲使用过iPhone）

注意布尔运算符。客户端可在查询界面中使用它们来进行任意复杂的查询以检索样本。

要求缓慢

有问题的客户正在尝试做一些直观上应该可以快速工作的事情：

 显示所有会议记录
适用于访问任何页面的用户
带有“ / job”的URL

这个网站上的流量很大，我们为此存储了超过一百万个唯一URL。他们想找到一个与其业务模型相关的非常简单的url模板。

初步调查

让我们看看数据库中发生了什么。以下是原始的慢速SQL查询：

SELECT count(*) FROM acc_{account_id}.urls as recordings_urls, acc_{account_id}.recording_data as recording_data, acc_{account_id}.sessions as sessions WHERE recording_data.usp_id = sessions.usp_id AND sessions.referrer_id = recordings_urls.id AND ( urls && array(select id from acc_{account_id}.urls where url ILIKE '%enterprise_customer.com/jobs%')::text[] ) AND r_time > to_timestamp(1542585600) AND r_time < to_timestamp(1545177599) AND recording_data.duration >=5 AND recording_data.num_of_pages > 0 ;

以下是时间安排：

 计划的时间：1.480毫秒
交货时间：1431924.650 ms

该请求绕过了15万行。查询计划程序显示了几个有趣的细节，但没有明显的瓶颈。

让我们进一步研究查询。如您所见，它将创建三个表JOIN ：

session ：显示会话信息：浏览器，用户代理，国家/地区等。
recording_data ：记录的URL，页面，访问持续时间
urls ：为避免重复很大的url，我们将它们存储在单独的表中。

另请注意，我们所有的表都已按account_id拆分。因此，当由于一个特别大的帐户而其他帐户有问题时，则排除了这种情况。

寻找证据

通过仔细检查，我们发现特定请求中的某些内容不正确。值得一看的是这一行：

 urls && array( select id from acc_{account_id}.urls where url ILIKE '%enterprise_customer.com/jobs%' )::text[]

首先想到的是，也许由于所有这些长URL中的ILIKE （我们为该帐户收集了超过140万个唯一 URL），性能可能会下降。

但是，不-这不是重点！

 SELECT id FROM urls WHERE url ILIKE '%enterprise_customer.com/jobs%'; id -------- ... (198661 rows) Time: 5231.765 ms

模板搜索请求本身仅需5秒钟。在百万个唯一的URL上搜索模式显然不是问题。

列表上的下一个可疑对象是几个JOIN 。也许他们的过度使用导致增长放缓？ JOIN通常是性能问题最明显的候选者，但是我不认为我们的案例很典型。

 analytics_db=# SELECT count(*) FROM acc_{account_id}.urls as recordings_urls, acc_{account_id}.recording_data_0 as recording_data, acc_{account_id}.sessions_0 as sessions WHERE recording_data.usp_id = sessions.usp_id AND sessions.referrer_id = recordings_urls.id AND r_time > to_timestamp(1542585600) AND r_time < to_timestamp(1545177599) AND recording_data.duration >=5 AND recording_data.num_of_pages > 0 ; count ------- 8086 (1 row) Time: 147.851 ms

这也不是我们的情况。事实证明JOIN的速度非常快。

我们缩小了嫌疑人的范围

我准备开始更改查询以实现任何可能的性能改进。我和我的团队提出了两个主要想法：

对子查询URL使用EXISTS ：我们想再次检查子查询的URL是否存在任何问题。实现此目的的一种方法是简单地使用EXISTS 。 EXISTS 可以极大地提高性能，因为它会在按条件找到单行后立即终止。

 SELECT count(*) FROM acc_{account_id}.urls as recordings_urls, acc_{account_id}.recording_data as recording_data, acc_{account_id}.sessions as sessions WHERE recording_data.usp_id = sessions.usp_id AND ( 1 = 1 ) AND sessions.referrer_id = recordings_urls.id AND (exists(select id from acc_{account_id}.urls where url ILIKE '%enterprise_customer.com/jobs%')) AND r_time > to_timestamp(1547585600) AND r_time < to_timestamp(1549177599) AND recording_data.duration >=5 AND recording_data.num_of_pages > 0 ; count 32519 (1 row) Time: 1636.637 ms

好吧，是的。子查询包装在EXISTS ，可使所有内容变得超快。下一个逻辑问题是为什么带有JOIN的查询和子查询本身的速度很快，但在一起的速度却非常慢？

我们将子查询移至CTE ：如果请求本身快速，我们可以简单地先计算快速结果，然后将其提供给主请求

 WITH matching_urls AS ( select id::text from acc_{account_id}.urls where url ILIKE '%enterprise_customer.com/jobs%' ) SELECT count(*) FROM acc_{account_id}.urls as recordings_urls, acc_{account_id}.recording_data as recording_data, acc_{account_id}.sessions as sessions, matching_urls WHERE recording_data.usp_id = sessions.usp_id AND ( 1 = 1 ) AND sessions.referrer_id = recordings_urls.id AND (urls && array(SELECT id from matching_urls)::text[]) AND r_time > to_timestamp(1542585600) AND r_time < to_timestamp(1545107599) AND recording_data.duration >=5 AND recording_data.num_of_pages > 0;

但是它仍然很慢。

找到罪魁祸首

一直以来，一件小事在我眼前闪过，我不停地从旁边掠过。但是，由于什么都没有了，我决定看她一眼。我说的是&&运算符。尽管EXISTS只是提高了性能，但&&是所有慢速查询版本中唯一剩下的共同因素。

查看文档，我们发现当需要查找两个数组之间的公共元素时&&使用&& 。

在原始请求中，这是：

 AND ( urls && array(select id from acc_{account_id}.urls where url ILIKE '%enterprise_customer.com/jobs%')::text[] )

这意味着我们对网址进行模板搜索，然后找到具有共享记录的所有网址的交集。这有点令人困惑，因为此处的“ URL”不是指包含所有URL的表，而是指recording_data表中的“ URL”列。

随着对&&怀疑的&& ，我试图在EXPLAIN ANALYZE生成的查询计划中找到确认（我已经有一个保存的计划，但是通常比尝试理解查询计划者的不透明性来进行SQL实验更方便）。

 Filter: ((urls && ($0)::text[]) AND (r_time > '2018-12-17 12:17:23+00'::timestamp with time zone) AND (r_time < '2018-12-18 23:59:59+00'::timestamp with time zone) AND (duration >= '5'::double precision) AND (num_of_pages > 0)) Rows Removed by Filter: 52710

仅来自&&的几行过滤器。这意味着该操作不仅昂贵，而且执行了多次。

我通过隔离条件进行了检查

 SELECT 1 FROM acc_{account_id}.urls as recordings_urls, acc_{account_id}.recording_data_30 as recording_data_30, acc_{account_id}.sessions_30 as sessions_30 WHERE urls && array(select id from acc_{account_id}.urls where url ILIKE '%enterprise_customer.com/jobs%')::text[]

该请求很慢。由于JOIN很快，子查询也很快，因此仅&&运算符保留。

这只是一个关键操作。我们始终需要在URL主表中进行搜索，以按模式进行搜索，并且始终需要找到交集。我们无法直接搜索url条目，因为这些仅仅是链接到urls标识符。

寻求解决方案

&&慢，因为这两个集合都很大。如果我将urls替换为{ "http://google.com/", "http://wingify.com/" }则该操作将相对较快。

我开始寻找一种在不使用&&情况下在Postgres中进行集合交集的方法，但没有取得太大的成功。

最后，我们决定简单地单独解决问题：给我所有与模式匹配的url字符串。如果没有其他条件，它将是-

 SELECT urls.url FROM acc_{account_id}.urls as urls, (SELECT unnest(recording_data.urls) AS id) AS unrolled_urls WHERE urls.id = unrolled_urls.id AND urls.url ILIKE '%jobs%'

代替JOIN语法，我只使用了一个子查询并扩展了recording_data.urls数组，以便可以将条件直接应用于WHERE 。

这里最重要的是&&用于检查给定条目是否包含适当的URL。斜视一下，您可以看到此操作在数组的元素（或表的行）中移动，并在满足条件（匹配）时停止。看起来不一样吗？是的，存在。

由于发生这种情况时可以从子查询的上下文外部引用recording_data.urls ，因此我们可以返回我们的老朋友EXISTS并用子查询将它们包装起来。

将所有内容组合在一起，我们得到了最终的优化查询：

 SELECT count(*) FROM acc_{account_id}.urls as recordings_urls, acc_{account_id}.recording_data as recording_data, acc_{account_id}.sessions as sessions WHERE recording_data.usp_id = sessions.usp_id AND ( 1 = 1 ) AND sessions.referrer_id = recordings_urls.id AND r_time > to_timestamp(1542585600) AND r_time < to_timestamp(1545177599) AND recording_data.duration >=5 AND recording_data.num_of_pages > 0 AND EXISTS( SELECT urls.url FROM acc_{account_id}.urls as urls, (SELECT unnest(urls) AS rec_url_id FROM acc_{account_id}.recording_data) AS unrolled_urls WHERE urls.id = unrolled_urls.rec_url_id AND urls.url ILIKE '%enterprise_customer.com/jobs%' );

最终运行Time: 1898.717 ms现在该庆祝吗？

没那么快！首先，您需要检查正确性。我对EXISTS优化非常怀疑，因为它会将逻辑更改到更早的阶段。我们必须确保我们没有在请求中添加非显而易见的错误。

一个简单的检查就是对大量不同数据集的慢速查询和快速查询执行count(*) 。然后，对于一小部分数据，我手动检查了所有结果的正确性。

所有检查均得出一致的积极结果。我们解决了！

经验教训

从这个故事中可以吸取很多教训：

查询计划不能说明全部内容，但可以提供线索
主要嫌疑人并不总是真正的罪魁祸首
可以打破慢查询以隔离瓶颈
并非所有的优化本质上都是还原性的
尽可能使用EXIST可以大大提高生产率。

结论

我们将请求时间从大约24分钟缩短到了2秒-极大地提高了性能！尽管这篇文章很大，但是我们所做的所有实验都是在同一天进行的，并且根据估算，优化和测试需要1.5到2个小时。

SQL是一种很棒的语言，即使不怕它，也请尝试学习和使用。充分了解SQL查询的执行方式，数据库如何生成查询计划，索引如何工作以及仅处理要处理的数据大小，您就可以在查询优化中大获成功。但是，同样重要的是，继续尝试不同的方法并慢慢解决问题，找到瓶颈。

获得此类结果的最好的部分是明显的速度改进-以前从未下载过的报告现在几乎立即可以加载。

特别感谢我的队友Aditya Misra ， Aditya Gauru和Varun Malhotra的集思广益，以及Dinkar Pandir在我们最终要求中发现了重要错误，然后才向他道别！

一次SQL调查的历史记录