查询时间从24分钟到2秒钟：记一次神奇的SQL优化 作者 | VWO译者 | 无明编辑 | VincentAI 前线导读：去年十二月份，VWO 平台支持团队发布了一份缺陷报告。这份报告很有意思，其中有一个来自某家企业用户的分析查询，它的运行速度非常慢。本文作者是这个数据平台的一员，所以立马开始着手诊断这个问题。 更多优质内容请关注微信公众号“AI 前线”（ID：ai-front）背 景首先，我觉得有必要介绍一下 VWO（https://vwo.com/）平台。人们可以在这个平台上运行各种与他们的网站有关的工作负载，比如 A/B 测试、跟踪访问用户、转换、漏斗分析、渲染热点图、重放访问用户步骤，等等。 这个平台真正强大的地方在于它所提供的报告。如果没有这个平台，即使企业用户收集了大量数据也是毫无用处的，因为他们无法从数据中获取洞见。 有了这个平台，用户可以针对海量数据执行各种复杂的查询，比如下面这个： Showall clicksbyvisitorsonwebpage"abc.com" FROMdated1TOdated2 forpeople who were either usingChromeasa browserOR (were browsingfromEuropeANDwereusingiPhone devices)请注意查询中的布尔运算符，查询接口为用户提供了这些东西，他们可以随意运行复杂的查询来获得想要的数据。 慢查询这个用户执行的查询从表面上看应该是很快的： Showme allsessionrecordings foruserswho visitedanywebpage containing theurlthat matches the pattern"/jobs"这个网站的流量是非常巨大的，我们保存了数百万个唯一的 URL。这个用户想要查询符合他们业务需求的 URL。 初步诊断现在让我们来看看在数据库方面都发生了什么。下面是相应的 SQL 语句： SELECT count(*) FROM acc_{account_id}.urlsasrecordings_urls, acc_{account_id}.recording_dataasrecording_data, acc_{account_id}.sessionsassessions WHERE recording_data.usp_id = sessions.usp_id ANDsessions.referrer_id = recordings_urls.id AND( urls array(selectidfromacc_{account_id}.urlswhereurlILIKE'%enterprise_customer.com/jobs%')::text[] ) ANDr_time to_timestamp(1542585600) ANDr_time to_timestamp(1545177599) ANDrecording_data.duration =5 ANDrecording_data.num_of_pages 0这是它的执行时间： Planningtime: 1.480ms Executiontime: 1431924.650ms这个语句查询的行数在 15 万行左右。查询计划显示了一些信息，但还不能看出瓶颈出在哪里。 我们再来进一步分析一下查询语句。这个语句连接了三张表： sessions：用于展示会话信息的表，例如 browser、user-agent、country，等等。 recording_data：记录 url、页面、时间段，等等。 urls：为了避免出现重复的 url，我们使用单独的表对 url 进行了规范化。 另外请注意，我们使用 account_id 对这三表进行了分区，所以不可能出现因为某些账号记录过多导致性能变慢的情况。 寻找线索经过进一步排查，我们发现这个查询有一些不一样的地方。比如下面这行： urls && array( selectidfromacc_{account_id}.urls whereurlILIKE'%enterprise_customer.com/jobs%' )::text[]最开始我认为针对所有长 URL 执行“ILIKE”操作是导致速度变慢的元凶，但其实并不是！ SELECTidFROMurlsWHEREurlILIKE'%enterprise_customer.com/jobs%'; id -------- ... (198661 rows) Time: 5231.765 ms模式匹配查询本身只花了 5 秒钟，所以要匹配数百万个 URL 显然并不是个问题。 第二个可疑的地方是 JOIN 语句，或许是大量的连接操作导致速度变慢？一般来说，如果查询速度变慢，我们首先会认为连接操作是罪魁祸首，但对于目前这个情况，我不认为是这样的。 analytics_db=# SELECT count(*) FROM acc_{account_id}.urlsasrecordings_urls, acc_{account_id}.recording_data_0asrecording_data, acc_{account_id}.sessions_0assessions WHERE recording_data.usp_id = sessions.usp_id ANDsessions.referrer_id = recordings_urls.id ANDr_time to_timestamp(1542585600) ANDr_time to_timestamp(1545177599) ANDrecording_data.duration =5 ANDrecording_data.num_of_pages 0 count ------- 8086 (1row) Time:147.851ms看，JOIN 操作实际上是很快的。 缩小可疑范围我开始调整查询语句，尽一切可能提升查询性能。我和我的团队想出了两个方案。 针对子查询使用 EXISTS：我们想要进一步确认问题是不是出在 URL 子查询上。一种方法是使用 EXISTS，它会在找到第一条匹配记录时就返回，对性能提升很有帮助。 SELECT count(*) FROM acc_{account_id}.urlsasrecordings_urls, acc_{account_id}.recording_dataasrecording_data, acc_{account_id}.sessionsassessions WHERE recording_data.usp_id = sessions.usp_id AND(1=1) ANDsessions.referrer_id = recordings_urls.id AND(exists(selectidfromacc_{account_id}.urlswhereurlILIKE'%enterprise_customer.com/jobs%')) ANDr_time to_timestamp(1547585600) ANDr_time to_timestamp(1549177599) ANDrecording_data.duration =5 ANDrecording_data.num_of_pages 0 count 32519 (1 row) Time: 1636.637 ms使用了 EXISTS 后，速度变快了很多。那么问题来了，为什么 JOIN 查询和子查询都很快，但放在一起就变得这么慢呢？ 将子查询移到 CTE 中：如果子查询本身很快，我们可以预先计算结果，然后再传给主查询。 WITH matching_urls AS ( selectid::textfromacc_{account_id}.urlswhereurlILIKE'%enterprise_customer.com/jobs%' ) SELECT count(*)FROMacc_{account_id}.urlsasrecordings_urls, acc_{account_id}.recording_dataasrecording_data, acc_{account_id}.sessionsassessions, matching_urls WHERE recording_data.usp_id = sessions.usp_id AND(1=1) ANDsessions.referrer_id = recordings_urls.id AND(urls array(SELECTidfrommatching_urls)::text[]) ANDr_time to_timestamp(1542585600) ANDr_time to_timestamp(1545107599) ANDrecording_data.duration =5 ANDrecording_data.num_of_pages 0;但这样仍然很慢。 寻找元凶还有个地方之前一直被我忽略了，但因为没有其他办法了，所以我决定看看这个地方，那就是 && 运算符。既然 EXISTS 对性能提升起到了很大作用，那么剩下的就只有 && 可能会导致查询变慢了。 && 被用来找出两个数组的公共元素。 初始查询中的 && 是这样的： AND ( urls && array(selectidfromacc_{account_id}.urlswhereurlILIKE'%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因为有好多行 &&，说明它被执行了好几次。 我通过单独执行这些过滤条件确认了是这个问题。 SELECT1 FROM acc_{account_id}.urlsasrecordings_urls, acc_{account_id}.recording_data_30asrecording_data_30, acc_{account_id}.sessions_30assessions_30 WHERE urls array(selectidfromacc_{account_id}.urlswhereurlILIKE'%enterprise_customer.com/jobs%')::text[]这个查询的 JOIN 很快，子查询也很快，所以问题出在 && 上面。 解决方案&& 之所以很慢，是因为两个集合都很大。如果我把 urls 替换成{"http://google.com/","http://wingify.com/"}，这个操作就很快。 我开始在谷歌上搜索如何在 Postgre 中不使用 && 进行交集操作，但并没有找到答案。 最后，我们决定这样做：获取所有匹配的 urls 行，像下面这样： SELECTurls.url FROM acc_{account_id}.urlsasurls, (SELECTunnest(recording_data.urls)ASid)ASunrolled_urls WHERE urls.id = unrolled_urls.idAND urls.urlILIKE'%jobs%'这里没有使用 JOIN 语句，而是使用了一个子查询，并展开 recording_data.urls 数组，这样就可以直接在 where 语句中应用查询条件。 这里的 && 用来判断一个给定的 recording 是否包含匹配的 URL。它会遍历数组（或者说表中的行），在条件满足时立即停止，这个看起来是不是跟 EXISTS 很像？ 因为我们可以在子查询之外引用 recording_data.urls，在必要时可以使用 EXISTS 来包装子查询。 把所有的东西放在一起，我们就得到了最终这个优化的查询： SELECT count(*) FROM acc_{account_id}.urlsasrecordings_urls, acc_{account_id}.recording_dataasrecording_data, acc_{account_id}.sessionsassessions WHERE recording_data.usp_id = sessions.usp_id AND(1=1) ANDsessions.referrer_id = recordings_urls.id ANDr_time to_timestamp(1542585600) ANDr_time to_timestamp(1545177599) ANDrecording_data.duration =5 ANDrecording_data.num_of_pages 0 ANDEXISTS( SELECTurls.url FROM acc_{account_id}.urlsasurls, (SELECTunnest(urls)ASrec_url_idFROMacc_{account_id}.recording_data) ASunrolled_urls WHERE urls.id = unrolled_urls.rec_url_idAND urls.urlILIKE'%enterprise_customer.com/jobs%' 这个查询的执行时间为 1898.717 毫秒，是不是值得庆祝一下？ 等等，我们还要验证一下结果是不是对的。我对 EXISTS 有所怀疑，因为它有可能会改变查询逻辑，导致过早地退出。我们要确保不会在查询中引入新的 bug。 我们对慢查询和快查询结果进行了 count(*) 比较，不同数据集的查询结果都是一致的。对于一些较小的数据集，我们还手动比对了具体数据，也没有问题。 学到的教训在这次性能排查过程中，我们学到了这些东西： 查询计划并不会告诉我们所有东西，但还是很有用的； 越是明显的疑点越不太可能是元凶； 一个慢查询可能包含多个单独的瓶颈点； 并非所有优化都是可简化的； 在可能的地方使用 EXISTS 来获得大幅性能提升。 结 论我们将一个查询的运行时间从 24 分钟降到了 2 秒钟，一个不可思议的性能提升！我们花了 1 个半到 2 个小时的时间来优化和测试这个查询。SQL 其实是一门非常神奇的语言，只要你放开心态去拥抱它。 英文原文： https://parallelthoughts.xyz/2019/05/a-tale-of-query-optimization/ 你也「在看」吗？??