测试环境: PostgreSQL
测试用例:
CREATE TABLE a (
aid serial PRIMARY KEY,
name varchar(20)
);
CREATE TABLE b (
bid serial PRIMARY KEY,
name varchar(20)
);
CREATE TABLE ab (
abid serial PRIMARY KEY,
aid serial REFERENCES a(aid),
bid serial REFERENCES b(bid)
);
CREATE VIEW v_ab
AS
SELECT a.name AS "aname",
b.name AS "bname",
ab.abid AS "abid"
FROM a, b, ab
WHERE a.aid = ab.aid
AND b.bid = ab.bid;
CREATE FUNCTION f_ab(int)
RETURNS TABLE(aname varchar, bname varchar) AS $$
SELECT a.name AS "aname",
b.name AS "bname"
FROM a, b, ab
WHERE a.aid = ab.aid
AND b.bid = ab.bid
AND ab.abid = $1
$$ LANGUAGE SQL;
INSERT INTO a VALUES (1, 'a');
INSERT INTO b VALUES (2, 'b');
INSERT INTO ab VALUES (12, 1, 2);
这两篇文章把我搞糊涂了:慎用自定义函数和通过自定义函数提高服务器性能
抱着"存在即合理"的想法,我想知道到底什么时候该使用自定义函数?
另外,当数据量较大时,下面这两条语句执行效率上有区别吗?
SELECT aname, bname FROM v_ab WHERE abid = 12;
SELECT aname, bname FROM f_ab(12);
虽然已经采纳答案了,我还是来补充两句哈:
- 视图只读不写,从功能上讲,pgsql的视图只是自定义函数的一个子集
- 视图只是一个SQL语句,其占用的空间并不会随着记录行数增多而增大
和你手工JOIN相比,视图几乎没有性能提升,只有物化视图(Materialized View,Oracle和Postgresql都有)能大大提升查询性能
自定义函数和存储过程基本可以认为是同义词,在各个数据库产品中叫法不同而已。MYSQL叫用户定义函数(UDF),SQL Server叫存储过程(Stored Procedure),PGSQL里Procedural Language是UDF和Trigger的基础
- 自定义函数/存储过程有缓存查询计划的功能,当第二次查询SELECT * FROM tbl WHERE a = ?,只是改变了a=?的数值,那么就不需要再做SQL Parsing、Query Planing,直接用上次生成的查询计划。所以理论上讲,存储过程快于直接的SQL查询
- 又,因为缓存查询计划的缘故,存储过程对SQL注射攻击天然免疫。如上面这个语句,当用户通过HTTP传递一个这样的a值:123 or 1=1,它得不到SELECT * FROM tbl WHERE a = 123 or 1=1这样的查询计划,实现不了攻击效果
- 《慎用自定义函数》一文讲得对,存储过程中一些复杂的SQL会导致查询优化器作用减弱。你在存储过程里干这么简单的联合查询的活儿,不如直接用视图,如果你的查询有较复杂的if, else逻辑,写成存储过程还是值得的。
回答自己提的第一个问题...
1. 什么时候该使用自定义函数?
* 如猫之良品所说
2. 什么时候该使用视图?
* 出于安全考虑,只希望用户查询表中的一部分数据
* 希望基于已存在的表,建立一张冗余表,以方便查询
* 由于使用视图进行select
操作和普通表一致, 所以对视图进行查询比通过函数获得返回值更具有灵活性. 有复杂的查询逻辑, 但是又不伴随其它操作时, 可优先考虑使用视图, 否则考虑使用函数(如: has_topic
).
对于2
, 考虑如下场景:
1. topic
和chat
都只能回复topic
2. 加载主页面时, 将topic
和chat
无差别的按最后回复时间排序取出
一种处理办法是: 建立一张表message
, 每当有chat
或topic
插入时, 通过触发器同时插入到message
但是这种办法浪费了大量的存储空间. 那么这种情况下, 通过视图建立一张虚拟表message_v
无疑是更好的处理办法.(参见下面代码)
CREATE SEQUENCE message_seq;
CREATE TABLE topic (
tid integer PRIMARY KEY DEFAULT nextval('message_seq'),
content varchar(40000),
reply_id integer REFERENCES topic(tid),
submit_time timestamp,
last_reply_time timestamp,
title varchar(500)
);
CREATE TABLE chat (
id integer PRIMARY KEY DEFAULT nextval('message_seq'),
content varchar(40000),
reply_id integer REFERENCES topic(tid),
submit_time timestamp
);
CREATE VIEW message_v
AS
SELECT *,
submit_time AS "last_reply_time",
NULL AS "title"
FROM chat c
UNION
SELECT *
FROM topic t;
create or replace function
has_topic()
returns bool
as $$
declare
_res bool;
begin
select true
from topic
where exists (select * from topic)
into _res;
if _res is null then
_res := false;
end if;
return _res;
end;
$$ language plpgsql;
你的文章是SQL server的,PostgreSQL我倒没用过。
在SQL server中视图仅仅是SQL的映射,它一般事不含有程序运算,仅用于查询。 自定义函数是程序块,它支持参数,并且返回类型多样化。
数据库一般提供数据存储的作用,业务逻辑运算却由其它应用程序处理,例如JAVA。如果业务逻辑比较复杂而且高度依赖数据库里的各种数据,这样JAVA与数据库之间可能需要通讯多次,才可以把流程完成。这样无疑会产生交互的时间,所以如果把复杂的运算直接让数据库中计算出来,那效率就会相当高,所以就有了自定义函数。