數據庫遷移中的Web翻頁優化實例

　　最近忙著把公司的數據庫從mysql遷移到oracle期間作了很多工作來優化oracle平台的性能不過這裡面最大的性能調整還是來自sql下面舉一個web翻頁sql調整的例子
　　
　　環境
　　Linux version custom (root@web) (gcc version (Red Hat Linux )) # SMP Thu Jun :: CST
　　Mem:　
　　Swap:
　　CPU:兩個超線程的Intel(R) Xeon(TM) CPU GHz
　　
　　優化前語句在mysql裡面查詢秒左右出來轉移到oracle後進行在不調整索引和語句的情況下執行時間大概是秒調整後執行時間小於秒
　　
　　翻頁語句
　　SELECT * FROM　(SELECT T* rownum as linenum FROM　(
　　SELECT /*+ index(a ind_old)*/
　　acategory FROM auction_auctions a WHERE acategory = AND aclosed= AND ends > sysdate AND (aapprove_status>=)　ORDER BY aends) T　WHERE rownum < ) WHERE linenum >=
　　
　　被查詢的表auction_auctions（產品表）
　　表結構
　　
　　Code: [Copy to clipboard]
　　
　　SQL> desc auction_auctions;
　　Name　　　　　　　　　　　　　　　　　　　Null?　　Type
　　
　　ID　　　　　　　　　　　　　　　　　　　　NOT NULL VARCHAR()
　　USERNAME　　　　　　　　　　　　　　　　　　　　　 VARCHAR()
　　TITLE　　　　　　　　　　　　　　　　　　　　　　　CLOB
　　GMT_MODIFIED　　　　　　　　　　　　　　　NOT NULL DATE
　　STARTS　　　　　　　　　　　　　　　　　　NOT NULL DATE
　　DESCRIPTION　　　　　　　　　　　　　　　　　　　　CLOB
　　PICT_URL　　　　　　　　　　　　　　　　　　　　　 CLOB
　　CATEGORY　　　　　　　　　　　　　　　　　NOT NULL VARCHAR()
　　MINIMUM_BID　　　　　　　　　　　　　　　　　　　　NUMBER
　　RESERVE_PRICE　　　　　　　　　　　　　　　　　　　NUMBER
　　BUY_NOW　　　　　　　　　　　　　　　　　　　　　　NUMBER
　　AUCTION_TYPE　　　　　　　　　　　　　　　　　　　 CHAR()
　　DURATION　　　　　　　　　　　　　　　　　　　　　 VARCHAR()
　　INCREMENTNUM　　　　　　　　　　　　　　　NOT NULL NUMBER
　　CITY　　　　　　　　　　　　　　　　　　　　　　　 VARCHAR()
　　PROV　　　　　　　　　　　　　　　　　　　　　　　 VARCHAR()
　　LOCATION　　　　　　　　　　　　　　　　　　　　　 VARCHAR()
　　LOCATION_ZIP　　　　　　　　　　　　　　　　　　　 VARCHAR()
　　SHIPPING　　　　　　　　　　　　　　　　　　　　　 CHAR()
　　PAYMENT　　　　　　　　　　　　　　　　　　　　　　CLOB
　　INTERNATIONAL　　　　　　　　　　　　　　　　　　　CHAR()
　　ENDS　　　　　　　　　　　　　　　　　　　NOT NULL DATE
　　CURRENT_BID　　　　　　　　　　　　　　　　　　　　NUMBER
　　CLOSED　　　　　　　　　　　　　　　　　　　　　　 CHAR()
　　PHOTO_UPLOADED　　　　　　　　　　　　　　　　　　 CHAR()
　　QUANTITY　　　　　　　　　　　　　　　　　　　　　 NUMBER()
　　STORY　　　　　　　　　　　　　　　　　　　　　　　CLOB
　　HAVE_INVOICE　　　　　　　　　　　　　　　NOT NULL NUMBER()
　　HAVE_GUARANTEE　　　　　　　　　　　　　　NOT NULL NUMBER()
　　STUFF_STATUS　　　　　　　　　　　　　　　NOT NULL NUMBER()
　　APPROVE_STATUS　　　　　　　　　　　　　　NOT NULL NUMBER()
　　OLD_STARTS　　　　　　　　　　　　　　　　NOT NULL DATE
　　ZOO　　　　　　　　　　　　　　　　　　　　　　　　VARCHAR()
　　PROMOTED_STATUS　　　　　　　　　　　　　 NOT NULL NUMBER()
　　REPOST_TYPE　　　　　　　　　　　　　　　　　　　　CHAR()
　　REPOST_TIMES　　　　　　　　　　　　　　　NOT NULL NUMBER()
　　SECURE_TRADE_AGREE　　　　　　　　　　　　NOT NULL NUMBER()
　　SECURE_TRADE_TRANSACTION_FEE　　　　　　　　　　　 VARCHAR()
　　SECURE_TRADE_ORDINARY_POST_FEE　　　　　　　　　　 NUMBER
　　SECURE_TRADE_FAST_POST_FEE　　　　　　　　　　　　 NUMBER
　　
　　表記錄數及大小
　　SQL> select count(*) from auction_auctions;
　　
　　COUNT(*)
　　
　　
　　
　　SQL> select segment_namebytesblocks from user_segments where segment_name =AUCTION_AUCTIONS;
　　
　　SEGMENT_NAME　　　　　BYTES　　 BLOCKS
　　AUCTION_AUCTIONS　　　　　
　　
　　表上原有的索引
　　create index ind_old on auction_auctions(closedapprove_statuscategoryends) tablespace tbsindex compress ;
　　
　　SQL> select segment_namebytesblocks from user_segments where segment_name = IND_OLD;
　　
　　SEGMENT_NAME　　　　　 BYTES　　 BLOCKS
　　IND_OLD　　　　　　　　　 　　　
　　
　　表和索引都已經分析過我們來看一下sql執行的費用
　　SQL> set autotrace trace;
　　SQL> SELECT * FROM　(SELECT T* rownum as linenum FROM　(SELECT a* FROM auction_auctions a WHERE acategory like % AND aclosed= AND ends　> sysdate AND (aapprove_status>=)　ORDER BY aends) T　WHERE rownum <) WHERE linenum >= ;
　　 rows selected
　　
　　Execution Plan
　　
　　　　　SELECT STATEMENT Optimizer=CHOOSE (Cost= Card= Byt
　　es=)
　　
　　　　　 VIEW (Cost= Card= Bytes=)
　　　　　　 COUNT (STOPKEY)
　　　　　　　 VIEW (Cost= Card= Bytes=)
　　　　　　　　 TABLE ACCESS (BY INDEX ROWID) OF AUCTION_AUCTIONS
　　(Cost= Card= Bytes=)
　　
　　　　　　　　　 INDEX (RANGE SCAN) OF IND_OLD (NONUNIQUE) (Cost
　　= Card=)
　　
　　Statistics
　　
　　　recursive calls
　　　db block gets
　　　consistent gets
　　　physical reads
　　　redo size
　　　bytes sent via SQL*Net to client
　　　bytes received via SQL*Net from client
　　　SQL*Net roundtrips to/from client
　　　sorts (memory)
　　　sorts (disk)
　　　rows processed
　　
　　我們可以看到這條sql語句通過索引范圍掃描找到最裡面的結果集然後通過兩個view操作最後得出數據其中　consistent gets　physical reads
　　
　　我們來看一下這個索引建的到底合不合理先看下各個查尋列的distinct值
　　select count(distinct ends) from auction_auctions;
　　
　　COUNT(DISTINCTENDS)
　　
　　
　　
　　SQL> select count(distinct category) from auction_auctions;
　　
　　COUNT(DISTINCTCATEGORY)
　　
　　
　　
　　SQL> select count(distinct closed) from auction_auctions;
　　
　　COUNT(DISTINCTCLOSED)
　　
　　
　　SQL> select count(distinct approve_status) from auction_auctions;
　　
　　COUNT(DISTINCTAPPROVE_STATUS)
　　
　　
　　
　　頁索引裡列平均存儲長度
　　SQL> select avg(vsize(ends)) from auction_auctions;
　　
　　AVG(VSIZE(ENDS))
　　
　　
　　
　　SQL> select avg(vsize(closed)) from auction_auctions;
　　
　　AVG(VSIZE(CLOSED))
　　
　　
　　
　　SQL> select avg(vsize(category)) from auction_auctions;
　　
　　AVG(VSIZE(CATEGORY))
　　
　　
　　
　　SQL> select avg(vsize(approve_status)) from auction_auctions;
　　
　　AVG(VSIZE(APPROVE_STATUS))
　　
　　
　　
　　我們來估算一下各種組合索引的大小可以看到closedapprove_statuscategory都是相對較低集勢的列（重復值較多）下面我們來大概計算下各種頁索引需要的空間
　　
　　column　　　　　　　 distinct num　　　　column len
　　ends　　　　　　　　　　　　　　　　
　　category　　　　　　　 　　　　　　　　
　　closed　　　　　　　　 　　　　　　　　　
　　approve_status　　　　　　　　　　　　　　
　　
　　index: (endsclosedcategoryapprove_status) compress
　　en
From:http://tw.wingwit.com/Article/program/Oracle/201311/18323.html