报名参加CloudWeGo黑客松,奖金直推双丰收! 了解详情
写点什么

查询时间从 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:0015592
用户头像

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

关注

评论

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

mcgs笔记 权限 按钮的操作权限设置

万里无云万里天

自动化 HMI mcgs

低代码开发:助力制造业数字化高质量发展

不在线第一只蜗牛

低代码

自控经验 自控的分层 工厂车间设备级控制

万里无云万里天

自动化 工厂运维

天池Fashion AI 比赛失败经历分享

阿里云天池

mcgs笔记 安全属性 使能控制与安全控制

万里无云万里天

自动化 HMI mcgs

mcgs笔记 报警 报警滚动条

万里无云万里天

自动化 HMI mcgs

基于LangChain手工测试用例转App自动化测试生成工具

霍格沃兹测试开发学社

基于LangChain手工测试用例转App自动化测试生成工具

测吧(北京)科技有限公司

测试

商家运营优化:基于京东API返回值的商品管理策略

技术冰糖葫芦

API Gateway API 接口 API 测试 pinduoduo API

开放原子开源生态大会OpenHarmony生态主题演讲报名开启

OpenHarmony开发者

OpenHarmony

mcgs笔记 用户 查看用户与用户组

万里无云万里天

自动化 HMI mcgs

【IoTDB 线上小课 07】多类写入接口,快速易懂的“说明书”!

Apache IoTDB

mcgs笔记 报警 各变量的报警属性功能

万里无云万里天

自动化 HMI mcgs

mcgs笔记 报警 报警浏览构件

万里无云万里天

自动化 HMI mcgs

mcgs笔记 用户 登录登出查看用户信息

万里无云万里天

自动化 HMI mcgs

支持 128TB 超大存储,GaussDB (for MySQL) 如何轻松应对海量数据挑战

华为云开发者联盟

数据库

Akamai 调研揭示:安全性成数字原生企业选择云服务首要考量,87%企业优先重视安全再议成本及可扩展性

财见

中国信通院可信人工智能基础平台(AI Infra)第八批评估工作正式启动

中国信通院AI Infra工作组

mcgs笔记 报警 报警统一配置

万里无云万里天

自动化 HMI mcgs

解决 AI 算法开发和存储难题,华为云 DTSE 助力文华云技术架构升级

华为云开发者联盟

一文搞定WeakHashMap

不在线第一只蜗牛

Java

自控经验 工厂在地理上的分区

万里无云万里天

自动化 工厂运维

mcgs笔记 报警 报警统一配置的导出与导入

万里无云万里天

自动化 HMI mcgs

科技创新推动教育普惠,华为云WeLink助力"青椒计划"举办种子教师研学活动

轶天下事

mcgs笔记 动画按钮 根据值分段显示内容

万里无云万里天

自动化 HMI mcgs

繁星·数智思享会:以流程为中心的数字化转型战役已打响

望繁信科技

数字化转型 流程管理 流程挖掘 流程资产 流程智能

数据中台的兴衰与数据飞轮的兴起

乌龟哥哥

数据中台 #数据飞轮

mcgs笔记 查看 辅助提示

万里无云万里天

自动化 HMI mcgs

自控经验 传统流程工业的特点

万里无云万里天

自动化 工厂运维

如何免费调用GPT API进行自然语言处理

幂简集成

API ChatGPT

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