open-courses
Search…
Embedded Database Logic

简介

到目前为止,我们都假设所有的业务逻辑都位于应用本身,应用通过与 DBMS 通过多次通信,来达到最终业务目的,如下图所示:
这种做法有两个坏处:
  • 多个 RTT,更多延迟
  • 不同的应用无法复用查询逻辑
如果能将部分业务逻辑转移到 DBMS 中,就能够在以上两个方面得到优化。本节将介绍将业务逻辑转移到 DBMS 中的几种方法:
  • User-defined Functions
  • Stored Procedures
  • Triggers
  • Change Notifications
  • User-defined Types
  • Views
注意:将业务逻辑嵌入 DBMS 中也有坏处,比如不同版本的应用依赖于不同版本的 Stored Procedures 等,后期将增加 DBMS 的运维成本,因此这种做法也有其劣势,要具体问题具体分析。

User-Defined Functions (UDF)

UDF 允许应用开发者在 DB 自定义函数,根据返回值类型可以分为:
  • Scalar Functions:返回单个数值
  • Table Functions:返回一张数据表
UDF 函数计算的定义可以通过两种方式:
  • SQL Functions
  • External Programming Languages

SQL Functions

SQL Functions 包含一列 SQL 语句,DBMS 按顺序执行这些语句,以最后一条语句的返回值作为整个 Function 的返回值:
CREATE FUNCTION get_foo(int) RETURNS foo AS $
SELECT * FROM foo WHERE foo.id = $1;
$ LANGUAGE SQL;

External Programming Language

一些 DBMSs 支持使用非 SQL 定义 UDF:
  • SQL Standard:SQL/PSM
  • Oracle/DBS:PL/SQL
  • Postgres:PL/pgSQL
  • MySQL/Sybase:Transact-SQL
以下是 PL/pgSQL 的例子:
CREATE OR REPLACE FUNCTION sum_foo(i int) RETURN int AS $
DECLARE foo_rec RECORD;
DECLARE out INT;
BEGIN
out := 0
FOR foo_rec IN SELECT id FROM foo
WHERE id > i LOOP
out := out + foo_rec.id;
END LOOP;
RETURN out;
END;
$ LANGUAGE plpgsql;

Stored Procedures

Stored Procedure 同样允许应用开发者自定义复杂逻辑,它的主要特点是:
  • 可以有多个输入和输出值
  • 可以修改数据表及数据结构
  • 通常不在 SQL 查询中调用
通常应用程序会直接调用 Stored Procedures,如下图所示:

Stored Procedures 与 UDF 的区别

抛开具体特征,从语义出发:
  • UDF: perform a subset of a read-only computation within a query
  • Stored Procedure: perform a complete computation that is independent of a query

Database Triggers

Trigger 通常被用来连接事件与 UDF:当某个 DB 事情发生时,监听相关事件的 trigger 负责调用对应的 UDF。
通常开发者需要定义:
  • 触发的事件类型: INSERT, UPDATE, DELETE, TRUNCATE, CREATE, ALTER, DROP
  • 事件的定义域: TABLE, DATABASE, VIEW, SYSTEM
  • 触发的时机:
    • before the statement executes
    • after the statement executes
    • before each row that the statement affects
    • instead of the statement
举例如下:
CREATE TABLE foo (
id INT PRIMARY KEY,
val VARCHAR(16)
);
CREATE TABLE foo_audit (
id SERIAL PRIMARY KEY,
foo_id INT REFERENCES foo (id),
orig_val VARCHAR,
cdate TIMESTAMP
);
CREATE OR REPLACE FUNCTION log_foo_updates() RETURNS trigger AS $
BEGIN
IF NEW.val <> OLD.val THEN
INSERT INTO foo_audit (foo_id, orig_val, cdate)
VALUES(OLD.id, OLD.val, NOW());
END IF
RETURN NEW
END
$ LANGUAGE plpgsql;
CREATE TRIGGER foo_updates BEFORE UPDATE ON foo FOR EACH ROW
EXECUTE PROCEDURE log_foo_updates();

Change Notifications

change notification 与 trigger 的功能类似,但前者触发的是外部函数。

User-Defined Types (UDT)

尽管 DBMSs 支持所有基本的原始数据类型,但如果我们想存储组合数据类型,如 struct,该如何做?就已有的知识,我们能想到两种方法:
  • Attribute Splitting:即将组合数据类型单独作为一张表 (pros&cons)
  • Application Serialization:即将组合数据序列化 (pros&cons)
除此之外,DBMS 通常还提供额外的 API,方便用户自定义数据,即 UDT:
  • Oracle supports PL/SQL
  • DB2 supports creating types based on build-in types
  • MySQL/Postgres only support type definition using external languages

Views

可以将 View 理解成一张虚拟表,这张表是一个只读查询的结果集,可以被其它查询引用。通常 View 的用途包括:
  • 简化查询语句
  • 对某些用户选择性隐藏数据
以下面这张 student 表为例:
创建 cs_students View:
CREATE VIEW cs_students AS
SELECT sid, name, login
FROM student
WHERE login LIKE '%@cs';
创建 cs_gpa View:
CREATE VIEW cs_gpa AS
SELECT AVG(gpa) AS avg_gpa
FROM student
WHERE login LIKE '%@cs';

Materialized Views

View 对应的查询在 View 每次被使用时都会被执行一次,如果我们希望 View 实体化,提高查询效率,可以使用 Materialized Views,后者的数据会随着底层数据改变而被自动更新,举例如下:
CREATE MATERIALIZED VIEW cs_gpa AS
SELECT AVG(gpa) AS avg_gpa
FROM student
WHERE login LIKE '%@cs';

Conclusion

将应用逻辑放入 DBMS 中的各有利弊:
  • Pros
    • 减少 RTT,提高效率
    • 不同应用之间实现逻辑重用
  • Cons
    • 迁移性差
    • 运维成本高

参考

slides