分享
 
 
 

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

王朝other·作者佚名  2008-05-31
窄屏简体版  字體: |||超大  

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

被查询的表:auction_auctions(产品表)

表结构:

Code: [Copy to clipboard]

SQL desc auction_auctions;

NameNull?Type

----------------------------------------- -------- ----------------------------

IDNOT NULL VARCHAR2(32)

USERNAME VARCHAR2(32)

TITLECLOB

GMT_MODIFIEDNOT NULL DATE

STARTSNOT NULL DATE

DESCRIPTIONCLOB

PICT_URL CLOB

CATEGORYNOT NULL VARCHAR2(11)

MINIMUM_BIDNUMBER

RESERVE_PRICENUMBER

BUY_NOWNUMBER

AUCTION_TYPE CHAR(1)

DURATION VARCHAR2(7)

INCREMENTNUMNOT NULL NUMBER

CITY VARCHAR2(30)

PROV VARCHAR2(20)

LOCATION VARCHAR2(40)

LOCATION_ZIP VARCHAR2(6)

SHIPPING CHAR(1)

PAYMENTCLOB

INTERNATIONALCHAR(1)

ENDSNOT NULL DATE

CURRENT_BIDNUMBER

CLOSED CHAR(2)

PHOTO_UPLOADED CHAR(1)

QUANTITY NUMBER(11)

STORYCLOB

HAVE_INVOICENOT NULL NUMBER(1)

HAVE_GUARANTEENOT NULL NUMBER(1)

STUFF_STATUSNOT NULL NUMBER(1)

APPROVE_STATUSNOT NULL NUMBER(1)

OLD_STARTSNOT NULL DATE

ZOOVARCHAR2(10)

PROMOTED_STATUS NOT NULL NUMBER(1)

REPOST_TYPECHAR(1)

REPOST_TIMESNOT NULL NUMBER(4)

SECURE_TRADE_AGREENOT 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_NAMEBYTES BLOCKS

AUCTION_AUCTIONS1059061760 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) T1WHERE rownum <18681) WHERE linenum = 18641;

40 rows selected.

Execution Plan

----------------------------------------------------------

0SELECT STATEMENT Optimizer=CHOOSE (Cost=19152 Card=18347 Byt

es=190698718)

10 VIEW (Cost=19152 Card=18347 Bytes=190698718)

21 COUNT (STOPKEY)

32 VIEW (Cost=19152 Card=18347 Bytes=190460207)

43 TABLE Access (BY INDEX ROWID) OF 'AUCTION_AUCTIONS'

(Cost=19152 Card=18347 Bytes=20860539)

54 INDEX (RANGE SCAN) OF 'IND_OLD' (NON-UNIQUE) (Cost

=810 Card=186003)

Statistics

----------------------------------------------------------

0recursive calls

0db block gets

19437consistent gets

18262physical reads

0redo size

114300bytes sent via SQL*Net to client

56356bytes received via SQL*Net from client

435SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

40rows processed

我们可以看到这条sql语句通过索引范围扫描找到最里面的结果集,然后通过两个view操作最后得出数据。

其中18502consistent gets,17901physical 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 numcolumn len

ends3389657

category 11485.5

closed 2 2

approve_status5 1.7

index1: (ends,closed,category,approve_status) compress 2

en

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有