IDC资讯中心
web翻页优化实例
作者:wanghai
环境：
linux version 2.4.20-8custom (root@web2) (gcc version 3.2.2 20030222 (red hat linux 3.2.2-5)) #3 smp thu jun 5 22:03:36 cst 2003
mem: 2113466368
swap: 4194881536
cpu:两个超线程的intel(r) xeon(tm) cpu 2.40ghz
优化前语句在mysql里面查询15秒左右出来，转移到oracle后进行在不调整索引和语句的情况下执行时间大概是4-5秒，调整后执行时间小于0.5秒。
翻页语句：
select * from (select t1.*, rownum as linenum from (
select /*+ index(a ind_old)*/
a.category from auction_auctions a where a.category = 170101 and a.closed=0 and ends > sysdate and (a.approve_status>=0) order by a.ends) t1 where rownum < 18681) where linenum >= 18641
被查询的表：auction_auctions（产品表）
表结构：
sql> desc auction_auctions;
name null? type
—————————————– ——– —————————-
id not null varchar2(32)
username varchar2(32)
title clob
gmt_modified not null date
starts not null date
description clob
pict_url clob
category not null varchar2(11)
minimum_bid number
reserve_price number
buy_now number
auction_type char(1)
duration varchar2(7)
incrementnum not null number
city varchar2(30)
prov varchar2(20)
location varchar2(40)
location_zip varchar2(6)
shipping char(1)
payment clob
international char(1)
ends not null date
current_bid number
closed char(2)
photo_uploaded char(1)
quantity number(11)
story clob
have_invoice not null number(1)
have_guarantee not null number(1)
stuff_status not null number(1)
approve_status not null number(1)
old_starts not null date
zoo varchar2(10)
promoted_status not null number(1)
repost_type char(1)
repost_times not null number(4)
secure_trade_agree not null number(1)
secure_trade_transaction_fee varchar2(16)
secure_trade_ordinary_post_fee number
secure_trade_fast_post_fee number
表记录数及大小
sql> select count(*) from auction_auctions;
count(*)
———-
537351
sql> select segment_name,bytes,blocks from user_segments where segment_name =auction_auctions;
segment_name bytes blocks
auction_auctions 1059061760 129280
表上原有的索引
create index ind_old on auction_auctions(closed,approve_status,category,ends) tablespace tbsindex compress 2;
sql> select segment_name,bytes,blocks from user_segments where segment_name = ind_old;
segment_name bytes blocks
ind_old 20971520 2560
表和索引都已经分析过，我们来看一下sql执行的费用
sql> set autotrace trace;
sql> select * from (select t1.*, rownum as linenum from (select a.* from auction_auctions a where a.category like 18% and a.closed=0 and ends > sysdate and (a.approve_status>=0) order by a.ends) t1 where rownum <18681) where linenum >= 18641;
40 rows selected.
execution plan
———————————————————-
0 select statement optimizer=choose (cost=19152 card=18347 byt
es=190698718)
1 0 view (cost=19152 card=18347 bytes=190698718)
2 1 count (stopkey)
3 2 view (cost=19152 card=18347 bytes=190460207)
4 3 table access (by index rowid) of auction_auctions
(cost=19152 card=18347 bytes=20860539)
5 4 index (range scan) of ind_old (non-unique) (cost
=810 card=186003)
statistics
———————————————————-
0 recursive calls
0 db block gets
19437 consistent gets
18262 physical reads
0 redo size
114300 bytes sent via sql*net to client
56356 bytes received via sql*net from client
435 sql*net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
40 rows processed
我们可以看到这条sql语句通过索引范围扫描找到最里面的结果集，然后通过两个view操作最后得出数据。其中18502 consistent gets，17901 physical reads
我们来看一下这个索引建的到底合不合理，先看下各个查寻列的distinct值
select count(distinct ends) from auction_auctions;
count(distinctends)
——————-
338965
sql> select count(distinct category) from auction_auctions;
count(distinctcategory)
———————–
1148
sql> select count(distinct closed) from auction_auctions;
count(distinctclosed)
———————
2
sql> select count(distinct approve_status) from auction_auctions;
count(distinctapprove_status)
—————————–
5
页索引里列平均存储长度
sql> select avg(vsize(ends)) from auction_auctions;
avg(vsize(ends))
—————-
7
sql> select avg(vsize(closed)) from auction_auctions;
avg(vsize(closed))
——————
2
sql> select avg(vsize(category)) from auction_auctions;
avg(vsize(category))
——————–
5.52313106
sql> select avg(vsize(approve_status)) from auction_auctions;
avg(vsize(approve_status))
————————–
1.67639401
我们来估算一下各种组合索引的大小，可以看到closed,approve_status，category都是相对较低集势的列（重复值较多），下面我们来大概计算下各种页索引需要的空间
column distinct num column len
ends 338965 7
category 1148 5.5
closed 2 2
approve_status 5 1.7
index1: (ends,closed,category,approve_status) compress 2
ends:distinct number—338965
closed: distinct number—2
index size=338965*2*(9+2)+ 537351*(1.7+5.5+6)=14603998
index2: (closed,category,ends,approve_status)
closed: distinct number—2
category: distinct number—1148
index size=2*1148*(2+5.5)+537351*(7+1.7+6)=7916279
index3: (closed,approve_status,category,ends)
closed: distinct number—2
approve_status: distinct number―5
index size=2*5*(2+1.7)+537351*(7+5.5+6)=9941030
结果出来了，index2: (closed,category,ends,approve_status)的索引最小
我们再来看一下语句
select * from (select t1.*, rownum as linenum from (select a.* from auction_auctions a where a.category like 18% and a.closed=0 and ends > sysdate and (a.approve_status>=0) order by a.ends) t1 where rownum <18681) where linenum >= 18641;
可以看出这个sql语句有很大优化余地，首先最里面的结果集select a.* from auction_auctions a where a.category like 18% and a.closed=0 and ends > sysdate and (a.approve_status>=0) order by a.ends，这里的话会走index range scan,然后table scan by rowid，这样的话如果符合条件的数据多的话相当耗资源，我们可以改写成
select a.rowid from auction_auctions a where a.category like 18% and a.closed=0 and ends > sysdate and (a.approve_status>=0) order by a.ends
这样的话最里面的结果集只需要index fast full scan就可以完成了，再改写一下得出以下语句
select * from auction_auctions where rowid in (select rid from (
select t1.rowid rid, rownum as linenum from
(select a.rowid from auction_auctions a where a.category like 18% and a.closed=0 and ends > sysdate and
(a.approve_status>=0) order by a.ends) t1 where rownum < 18681) where linenum >= 18641)
下面我们来测试一下这个索引的查询开销
select * from auction_auctions where rowid in (select rid from (
select t1.rowid rid, rownum as linenum from
(select a.rowid from auction_auctions a where a.category like 18% and a.closed=0 and ends > sysdate and
(a.approve_status>=0) order by a.closed,a.ends) t1 where rownum < 18681) where linenum >= 18641)
execution plan
———————————————————-
0 select statement optimizer=choose (cost=18698 card=18344 byt
es=21224008)
1 0 nested loops (cost=18698 card=18344 bytes=21224008)
2 1 view (cost=264 card=18344 bytes=366880)
3 2 sort (unique)
4 3 count (stopkey)
5 4 view (cost=264 card=18344 bytes=128408)
6 5 sort (order by stopkey) (cost=264 card=18344 byt
es=440256)
7 6 index (fast full scan) of idx_auction_browse
(non-unique) (cost=159 card=18344 bytes=440256)
8 1 table access (by user rowid) of auction_auctions (cost
=1 card=1 bytes=1137)
statistics
———————————————————-
0 recursive calls
0 db block gets
2080 consistent gets
1516 physical reads
0 redo size
114840 bytes sent via sql*net to client
56779 bytes received via sql*net from client
438 sql*net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
40 rows processed
可以看到consistent gets从19437降到2080，physical reads从18262降到1516，查询时间也丛4秒左右下降到0。5秒，可以来说这次sql调整取得了预期的效果。
又修改了一下语句，
sql> select * from auction_auctions where rowid in
2 (select rid from (
3 select t1.rowid rid, rownum as linenum from
4 (select a.rowid from auction_auctions a
5 where a.category like 18% and a.closed=0 and ends > sysdate and
a.approve_status>=0
6 7 order by a.closed,a.category,a.ends) t1
8 where rownum < 18600) where linenum >= 18560) ;
40 rows selected.
execution plan
———————————————————-
0 select statement optimizer=choose (cost=17912 card=17604 byt
es=20367828)
1 0 nested loops (cost=17912 card=17604 bytes=20367828)
2 1 view (cost=221 card=17604 bytes=352080)
3 2 sort (unique)
4 3 count (stopkey)
5 4 view (cost=221 card=17604 bytes=123228)
6 5 index (range scan) of idx_auction_browse (non-
unique) (cost=221 card=17604 bytes=422496)
7 1 table access (by user rowid) of auction_auctions (cost
=1 card=1 bytes=1137)
statistics
———————————————————-
0 recursive calls
0 db block gets
550 consistent gets
14 physical reads
0 redo size
117106 bytes sent via sql*net to client
56497 bytes received via sql*net from client
436 sql*net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
40 rows processed
在order by里加上索引前导列，消除了
6 5 sort (order by stopkey) (cost=264 card=18344 byt
es=440256)
，把consistent gets从2080降到550
