<转>关系数据库性能问题

2005-01-21

缩略显示

关系数据库性能问题

关键字: 企业应用       

http://buaawhl.iteye.com/category/?category=5350

关系数据库性能问题

一、任务描述

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

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

这种方案，第一次取出的数据集很大，循环步数很多，而且每一步里面都要涉及到多次数据库查询，速度很慢，处理一个月的上万条数据，都需要几个小时。类似于

代码

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 ……
}
….
}

<script>render_code();</script>

 

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

 

代码

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.. ) …
…
}

<script>render_code();</script>

 

一部分 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。

代码

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;

<script>render_code();</script>

 

其中的第8条，我恰好用得上。

代码

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子句中.

<script>render_code();</script>

 

正好用来更新 parent 纪录中关于 child的统计字段。以前我这么写，

代码

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 …

<script>render_code();</script>

 

现在我这么写，

代码

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 …

<script>render_code();</script>

 

三 临时表
优化了半天，瓶颈在于对其中一个Big View的多次使用上。只好用空间换时间，把这个多次使用的View先放到临时表里面。
业务逻辑有这样的匹配逻辑，选择最符合条件的数据，其他的数据不用。

代码

Select * from
(Select * from temp
where …
order by column_1, column_2, column_3) t
where t.rownum = 1  -- 只选择排在最前面的一条

<script>render_code();</script>

 

如果直接用上面这段SQL来过滤temp数据，比较麻烦。我干脆又在temp里面多加了一个额外字段match_level, 专门用来存放匹配级别。
创建临时表数据的时候，直接生成这个match_level。

代码

Insert into temp
select
big_view.*,
decode(column_1,  … ) || decode(column_2, …) || decode(column_3, …) as match_level
from big_view where …

<script>render_code();</script>

 

这里把多个column排序结果综合到一个字段里面，便于后面的比较。
于是，我就可以用一条相对比较简单的SQL删除掉 不是最佳匹配的纪录。

代码

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)

<script>render_code();</script>

 

处理一个月的数据，上万条记录的情况下，上面这两条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. 对象数据库 比 关系数据库 快

引用

原文
http://www.cnblogs.com/xgchang/archive/2004/12/05/26474.html
⑷性能的比较
ODBMS和RDBMS产品数据存取性能的差别已按通用测试标准验证过了。SUN公司的Riok Cattell等人著的“对象数据库评估”(1991年对象世界会议文集)已对四个ODBMS产品：Objectivity，Objectstore，Ontos，Versant以及两个RDBMS产品：Sybase和Ingress进行了测试。一般说来，对于“冷”数据存取（对磁盘数据库存取）ODBMS比RDBMS平均快5倍；对于“热”数据存取（在内存中的数据库存取）要快30倍，对于“热导航”（在内存中对某一给定的对象访问与之相联系的所有对象）ODBMS任何一个产品都比RDBMS的每一个产品性能要高出三个数量级。

 

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

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

引用

原文
http://www.fic.se/MScThesis.Frost.Beta.pdf

Multi-dimensional and relational data warehouses have problems.
expressing complex data types. Object orientation on the other hand deal with complex
data types but lacks in performance on the database technology side.
…
object-oriented database management systems (OODBMS) the poor performance on ad- hoc queries.
…
object-oriented data warehouse still face the problem of ad-hoc query performance on large datasets, a problem which multi-dimensional, relational and hybrid techniques overcomes.
…

 

 

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

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

DBPerformance.JPG
描述:
文件大小:	59 KB
看过的:	文件被下载或查看 421 次
下载