SQL优化案例解析：查询时间从24分钟到2秒钟

会员服务 ·

SQL优化案例解析：查询时间从24分钟到2秒钟

2019 年 6 月 6 日 InfoQ

作者 | VWO

译者 | 无明

去年十二月份，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 eitherusing 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 ;

这是它的执行时间：

Planning time: 1.480 msExecution time: 1431924.650 ms

这个语句查询的行数在 15 万行左右。查询计划显示了一些信息，但还不能看出瓶颈出在哪里。

我们再来进一步分析一下查询语句。这个语句连接了三张表：

sessions：用于展示会话信息的表，例如 browser、user-agent、country，等等。
recording_data：记录 url、页面、时间段，等等。
urls：为了避免出现重复的 url，我们使用单独的表对 url 进行了规范化。

另外请注意，我们使用 account_id 对这三表进行了分区，所以不可能出现因为某些账号记录过多导致性能变慢的情况。

寻找线索

经过进一步排查，我们发现这个查询有一些不一样的地方。比如下面这行：

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

最开始我认为针对所有长 URL 执行“ILIKE”操作是导致速度变慢的元凶，但其实并不是！

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

模式匹配查询本身只花了 5 秒钟，所以要匹配数百万个 URL 显然并不是个问题。

第二个可疑的地方是 JOIN 语句，或许是大量的连接操作导致速度变慢？一般来说，如果查询速度变慢，我们首先会认为连接操作是罪魁祸首，但对于目前这个情况，我不认为是这样的。

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

看，JOIN 操作实际上是很快的。

缩小可疑范围

我开始调整查询语句，尽一切可能提升查询性能。我和我的团队想出了两个方案。

针对子查询使用 EXISTS：我们想要进一步确认问题是不是出在 URL 子查询上。一种方法是使用 EXISTS，它会在找到第一条匹配记录时就返回，对性能提升很有帮助。

SELECT   count(*) FROM    acc_{account_id}.urls as recordings_urls,   acc_{account_id}.recording_data as recording_data,   acc_{account_id}.sessions as sessionsWHERE   recording_data.usp_id = sessions.usp_idAND ( 1 = 1 )AND sessions.referrer_id = recordings_urls.idAND (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 >=5AND 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_urlsWHERE    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 进行交集操作。这里的“urls“并不是指包含了所有 URL 的表，而是 recording_data 的”urls“列。

因为现在对 && 有所怀疑，我使用 EXPLAIN ANALYZE 对查询语句进行了分析。

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 1FROM   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 很快，子查询也很快，所以问题出在 && 上面。

解决方案

&& 之所以很慢，是因为两个集合都很大。如果我把 urls 替换成{"http://google.com/","http://wingify.com/"}，这个操作就很快。

我开始在谷歌上搜索如何在 Postgre 中不使用 && 进行交集操作，但并没有找到答案。

最后，我们决定这样做：获取所有匹配的 urls 行，像下面这样：

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

这里没有使用 JOIN 语句，而是使用了一个子查询，并展开 recording_data.urls 数组，这样就可以直接在 where 语句中应用查询条件。

这里的 && 用来判断一个给定的 recording 是否包含匹配的 URL。它会遍历数组（或者说表中的行），在条件满足时立即停止，这个看起来是不是跟 EXISTS 很像？

因为我们可以在子查询之外引用 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%'   );