首頁 php教程 php手册 (Oralce) Web翻页优化实例

(Oralce) Web翻页优化实例

Jun 21, 2016 am 09:15 AM
auction from select

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 = 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 = 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 number―5

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 = 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 = 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 = 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 = 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








本網站聲明
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn

熱AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智慧驅動的應用程序,用於創建逼真的裸體照片

AI Clothes Remover

AI Clothes Remover

用於從照片中去除衣服的線上人工智慧工具。

Undress AI Tool

Undress AI Tool

免費脫衣圖片

Clothoff.io

Clothoff.io

AI脫衣器

Video Face Swap

Video Face Swap

使用我們完全免費的人工智慧換臉工具,輕鬆在任何影片中換臉!

熱工具

記事本++7.3.1

記事本++7.3.1

好用且免費的程式碼編輯器

SublimeText3漢化版

SublimeText3漢化版

中文版,非常好用

禪工作室 13.0.1

禪工作室 13.0.1

強大的PHP整合開發環境

Dreamweaver CS6

Dreamweaver CS6

視覺化網頁開發工具

SublimeText3 Mac版

SublimeText3 Mac版

神級程式碼編輯軟體(SublimeText3)

jquery如何隱藏select元素 jquery如何隱藏select元素 Aug 15, 2023 pm 01:56 PM

jquery隱藏select元素的方法:1、hide()方法,在HTML頁面中引入jQuery庫,可以使用不同選擇器來隱藏select元素,ID選擇器將selectId替換為你實際使用的select元素的ID;2、 css()方法,使用ID選擇器選擇需要隱藏的select元素,使用css()方法將display屬性設為none,並將selectId替換為select元素的ID。

使用golang進行Select Channels Go並發式程式設計的非同步處理方法 使用golang進行Select Channels Go並發式程式設計的非同步處理方法 Sep 28, 2023 pm 05:27 PM

使用golang進行SelectChannelsGo並發式程式設計的非同步處理方法引言:並發式程式設計是現代軟體開發中的重要領域,它可以有效地提高應用程式的效能和回應能力。在Go語言中,使用Channels和Select語句可以簡單而有效率地實現並發程式設計。本文將介紹如何使用golang進行SelectChannelsGo並發式程式設計的非同步處理方法,並提供具體的

jQuery中如何實作select元素的改變事件綁定 jQuery中如何實作select元素的改變事件綁定 Feb 23, 2024 pm 01:12 PM

jQuery是一個受歡迎的JavaScript函式庫,可以用來簡化DOM操作、事件處理、動畫效果等。在web開發中,常常會遇到需要對select元素進行改變事件綁定的情況。本文將介紹如何使用jQuery實作對select元素改變事件的綁定,並提供具體的程式碼範例。首先,我們需要使用標籤來建立一個包含選項的下拉式選單:

AUCTION是什麼幣種? AUCTION是什麼幣種? Feb 25, 2024 am 10:00 AM

什麼是AUCTION幣種? AUCTION幣種是一種加密貨幣,也被稱為拍賣幣。它是由一家名為Auction的公司發行和管理的。 AUCTION幣種在整個加密貨幣市場上備受矚目,吸引了廣大投資者和交易者的注意。在本文中,我們將探討AUCTION幣種的基本概念、特徵和用途。基本概念AUCTION幣種是基於區塊鏈技術的數位貨幣。它採用了去中心化的方式,避免了傳統金融體系的限制和監管,為使用者提供了隱私和安全保障。 AUCTION幣種是透過智慧合約實現的,這意味著交易可以在不需要第三方介入的情況下進行。特點A

linux要用select的原因是什麼 linux要用select的原因是什麼 May 19, 2023 pm 03:07 PM

因為select可以讓開發者同時等待多個檔案緩衝區,可減少IO等待的時間,能夠提高進程的IO效率。 select()函數是IO多路復用的函數,允許程式監視多個檔案描述符,等待所監視的一個或多個檔案描述符變成「準備好」的狀態;所謂的」準備好「狀態是指:檔案描述子不再是阻塞狀態,可以用於某類IO操作了,包括可讀,可寫,發生異常三種。 select是一個電腦函數,位於頭檔#include。此函數用於監視文件描述符的變化情況-讀寫或是異常。 1.select函數介紹select函數是IO多工的函

mysql的select語法怎麼使用 mysql的select語法怎麼使用 Jun 01, 2023 pm 07:37 PM

1.SQL語句中的關鍵字對大小寫不敏感,SELECT等效於SELECT,FROM等效於from。 2.從users表中選擇所有列的,可以用符號*代替列的名稱。語法--這是註解--從FEOM指定的[表中],查詢出[所有的]資料.*表示[所有列]SELECT*FROM--透過從FROM從指定的[表中],查詢出指定列名稱(字段)的資料SELECT列名稱FROM表名稱實例--注意:多個列之間,使用英文的逗號來分隔selectusername,passwordfrom

You can't specify target table 'table_name' for update in FROM clause - 如何解決MySQL報錯:無法在FROM子句中更新目標表 You can't specify target table 'table_name' for update in FROM clause - 如何解決MySQL報錯:無法在FROM子句中更新目標表 Oct 05, 2023 am 10:25 AM

你好,以下是一篇1500字以內的文章,標題為:Youcan'tspecifytargettable'table_name'forupdateinFROMclause-如何解決MySQL報錯:無法在FROM子句中更新目標表,需要具體程式碼範例。在進行MySQL資料庫開發過程中,我們有時會遇到以下報錯資訊:Youcan'tspecify

透過golang實現Select Channels Go並發式程式設計的效能優化 透過golang實現Select Channels Go並發式程式設計的效能優化 Sep 27, 2023 pm 01:09 PM

透過golang實現SelectChannelsGo並發式程式設計的效能優化在Go語言中,使用goroutine和channel實現並發程式設計是非常常見的。而在處理多個channel的情況下,我們通常會使用select語句來進行多重化。但是,在大規模並發的情況下,使用select語句可能會導致效能下降。在本文中,我們將介紹一些透過golang實作select

See all articles