分手快乐 发表于 2015-2-4 00:30:07

PHP教程之(Oralce) Web翻页优化实例

兴趣可能会慢慢消亡,所以适当培养兴趣会激发自己无线的乐趣,有了乐趣,编程有啥难的。web|翻页|优化   Web翻页优化实例

作者:Wanghai





情况:

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(产物表)

表布局:

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

ends:distinct number---338965

closed: distinct number---2

index size=338965*2*(9+2)+ 537351*(1.7+5.5+6)=14603998



index2: (closed,category,ends,approve_status)

closed: distinct number---2

category: distinct number---1148

index size=2*1148*(2+5.5)+537351*(7+1.7+6)=7916279



index3: (closed,approve_status,category,ends)

closed: distinct number---2

approve_status: distinct numberD5

index size=2*5*(2+1.7)+537351*(7+5.5+6)=9941030



了局出来了,index2: (closed,category,ends,approve_status)的索引最小



咱们再来看一下语句

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;

可以看出这个sql语句有很大优化余地,起首最外面的了局集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,这里的话会走index range scan,然后table scan by rowid,如许的话假如合适前提的数据多的话相当耗资本,咱们可以改写成

SELECT a.rowid 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

如许的话最外面的了局集只需求index fast full scan就能够完成了,再改写一下得出以下语句



select * from auction_auctions where rowid in (SELECT rid FROM (

SELECT T1.rowid rid, rownum as linenum FROM

(SELECT a.rowid 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)



上面咱们来测试一下这个索引的查询开支



select * from auction_auctions where rowid in (SELECT rid FROM (

SELECT T1.rowid rid, rownum as linenum FROM

(SELECT a.rowid FROM auction_auctions a WHERE a.category like '18%' AND a.closed='0' AND ends > sysdate AND

(a.approve_status>=0) ORDER BY a.closed,a.ends) T1 WHERE rownum < 18681) WHERE linenum >= 18641)

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=18698 Card=18344 Byt

es=21224008)



1 0 NESTED LOOPS (Cost=18698 Card=18344 Bytes=21224008)

2 1 VIEW (Cost=264 Card=18344 Bytes=366880)

3 2 SORT (UNIQUE)

4 3 COUNT (STOPKEY)

5 4 VIEW (Cost=264 Card=18344 Bytes=128408)

6 5 SORT (ORDER BY STOPKEY) (Cost=264 Card=18344 Byt

es=440256)



7 6 INDEX (FAST FULL SCAN) OF 'IDX_AUCTION_BROWSE'

(NON-UNIQUE) (Cost=159 Card=18344 Bytes=440256)



8 1 TABLE ACCESS (BY USER ROWID) OF 'AUCTION_AUCTIONS' (Cost

=1 Card=1 Bytes=1137)



Statistics

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

0 recursive calls

0 db block gets

2080 consistent gets

1516 physical reads

0 redo size

114840 bytes sent via SQL*Net to client

56779 bytes received via SQL*Net from client

438 SQL*Net roundtrips to/from client

2 sorts (memory)

0 sorts (disk)

40 rows processed



可以看到consistent gets从19437降到2080,physical reads从18262降到1516,查询工夫也丛4秒摆布下落到0。5秒,可以来讲此次sql调剂获得了预期的后果。



又修正了一下语句,



SQL> select * from auction_auctions where rowid in

2 (SELECT rid FROM (

3 SELECT T1.rowid rid, rownum as linenum FROM

4 (SELECT a.rowid FROM auction_auctions a

5 WHERE a.category like '18%' AND a.closed='0' AND ends > sysdate AND

a.approve_status>=0

6 7 ORDER BY a.closed,a.category,a.ends) T1

8 WHERE rownum < 18600) WHERE linenum >= 18560) ;



40 rows selected.



Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=17912 Card=17604 Byt

es=20367828)



1 0 NESTED LOOPS (Cost=17912 Card=17604 Bytes=20367828)

2 1 VIEW (Cost=221 Card=17604 Bytes=352080)

3 2 SORT (UNIQUE)

4 3 COUNT (STOPKEY)

5 4 VIEW (Cost=221 Card=17604 Bytes=123228)

6 5 INDEX (RANGE SCAN) OF 'IDX_AUCTION_BROWSE' (NON-

UNIQUE) (Cost=221 Card=17604 Bytes=422496)



7 1 TABLE ACCESS (BY USER ROWID) OF 'AUCTION_AUCTIONS' (Cost

=1 Card=1 Bytes=1137)



Statistics

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

0 recursive calls

0 db block gets

550 consistent gets

14 physical reads

0 redo size

117106 bytes sent via SQL*Net to client

56497 bytes received via SQL*Net from client

436 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

40 rows processed



在order by里加上索引前导列,消弭了

6 5 SORT (ORDER BY STOPKEY) (Cost=264 Card=18344 Byt

es=440256)

,把consistent gets从2080降到550






通过这段时间的学习实践,对软件开发有了更多新的认识,不在局限于之前的片面性。当然,现在所学到的东西其实并不多,离当一个真正的程序员,还有很大的差距。

小妖女 发表于 2015-2-4 13:14:55

使用zendstdio 写代码的的时候,把tab 的缩进设置成4个空格是很有必要的

灵魂腐蚀 发表于 2015-2-7 01:57:49

,熟悉html,能用div+css,还有javascript,优先考虑linux。我在开始学习的时候,就想把这些知识一起学习,我天真的认为同时学习能够互相呼应,因为知识是相通的。

仓酷云 发表于 2015-2-10 09:59:47

兴趣是最好的老师,百度是最好的词典。

飘灵儿 发表于 2015-3-1 07:54:34

建议加几个专业的phper的群,当然啦需要说话的人多,一处一点问题能有人回答你的,当然啦要让人回答你的问题,平时就得躲在里面聊天,大家混熟啦,愿意回答你问题的人自然就多啦。

爱飞 发表于 2015-3-4 14:46:57

学好程序语言,多些才是王道,写两个小时代码的作用绝对超过看一天书,这个我是深有体会(顺便还能练打字速度)。

简单生活 发表于 2015-3-19 11:02:18

学好程序语言,多些才是王道,写两个小时代码的作用绝对超过看一天书,这个我是深有体会(顺便还能练打字速度)。

小魔女 发表于 2015-3-23 09:27:51

建数据库表的时候,int型要输入长度的,其实是个摆设的输入几位都没影响的,只要大于4就行,囧。

乐观 发表于 2015-4-6 19:45:14

我学习了一段时间后,我发现效果并不好(估计是我自身的问题)。因为一个人的精力总是有限的,同时学习这么多,会导致每个的学习时间都得不到保证。

海妖 发表于 2015-4-6 23:45:45

爱上php,他也会爱上你。

柔情似水 发表于 2015-4-10 23:39:38

装在C盘下面可以利用windows的ghost功能可以还原回来(顺便当做是重转啦),当然啦我的编译目录要放在别的盘下,不然自己的劳动成果就悲剧啦。

分手快乐 发表于 2015-4-24 00:55:28

当然这种网站的会员费就几十块钱。

愤怒的大鸟 发表于 2015-4-26 14:11:57

要进行开发,搭建环境是首先需要做的事,windows下面我习惯把环境那个安装在C盘下面,因为我配的环境经常出现诡异事件,什么事都没做环境有的时候就不能用啦。

莫相离 发表于 2015-5-4 21:23:21

装在C盘下面可以利用windows的ghost功能可以还原回来(顺便当做是重转啦),当然啦我的编译目录要放在别的盘下,不然自己的劳动成果就悲剧啦。

兰色精灵 发表于 2015-6-21 20:58:42

先学习php和mysql,还有css(html语言很简单)我认为现在的效果比以前的方法好。

再见西城 发表于 2015-7-9 03:38:17

我还是推荐用firefox ,配上firebug 插件调试js能省下不受时间。谷歌的浏览器最好也不少用,因为谷歌的大侠们实在是太天才啦,把一些原来的js代码加了一些特效。

admin 发表于 2015-9-29 00:17:18

当然这种网站的会员费就几十块钱。

深爱那片海 发表于 2015-10-1 22:41:04

至于模板嘛,各位高人一直以来就是争论不休,我一只小菜鸟就不加入战团啦,咱们新手还是多学点东西的好。

第二个灵魂 发表于 2015-10-1 23:08:48

其实没啥难的,多练习,练习写程序,真正的实践比看100遍都有用。不过要熟悉引擎
页: [1]
查看完整版本: PHP教程之(Oralce) Web翻页优化实例