SQL Server多表查询优化方案总结

SQL Server多表查询的优化方案是本文我们主要要介绍的内容，本文我们给出了优化方案和具体的优化实例，接下来就让我们一起来了解一下这部分内容。

创新互联建站专注于凤山网站建设服务及定制，我们拥有丰富的企业做网站经验。 热诚为您提供凤山营销型网站建设，凤山网站制作、凤山网页设计、凤山网站官网定制、重庆小程序开发服务，打造凤山网络公司原创品牌,更为您提供凤山网站排名全网营销落地服务。

1.执行路径

ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用:我们发现,单表数据的统计比多表统计的速度完全是两个概念.单表统计可能只要0.02秒,但是2张表联合统计就

可能要几十秒了.这是因为ORACLE只对简单的表提供高速缓冲(cache buffering) ,这个功能并不适用于多表连接查询..数据库管理员必须在init.ora中为这个区域设置合适的参数,当这个内存区域越大,就可以保留更多的语句,当然被共享的可能性也就越大了.

2.选择最有效率的表名顺序(记录少的放在后面)

ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理. 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时, 会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并.

例如:

表 TAB1 16,384 条记录

表 TAB2 1条记录

选择TAB2作为基础表 (最好的方法)

select count(*) from tab1,tab2 执行时间0.96秒

选择TAB2作为基础表 (不佳的方法)

select count(*) from tab2,tab1    执行时间26.09秒

如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.

例如:    EMP表描述了LOCATION表和CATEGORY表的交集.

 
 
 
      
  
  
  1. SELECT *  
    2.   
  
  
  2. FROM LOCATION L ,  
    3.   
  
  
  3.        CATEGORY C,  
    4.   
  
  
  4.        EMP E  
    5.   
  
  
  5. WHERE E.EMP_NO BETWEEN 1000 AND 2000  
    6.   
  
  
  6. AND E.CAT_NO = C.CAT_NO  
    7.   
  
  
  7. AND E.LOCN = L.LOCN 
    8.

将比下列SQL更有效率

 
 
 
      
  
  
  1. SELECT *  
    2.   
  
  
  2. FROM EMP E ,  
    3.   
  
  
  3. LOCATION L ,  
    4.   
  
  
  4.        CATEGORY C  
    5.   
  
  
  5. WHERE   E.CAT_NO = C.CAT_NO  
    6.   
  
  
  6. AND E.LOCN = L.LOCN  
    7.   
  
  
  7. AND E.EMP_NO BETWEEN 1000 AND 2000 
    8.

3.WHERE子句中的连接顺序(条件细的放在后面)

ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.

例如:

(低效,执行时间156.3秒)

 
 
 
      
  
  
  1. SELECT …  
    2.   
  
  
  2. FROM EMP E  
    3.   
  
  
  3. WHERE   SAL > 50000  
    4.   
  
  
  4. AND     JOB = ‘MANAGER’  
    5.   
  
  
  5. AND     25 < (SELECT COUNT(*) FROM EMP  
    6.   
  
  
  6. WHERE MGR=E.EMPNO);  
    7.   
  
  
  7. (高效,执行时间10.6秒)  
    8.   
  
  
  8. SELECT …  
    9.   
  
  
  9. FROM EMP E  
    10.   
  
  
  10. WHERE 25 < (SELECT COUNT(*) FROM EMP  
    11.   
  
  
  11.               WHERE MGR=E.EMPNO)  
    12.   
  
  
  12. AND     SAL > 50000  
    13.   
  
  
  13. AND     JOB = ‘MANAGER’; 
    14.

4.SELECT子句中避免使用'* '

当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用 '*' 是一个方便的方法.不幸的是,这是一个非常低效的方法. 实际上,ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间.

5.减少访问数据库的次数

当执行每条SQL语句时, ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等等. 由此可见, 减少访问数据库的次数 , 就能实际上减少ORACLE的工作量.

方法1 (低效)

 
 
 
      
  
  
  1. SELECT EMP_NAME , SALARY , GRADE  
    2.   
  
  
  2.      FROM EMP  
    3.   
  
  
  3.      WHERE EMP_NO = 342;  
    4.   
  
  
  4.       SELECT EMP_NAME , SALARY , GRADE  
    5.   
  
  
  5.      FROM EMP  
    6.   
  
  
  6.      WHERE EMP_NO = 291; 
    7.

方法2 (高效)

 
 
 
      
  
  
  1. SELECT A.EMP_NAME , A.SALARY , A.GRADE,  
    2.   
  
  
  2.              B.EMP_NAME , B.SALARY , B.GRADE  
    3.   
  
  
  3.      FROM EMP A,EMP B  
    4.   
  
  
  4.      WHERE A.EMP_NO = 342 
    5.   
  
  
  5.      AND    B.EMP_NO = 291; 
    6.

6.删除重复记录

最高效的删除重复记录方法 ( 因为使用了ROWID)

 
 
 
      
  
  
  1. DELETE FROM EMP E  
    2.   
  
  
  2. WHERE E.ROWID > (SELECT MIN(X.ROWID)  
    3.   
  
  
  3.                     FROM EMP X  
    4.   
  
  
  4.                     WHERE X.EMP_NO = E.EMP_NO); 
    5.

7.用TRUNCATE替代DELETE

当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况)，而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短.

8.尽量多使用COMMIT

只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少:

COMMIT所释放的资源:

a.  回滚段上用于恢复数据的信息.

b.  被程序语句获得的锁

c.  redo log buffer 中的空间

d.  ORACLE为管理上述3种资源中的内部花费(在使用COMMIT时必须要注意到事务的完整性,现实中效率和事务完整性往往是鱼和熊掌不可得兼)

9.减少对表的查询

在含有子查询的SQL语句中,要特别注意减少对表的查询.

例如：

低效：

 
 
 
      
  
  
  1. SELECT TAB_NAME  
    2.   
  
  
  2.            FROM TABLES  
    3.   
  
  
  3.            WHERE TAB_NAME = ( SELECT TAB_NAME  
    4.   
  
  
  4.                                  FROM TAB_COLUMNS  
    5.   
  
  
  5.                                  WHERE VERSION = 604)  
    6.   
  
  
  6.            AND　DB_VER= ( SELECT DB_VER  
    7.   
  
  
  7.                             FROM TAB_COLUMNS  
    8.   
  
  
  8.                             WHERE VERSION = 604 
    9.

 高效：

 
 
 
      
  
  
  1. SELECT TAB_NAME  
    2.   
  
  
  2.            FROM TABLES  
    3.   
  
  
  3.            WHERE   (TAB_NAME,DB_VER)  
    4.   
  
  
  4. = ( SELECT TAB_NAME,DB_VER)  
    5.   
  
  
  5.                     FROM TAB_COLUMNS  
    6.   
  
  
  6.                     WHERE VERSION = 604) 
    7.

Update 多个Column 例子:

低效：

 
 
 
      
  
  
  1. UPDATE EMP  
    2.   
  
  
  2.             SET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES),  
    3.   
  
  
  3.                SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES)  
    4.   
  
  
  4.             WHERE EMP_DEPT = 0020; 
    5.

高效:

 
 
 
      
  
  
  1. UPDATE EMP  
    2.   
  
  
  2.             SET (EMP_CAT, SAL_RANGE)  
    3.   
  
  
  3. = (SELECT MAX(CATEGORY) , MAX(SAL_RANGE)  
    4.   
  
  
  4. FROM EMP_CATEGORIES)  
    5.   
  
  
  5.             WHERE EMP_DEPT = 0020; 
    6.

10.用EXISTS替代IN,用NOT EXISTS替代NOT IN

在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率.

低效:

 
 
 
      
  
  
  1. SELECT *  
    2.   
  
  
  2. FROM EMP (基础表)  
    3.   
  
  
  3. WHERE EMPNO > 0  
    4.   
  
  
  4. AND DEPTNO IN (SELECT DEPTNO  
    5.   
  
  
  5. FROM DEPT  
    6.   
  
  
  6. WHERE LOC = ‘MELB’) 
    7.

高效:

 
 
 
      
  
  
  1. SELECT *  
    2.   
  
  
  2. FROM EMP (基础表)  
    3.   
  
  
  3. WHERE EMPNO > 0  
    4.   
  
  
  4. AND EXISTS (SELECT ‘X’  
    5.   
  
  
  5. FROM DEPT  
    6.   
  
  
  6. WHERE DEPT.DEPTNO = EMP.DEPTNO  
    7.   
  
  
  7. AND LOC = ‘MELB’) 
    8.

(相对来说,用NOT EXISTS替换NOT IN 将更显著地提高效率)

在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历).   为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.

例如:

 
 
 
      
  
  
  1. SELECT …  
    2.   
  
  
  2. FROM EMP  
    3.   
  
  
  3. WHERE DEPT_NO NOT IN (SELECT DEPT_NO  
    4.   
  
  
  4.                           FROM DEPT  
    5.   
  
  
  5.                           WHERE DEPT_CAT='A'); 
    6.

为了提高效率.改写为:

(方法一: 高效)

 
 
 
      
  
  
  1. SELECT ….  
    2.   
  
  
  2. FROM EMP A,DEPT B  
    3.   
  
  
  3. WHERE A.DEPT_NO = B.DEPT(+)  
    4.   
  
  
  4. AND B.DEPT_NO IS NULL  
    5.   
  
  
  5. AND B.DEPT_CAT(+) = 'A' 
    6.

(方法二: 最高效)

 
 
 
      
  
  
  1. SELECT ….  
    2.   
  
  
  2. FROM EMP E  
    3.   
  
  
  3. WHERE NOT EXISTS (SELECT 'X'  
    4.   
  
  
  4.                      FROM DEPT D  
    5.   
  
  
  5.                      WHERE D.DEPT_NO = E.DEPT_NO  
    6.   
  
  
  6.                      AND DEPT_CAT = 'A'); 
    7.

当然,最高效率的方法是有表关联.直接两表关系对联的速度是最快的!

11.识别'低效执行'的SQL语句

用下列SQL工具找出低效SQL:

 
 
 
      
  
  
  1. SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,  
    2.   
  
  
  2.          ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,  
    3.   
  
  
  3.          ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,  
    4.   
  
  
  4.          SQL_TEXT  
    5.   
  
  
  5. FROM    V$SQLAREA  
    6.   
  
  
  6. WHERE   EXECUTIONS>0  
    7.   
  
  
  7. AND      BUFFER_GETS > 0  
    8.   
  
  
  8. AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8 
    9.   
  
  
  9. ORDER BY 4 DESC; 
    10.

(虽然目前各种关于SQL优化的图形化工具层出不穷,但是写出自己的SQL工具来解决问题始终是一个最好的方法)

关于SQL Server多表查询优化方案的相关知识就介绍到这里了，希望本次的介绍能够对您有所收获！

 【编辑推荐】

  1. SQL Server数据库ISNULL函数的应用实例
  2. SQL Server数据库DATEPART的语法及使用实例
  3. SQL Server查找XML里面符合某个条件的数据的用法
  4. SQL Server的执行计划及SQL查询优化实例分析与总结
  5. SQL Server脏读方式数据提取之NOLOCK和READPAST

分享文章：SQL Server多表查询优化方案总结
网站地址：http://www.shufengxianlan.com/qtweb/news29/194329.html

网站建设、网络推广公司-创新互联，是专注品牌与效果的网站制作，网络营销seo公司；服务项目有等

广告

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源： 创新互联

猜你还喜欢下面的内容

用户体验知识

分类信息网站

成都微信小程序　　 　户外广告设计　　 　百度关键词排名　　 　软文平台　　 　seo优化排名　　 　普格博源建站　　 　重庆建站　　 　成都网站制作　　 　虚拟主机　　 　成都响应式网站建设　　 　网站空间　　 　自贡托管服务器　　 　SSL证书　　 　成都服务器租用　　 　成都网页设计　　 　成都定制网站　　 　成都广告设计公司　　 　梅州网站建设　　 　外贸网站设计方案　　 　h5响应式网站