数据库迁移中的Web翻页优化实例

　　最近忙着把公司的数据库从mysql迁移到Oracle,期间作了很多工作来优化oracle平台的性能，不过这里面最大的性能调整还是来自sql。下面举一个web翻页sql调整的例子。
　　
　　环境：
　　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（产品表）
　　表结构：
　　
　　Code: [Copy to clipboard]
　　
　　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
　　en