分享
 
 
 

举一个web翻页sql调整的实例

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

环境:

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

我们来估算一下各种组合索引的大小

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
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- 王朝網路 版權所有