专栏首页有关SQL6 图助你理解 SQL 优化策略

6 图助你理解 SQL 优化策略

写在前

抱歉,这估计又是一篇 CRUD Boy/Girl 读起来费劲的文章!

玩 SQL 1 - 2 年的朋友,对于 Execution Plan (执行计划)估计不陌生了。但也有特例,3 - 4 年的朋友有时候也不知道如何查看 Execution Plan. 这事儿我还真见的不少。

2010 年我在百胜做 ERP 的时候,有个呆了 2.5 年的 BI(Business Intelligence) 小哥问我,“Lenis, 为什么我的 SQL 跑了 10 来秒才出结果,帮我看看为什么啊”。“Execution Plan 截图发我看下”。等了十来秒,“老哥,Execution Plan 是什么, 我没找到菜单 ”。我走到他屏幕前,CTL + M 了一下,指着粗箭头跟他说,在这张表上加个索引。

有时候,Execution Plan 就是这么一针见血!

初生牛犊,可以不怕狼,不怕虎。但没有敬畏之心,估计做不好 SQL 开发。在项目初期,流量还没起来的时候,你怎么写 SQL 都是对的。可以自认为一切 SQL 都是纸老虎,没有你搞不定的数据库。但经过 2-3 个项目锻炼之后,你还是那么以为,那么有点 too young too simple 了。数据库背后暗藏的坑,你是填不完的。

在T-SQL开发中,如果你知道 rewind, rebind ,那我接受你的臭鸡蛋和板砖;如果你不知道这两者,我也接受你的鲜花和掌声,还有赞赏!

案例

在本例中,举一个表函数( table-valued function)来说明下 rewind, rebind 微妙的关系。

函数的意图很明显,根据 PK 拿到表中其他的几个字段,封装成表返回。

第一种写法:

在这里,说先要说明的是 EXT_GAP_EMP 是一张从表,EXT_GAP_INITIATIVES 是主表。通过 GAP_ID 来关联。因此 EXT_GAP_EMP 与 EXT_GAP_INITIATIVES 是多对一的关系。而表值函数(Table Valued Functions)将会重复的去捞取同一个 GAP_ID 下的多个属性,因此 产生了大量的数据。聪明的你肯定在想,如果这些重复拉取的过程可以将之前的数据都缓存起来,就能提高性能。没错,你想到的方案,微软那帮子牛上天的工程师也想到了。如果你认识轮子哥,你可以求证下,当年他在 SQL Server 做过优化器的引擎开发。我在他隔壁 :)。言归正传,我用图证明:

标红的 Actual Rewinds 就是将连续同值的 GAP_ID 返回的结果缓存起来,以快速返回给下一个等值的 GAP_ID. 而 Actual Rebinds 统计的就是有多少次连续同值的 GAP_ID 出现。一定是连续同值,连续同值,连续同值!

一定要将上面的例子好好理解,继续往下看才能保证理解的了!

第二种写法:

接下来,我不标红 SQL 的修改部分,可能大多数读者都不会注意。请仔细看 SQL 的变化。因为 Execution Plan 变得完全不一样了,有了 sort 反而变快了,是不是很难理解?因为我们一直在优化部分强调不要用 order by 来进行排序,尽量避免 sort 操作。对于 rewind, rebind 来说,情况变了

细心的你,发现了没,标红部分 Actual Rebinds 减少了至少 3 倍。意味着 Actual Rewinds 有效提高了缓存。

前面一篇对 SQL 运行时执行统计信息的文章,提到的收集执行统计信息的方法,还记得吗,SET Statistics IO/TIME ON, 可以派上用场了。我们来比较下两段 SQL 的异同:

CPU 时间少了至少 3 倍,而执行流失时间则少了近 1 倍!原因全在于 ORDER BY GEMP.GAP_ID !

结论

一个 Order By 竟然这么神奇!

刚才要求大家仔细看的那段,不知道还有谁能记得。连续等值!是理解本篇的重点

Order By 就是将离散分布的数据集重新汇聚成一个有序的集合。这样连续等值就可以充分发挥 rewind 的作用,将数据缓存起来,给下一个等值返回缓存的结果集,一旦下一个值变了,就丢弃这份缓存,重新去拉取新数据。而此时,rebind 则重新计数,有多少次遇到不等值,就有多少次 rebind. 可见离散的时候,等值很多,但不连续,造成 rewind 频繁的去抓取数据,浪费时间。

本文分享自微信公众号 - 有关SQL(SQLHub),作者:Lenis

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-01-22

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 想精通SQL Server, T-SQL的全历史你听过吗?

    早在20世纪70年代,两位年轻人Ray Boyce 和 Donald D. Chamberlin,成功将上帝语言(关系数学)编译为凡人的工具。

    Lenis
  • 移动下SQL中的表位置,性能提高18倍

    平日里2-3秒搞定的SQL,这会非得弄个7-8秒。timeout更是频频爆出。搞得办公室怨叫声此起彼伏,真有点《生命协奏曲》的味道。

    Lenis
  • 零基础 SQL 数据库小白,从入门到精通的学习路线与书单

    我观察了 865 个 SQL 入门者,发现大家在学习 SQL 的时候,最大的问题不是 SQL 语法,而是对 SQL 原理的不熟悉。

    Lenis
  • Mybatis入门到精通

    IT故事会
  • 浅谈pycharm下找不到sqlalchemy的问题

    问题始于我在pycharm下建了一个flask工程,然后导入sqlalchemy的包:

    砸漏
  • 彻底搞懂Scrapy的中间件(三)

    在前面两篇文章介绍了下载器中间件的使用,这篇文章将会介绍爬虫中间件(Spider Middleware)的使用。

    青南
  • 利用虚拟硬盘(把内存当作硬盘)来提高数据库的效率 续

    续 上一篇引起了大家的讨论,看着讨论我是比较晕的,这也怪我没有说清楚,所以再补一个续把问题说清楚吧。 笔记本配置 CPU:Core 2 7250 2.0G ...

    用户1174620
  • SQL Server索引简介:SQL Server索引进阶 Level 1

    Woodson
  • 教程 | 用数据做酷的事!手把手教你搭建问答系统

    选自TowardsDataScience 作者:Priya Dwivedi 机器之心编译 参与:Pedro、路 本文介绍了如何基于 SQuAD 数据集搭建问答系...

    机器之心
  • 时间复杂度中的log(n)底数到底是多少?

    假设有底数为2和3的两个对数函数,如上图。当X取N(数据规模)时,求所对应的时间复杂度得比值,即对数函数对应的y值,用来衡量对数底数对时间复杂度的影响。

    城市中的游牧民族

扫码关注云+社区

领取腾讯云代金券