數(shù)據(jù)庫(kù)遷移中的Web翻頁(yè)優(yōu)化實(shí)例
2024-07-21 02:37:58
供稿：網(wǎng)友
最近忙著把公司的數(shù)據(jù)庫(kù)從MySQL遷移到Oracle,期間作了很多工作來(lái)優(yōu)化oracle平臺(tái)的性能，不過(guò)這里面最大的性能調(diào)整還是來(lái)自sql。下面舉一個(gè)web翻頁(yè)sql調(diào)整的例子。
環(huán)境：
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:兩個(gè)超線程的Intel(R) Xeon(TM) CPU 2.40GHz
優(yōu)化前語(yǔ)句在mysql里面查詢15秒左右出來(lái)，轉(zhuǎn)移到oracle后進(jìn)行在不調(diào)整索引和語(yǔ)句的情況下執(zhí)行時(shí)間大概是4-5秒，調(diào)整后執(zhí)行時(shí)間小于0.5秒。
翻頁(yè)語(yǔ)句：
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（產(chǎn)品表）
表結(jié)構(gòu)：
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
表記錄數(shù)及大小
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
表和索引都已經(jīng)分析過(guò)，我們來(lái)看一下sql執(zhí)行的費(fèi)用
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語(yǔ)句通過(guò)索引范圍掃描找到最里面的結(jié)果集，然后通過(guò)兩個(gè)view操作最后得出數(shù)據(jù)。
其中18502 consistent gets，17901 physical reads
我們來(lái)看一下這個(gè)索引建的到底合不合理，先看下各個(gè)查尋列的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
頁(yè)索引里列平均存儲(chǔ)長(zhǎng)度
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
我們來(lái)估算一下各種組合索引的大小，可以看到closed,approve_status，category都是相對(duì)較低集勢(shì)的列（重復(fù)值較多），下面我們來(lái)大概計(jì)算下各種頁(yè)索引需要的空間
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
