该文章为YeSQL的Demo，完整版请看另外一篇Blog

如何让数据库中的Python跑的更快-VLDB22-YeSQL文章阅读

简要概括

关键词：Python，DataBase UDF，JIT

来自希腊的研究学者，VLDB 2022的论文

文章4页，不长

做了Scalar , Aggregator, Table 这三块Function

对应textwindow, toregrex, file and sample

测试数据：zillow(美国房地产数据)，flights(U.S. Department of Transportation Bureau of Transportation Statistics. 2020)

Note: 不要与一款名为YeSQL的Go ORM混淆

部分内容摘抄+评论

“MonetDB natively supports vectorized Python UDFs using Numpy”

“natively supports ？”有机会可以看看🤩

“PostgreSQL also supports Python UDFs without a a JIT compiler.”

今天才知道，不过也不意外就是了😘

“Tuplex [5], consider performance improvements (e.g., JIT compiler), but lack usability features (e.g., stateful, parametric functions) that would render these approaches more practical in real-world applications.”

Tuplex是SIGMOD 21的文章与项目，用于Spark上

“SQLITE API natively supports extended-SQL functionality through C UDFs.”

一个并不意外的有用信息🤗

Simple Code Review

https://github.com/athenarc/YeSQL/

运行在Pypy2.7(Note:虽然Python2已经被废弃，但Pypy2.7截止到2024.08.22还是可是维护状态)当中，Server-Base的话使用CFFI与系统交互，Embedded直接使用API(多半也是C/C++ API)

UDF使用比PG-Python快68倍

说明指南：https://athenarc.github.io/YeSQL/

个人评价

没有给出一个很好的Reason解释为什么JIT能加速UDF😐

文章给出的解释是”JIT uses frequently executed loops (“hot loops”) as their unit of compilation. This has an excellent fit to UDFs…The YeSQL query compilation meshes well with PyPy compilation and can be viewed as a pre-optimization step”，这个解释合理但缺乏细节，我可以认为作者并不了解Pypy的JIT实现细节。

文章标题为：“Rich User-Defined Functions without the Overhead”(无需额外开销的丰富UDF（用户自定义功能)），这点我持保留意见：名字这么取没问题，但别自己给自己骗了😀

CFFI就没有额外开销么？要知道底层语言实现抽象都有开销，API调用接口就没有额外开销么？😆一个可以圆过去的小问题

文章并未提及BenchMark的实际测试时长以及测试所使用的平台，未提及到系统底层优化的情况

这篇文章改变了先前我对VLDB的认知滤镜😅很难理解这个文章的Novelty是可以上VLDB的，只能说明我文章看的还不够多

知识补充

UDF

数据库中的**用户定义函数（User-Defined Function, UDF）**是指由用户创建的函数，用于扩展数据库的内置功能。UDF能够执行复杂的计算、数据处理、格式转换等操作，并可以在SQL查询中像内置函数一样调用。

UDF 的主要类型

UDF 通常分为以下几种类型：

  1. 标量函数（Scalar Function）

    • 作用：返回单一值（标量），每次调用只返回一个结果。

    • 用法：可以在 SELECT、WHERE、HAVING 语句中使用。

    • 示例：

      CREATE FUNCTION get_full_name (@first_name NVARCHAR(50), @last_name NVARCHAR(50))
RETURNS NVARCHAR(100)
AS
BEGIN
    RETURN @first_name + ' ' + @last_name
END;

      调用：

      SELECT get_full_name('John', 'Doe');

  2. 表值函数（Table-Valued Function, TVF）

    • 作用：返回一张表（可以有多行和多列）。

    • 用法：可以在 FROM 子句中像表一样使用。

    • 示例：

      CREATE FUNCTION get_employees_by_department (@department_id INT)
RETURNS TABLE
AS
RETURN (
    SELECT EmployeeID, EmployeeName
    FROM Employees
    WHERE DepartmentID = @department_id
);

      调用：

      SELECT * FROM get_employees_by_department(2);

  3. 多语句表值函数（Multi-Statement Table-Valued Function）

    • 作用：与表值函数类似，但允许在函数中使用多个语句，适合复杂查询或逻辑。

    • 示例：

      CREATE FUNCTION get_sales_by_region (@region NVARCHAR(50))
RETURNS @SalesTable TABLE (ProductID INT, SalesAmount DECIMAL(10, 2))
AS
BEGIN
    INSERT INTO @SalesTable
    SELECT ProductID, SUM(SalesAmount)
    FROM Sales
    WHERE Region = @region
    GROUP BY ProductID;
    RETURN;
END;

      调用：

      SELECT * FROM get_sales_by_region('North');

UDF的优点

  1. 重用性：可以将常用的逻辑封装在函数中，便于复用。
  2. 提高可读性：通过自定义函数，可以使SQL语句更加简洁和清晰。
  3. 隔离业务逻辑：可以将复杂的业务逻辑封装在函数中，减少主查询中的复杂度。

UDF的限制

  1. 性能问题：UDF可能会导致性能下降，尤其是在处理大量数据时。因为每次调用UDF时，数据库需要额外执行函数内部的操作。
  2. 不能修改数据库状态：UDF（尤其是标量函数）通常不能进行数据插入、更新、删除等修改操作。
  3. 限制事务处理：UDF中的事务管理较为有限，不能像存储过程那样进行复杂的事务控制。

应用场景

  • 数据转换：可以用于转换特定格式的数据。
  • 复杂计算：当SQL查询中的某些计算逻辑较为复杂时，可以通过UDF来简化查询逻辑。
  • 数据清理：通过自定义函数进行字符串处理、日期格式转换等操作。

用户定义函数是数据库中一种灵活的扩展机制，能在提高代码可读性和复用性的同时，满足特定的业务需求。不过在使用时需要谨慎，避免对数据库性能产生负面影响。

为什么选择UDF，而不是在应用层实现相关操作？

选择在数据库中使用**用户定义函数（UDF）**而不是在应用层实现相关操作，通常取决于以下几个方面的考虑：

1. 性能和效率

  • 减少数据传输：当数据量较大时，如果将数据拉到应用层进行处理，可能会产生大量的数据传输开销。通过 UDF 可以在数据库层直接执行计算和处理，减少与应用层之间的数据传输量，提高性能。
  • 优化查询执行：数据库引擎通常有专门的优化机制，可以在执行 SQL 查询时进行查询优化。如果将复杂的计算或业务逻辑封装在 UDF 中，数据库可能会更好地优化整体查询执行计划，而不是在应用层进行分段处理。

2. 逻辑复用

  • 集中业务逻辑：将某些通用的业务逻辑（例如复杂的计算、数据清洗等）封装在数据库中，使得这些逻辑可以在多个查询中重复使用。如果这些逻辑在应用层实现，则可能需要在不同应用模块中重复编写相同的代码，这会增加代码维护的复杂性和重复劳动。
  • 减少冗余：在数据库层使用 UDF，可以确保业务逻辑的一致性，防止多个应用程序或不同开发人员实现重复的功能代码，从而避免逻辑不一致的问题。

3. 数据库特有功能

  • SQL内置功能支持：某些操作与数据库的内置功能紧密相关，例如复杂的查询、聚合操作、数据过滤等。如果这些操作可以在 SQL 查询中直接进行，使用 UDF 可以更方便地调用数据库提供的特有功能，而应用层需要额外开发，增加了复杂度。
  • 利用数据库特性：有时数据库引擎支持特定的特性（如窗口函数、地理空间处理、全文搜索等），通过 UDF 可以更加充分地利用这些特性，而应用层可能难以实现同样的功能。

4. 降低开发和维护成本

  • 减少开发工作：在数据库层实现逻辑可以减少应用层的开发工作量，尤其是当涉及到复杂的查询逻辑时。开发人员只需调用 UDF 即可完成相关操作，而不用在每次查询前都实现复杂的业务逻辑。
  • 维护方便：集中业务逻辑在数据库中实现，一旦业务规则发生变化，只需要更新数据库中的 UDF 而不是修改多个应用层代码，降低了维护难度和风险。

5. 安全性

  • 数据隐私和权限控制：数据库层的操作可以通过 UDF 实现特定的访问控制和数据处理逻辑，减少应用层直接访问敏感数据的机会。例如，某些敏感数据处理可以封装在 UDF 中，仅授予用户对 UDF 的执行权限，而不是直接授予对底层数据表的访问权限。
  • 避免SQL注入攻击：通过 UDF 可以将复杂的 SQL 逻辑封装起来，从而减少 SQL 注入的风险。由于 UDF 是预编译的，它们在执行时不需要动态构建 SQL 语句，从而更安全。

6. 事务控制

  • 在某些场景下，将逻辑封装在数据库中通过 UDF 可以与数据库的事务机制结合得更好。虽然 UDF 本身不能直接控制事务，但可以作为更大事务的一部分，确保数据的一致性和原子性。

何时选择应用层？

尽管 UDF 有很多优势，但在以下情况下，应用层实现可能是更好的选择：

  1. 业务逻辑复杂：如果逻辑非常复杂，超出了数据库的处理能力或效率较低，应用层的编程语言可能更加灵活和高效。

  2. 跨平台兼容性：如果你的应用需要支持多个不同的数据库，应用层的逻辑更加通用，因为不同的数据库管理系统对 UDF 的实现细节可能存在差异。

  3. 可扩展性和灵活性：应用层可以使用现代编程语言的高级功能，比如并行计算、多线程、分布式计算等，处理性能密集型任务时可能比数据库 UDF 更加高效。

总结

选择 UDF 还是应用层实现，主要取决于性能需求、逻辑复用、维护成本、数据库特性、数据传输效率等因素。如果在数据库内直接处理数据能够提供更好的性能或更容易复用逻辑，UDF 是不错的选择；而在需要复杂业务逻辑、跨数据库兼容或可扩展性时，应用层的实现更为合适。

参考资料

https://sebd2023.dei.unipd.it/

https://www.youtube.com/watch?v=NDPqO1o4Ba8