SQL优化:理解SQL的意图

最新推荐文章于 2024-08-26 20:50:54 发布

cijue8143

最新推荐文章于 2024-08-26 20:50:54 发布

阅读量85

点赞数

如果你的系统中，通过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;

[@more@]

方案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的记录都排除了。