《HarmonyOS:领航者说》技术公开课来啦,大咖分享、实战解码,不容错过 了解详情
写点什么

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

  • 2019-06-02
  • 本文字数:4394 字

    阅读完需:约 14 分钟

查询时间从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 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 万行左右。查询计划显示了一些信息,但还不能看出瓶颈出在哪里。


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


  1. sessions:用于展示会话信息的表,例如 browser、user-agent、country,等等。

  2. recording_data:记录 url、页面、时间段,等等。

  3. 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=# 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 sessionsWHERE    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 子查询上。一种方法是使用 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_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 查询和子查询都很快,但放在一起就变得这么慢呢?


将子查询移到 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%'    );
复制代码


这个查询的执行时间为 1898.717 毫秒,是不是值得庆祝一下?


等等,我们还要验证一下结果是不是对的。我对 EXISTS 有所怀疑,因为它有可能会改变查询逻辑,导致过早地退出。我们要确保不会在查询中引入新的 bug。


我们对慢查询和快查询结果进行了 count(*)比较,不同数据集的查询结果都是一致的。对于一些较小的数据集,我们还手动比对了具体数据,也没有问题。

学到的教训

在这次性能排查过程中,我们学到了这些东西:


  1. 查询计划并不会告诉我们所有东西,但还是很有用的;

  2. 越是明显的疑点越不太可能是元凶;

  3. 一个慢查询可能包含多个单独的瓶颈点;

  4. 并非所有优化都是可简化的;

  5. 在可能的地方使用 EXISTS 来获得大幅性能提升。

结论

我们将一个查询的运行时间从 24 分钟降到了 2 秒钟,一个不可思议的性能提升!我们花了 1 个半到 2 个小时的时间来优化和测试这个查询。SQL 其实是一门非常神奇的语言,只要你放开心态去拥抱它。


英文原文:


https://parallelthoughts.xyz/2019/05/a-tale-of-query-optimization/



2019-06-02 08:0015713
用户头像

发布了 731 篇内容, 共 469.2 次阅读, 收获喜欢 2007 次。

关注

评论

发布
暂无评论
发现更多内容

云算力矿机挖矿系统搭建,Bzz节点挖矿APP源码

电池战争:“新石油”与中欧分野

脑极体

你公司里有不能碰的红线吗?

石云升

法律 职场经验 6月日更

Rust从0到1-函数式编程-闭包

rust 函数式编程 闭包 functional closures

vivo商城促销系统架构设计与实践-概览篇

vivo互联网技术

架构 服务器 商城项目

5分钟速读之Rust权威指南(三十一)线程通信

wzx

rust

精致女孩的护牙小心机,藏在这瓶冰泉漱口水里

Geek_50a546

带你掌握C++中三种类成员初始化方式

华为云开发者联盟

c++ 初始化 类成员初始化 声明时初始化 初始化列表

浪潮云荣获“2021中国智能运维领导厂商”奖项

从设计模式看如何提升程序员的工程素养

学无止境的阿奔

Linux 后端 设计模式 C/C++ 服务器

攻击方式学习之- 拒绝服务(DOS/DDOS/DRDOS)

网络安全学海

网络安全 信息安全 渗透测试

架构实战营 - 模块 7- 作业

请弄脏我的身体

架构实战营

区块链服务网络全球首发 将极大降低政务、金融、能源等领域区块链应用成本

CECBC

JavaScript 学习(十二)--- String对象中的模式匹配方法

空城机

JavaScript 大前端 6月日更

AQS知多少

卢卡多多

AQS 6月日更 锁分析

Linux之more命令

入门小站

Linux

服务频繁出现100毫秒的延迟,原因是什么?

BUG侦探

运维 开发

数据治理

奔向架构师

大数据 数据架构 数据治理

Redis - Cluster - 分布式存储

旺仔大菜包

redis

2021年最危险的七大攻击技术,我不信你不知道

网络安全学海

程序员 网络安全 信息安全 漏洞扫描 攻击技术

极狐GitLab14.0正式面世!

极狐GitLab

阿里巴巴6月新作:“Java辟邪剑谱”,看完必让你直呼好家伙!

Java架构师迁哥

深度学习在推荐系统中的应用(二十一)

Databri_AI

深度学习 算法 推荐系统

在线正则表达式测试,正则替换工具

入门小站

工具

手写Spring,定义标记类型Aware接口,实现感知容器对象

小傅哥

Java spring 小傅哥 aware

项目管理复杂多变,如何成为一个好的项目经理?

万事ONES

项目管理 研发管理 ONES 开发管理

常用 Git 命令总结

编程三昧

git 大前端 工具

数字化的三大本质

CECBC

带老弟做个实时排行榜

程序员鱼皮

Java c++ 大前端 后端 项目实战

纯CSS实现随机翻牌效果之steps 妙用

devpoint

CSS css3 6月日更

跳槽要不要找猎头

SwiftDD

面试

查询时间从24分钟到2秒钟:记一次神奇的SQL优化_AI&大模型_VWO_InfoQ精选文章