buaawhl

关系数据库性能问题

一、任务描述

工作中的一个数据批量任务，涉及到4张基本表和4张业务数据表。
基本表 (Basic Table) 数据量不大，每个表最多几百条记录；业务表 (Transaction Table) 数据量较大，每个表有几十万条记录。

以前的版本使用OO（O/R ?）方式，
(1) SQL查询数据库选出一个业务表的数据，每条记录映射为一个Object。
(2) 循环每个Object，根据属性 查询数据库，取出关联的表数据，映射为Object；依此类推，一步步取出相关数据Object。然后计算并生成结果数据。

这种方案，第一次取出的数据集很大，循环步数很多，而且每一步里面都要涉及到多次数据库查询，速度很慢，处理一个月的上万条数据，都需要几个小时。类似于
[code:1]
Select * from Transact_A where ….
While(rs.next()){
a.populate(rs);

If(a…) {
Select * from Transact_B where ….
If( … ) select * from Basic_B where ….
}else{
Select * from Basic_A where ……
}
….
}
[/code:1]

后续版本，我决定采用 Join Table的方式来处理，试图用一个复杂的Big Join Query / View 一次把所有的相关数据记录全都取出来。整个处理过程中，只需要一次数据库查询。

[code:1]
Select * from (Transact_A left join Basic_B on ….) inner join Transact_B on .., C, D, E… Where … < …. > … not exists… a lot of conditions.

While(rs.next()){
a.populate(rs);
b.populate(rs);

if(a… b.. ) …
…
}
[/code:1]

一部分 Business Logic （条件比较等）移动到SQL里面，代码的可读性变差，运行速度提高。
由于业务逻辑的复杂性，一条SQL很难恰好选出需要处理的数据，为了最大限度的减少不必要的数据，有些比较大的复合View还被多次引用。
几千条数据，这条SQL工作还好，几秒钟、十几秒钟就返回结果集；几万条数据，这条SQL需要几分钟才能够返回；几十万条数据，运气好，10多分钟、几十分钟返回，运气不好，就干脆不返回了。

二 SQL Optimization
Oracle网站有Performance Tuning的PDF文档，详细介绍了语句置换，hint, index, explain plan, SQL Trace等方法。
这里有一篇比较全的翻译的SQL调优文章，
http://www.chinaunix.net/jh/19/214182.html

其中的第15条明确说明，SQL中的 = 操作符 支持 Tuple元组操作，这个 = 可以用来给一个元组Tuple赋值，也可以比较两个元组Tuple。
[code:1]
15. 减少对表的查询
在含有子查询的SQL语句中,要特别注意减少对表的查询.
例如:
Slow:
SELECT TAB_NAME
FROM TABLES
WHERE TAB_NAME = ( SELECT TAB_NAME
FROM TAB_COLUMNS
WHERE VERSION = 604)
AND　DB_VER= ( SELECT DB_VER
FROM TAB_COLUMNS
WHERE VERSION = 604)

Fast:
SELECT TAB_NAME
FROM TABLES
WHERE (TAB_NAME,DB_VER)
= ( SELECT TAB_NAME,DB_VER)
FROM TAB_COLUMNS
WHERE VERSION = 604)

Update 多个Column 例子:
Slow
UPDATE EMP
SET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES),
SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES)
WHERE EMP_DEPT = 0020;

Fast
UPDATE EMP
SET (EMP_CAT, SAL_RANGE)
= (SELECT MAX(CATEGORY) , MAX(SAL_RANGE)
FROM EMP_CATEGORIES)
WHERE EMP_DEPT = 0020;
[/code:1]

其中的第8条，我恰好用得上。
[code:1]
8. 使用DECODE函数来减少处理时间
使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.
例如:
SELECT COUNT(*)，SUM(SAL)
FROM　EMP
WHERE DEPT_NO = 0020
AND ENAME LIKE　‘SMITH%’;
SELECT COUNT(*)，SUM(SAL)
FROM　EMP
WHERE DEPT_NO = 0030
AND ENAME LIKE　‘SMITH%’;
你可以用DECODE函数高效地得到相同结果
SELECT COUNT(DECODE(DEPT_NO,0020,’X’,NULL)) D0020_COUNT,
COUNT(DECODE(DEPT_NO,0030,’X’,NULL)) D0030_COUNT,
SUM(DECODE(DEPT_NO,0020,SAL,NULL)) D0020_SAL,
SUM(DECODE(DEPT_NO,0030,SAL,NULL)) D0030_SAL
FROM EMP WHERE ENAME LIKE ‘SMITH%’;
类似的,DECODE函数也可以运用于GROUP BY 和ORDER BY子句中.
[/code:1]

正好用来更新 parent 纪录中关于 child的统计字段。以前我这么写，
[code:1]
update parent p
set
balance_1 = (select sum(amount) from child c where c.parent_id = p.id and type in (1, 2))
balance_2 = (select sum(amount) from child c where c.parent_id = p.id and type in (3, 4))
where …
[/code:1]

现在我这么写，
[code:1]
update parent p
set
(balance_1, balance_2) = (
select
sum(decode(type, 1, amount, 2, amount, null)) as balance_1,
sum(decode(type, 3, amount, 4, amount, null)) as balance_2,
from child c where c.parent_id = p.id)
where …
[/code:1]

三 临时表
优化了半天，瓶颈在于对其中一个Big View的多次使用上。只好用空间换时间，把这个多次使用的View先放到临时表里面。
业务逻辑有这样的匹配逻辑，选择最符合条件的数据，其他的数据不用。
[code:1]
Select * from
(Select * from temp
where …
order by column_1, column_2, column_3) t
where t.rownum = 1 -- 只选择排在最前面的一条
[/code:1]

如果直接用上面这段SQL来过滤temp数据，比较麻烦。我干脆又在temp里面多加了一个额外字段match_level, 专门用来存放匹配级别。
创建临时表数据的时候，直接生成这个match_level。
[code:1]
Insert into temp
select
big_view.*,
decode(column_1, … ) || decode(column_2, …) || decode(column_3, …) as match_level
from big_view where …
[/code:1]

这里把多个column排序结果综合到一个字段里面，便于后面的比较。
于是，我就可以用一条相对比较简单的SQL删除掉 不是最佳匹配的纪录。
[code:1]
delete temp
where exists(
select * from
(select id, max(match_level) from temp t group by id having count(*) > 1) t
where temp.id = t.id and temp.match_level < t.match_level)
[/code:1]

处理一个月的数据，上万条记录的情况下，上面这两条insert和 delete执行时间加起来在10 -- 20秒左右。后面的一个Big Query就可以用这个temp table直接把恰好要使用的数据取出来，10秒左右就可以返回结果集（几万条）。处理这个结果集的时间，几分钟左右。
处理24个月的几十万条数据，需要几十分钟。速度提高了几十倍。

既然为了速度，用了这么多vendor native SQL feature, Big Join, temp table, 已经没有移植性可言，为什么不干脆用PL/SQL？
我对PL/SQL语法不熟悉，只是尽量把条件判断过滤放到SQL里面，而重要的计算公式部分（还是比较复杂的）在Java里面处理。我觉得，SQL即使带一点Native feature，也应该比Stored Procedure容易移植。

四 对象数据库

关系数据库号称 更快，支持更复杂的数据类型和关系，数据间的关联查询更快。

不算那些Java, C++等Object的存储工具，只算那些称得上DB的，开源的Object Database有db4object, OZONE, GigaBase(Object Relational Database)等。
(Berkeley DB勉强算得上Object Database？)
存储空间和性能还无法和商业数据库比。不像关系数据库，有了MaxDB, Ingres, PostgreSQL等比较成熟的开源关系数据库。
商业对象数据库挺多，这里就不罗列了。我看的资料比较的多的是Objectivity/DB和Intersystems Cache’。
http://www.objectivity.com
http://www.intersystems.cn
http://www.intersystems.com

两者都号称大数据量、高并发、高性能。通过看过的文档和资料，我更看好Cache’一些。似乎Cache’ 更快，更便宜，支持关系模型更好。Cache’号称 后关系数据库 Post Relational Database。当然，Objectivity是purer Object Database，面向对象特性也许更多一些。

关于对象数据库性能，有两种相反的说法。

1. 对象数据库 比 关系数据库 快


Objectivity/DB自己的Bench Mark文章这样写，在小数据量下，RDBMS（Oracle）更快，数据量越大，Objectivity就会比RDBMS快的越多。

2. 对象数据库 比 关系数据库 慢



原文
http://www.intersystems.co.jp/forum2004/pdf/cache.pdf

从这张图可以看出，Objectivity比关系数据库慢，Cache’比关系数据库快。