(Oralce) Web翻頁優化實例

2024-05-04 22:53:46

字體：大中小

來源：轉載

供稿：網友

web翻頁優化實例

作者:wanghai

環境：

linux version 2.4.20-8custom ([email protected]) (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

上一篇：為面向對象而生的PHP5

下一篇：Cookies,SSL，httpclient的多線程處理，HTTP方法