一种提升 SQL 改写效率的方法

本文分享自天翼云开发者社区《一种提升SQL改写效率的方法》,作者:唐****律

一、背景

SQL 改写是数据库产品中使用比较频繁的一个技术，在大多数产品中的调用频率也非常高，通常对性能的需求需要接近对应数据库产品的上限。例如在天翼云关系型数据库中的 Mysql 语法兼容组件，其性能测试标准需要达到接近 30 万 TPS，也意味着 SQL 改写环节的性能标准需要支持至少每秒 30 万次以上，否则会成为系统的性能瓶颈。

SQL 改写的基础是抽象语法树，而抽象语法树则是由 SQL 字符串经过词法分析和语法分析之后得到的。词法分析器和语法分析器在市面上有非常多的种类可供挑选，例如 Lexer、YACC、Antlr、Druid 等，一般数据库产品都只在其基础上进行 SQL 改写,例如基于 C 语言开发的 PG 的分布式数据库插件 Citus。对于一些 Java 语言开发的数据库产品，SQL 解析的性能则会有所下降，有的数据库会在此基础上再进行优化，例如分布式数据库 Mycat，则是在 Druid 的基础上再加入了一个 SQL 缓存，用以减轻 SQL 解析和改写的代价。但是这对于一些对于 SQL 改写需求特别大或者请求语句特别复杂的数据库产品来说还是不够的，例如在天翼云关系型数据库中的 Mysql 语法兼容组件中，SQL 改写过程中需要进行元数据收集、类型推断、通配符分析、子查询和嵌套查询处理、别名分析和修正、类型适配、隐式类型转换、系统参数计算、以及近 50 条语法兼容规则，这样一来，每次进行 SQL 改写就是一个非常大的开销，因此需要对 SQL 改写环节进行性能优化。

对于此类问题，业界也有一些的解决方案，例如分布式数据库 Mycat，它使用缓存对 SQL 改写进行性能优化，以 SQL 为 key 对抽象语法树进行缓存，减轻了部分 SQL 解析的负担，特点是缓存命中率低，性能提升有限，消耗内存大。

二、方案

本方案以提高解析能力为目标，从缓存方向出发，考虑如何提高缓存命中率，以减少不必要的性能消耗。结合应用在使用 SQL 的过程中的主体结构不轻易改变的特性，使用参数化 SQL 作为缓存 key，处理过程中预先对 SQL 进行词法分析，分解为参数化 SQL 和参数列表，并以参数化 SQL 为 key 对抽象语法树进行缓存。如果缓存未命中，则对参数化 SQL 依次进行词法分析、语法分析、改写处理，最后在改写完毕之后，再结合先前记录的参数生成目标 SQL，即完成完整的 SQL 改写过程。

这个方案减少了大部分的性能消耗，缓存命中率高，内存消耗小，大幅提升了性能，其核心逻辑是以额外的性能消耗极小的词法分析和参数化环节为代价，大幅缩短了性能消耗极高的抽象语法树改写过程。

需要注意的是，该方案的应用对改写环节提出了更高的要求，开发者需要预见参数在整个改写过程中的作用并进行正确的处理。举例来说，在分布式数据库中有一些 SQL 改写，需要依据过滤条件的值的 hash 值，来决定将哪些 SQL 分发到哪些数据节点，这个时候由于过滤条件的值已被参数化，所以 SQL 改写过程中就不能直接决定其需要分发的节点了，而是要改为在最后结合参数生成目标 SQL 的时候计算分发的节点。

三、优点

本方案提出一种提升 SQL 改写效率的方法，通过预先对 SQL 进行词法分析，分解为参数化 SQL 和参数列表，并以参数化 SQL 为 key 对抽象语法树进行缓存，然后进行抽象语法树改写，最后再结合参数列表生成目标 SQL，大幅提升了缓存命中率和 SQL 改写效率。

经过相同环境下的测试对比，可知本方案在提高 SQL 改写效率方面产生了巨大的提升，并且由于测试样本较少，缓存命中率更高的方案显然会在实际应用场景中获得更大的优势。表 1 为 3 种方案对于 SQL 改写的性能对比：从天翼云云电脑生产环境中随机摘取 100 万条数据对其进行 Mysql 语法到 PostgreSQL 语法的改写，在 Intel Core i7-6700 CPU 和 24GB 内存的测试环境下，各使用 10 个线程分别按上述 3 个方案进行测试。