Web翻页优化实例

最新推荐文章于 2021-01-26 18:35:24 发布

fanboy2001

最新推荐文章于 2021-01-26 18:35:24 发布

阅读量1.3k

点赞数

分类专栏：数据库文章标签：优化 web sql statistics null loops

本文链接：https://blog.csdn.net/fanboy2001/article/details/491974

版权

数据库专栏收录该内容

5 篇文章 0 订阅

订阅专栏

Web翻页优化实例

                  Web翻页优化实例
最近忙着把公司的数据库从mysql迁移到oracle,期间作了很多工作来优化oracle平台的性能，不过这里面最大的性能调整还是来自sql。下面举一个web翻页sql调整的例子。

环境：
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

我们来估算一下各种组合索引的大小，可以看到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 <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