查询时间从 24 分钟到 2 秒钟：记一次神奇的 SQL 优化

去年十二月份，VWO 平台支持团队发布了一份缺陷报告。这份报告很有意思，其中有一个来自某家企业用户的分析查询，它的运行速度非常慢。因为我是这个数据平台的一员，所以立马开始着手诊断这个问题。

背 景

首先，我觉得有必要介绍一下 VWO（ https://vwo.com/ ）平台。人们可以在这个平台上运行各种与他们的网站有关的工作负载，比如 A/B 测试、跟踪访问用户、转换、漏斗分析、渲染热点图、重放访问用户步骤，等等。

这个平台真正强大的地方在于它所提供的报告。如果没有这个平台，即使企业用户收集了大量数据也是毫无用处的，因为他们无法从数据中获取洞见。

有了这个平台，用户可以针对海量数据执行各种复杂的查询，比如下面这个：

Show all clicks by visitors on webpage "abc.com"
FROM <date d1> TO <date d2>
for people who were either
using Chrome as a browser OR 
(were browsing from Europe AND were using iPhone devices)

请注意查询中的布尔运算符，查询接口为用户提供了这些东西，他们可以随意运行复杂的查询来获得想要的数据。

慢查询

这个用户执行的查询从表面上看应该是很快的：

Show me all session recordings 
for users who visited any webpage 
containing the url that matches the pattern "/jobs"

这个网站的流量是非常巨大的，我们保存了数百万个唯一的 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 ;

这是它的执行时间：

原文链接:【 https://www.infoq.cn/article/oJswd3DkdzCwFl*ZDGNs 】。未经作者许可，禁止转载。