您现在的位置： IT专家网 > 数据库子站 > 数据库技巧

SQL优化:理解SQL的意图

作者: xzh2000, 　出处:Blog,　责任编辑: 李书琴,　

2007-11-06 00:00

　　进行SQL调优时，理解SQL的意图很重要，本文指引DBA该着手进行SQL的优化……

　　如果你的系统中，通过statspack report或awr report，发现这样的SQL执行比较频繁，而且test表的记录数比较多，test表中附合status = '1'的记录数也非常多，占test表记录数90%的比例吧，从statspack report或awr report可以发现该SQL的逻辑读会相当大，假定该表没有任何索引，那DBA该如何着手进行SQL的优化呢?

　　SELECT seller_id
　　FROM (SELECT * FROM (SELECT seller_id, max(gmt_create) FROM test WHERE status = 1 GROUP BY seller_id)
　　ORDER BY gmt_create DESC)
　　WHERE rownum <= 10;

　　方案A

　　假如test表需要存储空间4g，在test表的(status,gmt_create,sellerid)字段上创建组合索引需要存储空间1g，那在test表(status,gmt_create,sellerid)上创建索引，确实可以有效地提高SQL的性能，因为从全表扫描转换成快速索引全扫描，需要扫描的索引块数虽然远远少于全表扫描时的数据块数，但实际上需要计算的记录数并没有减少，逻辑读还是相当大。

　　SQL> explain plan for SELECT seller_id
　　2 FROM (SELECT *
　　3 FROM (SELECT seller_id, max(gmt_create) AS gmt_create
　　4 FROM test
　　5 WHERE status = 1
　　6 GROUP BY seller_id)
　　7 ORDER BY gmt_create DESC)
　　8 WHERE rownum <= 10;
　　SQL> select * from table(dbms_xplan.display);
　　PLAN_TABLE_OUTPUT
　　-----------------------------------------------------------------------
　　| Id |Operation | Name | Rows |Bytes |Cost |
　　-----------------------------------------------------------------------
　　| 0 |SELECT STATEMENT | | 10 | 180 | 7916 |
　　|* 1 | COUNT STOPKEY | | | | |
　　| 2 | VIEW | | 14194 | 249K| 7916 |
　　|* 3 | SORT ORDER BY STOPKEY | | 14194 | 609K| 7916 |
　　| 4 | SORT GROUP BY | | 14194 | 609K| 7916 |
　　|* 5 | INDEX FAST FULL SCAN|IDX_TEST_STATUS| 849K| 35M| 1066 |
　　-----------------------------------------------------------------------

　　方案B

　　那我们该如何进一步进行优化呢?现在需要分析一下该SQL的意图，只有明白了SQL的意图，才可以找到更有效的优化方法：该SQL最内层的意图很明显，就是把记录按seller_id进行分组，并按gmt_create进行逆向排序;外层意图就更简单啦，取创建时间最新的10条记录。即然是取最新的10条记录，那我们就可以把该SQL演化一下：

　　SQL> explain plan for SELECT seller_id
　　2 FROM (SELECT *
　　3 FROM (SELECT seller_id, max(gmt_create) AS gmt_create
　　4 FROM test
　　5 WHERE status = 1
　　6 AND gmt_create >= sysdate - 1/24
　　7 GROUP BY seller_id)
　　8 ORDER BY gmt_create DESC)
　　9 WHERE rownum <= 10;
　　SQL> select * from table(dbms_xplan.display);
　　---------------------------------------------------------------------
　　| Id |Operation | Name |Rows |Bytes |Cost |
　　---------------------------------------------------------------------
　　| 0 |SELECT STATEMENT | | 10 | 180 | 495 |
　　|* 1 | COUNT STOPKEY | | | | |
　　| 2 | VIEW | |13523 | 237K| 495 |
　　|* 3 | SORT ORDER BY STOPKEY| |13523 | 581K| 495 |
　　| 4 | SORT GROUP BY | |13523 | 581K| 495 |
　　|* 5 | INDEX RANGE SCAN |IDX_TEST_STATUS|42482 | 1825K| 78 |
　　---------------------------------------------------------------------

　　说明：即然是取最新的10记录，其实是没有必要把所有的数据都group by一次的，所以我们可以取最近一小时的数据，做group by就可以满足需求，如果增量数据很大，也可以考虑取走分钟(5/1440)的增量数据等，通过查询条件gmt_create >= sysdate - 1/24，把许多不需要进行group by的记录都排除了。

　　也就是说，进行SQL调优时，理解SQL的意图很重要。。。