🥇一次 SQL 调查的故事

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

史前

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

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

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

Показать все клики на странице "abc.com"
ОТ <даты d1> ДО <даты d2>
для людей, которые
использовали Chrome ИЛИ
(находились в Европе И использовали iPhone)

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

请求缓慢

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

显示访问 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 调查的故事

史前