数据库迁移中的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
