数据库迁移中的Web翻页优化实例-web前端-IT落伍者

最近忙着把公司的数据库从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) TWHERE rownum < ) WHERE linenum >=

被查询的表auction_auctions（产品表）

表结构

Code: [Copy to clipboard]

SQL> desc auction_auctions;

NameNull?Type

IDNOT NULL VARCHAR()

USERNAME VARCHAR()

TITLECLOB

GMT_MODIFIEDNOT NULL DATE

STARTSNOT NULL DATE

DESCRIPTIONCLOB

PICT_URL CLOB

CATEGORYNOT NULL VARCHAR()

MINIMUM_BIDNUMBER

RESERVE_PRICENUMBER

BUY_NOWNUMBER

AUCTION_TYPE CHAR()

DURATION VARCHAR()

INCREMENTNUMNOT NULL NUMBER

CITY VARCHAR()

PROV VARCHAR()

LOCATION VARCHAR()

LOCATION_ZIP VARCHAR()

SHIPPING CHAR()

PAYMENTCLOB

INTERNATIONALCHAR()

ENDSNOT NULL DATE

CURRENT_BIDNUMBER

CLOSED CHAR()

PHOTO_UPLOADED CHAR()

QUANTITY NUMBER()

STORYCLOB

HAVE_INVOICENOT NULL NUMBER()

HAVE_GUARANTEENOT NULL NUMBER()

STUFF_STATUSNOT NULL NUMBER()

APPROVE_STATUSNOT NULL NUMBER()

OLD_STARTSNOT NULL DATE

ZOOVARCHAR()

PROMOTED_STATUS NOT NULL NUMBER()

REPOST_TYPECHAR()

REPOST_TIMESNOT NULL NUMBER()

SECURE_TRADE_AGREENOT 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_NAMEBYTES 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) TWHERE 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 getsphysical 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 numcolumn len

ends