`

转载一个 sql优化 （[精华] 一个查询优化的分析调整全过程！很值得一看 ）

sql 
阅读更多
见   http://www.itpub.net/forum.php?mod=viewthread&tid=239011




Web翻页优化实例
提交时间: 2004-6-18 15:37:49      回复    发消息 


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



分享到：
| JS常用的事件及方法
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    sql查询优化（提高MySQL数据库查询效率的几个技巧）

    在 MySQL 数据库中，查询优化是一个非常重要的方面。在实际应用中，高效的查询可以提高整个系统的性能和响应速度。下面我们将介绍几个提高 MySQL 数据库查询效率的技巧。 使用 Statement 进行绑定查询 使用 ...

    SQL.Server.2005.技术内幕.查询.调整和优化.pdf

    《SQL Server 2005 技术内幕：查询、调整和优化》是一本深入探讨Microsoft SQL Server 2005数据库管理系统核心技术的专著。这本书涵盖了SQL Server 2005在处理查询、性能调整和优化方面的核心知识点，旨在帮助数据库...

    SQL_Server_2008查询性能优化

    总的来说，SQL Server 2008查询性能优化是一个涉及多个层面的综合过程，包括查询设计、索引管理、存储过程、数据库设计以及服务器配置等多个方面。通过深入理解和实践这些技巧，开发者可以显著提升其SQL代码的运行...

    ORACLE-SQL性能优化大全.pdf

    - **及时处理过程中发生的意外和变化**：性能优化是一个动态过程，需要灵活应对各种突发情况。 - **80/20定律**：通常情况下，20%的优化工作可以带来80%的性能提升。 - **SQL优化机制**： - **SQL语句处理过程**...

    基于SQL Server的SQL优化.pdf

    SQL优化涉及到多个层面，包括查询设计、索引策略、存储过程优化、执行计划分析以及资源管理等。本篇文章将深入探讨这些方面，帮助读者理解如何针对SQL Server进行有效的SQL优化。 首先，查询设计是SQL优化的基础。...

    基于案例学SQL优化

    1. **优化总体思路**：这通常包括分析查询执行计划，识别性能瓶颈，调整索引，以及优化查询语句结构。 2. **常见误区**：比如过度依赖全表扫描，忽视索引使用，或者不恰当的数据类型选择等，这些都是在优化过程中...

    Microsoft SQL Server 2005技术内幕 查询、调整和优化

    　本书适合于专业数据库开发者、BI开发者、DBA和以SQL Server作为后台数据库的一般应用程序开发者，读者可以通过书中的最佳实践、高级技巧和代码示例来掌握查询调整和优化的技巧，以针对不同问题开发出切合实际的高...

    ORACLE_SQL性能优化(全).ppt

    综上，Oracle SQL性能优化是一个涉及广泛领域的复杂过程，要求开发人员具备深厚的数据库理论知识和实践经验。通过深入理解SQL处理过程、优化器工作原理以及执行计划分析，可以有效地提高SQL执行效率，进而提升整个...

    《基于Oracle的SQL优化》PDF版本下载.txt

    一个经过优化的SQL查询不仅能够减少资源消耗，提高系统性能，还能降低硬件成本，提升业务效率。 ### Oracle数据库的特点 Oracle是一款广泛应用于企业环境中的关系型数据库管理系统。它具有高度可扩展性、强大的...

    mysql的sql优化

    MySQL的SQL优化是数据库管理中的重要环节，尤其对于有经验的开发者来说，了解并掌握这一技能可以显著提升数据库性能...总的来说，MySQL的SQL优化是一个综合性的过程，需要结合理论知识与实践经验，才能达到理想的效果。

    SQL优化工具下载，语句优化

    语句优化是指通过调整SQL查询的编写方式，使其更有效地从数据库中检索数据。这可能包括减少查询的复杂性、利用正确的索引、避免全表扫描等策略。性能提高则是优化的目标，通过改进SQL语句，可以显著降低查询响应时间...

    ORACLESQL性能优化.pptx

    SQL 语句优化的过程包括定位有问题的语句、检查执行计划、检查执行过程中优化器的统计信息、分析相关表的记录数、索引情况、改写 SQL 语句、使用 HINT、调整索引、表分析等。只有通过这种方式，才能达到最佳执行...

    PB写的一个SQL查询分析器

    这个“PB写的一个SQL查询分析器”可能是使用PowerBuilder开发的一款专门用于分析SQL查询性能的应用。 在SQL查询分析器中，有几个关键知识点： 1. **SQL语法解析**：分析器首先会解析SQL语句，检查语法是否正确，...

    SQLServer2008查询性能优化 2/2

    5.2.1 调整一个查询 143 5.2.2 调整一个跟踪工作负载 146 5.3 数据库引擎调整顾问的局限性 148 5.4 小结 149 第6章 书签查找分析 150 6.1 书签查找的目的 150 6.2 书签查找的缺点 152 6.3 分析书签查找的...

    高性能SQL优化.ppt

    Oracle性能管理是一个系统性的、逐步的过程，它涉及到主动和被动两种管理模式。主动性能管理强调在系统设计和开发阶段就考虑高性能架构，并定期监控，预防性能问题的发生。而被动性能管理则是在问题出现后进行的评估...

    ORACLE SQL优化工具sqlhc

    首先，我们来看`sqlhc.sql`，这是SQL Health Check的主要脚本，它会执行一系列的检查，分析数据库中的SQL语句，查找可能存在的性能问题。通过运行这个脚本，你可以获得关于SQL执行计划、绑定变量、索引使用情况、...

    《Sql Server 2008查询性能优化》扫描版-part2

    最后以一个实际的工作负载将所有技巧联系起来，并且提供了“宝典”式的最佳实践列表。 本书适合于关心数据库应用系统性能的开发人员和数据库管理人员阅读。通过阅读本书，不仅可以学习到数据库性能管理的许多知识和...

Magicbox
Global site tag (gtag.js) - Google Analytics