数据库迁移中的Web翻页优化实例

2/9/2008来源:Oracle教程人气:4956


  最近忙着把公司的数据库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
  en