写点什么

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

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

关注

评论

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

动手实践丨使用华为云IoT边缘体验“边云协同”

华为云开发者联盟

云计算 华为云 企业号十月 PK 榜

React性能优化的8种方式

xiaofeng

React

玩转云端| 无惧秒杀,天翼云数据库让您双十一稳稳购

天翼云开发者社区

融云 CDN 播放器 2.0 版本正式上线

融云 RongCloud

React组件复用的发展史

夏天的味道123

React

Oracle、MySQL等数据库故障处理优质文章分享 | 10月文章汇总

墨天轮

MySQL 数据库 oracle 性能优化 故障恢复

React核心工作原理

xiaofeng

React

筑牢国产芯片软件生态,天翼云bcache解决方案来了!

天翼云开发者社区

React组件复用的技巧

夏天的味道123

React

浅谈:数字资产永续合约交易所开发有什么好处?

W13902449729

合约交易所开发 区块链交易所开发

深入浅出分布式,阿里大牛手写《分布式核心原理》Github一夜爆火

Java永远的神

分布式 程序人生 分布式计算 分布式系统 分布式存储

CIO们开始将软件供应链升级为安全优先级top

SEAL安全

DevOps 开源软件 软件供应链 SBOM 软件供应链安全

元宇宙场景技术实践|实现“虚拟人”自由

ZEGO即构

Q3手机银行运营报告:直销银行江湖再起波澜,数字员工助力手机银行活跃度提升

易观分析

金融 手机银行

美团前端常考手写面试题(边面边更)

helloworld1024fd

JavaScript

一本书,带你走出Spring新手村

博文视点Broadview

【web 开发基础】通过模拟地铁售票系统介绍PHP 自定义函数之函数的参数-PHP 快速入门 (26)

迷彩

记录函数参数和返回值 参数列表 PHP基础 11月月更 函数参数

用了1年的录屏软件被我含泪甩了,因为我发现了它

淋雨

想会用synchronized锁,先掌握底层核心原理

华为云开发者联盟

开发 华为云 企业号十月 PK 榜

走进 Orca 架构及技术世界

KaiwuDB

数据库·

React的5种高级模式

夏天的味道123

React

热备与冷备的三大区别讲解-行云管家

行云管家

热备 冷备 双机热备

共筑使能千行百业的数字底座 | HDC 2022松湖对话顺利召开

OpenHarmony开发者

OpenHarmony

重磅!涛思数据发布TDengine PI连接器

TDengine

数据库 tdengine 时序数据库

一种基于Prompt的通用信息抽取(UIE)框架

阿里技术

深度学习 信息抽取

python小知识-并发编程(3)

AIWeker

Python 人工智能 python小知识 11月月更

哪些企业需要上云?上哪家好?

行云管家

云计算 云服务 企业上云

阿里云Imagine Computing创新技术大赛正式开启!

阿里云CloudImagine

阿里云 技术大赛

React组件设计模式-纯组件,函数组件,高阶组件

xiaofeng

React

音视频开发进阶|第六讲:色彩和色彩空间·下篇

ZEGO即构

音视频开发 色彩

淄博教育局5G交互式教学项目获“绽放杯”一等奖 天翼云提供技术底座

天翼云开发者社区

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