SQL（及存储过程）跑得太慢怎么办？
| 阿里云国内75折 回扣 微信号：monov8 |
| 阿里云国际，腾讯云国际，低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号：monov8 飞机：@monov6 |
SQL作为目前最常用的数据处理语言广泛应用于查询、跑批等场景。当数据量较大时使用SQL以及存储过程经常会发生跑得很慢的情况这就要去优化SQL。优化SQL有一些特定的套路通常先要查看执行计划来定位SQL慢的原因然后针对性改写来优化SQL比如对于连续数值判断可以用between来替代inselect语句指明字段名称用union all替代union把exists改写成join等。当然还有一些工程上的优化手段如建立索引使用临时表/汇总表等优化的方法有很多相信各位DBA都不会陌生。
但遗憾的是仍然有相当多情况无论怎样优化都不可能跑得更快。这里做 SQL 性能优化真是让人干瞪眼 介绍了一些并做了相应的技术分析。由于其理论基础关系代数的局限SQL缺乏离散性和有序集合等特性的支持使得SQL在表达某些高性能算法时异常困难甚至完全写不出来只能采用比较笨的低性能算法眼睁睁地看着硬件资源被白白浪费。在 写着简单跑得又快的数据库语言 SPL 中有对SQL理论基础缺陷的通俗解释。也就是说SQL的慢是理论性的这种问题仅仅由数据库在工程层面优化只能局部改善确实有不少商业数据库能够自动识别某些SQL并转换成高性能算法而不能根本地解决问题情况复杂时数据库优化引擎都会“晕”掉只能按SQL的书写逻辑执行成低性能算法。理论性的缺陷当然也不能寄希望于更换数据库而得到解决只要还是用SQL即使采用分布式数据库、内存数据库也还是这种情况在消耗更大成本的资源后当然也能有一定的性能提升但和硬件本应能够达到的性能仍然有巨大的差距。
那还能怎么办
那就不能再用SQL也就不能再用关系数据库了。
那用什么
SQL描述不了这些高性能算法用JavaC++行吗
没问题从理论上讲Java、C++什么算法都能实现而且因为可以控制计算机底层的动作这类代码通常可以跑出很好的速度只要程序员能力不是太差。
不过不要高兴得太早虽然都写得出来但由于这些开发语言过于原生本身没有提供什么面向数据处理的高性能计算类库想实现这些算法就必须从头实现而这恐怕要累死。以哈希关联为例Java实现至少要写几百行代码不仅要设计合适的哈希函数还要解决可能出现的哈希冲突这一套下来的工程量可不小还有在Java中进行多线程编程也并非易事但并行计算又是提升计算性能的有效手段。类似的涉及结构化数据计算的算法还有很多这些都自己来做的复杂度可想而知。如果一个计算的实现过于复杂其开发代价已经远远超过性能优化本身那也就没有优化的意义了。
Python也面临类似的问题虽然在结构化数据计算类库方面要比Java丰富得多但并没有提供必要的高性能算法库和存储方案比如没有提供为大数据服务的游标类型及相关运算也没提供有效的并行机制。想要实施那些高性能算法也只能自己开发但Python作为解释执行语言本身运行效率不高在此基础上再开发的算法也往往达不到高性能要求。同样Scala也缺乏足够的高性能计算类库自己编写的算法同样复杂度相当高对于不熟悉这些算法的程序员来讲从头实现的代码的运行效率往往还不如努力优化后商用数据库SQL的速度。
那就只能忍受SQL的慢了吗
还可以用SPL
SPL和高性能
开源SPLStructured Process Language一个专门面向结构化数据处理的程序语言。使用SPL可以让原本SQL跑得慢的计算变快。
为什么SPL能跑得快是拥有了什么改变硬件性能的黑科技吗
那倒没有。软件改变不了硬件的计算性能SPL也一样。简单来说SPL快就是上面说的要使用更高性能的算法。SPL中提供了大量基础的高性能算法类库基于这些算法库实现的代码可以有效减少计算量而我们做计算就是组合运用这些算法每种计算都快一些那整体上就会快很多从而达到提升计算性能的目的。
SPL设计的这些高性能算法像遍历复用、有序归并、外键预关联、标签位维度、并行计算等都已经封装好。这其中有很多算法都是SPL独创的在业内也是首次出现。
基于这些封装好的算法库再写程序会就很方便拿来直接用而不需要从头自己开发不仅性能高开发也快。从这个角度来看跑得快和写着简单其实是一回事就是能高效率地编写高性能算法。反观Java、C++、Python、Scala由于缺少这些算法库想要实现高性能也就很难了。
这里有一些SPL 中高性能算法的例子及与SQL的对比用例
性能优化技巧遍历复用
性能优化技巧TopN
性能优化技巧预关联
性能优化技巧外键序号化
性能优化技巧附表
性能优化技巧单边分堆
性能优化技巧有序分组
…
SPL采用和SQL不同的观念看待同一个计算任务继而可以采用不同更低复杂度的计算方法。
在实战中SPL目前已经做过不少性能优化案例少则提速数倍多则数十倍极端情况还有提速上千倍的提速一个数量级基本上是常态。
比如在优化某保险公司车险保单跑批的案例 开源 SPL 优化保险公司跑批优从 2 小时到 17 分钟 中使用SPL将计算时间从2小时缩短到17分钟同时代码量减少了2/3。这里使用了SPL特有的遍历复用技术可以在对大数据的一次遍历过程中实现多种运算有效地减少外存访问量。这个案例涉及对一个大表进行三次关联和汇总的运算使用SQL要将大表遍历三次而使用SPL只需要遍历一次并在关联运算上也采用了不同的方法因此获得了巨大的性能提升。
还有在 开源 SPL 将银行手机账户查询的预先关联变成实时关联 的案例中使用SPL将原本只能预关联的手机账户查询变成实时关联同时服务器数量从6台降为1台。这里充分利用了SPL的有序存储机制一次性读取整个账户数据时可以有效减少硬盘时间物理存储连续再借助区分维表和事实表的外键实时关联技术使用单机就能完成实时关联查询性能提升明显硬件需求也降低了许多。
这里还整理了一些常见的业务场景可以利用SPL的算法库来实现的高性能
关于SPL高性能的原因在 快出数量级的性能是怎样炼成的 里也进行了详细分析并给出了更多实际优化案例供参考。
进一步讨论
说到这里你可能会想那是不是学会SPL语法就能把计算跑得快了
也没这么简单
关于算法
使用SPL可以获得更高性能不是因为SPL语法SPL语法虽然有些特色但并不是跑得快的根本原因。最关键的是掌握和运用高性能算法。
实现性能优化有两步第一步设计出低复杂的计算方案第二步用足够低的成本实现它。其中更关键的是第一步这需要由有一定经验和知识储备的程序员来做即掌握和运用高性能算法第二步才是用SPL来做。换句话说SPL并不负责设计解决问题的方法而只是负责让解法更容易实现出来。
SPL语法很简单比 Java 容易得多两小时就能基本上手两三周就能比较熟练了。但算法却没那么简单需要认真学习反复练习才能掌握。反过来只要掌握了算法用什么语法就是个相对次要的问题了当然用 SQL 这种太粗线条的语言还是不行。这就像给病人看病找出病理原因后能分析出什么成分的药能管用。无论直接购买成药使用封装过的 SPL还是上山采药使用 Java/C++ 硬写都可以治好病无非就是麻烦程度和支付成本不同。
因为实际中很少使用有不少应用程序员工作几年后都把大学时代学过的数据结构和算法课程内容忘了而不理解这些基础算法知识时也就没办法设计出高性能算法方案。为此SPL设置了专门的高性能专题不仅涵盖高性能算法与优化技巧还有性能优化课程与性能图书来授人以渔。
关于存储
和算法密切相关的高性能计算还有一个关键点是数据存储高性能计算离不开合理的数据存储方式。使用SPL实施高性能计算时也不能再基于数据库来做需要将数据从数据库里搬出来重新组织。
为什么呢
慢的数据计算任务可以分为计算密集型和数据密集型两大类。单纯的计算密集型任务涉及的数据量不大而只是计算量很大计算量大并不是由于数据量大造成的这样不用改变存储方式只要实施了好的计算方法也能大幅提升性能也就是说可以继续在原有的存储方式比如数据库上使用SPL来优化性能。而数据密集型任务涉及的计算量也很大但计算量大主要是由数据量大造成的这时候如果不改变存储方式数据读取时间很可能会很长即使能把计算时间优化到0整体运算时间也不能得到有效的优化。
遗憾的是我们面临的计算慢的场景绝大部分属于数据密集型计算。如果数据还存在数据库中而数据库取数接口如JDBC通常又非常慢将数据读出就要消耗很长时间IO效率很低经常远远超过后续SPL计算的时间这也就不可能达到优化效果了。而且SPL中有相当多的算法也对存储组织有要求比如单边分堆算法就要求有序的存储方式而常规关系数据库无法满足这个前提这些算法也无法实施了。
为了解决这个问题SPL提供了自有的存储机制直接采用文件系统将数据从数据库导出到特定格式的文件中不仅可以获得更高的IO存取效率以及文件系统灵活的管理能力还可以充分利用自有格式的列存、有序、压缩、并行分段等数据存储优势从而高效地发挥高性能算法效力。
使用文件存储数据还可以有效减少数据入库的时间进一步提升计算性能。有些计算场景不仅要从数据源读还要将计算结果落地存入数据库以方便后续计算使用。像ETL就是典型的读写并存的计算还有某些大数据计算或复杂计算需要将中间结果暂存后续计算还需要再使用的情况。我们知道数据库写入是非常慢的动作伴随写入的计算场景性能自然低下。这时就可以将原本需要入库的数据存储在文件中虽然这是工程方面的优势但仍可获得接近数量级的读写性能提升再利用SPL的文件计算能力直接计算从而实现高性能。
关于T+0
如果把数据都移到数据库外那么是不是就无法完成实时数据计算了毕竟数据总是在不断地产生。
没有问题。
对于全量T+0实时查询SPL提供了多源混合计算的能力以满足这类场景。冷数据量大且不再变化使用SPL的高性能文件存储这样可以获得更高地计算性能热数据量小仍然存放在原有数据源中SPL直接读取计算支持多样性数据源由于热数据量并不大直接基于生产数据源查询也不会对其造成太大影响访问时间也不会太长。冷热数据混合计算就可以获得针对全量数据的T+0实时查询。我们只要定期将变冷的数据固化到SPL的高性能存储中原数据源只需要保持少量近期新产生的热数据即可。整体架构如下
如何开始
从前面的分析可以知道完成性能优化任务必须熟悉高性能算法和存储机制但从上面的课程图书也可以看出来这些内容并不少都要融会贯通也不是很容易的事。特别是很多程序员都习惯了SQL的思维方式很难跳出这个窠臼。面对一个性能优化任务即使有了开源SPL这样的有利武器也常常有点无从下手。打个比方一个赶马车的高手想跑得更快时会习惯于寻找缰绳和鞭子而对于初次见到的汽车上的方向盘和油门却会感到一头雾水。
为此SPL团队也提供相应的咨询服务你可以把遇到的性能问题拿过来与我们一起讨论和设计优化方案必要的时候还可以进行POC。
我们通常关心这样一些必要的问题信息业务场景、面临痛点、当前计算的数据量和并发量以及响应时间如果还能提供SQL脚本、表结构和测试数据就更好了。留个邮箱方便交流spl@scudata.com。
相信我们从不失手
经历过一两个案子程序员们就会熟悉SPL的思维方式理解了方向盘和油门以后再自己做性能优化就不是问题了。
天下武功唯快不破但只有掌握了快的本质和方法才能所向无敌。你说是不是