sql语句性能的调整

显示全部楼层 · 2009-7-20 14:59:58

目前在做的一个全库检索的系统，实现方式是这样的：在发布内容我们把所有的内容都抽取出来，插入rgs_text的一个字段file_data中，
在用户执行关键词查询的时候，我们用sql语句中like '%keyword%'的方式进行关键词的查找，将所有符合查询的条件返回到前台页面处理。
我不知道咱们itpub论坛的全论坛搜索功能是怎么做的，现在发现按照我的这种like '%keyword%'的方式查找非常耗资源，效率极低，其实现在回想当初应该用lucene来做这种全库检索的功能，当然这是后话，可以考虑在做第二版的时候用这种技术。
先说说当下的实现。项目现在还没有正式上线，但目前测试查询速度比较慢，除了寄希望于服务器的升级外，剩下的就是优化SQL了。

贴出下面的这段代码（用的是ibatis框架），

file_sendtype=#rgsType# and

这一段表示如果传进来的参数有rgsType，则在这个位置拼接上file_sendtype=#rgsType# and这句语句。

file_data like '%'||#keyWords[]#||'%'

这一段用于多关键词查询，将所有关键词做为一个数组传进来，如果数组只包含一个关键词，则在整个查询SQL的这个位置上拼接上
file_data likekeyWords[0]如果数组包含2个关键词，则在整个查询SQL的这个位置上拼接上
file_data likekeyWords[0] and file_data likekeyWords[1]大致这段SQL就表示这个意思了。

selectrgs_text.file_id,rgs_text.file_pos,file_data,file_name,file_time send_time,rgs.name send_unit
from rgs_text ,rgs_itemdata,rgs
where rgs_text.file_id = rgs_itemdata.file_uppath and
rgs.id = rgs_itemdata.id and
rgs_itemdata.file_state='0' and
rgs_itemdata.file_version=(select max(r.file_version) from rgs_itemdata r where r.file_id=rgs_itemdata.file_id) and

file_time between #beginDate# and #endDate# and

file_sendtype=#rgsType# and

file_data like '%'||#keyWords[]#||'%'

order by file_name

千问 · 2009-7-20 14:59:58

select /*+FIRST_ROWS*/ rgs_text.file_id,rgs_text.file_pos,file_data,file_name,
file_time send_time,rgs.name send_unit
from
rgs_text ,rgs_itemdata,rgs where rgs_text.file_id =
rgs_itemdata.file_uppath and
rgs.id = rgs_itemdata.id and
rgs_itemdata.file_state='0' and

rgs_itemdata.file_version=(select max(r.file_version) from rgs_itemdata r
where r.file_id=rgs_itemdata.file_id) and
file_time between :1
and :2 and
file_data
like '%'||:3||'%'
order by file_name

call count cpuelapsed diskquerycurrentrows
------- -------------- ---------- ---------- ---------- --------------------
Parse10.05 0.18
0 74
0 0
Execute10.01 0.15
0164
0 0
Fetch 39 57.7593.17 3349 188155
0 384
------- -------------- ---------- ---------- ---------- --------------------
total 41 57.8293.50 3349 188393
0 384
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 88
Rows Row Source Operation
----------------------------------------------------------
384SORT ORDER BY (cr=188155 pr=3349 pw=0 time=92619177 us)
384 FILTER(cr=188155 pr=3349 pw=0 time=277040735 us)
398FILTER(cr=187563 pr=3349 pw=0 time=220045656 us)
398 NESTED LOOPS(cr=187563 pr=3349 pw=0 time=36775390 us)
398NESTED LOOPS(cr=186369 pr=3347 pw=0 time=219988841 us)
64 TABLE ACCESS BY INDEX ROWID RGS_ITEMDATA (cr=6 pr=2 pw=0 time=48067 us)
64BITMAP CONVERSION TO ROWIDS (cr=2 pr=2 pw=0 time=46694 us)
1 BITMAP AND(cr=2 pr=2 pw=0 time=1701 us)
1
BITMAP CONVERSION FROM ROWIDS (cr=1 pr=1 pw=0 time=1260 us)
64 INDEX RANGE SCAN I_RGS_ITEMDATA_FILE_STATE (cr=1 pr=1 pw=0 time=604 us)(object id 56463)
1
BITMAP CONVERSION FROM ROWIDS (cr=1 pr=1 pw=0 time=383 us)
68 SORT ORDER BY (cr=1 pr=1 pw=0 time=690 us)
68
INDEX RANGE SCAN I_RGS_ITEMDATA_FILE_TIME (cr=1 pr=1 pw=0 time=609 us)(object id 56464)
398 TABLE ACCESS BY INDEX ROWID RGS_TEXT (cr=186363 pr=3345 pw=0 time=93710814 us)
398BITMAP CONVERSION TO ROWIDS (cr=185970 pr=2954 pw=0 time=88857786 us)
38 BITMAP AND(cr=185970 pr=2954 pw=0 time=88854692 us)
64
BITMAP CONVERSION FROM ROWIDS (cr=112 pr=49 pw=0 time=547449 us)
3940 INDEX RANGE SCAN I_1 (cr=112 pr=49 pw=0 time=406369 us)(object id 55198)
64
BITMAP CONVERSION FROM ROWIDS (cr=185858 pr=2905 pw=0 time=88304648 us)
29440 SORT ORDER BY (cr=185858 pr=2905 pw=0 time=88406662 us)
29440
INDEX RANGE SCAN I_RGS_TEXT (cr=185858 pr=2905 pw=0 time=68160436 us)(object id 55103)
398TABLE ACCESS FULL RGS (cr=1194 pr=2 pw=0 time=39316 us)
37SORT AGGREGATE (cr=592 pr=0 pw=0 time=7652 us)
38 TABLE ACCESS FULL RGS_ITEMDATA (cr=592 pr=0 pw=0 time=5817 us)
********************************************************************************
这是生成的执行计划
还有下面这段
select /*+FIRST_ROWS*/ rgs_text.file_id,rgs_text.file_pos,file_data,file_name,
file_time send_time,rgs.name send_unit
from
rgs_text ,rgs_itemdata,rgs where rgs_text.file_id =
rgs_itemdata.file_uppath and
rgs.id = rgs_itemdata.id and
rgs_itemdata.file_state='0' and

rgs_itemdata.file_version=(select max(r.file_version) from rgs_itemdata r
where r.file_id=rgs_itemdata.file_id) and
file_time between :1
and :2 and
file_data
like '%'||:3||'%'
order by file_name

call count cpuelapsed diskquerycurrentrows
------- -------------- ---------- ---------- ---------- --------------------
Parse10.01 0.18
4 30
0 0
Execute10.03 0.19 13170
0 0
Fetch 39 57.1664.00609 188185
0 384
------- -------------- ---------- ---------- ---------- --------------------
total 41 57.2264.37626 188385
0 384
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 88
Rows Row Source Operation
----------------------------------------------------------
384SORT ORDER BY (cr=188185 pr=609 pw=0 time=63883689 us)
384 FILTER(cr=188185 pr=609 pw=0 time=231808748 us)
398FILTER(cr=187593 pr=609 pw=0 time=178264537 us)
398 NESTED LOOPS(cr=187593 pr=609 pw=0 time=7451731 us)
398NESTED LOOPS(cr=186399 pr=609 pw=0 time=178231053 us)
64 TABLE ACCESS FULL RGS_ITEMDATA (cr=16 pr=0 pw=0 time=1075 us)
398 TABLE ACCESS BY INDEX ROWID RGS_TEXT (cr=186383 pr=609 pw=0 time=68363805 us)
398BITMAP CONVERSION TO ROWIDS (cr=185990 pr=219 pw=0 time=59502208 us)
38 BITMAP AND(cr=185990 pr=219 pw=0 time=59499426 us)
64
BITMAP CONVERSION FROM ROWIDS (cr=112 pr=23 pw=0 time=490421 us)
3940 INDEX RANGE SCAN I_1 (cr=112 pr=23 pw=0 time=125355 us)(object id 55198)
64
BITMAP CONVERSION FROM ROWIDS (cr=185878 pr=196 pw=0 time=59006374 us)
29440 SORT ORDER BY (cr=185878 pr=196 pw=0 time=59108106 us)
29440
INDEX RANGE SCAN I_RGS_TEXT (cr=185878 pr=196 pw=0 time=56134553 us)(object id 55103)
398TABLE ACCESS FULL RGS (cr=1194 pr=0 pw=0 time=20411 us)
37SORT AGGREGATE (cr=592 pr=0 pw=0 time=6888 us)
38 TABLE ACCESS FULL RGS_ITEMDATA (cr=592 pr=0 pw=0 time=5692 us)

千问 · 2009-7-20 14:59:58

这么复杂啊！

千问 · 2009-7-20 14:59:58

context index?

千问 · 2009-7-20 14:59:58

原帖由 opsc 于 2009-10-4 21:47 发表
context index?
另外lucene的中文支持和维护不是很方便，个人意见。

千问 · 2009-7-20 14:59:58

应该建立全文索引，这样like查找估计怎么也快不到哪去吧。