🥇一次SQL调查的故事

去年 XNUMX 月，我收到了来自 VWO 支持团队的一份有趣的错误报告。大型企业客户的一份分析报告的加载时间似乎令人望而却步。而且由于这是我的职责范围，所以我立即集中精力解决问题。

史前

为了清楚地说明我在说什么，我将向您介绍一些有关 VWO 的信息。通过这个平台，您可以在您的网站上发起各种有针对性的活动：进行 A/B 实验、跟踪访问者和转化、分析销售漏斗、显示热图并播放访问记录。

但该平台最重要的是报告。所有上述功能都是相互关联的。对于企业客户来说，如果没有一个以分析形式呈现的强大平台，大量信息将毫无用处。

使用该平台，您可以对大型数据集进行随机查询。这是一个简单的例子：

显示使用 Chrome 或（位于欧洲并使用 iPhone）的用户在“abc.com”页面上从 <日期 d1> 到 <日期 d2> 的所有点击

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

请求缓慢

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

显示访问 URL 包含“/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 ;

以下是时间安排：

计划时间：1.480 ms 执行时间：1431924.650 ms

该查询爬取了 150 万行。查询规划器显示了一些有趣的细节，但没有明显的瓶颈。

让我们进一步研究该请求。正如你所看到的，他确实 JOIN 三个表：

招生面试：显示会话信息：浏览器、用户代理、国家/地区等。
录音数据：记录的URL、页面、访问时长
网址：为了避免重复非常大的 URL，我们将它们存储在单独的表中。

另请注意，我们所有的表都已按以下方式分区 account_id。这样就排除了一个特别大的账户给其他账户带来问题的情况。

寻找线索

经过仔细检查，我们发现特定请求有问题。值得仔细看看这一行：

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

第一个想法是，也许是因为 ILIKE 在所有这些长 URL 上（我们有超过 1,4 万个独特为此帐户收集的 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事实证明速度相当快。

缩小嫌疑范围

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

使用 EXISTS 作为子查询 URL：我们想再次检查 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-ami 和子查询本身单独运行很快，但一起运行却非常慢？

将子查询移至 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 的交集。这有点令人困惑，因为这里的“urls”并不是指包含所有 URL 的表，而是指表中的“urls”列 recording_data.

随着越来越多的怀疑 &&，我试图在生成的查询计划中找到它们的确认 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-s 很快，子查询也很快，唯一剩下的就是 && 操作员。

这只是一个关键操作。我们总是需要搜索整个底层 URL 表来搜索模式，并且总是需要找到交集。我们不能直接通过URL记录来搜索，因为这些只是指代的ID urls.

在寻求解决方案的路上

&& 慢是因为两组都很大。如果更换的话操作会比较快 urls 上 { "http://google.com/", "http://wingify.com/" }.

我开始寻找一种在 Postgres 中设置交集而不使用的方法 &&，但没有太大的成功。

最后，我们决定单独解决问题：给我一切 urls 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。如果你稍微眯起眼睛，你可以看到这个操作遍历数组的元素（或表的行），并在满足条件（匹配）时停止。没有提醒你什么吗？是的， 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%'
    );

以及最终交货时间 Time: 1898.717 ms 是时候庆祝一下了？！？

没那么快！首先您需要检查正确性。我非常怀疑 EXISTS 优化，因为它改变了更早完成的逻辑。我们需要确保没有向请求添加不明显的错误。

一个简单的测试是运行 count(*) 对大量不同数据集的慢速和快速查询。然后，对于一小部分数据，我手动验证所有结果是否正确。

所有测试均给出一致的阳性结果。我们修好了一切！

得到教训

从这个故事中我们可以得到很多教训：

查询计划并不能说明全部情况，但它们可以提供线索
主要嫌疑人并不总是真正的罪魁祸首
可以分解慢查询以隔离瓶颈
并非所有优化本质上都是还原性的
使用 EXIST在可能的情况下，可以显着提高生产率

结论

我们的查询时间从约 24 分钟缩短到 2 秒——性能显着提升！虽然这篇文章写得很大，但我们所做的所有实验都是在一天内完成的，估计优化和测试需要 1,5 到 2 个小时。

如果您不害怕 SQL，而是尝试学习和使用它，那么 SQL 是一门很棒的语言。通过充分了解 SQL 查询的执行方式、数据库如何生成查询计划、索引如何工作以及正在处理的数据大小，您可以非常成功地优化查询。但同样重要的是，继续尝试不同的方法，慢慢分解问题，找到瓶颈。

实现这样的结果的最好部分是显着、可见的速度改进 - 以前甚至无法加载的报告现在几乎可以立即加载。

特别感谢 我的战友们 在阿迪亚·米什拉的指挥下, 阿迪亚·高鲁 и 瓦伦·马尔霍特拉 用于头脑风暴和 丁卡·潘迪尔 在我们最终告别它之前，在我们的最终请求中发现了一个重要错误！

来源： habr.com

一项 SQL 调查的故事

史前

请求缓慢

初步调查

寻找线索

缩小嫌疑范围

寻找罪魁祸首

在寻求解决方案的路上

得到教训

结论

添加评论取消回复

一项 SQL 调查的故事

史前

请求缓慢

初步调查

寻找线索

缩小嫌疑范围

寻找罪魁祸首

在寻求解决方案的路上

得到教训

结论

添加评论 取消回复

添加评论取消回复