写点什么

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

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

关注

评论

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

Easysearch 磁盘水位线注意事项

极限实验室

easysearch

【每日学点HarmonyOS Next知识】自定义弹窗背景、状态管理V2实践、底部安全高度、OCR识别结果处理、Grid分割线

轻口味

HarmonyOS HarmonyOS NEXT

【每日学点HarmonyOS Next知识】顶部状态栏、文本最大行数后缀、弹窗背景、状态栏颜色、导航

轻口味

HarmonyOS HarmonyOS NEXT

KCD 北京站丨Volcano 邀您畅聊云原生智能调度技术与应用

华为云原生团队

云计算 容器 云原生 Volcano

医疗 AI 劲旅登场,狄耐克鸿蒙智慧医院稳步前行

新消费日报

优秀团队的“双向奔赴”:新晋 Committer 的进化之路

Apache IoTDB

鸿蒙二十四节气应用

坚果

鸿蒙 HarmonyOS

【每日学点HarmonyOS Next知识】上下拉刷新、scroll嵌套web、this指向、路由方案、输入法遮挡

轻口味

HarmonyOS HarmonyOS NEXT

【每日学点HarmonyOS Next知识】拖拽调整列表顺序、tab回弹、自定义弹窗this、状态变量修饰枚举

轻口味

HarmonyOS HarmonyOS NEXT

京东商品评论API接口全攻略

tbapi

京东API 京东商品评论API 京东评论数据接口 京东评论采集

NocoBase v1.6.0 正式版发布

NocoBase

开源 低代码 集群 无代码 版本更新

精细化运营成共识,分享三点经验一个模型

奇点云

互联网 运营 数字化

AI大模型运维开发探索第四篇:智能体分阶段演进路线

阿里云大数据AI技术

开源 AI 中间件 智能运维 大模型

【每日学点HarmonyOS Next知识】swiper样式、日期选择、自定义弹窗键盘、文本组件换行、富文本适配

轻口味

HarmonyOS HarmonyOS NEXT

【每日学点HarmonyOS Next知识】span问题、组件标识属性、属性动画回调、图文混排、相对布局问题

轻口味

HarmonyOS HarmonyOS NEXT

烟草公司×中烟创新 | 同频共振,共赴数字征程

中烟创新

Blackbox.Ai体验:AI编程插件如何提升开发效率

袁袁袁袁满

大模型 ChatGPT AI插件 免费AI工具

【每日学点HarmonyOS Next知识】自定义对话框关闭、WaterFlow嵌套、状态栏颜色、滚动吸附、滚动动效

轻口味

HarmonyOS HarmonyOS NEXT

【每日学点HarmonyOS Next知识】状态变量、动画UI残留、Tab控件显示、ob前缀问题、文字背景拉伸

轻口味

HarmonyOS HarmonyOS NEXT

国产大模型崛起!ERP国产替代破局时刻到来!

用友BIP

智能体 数智化 用友BIP ERP国产替代

京东商品列表API接口全攻略

tbapi

淘宝API 京东商品列表数据采集 京东商品列表API

【每日学点HarmonyOS Next知识】防止重复点击、对话框收拾拦截、自定义键盘焦点、页面层级、自定义对话框创建

轻口味

HarmonyOS HarmonyOS NEXT

【每日学点HarmonyOS Next知识】重叠顺序、对话框位置、事件总线、PageMap显示、多表多item类型

轻口味

HarmonyOS HarmonyOS NEXT

用友BIP数智采购:以AI 驱动的SRM提高采购决策

用友BIP

智能体 数智化 用友BIP 采购

查询队列(Query Queue)快速入门

阿里云大数据AI技术

大数据 阿里云 OLAP hologres 查询队列

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