(Oralce) Web ページめくりの最適化の例

ウェブ|ページめくり|最適化

Web ページめくりの最適化例

作成者: Wanghai





環境:

Linux バージョン 2.4.20-8custom (root@web2) (gcc バージョン 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: 2 つのハイパースレッド Intel(R) Xeon(TM) CPU 2.40 GHz



最適化ステートメントの前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>

名前タイプ

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

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 BER

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 _入札番号

クローズド文字(2) )

PHOTO_UPLOADED CHAR(1)

数量NUMBER(11)

ストーリーCLOB

HAVE_INVOICE NOT NUMBER(1)

HAVE_GUARANTEE NOT NUMBER(1)

STUFF_STAT米国は NULL ではありません (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_TRA DE_AGREE が NULL ではありません(1)

SECURE_TRADE_TRANSACTION_FEE VARCHAR2(16)

SECURE_TRADE_ORDINARY_POST_FEE NUMBER

SECURE_TRADE_FAST_POST_FEE NUMBER



テーブルレコード番号とサイズ

SQL> ; オークション_オークションからカウント (*) を選択します



- --- ------

537351



SQL>segment_name,bytes,blocks from user_segments wheresegment_name ='AUCTION_AUCTIONS';



SEGMENT_NAME BYTES BLOCKS

AUCTION_AUCTIONS 1059 06 1760 129280



テーブルインデックスのオリジナル

auctions(closed,approve_status,category,ends) テーブルスペースにインデックス ind_old を作成します



SQL> selectsegment_name,bytes,blocks from user_segments wheresegment_name = 'IND_OLD';
SEGMENT_NAME BYTES BLOCKS

IND_OLD 20971520 2560

SQL 実行のコストを見てみましょう

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 行が選択されました

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

0 SELECT ステートメント オプティマイザー=選択 (コスト=19152 カード= 18347 バイト

es =190698718)



1 0 ビュー (コスト=19152 カード=18347 バイト=190698718)

2 1 カウント (ストップキー)

3 2 ビュー (コスト=19152)カード=18347バイト=19046020 7 )

4 3 'AUCTION_AUCTIONS' のテーブル アクセス (インデックス ROWID による)

(コスト = 19152 カード = 18347 バイト = 20860539)



5 4 'IND_OLD' のインデックス (範囲スキャン) (非一意) ) (コスト

=810 カード =186003)



統計

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

0 再帰呼び出し

0 db ブロック取得

19437 一貫性取得

18262 物理読み取り

0 REDO サイズ

SQL*Net を介してクライアントに送信された 114300 バイト

SQL*Net を介してクライアントから受信された 56356 バイト

435 SQL*Net がクライアントとの間で往復する往復数

0 ソート (メモリ)

0 ソート (ディスク)

40行が処理されました



この SQL ステートメントは、インデックス範囲スキャンを通じて最も内側の結果セットを見つけ、最後に 2 つのビュー操作を通じてデータを取得していることがわかります。そのうち、18502 件の一貫した取得と 17901 件の物理読み取り



まず、各検索列の個別の値を見てみましょう

select count(distinct ends) from Auction_auctions;
COUNT (個別)

--------

338965



SQL> オークション_オークションからカウント(個別のカテゴリ) を選択します



COUNT( DISTINCTCATEGORY)

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

1148



SQL> オークション_オークションからカウント (終了済み) を選択します



COUNT; (DISTINCTCLOSED )

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

2

SQL> オークション_オークションからカウント(個別の承認ステータス) を選択します



COUNT(DISTINCTAPPROVE_STATUS)

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

5



ページインデックス内の列の平均保存長

SQL> オークション_オークションから avg(vsize(ends)) を選択します



AVG(VSIZE(ENDS))

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

7

SQL> ; オークション_オークションから avg(vsize (クローズド)) を選択します



AVG(VSIZE(クローズド))

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

2



SQL> オークション_オークションから avg( vsize(category)) を選択



AVG(VSIZE(CATEGORY))

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

5.52313106



SQL> Auction_auctions から avg(vsize(approve_status)) を選択



AVG(VSIZE(APPROVE_STATUS))

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

1.67639401



さまざまな結合インデックスのサイズを推定してみましょう。closed、approve_status、category はすべて比較的低いセットの可能性を持つ列 (より多くの繰り返し値) であることがわかります。ページインデックスに必要なスペース



column 個別の num 列 len

ends 338965 7

category 1148 5.5

closed 2 2

approve_status 5 1.7



Index1: (終了、クローズ、カテゴリ、承認ステータス) 圧縮2

終了: 個別の番号 --- 338965

終了: 個別の番号 --- 2

インデックス サイズ= 338965*2*(9+2)+ 537351*(1.7+5.5+6)=14603998



index2: (closed,category,ends,approve_status)

closed: 個別の番号 --- 2

カテゴリ: 個別の番号 --- 1148

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



index3: (closed,approve_status,category,ends)

closed: 個別の番号---2

approve_status: 個別の番号-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 <18681) WHERE linenum >= 18641;

まず、最も内側の結果から、この SQL ステートメントには最適化の余地がたくさんあることがわかります。 set 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、ここではインデックスになりますこの場合、条件を満たすデータが大量にある場合は、

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

この場合、最も内側の結果セットにはインデックス高速フル スキャンのみが必要です。完了したら、次のステートメントを取得するように書き換えます



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)

実行計画

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

0 SELECT ステートメント オプティマイザー=選択 (コスト=18698 カード=18344 バイト

es=21224008)



1 0 ネストされたループ (コスト = 18698 カード = 18344 バイト = 21224008)

2 1 ビュー (コスト=264 カード=18344 バイト=366880)

3 2 ソート (ユニーク)

4 3 カウント (ストップキー)

5 4 ビュー (コスト=264 カード=18344 バイト=128408)

6 5ソート (ストップキーによる順序) (コスト = 264 カード = 18344 バイト

es=440256)



7 6 'IDX_AUCTION_BROWSE' のインデックス (高速フルスキャン)

(非固有) (コスト = 159 カード = 18344バイト=440256)



8 1 「AUCTION_AUCTIONS」のテーブルアクセス(ユーザーROWIDによる) (コスト

=1カード=1バイト=1137)



統計

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

0 再帰呼び出し

0 db ブロック取得

2080 一貫性取得

1516 物理読み取り

0 REDO サイズ

SQL*Net 経由でクライアントに送信された 114840 バイト

クライアントから SQL*Net 経由で受信された 56779 バイト

438クライアントとの間の SQL*Net ラウンドトリップ

2 ソート (メモリ)

0 ソート (ディスク)

40 行処理



一貫した取得が確認可能 19437 から 2080、物理読み取りが 18262 から 1516 に減少チェック時間も 4 秒ほどで 0 になります。5 秒以降、この 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 行が選択されました。



実行計画

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

0 SELECT ステートメント オプティマイザー=選択 (コスト=17912 カード=17604 バイト

es=20367828)



1 0 ネストされたループ (コスト=17912)カード=17604 バイト=20367828)

2 1 ビュー (コスト=221 カード=17604 バイト=352080)

3 2 ソート (ユニーク)

4 3 カウント (ストップキー)

5 4 ビュー (コスト=221 カード= 17604 バイト=123228)

6 5 'IDX_AUCTION_BROWSE' のインデックス (範囲スキャン) (非

一意) (コスト = 221 カード = 17604 バイト = 422496)



7 1 テーブルアクセス (ユーザー ROWID 別) )OF 'AUCTION_AUCTIONS' (コスト

=1 カード = 1 バイト = 1137)



統計

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

0 再帰呼び出し

0 db ブロック取得

550 一貫性取得

14物理読み取り数

0 やり直しサイズ

117106 SQL*Net 経由でクライアントに送信されたバイト数

56497 クライアントから SQL*Net 経由で受信されたバイト数

436 SQL*Net がクライアントとの間で往復する往復数

1 ソート (メモリ)

0ソート (ディスク)

40 行処理されました



在order by里加上インデックス前导列、消除完了

6 5 SORT (ORDER BY STOPKEY) (コスト=264 カード=18344 バイト

es=440256)

、一貫した取得結果 2080 が 550 に落ちました