music unfamous original game design efficient software wtf
life ui algorithm fix programming

数据库优化----ORDER BY

作者:trinity  数据库    2014-8-20  标签:  design  efficient  algorithm 

背景

    最近在一个项目中出现一个问题:用户输入查询条件点击查询,出现页面白屏。

    原来没有过此类问题,经过调试,发现是慢慢数据增长,查询慢造成HTTP 超时,经在PL/SQL 中查看约30s 以上。

环境

   WIN-SERVER 2008; ORACLE11g, 数据行大约在6M,ThinkPHP3.1服务端;DWZ做的前端。因为用的 volist 输出 html ,所以当超时的时候会输出不来 html 页面,造成白屏。

解决

    那剩下的问题就是提速SQL,或者采用其他 SQL。

    经过用户输入查询条件, where 子句大约是这样的 :

    $condition = WHERE cond1=int1 AND cond2=int2 AND cond3=int3 AND cond4=int4 AND cond5='string5'
    其中一条语句:
    SELECT COUNT(*) FROM TABLE $condition
     第二条:
    SELECT * FROM (SELECT row_.*, ROWNUM rownum_ FROM (SELECT /*+index(ITAS_T_DATA, INDEX_QUERY1)*/* FROM ITAS_T_DATA WHERE".$condition.")     row_ WHERE ROWNUM <=".$endPos.") WHERE rownum_ >".$startPos." ORDER BY sjid DESC"

    ###

    经过实际观察,删去 ORDER BY 与带 ORDER BY 执行的效率差别相当大。想想也是正常的。

    其实几个查询条件中,客户经常用的条件有4个,ORDER BY 子句也是按照时间(4个条件中的1个)来排序,把这4个条件做索引,然后在在 SQL 中显式指定使用索引,同时索引按照时间进行 DESC 排序,那么在SQL 中可以删去 ORDER BY 子句,出来的结果仍然是有序的。

最终

    就是这么简单。即使这样,当时仍然让我花费了几天来调整,测试,评估。其中还有其他一些不是很关键的优化,比如用 instr 代替 LIKE ,SESSION 保存字典表等。这些不影响大局。

    值得一提的是,有人介绍使用 设置 ORACLE workarea_size_policy work_area_size 的值来扩大 ORDER BY 排序的内存空间,进而优化 ORDER BY 的效率,我相信在其他情况下是有效果的,但我测试,重启服务之后,ORDER BY 达到惊人的几十分钟。不知是什么原因。

    


stupid-student  发布于 2014-08-21 00:52
我也碰到过这种情况,但没有弄好......