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
通过这段时间的学习实践，对软件开发有了更多新的认识，不在局限于之前的片面性。当然，现在所学到的东西其实并不多，离当一个真正的程序员，还有很大的差距。 使用zendstdio 写代码的的时候，把tab 的缩进设置成4个空格是很有必要的 ,熟悉html,能用div+css，还有javascript，优先考虑linux。我在开始学习的时候，就想把这些知识一起学习，我天真的认为同时学习能够互相呼应，因为知识是相通的。 兴趣是最好的老师，百度是最好的词典。 建议加几个专业的phper的群，当然啦需要说话的人多，一处一点问题能有人回答你的，当然啦要让人回答你的问题，平时就得躲在里面聊天，大家混熟啦，愿意回答你问题的人自然就多啦。 学好程序语言，多些才是王道，写两个小时代码的作用绝对超过看一天书，这个我是深有体会（顺便还能练打字速度）。 学好程序语言，多些才是王道，写两个小时代码的作用绝对超过看一天书，这个我是深有体会（顺便还能练打字速度）。 建数据库表的时候，int型要输入长度的，其实是个摆设的输入几位都没影响的，只要大于4就行，囧。 我学习了一段时间后，我发现效果并不好（估计是我自身的问题）。因为一个人的精力总是有限的，同时学习这么多，会导致每个的学习时间都得不到保证。 爱上php，他也会爱上你。 装在C盘下面可以利用windows的ghost功能可以还原回来（顺便当做是重转啦），当然啦我的编译目录要放在别的盘下，不然自己的劳动成果就悲剧啦。 当然这种网站的会员费就几十块钱。 要进行开发，搭建环境是首先需要做的事，windows下面我习惯把环境那个安装在C盘下面，因为我配的环境经常出现诡异事件，什么事都没做环境有的时候就不能用啦。 装在C盘下面可以利用windows的ghost功能可以还原回来（顺便当做是重转啦），当然啦我的编译目录要放在别的盘下，不然自己的劳动成果就悲剧啦。 先学习php和mysql,还有css(html语言很简单)我认为现在的效果比以前的方法好。 我还是推荐用firefox ，配上firebug 插件调试js能省下不受时间。谷歌的浏览器最好也不少用，因为谷歌的大侠们实在是太天才啦，把一些原来的js代码加了一些特效。 当然这种网站的会员费就几十块钱。 至于模板嘛，各位高人一直以来就是争论不休，我一只小菜鸟就不加入战团啦，咱们新手还是多学点东西的好。 其实没啥难的，多练习，练习写程序，真正的实践比看100遍都有用。不过要熟悉引擎
页:
[1]