精华内容
下载资源
问答
  • ORACLE多表查询优化

    千次阅读 2009-10-28 17:39:00
     ORACLE多表查询优化这里提供的是执行性能的优化,而不是后台数据库优化器资料:参考数据库开发性能方面的各种问题,收集了一些优化方案统计如下(当然,象索引等优化方案太过简单就不列入了,嘿嘿):执行路径:ORACLE的这...

     转自某地,对作者很愧疚- -!不晓得地址了..

     

    ORACLE多表查询优化

    这里提供的是执行性能的优化,而不是后台数据库优化器资料:

    参考数据库开发性能方面的各种问题,收集了一些优化方案统计如下(当然,象索引等优化方案太过简单就不列入了,嘿嘿):

    执行路径:ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用:我们发现,单表数据的统计比多表统计的速度完是两个概念.单表统计可能只要0.02秒,但是2张表联合统计就可能要几十表了.这是因为ORACLE只对简单的表提供高速缓冲(cache buffering) ,这个功能并不适用于多表连接查询..数据库管理员必须在init.ora中为这个区域设置合适的参数,当这个内存区域越大,就可以保留更多的语句,当然被共享的可能性也就越大了.

    当你向ORACLE提交一个SQL语句,ORACLE会首先在这块内存中查找相同的语句.
    这里需要注明的是,ORACLE对两者采取的是一种严格匹配,要达成共享,SQL语句必须
    相同(包括空格,换行等).
          共享的语句必须满足三个条件:
    A.       字符级的比较:
    当前被执行的语句和共享池中的语句必须完相同.
           例如:
               SELECT * FROM EMP;
           和下列每一个都不同
               SELECT * from EMP;
               Select * From Emp;
               SELECT       *      FROM EMP;

    B.       两个语句所指的对象必须完相同:

       用户对象名                       如何访问
    Jack   sal_limit                      private synonym
    Work_city                           public synonym
    Plant_detail                         public synonym
    Jill sal_limit                         private synonym
    Work_city                           public synonym
    Plant_detail                        table owner
          考虑一下下列SQL语句能否在这两个用户之间共享.
    SQL 能否共享 原因
    select max(sal_cap) from sal_limit; 不能 每个用户都有一个private synonym - sal_limit , 它们是不同的对象
    select count(*) from work_city where sdesc like 'NEW%'; 能 两个用户访问相同的对象public synonym - work_city
    select a.sdesc,b.location from work_city a , plant_detail b where a.city_id = b.city_id 不能 用户jack 通过private synonym访问plant_detail 而jill 是表的所有者,对象不同.

    C.       两个SQL语句中必须使用相同的名字的绑定变量(bind variables)
    例如:第一组的两个SQL语句是相同的(可以共享),而第二组中的两个语句是不同的(即使在运行时,赋于不同的绑定变量相同的值)
    a.
    select pin , name from people where pin = :blk1.pin;
    select pin , name from people where pin = :blk1.pin;
    b.
    select pin , name from people where pin = :blk1.ot_ind;
    select pin , name from people where pin = :blk1.ov_ind;
    重点关注1:选择最有效率的表名顺序(只在基于规则的优化器中有效)重点关注
    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表的交集.
    SELECT *
    FROM LOCATION L ,
           CATEGORY C,
           EMP E
    WHERE E.EMP_NO BETWEEN 1000 AND 2000
    AND E.CAT_NO = C.CAT_NO
    AND E.LOCN = L.LOCN
    将比下列SQL更有效率
    SELECT *
    FROM EMP E ,
    LOCATION L ,
           CATEGORY C
    WHERE   E.CAT_NO = C.CAT_NO
    AND E.LOCN = L.LOCN
    AND E.EMP_NO BETWEEN 1000 AND 2000
    重点关注2:WHERE子句中的连接顺序.重点关注

    ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.
    例如:
    (低效,执行时间156.3秒)
    SELECT …
    FROM EMP E
    WHERE   SAL >; 50000
    AND     JOB = ‘MANAGER’
    AND     25 < (SELECT COUNT(*) FROM EMP

    WHERE MGR=E.EMPNO);
    (高效,执行时间10.6秒)
    SELECT …
    FROM EMP E
    WHERE 25 < (SELECT COUNT(*) FROM EMP
                  WHERE MGR=E.EMPNO)
    AND     SAL >; 50000
    AND     JOB = ‘MANAGER’;

    重点关注3:SELECT子句中避免使用 ‘ * ‘ .重点关注

    当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用 ‘*’ 是一个方便的方法.不幸的是,这是一个非常低效的方法. 实际上,ORACLE在解析的过程中, 会将’*’ 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间.
    7.      减少访问数据库的次数
    当执行每条SQL语句时, ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等等. 由此可见, 减少访问数据库的次数 , 就能实际上减少ORACLE的工作量.
    例如,
         以下有三种方法可以检索出雇员号等于0342或0291的职员.
    方法1 (最低效)
         SELECT EMP_NAME , SALARY , GRADE
         FROM EMP
         WHERE EMP_NO = 342;
          SELECT EMP_NAME , SALARY , GRADE
         FROM EMP
         WHERE EMP_NO = 291;
    方法2 (次低效)
            DECLARE
             CURSOR C1 (E_NO NUMBER) IS
             SELECT EMP_NAME,SALARY,GRADE
    FROM EMP
             WHERE EMP_NO = E_NO;
         BEGIN
             OPEN C1(342);
             FETCH C1 INTO …,..,.. ;
                     OPEN C1(291);
            FETCH C1 INTO …,..,.. ;
              CLOSE C1;
           END;
    方法3 (高效)
         SELECT A.EMP_NAME , A.SALARY , A.GRADE,
                 B.EMP_NAME , B.SALARY , B.GRADE
         FROM EMP A,EMP B
         WHERE A.EMP_NO = 342
         AND    B.EMP_NO = 291;
    注意:
    在SQL*Plus , SQL*Forms和Pro*C中重新设置ARRAYSIZE参数, 可以增加每次数据库访问的检索数据量 ,建议值为200.

    重点关注4:使用DECODE函数来减少处理时间.重点关注
    使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.
    例如:
        SELECT COUNT(*),SUM(SAL)
        FROM EMP
        WHERE DEPT_NO = 0020
        AND ENAME LIKE ‘SMITH%’;
        SELECT COUNT(*),SUM(SAL)
        FROM EMP
        WHERE DEPT_NO = 0030
        AND ENAME LIKE ‘SMITH%’;
    你可以用DECODE函数高效地得到相同结果
    SELECT COUNT(DECODE(DEPT_NO,0020,’X’,NULL)) D0020_COUNT,
             COUNT(DECODE(DEPT_NO,0030,’X’,NULL)) D0030_COUNT,
             SUM(DECODE(DEPT_NO,0020,SAL,NULL)) D0020_SAL,
             SUM(DECODE(DEPT_NO,0030,SAL,NULL)) D0030_SAL
    FROM EMP WHERE ENAME LIKE ‘SMITH%’;
    类似的,DECODE函数也可以运用于GROUP BY 和ORDER BY子句中.

    重点关注5: 删除重复记录.重点关注

    最高效的删除重复记录方法 ( 因为使用了ROWID)
    DELETE FROM EMP E
    WHERE E.ROWID >; (SELECT MIN(X.ROWID)
                        FROM EMP X
                        WHERE X.EMP_NO = E.EMP_NO);

    重点关注6: 用TRUNCATE替代DELETE.重点关注

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

    重点关注7: 尽量多使用COMMIT.重点关注

    只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少:
    COMMIT所释放的资源:
    a.        回滚段上用于恢复数据的信息.
    b.        被程序语句获得的锁
    c.        redo log buffer 中的空间
    d.       ORACLE为管理上述3种资源中的内部花费
    (译者按: 在使用COMMIT时必须要注意到事务的完整性,现实中效率和事务完整性往往是鱼和熊掌不可得兼)
    重点关注8:减少对表的查询.重点关注

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

    例如:
          低效
               SELECT TAB_NAME
               FROM TABLES
               WHERE TAB_NAME = ( SELECT TAB_NAME
                                     FROM TAB_COLUMNS
                                     WHERE VERSION = 604)
               AND DB_VER= ( SELECT DB_VER
                                FROM TAB_COLUMNS
                                WHERE VERSION = 604)
          高效
               SELECT TAB_NAME
               FROM TABLES
               WHERE   (TAB_NAME,DB_VER)
    = ( SELECT TAB_NAME,DB_VER)
                        FROM TAB_COLUMNS
                        WHERE VERSION = 604)
          Update 多个Column 例子:
          低效:
                UPDATE EMP
                SET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES),
                   SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES)
                WHERE EMP_DEPT = 0020;
          高效:
                UPDATE EMP
                SET (EMP_CAT, SAL_RANGE)
    = (SELECT MAX(CATEGORY) , MAX(SAL_RANGE)
    FROM EMP_CATEGORIES)
                WHERE EMP_DEPT = 0020;

    重点关注9:用EXISTS替代IN.重点关注

    在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率.
    低效:
    SELECT *
    FROM EMP (基础表)
    WHERE EMPNO >; 0
    AND DEPTNO IN (SELECT DEPTNO
    FROM DEPT
    WHERE LOC = ‘MELB’)
         高效:
    SELECT *
    FROM EMP (基础表)
    WHERE EMPNO >; 0
    AND EXISTS (SELECT ‘X’
    FROM DEPT
    WHERE DEPT.DEPTNO = EMP.DEPTNO
    AND LOC = ‘MELB’)
    (译者按: 相对来说,用NOT EXISTS替换NOT IN 将更显著地提高效率,下一节中将指出)

    重点关注10:用NOT EXISTS替代NOT IN .重点关注

    在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个表遍历).   为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.
    例如:
    SELECT …
    FROM EMP
    WHERE DEPT_NO NOT IN (SELECT DEPT_NO
                              FROM DEPT
                              WHERE DEPT_CAT=’A’);
    为了提高效率.改写为:
    (方法一: 高效)
    SELECT ….
    FROM EMP A,DEPT B
    WHERE A.DEPT_NO = B.DEPT(+)
    AND B.DEPT_NO IS NULL
    AND B.DEPT_CAT(+) = ‘A’
    (方法二: 最高效)
    SELECT ….
    FROM EMP E
    WHERE NOT EXISTS (SELECT ‘X’
                         FROM DEPT D
                         WHERE D.DEPT_NO = E.DEPT_NO
                         AND DEPT_CAT = ‘A’);

    当然,最高效率的方法是有表关联.直接两表关系对联的速度是最快的!
    重点关注11:识别’低效执行’的SQL语句.重点关注

    用下列SQL工具找出低效SQL:
    SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,
             ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,
             ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,
             SQL_TEXT
    FROM    V$SQLAREA
    WHERE   EXECUTIONS>;0
    AND      BUFFER_GETS >; 0
    AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
    ORDER BY 4 DESC;
          (译者按: 虽然目前各种关于SQL优化的图形化工具层出不穷,但是写出自己的SQL工具来解决问题始终是一个最好的方法)

    展开全文
  • sqlserver 多表查询优化

    千次阅读 2019-11-06 22:38:31
    没别的 就是加索引 聚集索引 或者复合非聚集索引 都行 俩个都需要加 如果不加索引 比如 主ID是 3 2 1 ...子没加索引 ...每一次查询 1 的时候会遍历一次全子 也就是 查...

     没别的 就是加索引 聚集索引 或者复合非聚集索引 都行

    俩个表都需要加

    如果不加索引

     比如 主表ID是

    3  
    2  
    1  

     

    子表没加索引

    1  
    2  
    1  
    1  
    2  
    3  
    3  
    1  

     

    每一次查询主表 1 的时候会遍历一次全子表 也就是 

    查询一次数据 数据库需要 3*8 =24次 子表

    但是加索引以后

    1  
    2  
    3  

     

    子表

    1  
    1  
    1  
    1  
    2  
    2  
    3  
    3  

     

     join 查询主表1的时候 便利到子表最后一个1 发现下一个没了 就不再遍历  查询2的时候 就会从2开始 到最后一个2结束

    就不用每一次 遍历全表~

     

     

     

    展开全文
  • 比如我要查一张菜单,想查询名字为‘product’和子菜单名字为‘product’的菜单,下面是我写的语句。 请问这种怎么用join才代替in,或者有没有别的办法让效率变得高点 select * FROM menu As m where m.name = '...
  • mysql中left join多表查询优化

    千次阅读 2019-07-11 16:24:26
    关于MYSQL 中left join 的sql优化的问题 最近碰到一个sql超级慢的案例示例如下建表语句如下执行时间约4秒中,速度太慢了,体验太差 最近碰到一个sql超级慢的案例示例如下 建表语句如下 CREATE TABLE `sys_dept` ( `...

    最近碰到一个sql超级慢的案例示例如下

    建表语句如下

    CREATE TABLE `sys_dept` (
      `dept_id` int(10) NOT NULL,
      `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '部门名称',
      `sort` int(11) DEFAULT NULL COMMENT '排序',
      `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
      `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      `del_flag` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '0' COMMENT '是否删除  -1:已删除  0:正常',
      `parent_id` int(10) DEFAULT '0' COMMENT '父id',
      `tenant_id` int(11) DEFAULT '1',
      PRIMARY KEY (`dept_id`) USING BTREE,
      KEY `index_dept_name` (`name`) USING BTREE,
      KEY `index_parent` (`parent_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='部门管理';
    CREATE TABLE `sys_user` (
      `user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
      `username` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '用户名',
      `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '密码',
      `mail` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '邮箱',
      `cn` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'cn',
      `distinguished_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '辨别名称',
      `salt` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '随机盐',
      `name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '姓名',
      `phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '手机号',
      `avatar` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '头像',
      `dept_id` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '部门ID',
      `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
      `update_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
      `lock_flag` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT '0' COMMENT '0-正常,9-锁定',
      `del_flag` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT '0' COMMENT '0-正常,1-删除',
      `wx_openid` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '微信openid',
      `qq_openid` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'QQ openid',
      `tenant_id` int(11) DEFAULT '1' COMMENT '所属租户',
      PRIMARY KEY (`user_id`) USING BTREE,
      UNIQUE KEY `user_idx1_username` (`username`) USING BTREE,
      KEY `user_wx_openid` (`wx_openid`) USING BTREE,
      KEY `user_qq_openid` (`qq_openid`) USING BTREE,
      KEY `12312` (`dept_id`) USING BTREE,
      KEY `21322` (`user_id`,`username`,`dept_id`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=37549810 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='用户表';
    

    我们对这两张表中,表一月2000条数据,表二8000条数据执行sql如下:

    SELECT
    		`user`.user_id,
    		`user`.username,
    		`user`.name,
    		`user`.`password`,
    		`user`.salt,
    		`user`.distinguished_name,
    		`user`.cn,
    		`user`.mail,
    		`user`.phone,
    		`user`.avatar,
    		`user`.wx_openid,
    		`user`.qq_openid,
    		`user`.dept_id AS deptId,
    		`user`.create_time AS ucreate_time,
    		`user`.update_time AS uupdate_time,
    		`user`.del_flag AS udel_flag,
    		`user`.lock_flag AS lock_flag,
    		`user`.tenant_id AS tenantId ,
    		d.name
    		FROM
    		sys_user AS `user`
    		left join sys_dept d  
    		on `user`.dept_id=d.dept_id
    		ORDER BY `user`.create_time DESC
    

    执行时间约4秒中,速度太慢了,体验太差

    下面explain该sql语句如下图
    可以看得到,这是优化后的,
    也就是吧关联字段由原来的varchar改为int ,简直不是一个量级的,速度达到了17毫秒
    总结

    1. 表设计: 主键尽量int类型的字段进行关联,减少数据库查询条件,因为varchar将会有一个转换的过程;
    2. 关联字段最好创建索引;
    3. 数据量小的表尽量在前
    展开全文
  • SQL Server多表查询优化方案总结

    千次阅读 2014-03-12 10:43:36
    SQL Server多表查询优化方案是本文我们主要要介绍的内容,本文我们给出了优化方案和具体的优化实例,接下来就让我们一起来了解一下这部分内容。 1.执行路径 ORACLE的这个功能大大地提高了SQL的执行性能并节省了...

    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. FROM LOCATION L ,
    3. CATEGORY C,
    4. EMP E
    5. WHERE E.EMP_NO BETWEEN 1000 AND 2000
    6. AND E.CAT_NO = C.CAT_NO
    7. AND E.LOCN = L.LOCN

    将比下列SQL更有效率

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

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

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

    例如:

    (低效,执行时间156.3秒)

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

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

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

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

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

    方法1 (低效)

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

    方法2 (高效)

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

    6.删除重复记录

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

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

    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. FROM TABLES
    3. WHERE TAB_NAME = ( SELECT TAB_NAME
    4. FROM TAB_COLUMNS
    5. WHERE VERSION = 604)
    6. AND DB_VER= ( SELECT DB_VER
    7. FROM TAB_COLUMNS
    8. WHERE VERSION = 604

    高效:

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

    Update 多个Column 例子:

    低效:

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

    高效:

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

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

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

    低效:

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

    高效:

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

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

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

    例如:

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

    为了提高效率.改写为:

    (方法一: 高效)

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

    (方法二: 最高效)

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

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

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

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

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

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

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

    展开全文
  • sql 多表查询优化

    万次阅读 2011-12-04 20:26:17
    为了提高查询速度,提高数据库应用系统的执行效率,文章从分析关系数据库多表查询处理过程入手讨论查询优化技巧,指出多表查询优化的原则。通过几种优化策略的研究,在时间和空间上提高了系统的性能,在一定程度上...
  • 这个功能大大地提高了SQL的执行性能并节省了内存的使用:我们发现,... buffering) ,这个功能并不适用于多连接查询..数据库管理员必须在init.ora中为这个区域设置合适的参数,当这个内存区域越大,就可以保留更的语句,
  • MySQL多表查询核心优化

    万次阅读 多人点赞 2016-03-22 17:00:33
    在一般的项目开发中,对数据表的多表查询是必不可少的。而对于存在大量数据量的情况时(例如百万级数据量),我们就需要从数据库的各个方面来进行优化,本文就先从多表查询开始。
  • 多表查询优化

    千次阅读 2012-09-27 11:45:02
    输出每个查询的时间耗费之后发现,我的一个多表连接查询耗费了很多的时间。网上搜了一下性能优化的办法,大部分的方案是在表的连接字段建索引。  我的三表连接查询语句:  SELECT count(DISTINCT(c.pin)) as cnt ...
  • Oracle 查询技巧与优化(二) 多表查询

    万次阅读 多人点赞 2016-08-05 08:59:59
    关于Oracle多表查询的一些技巧和优化~
  • MySQL多表关联查询优化

    千次阅读 2019-11-28 10:37:20
    最近在对运营报表导出进行优化,总结了一些多表关联查询优化的点记录一下。 避免临时表 通过 Explain 分析 SQL 语句,尽量不要使用到临时表。GROUP BY (Explain具体详解,可以看这篇博客) 最容易造成使用临时表,...
  • 查询优化:SQL优化 场景:当列表展示的数据来自很多表中的数据时,由于...JOIN连接查询也很方便地获取到要返回的数据,但是问题来了,这么多表查询起来关联的数据非常多,查询耗时很大,甚至超过30秒,影响用户体...
  • Mysql 多表联合查询效率分析及优化

    万次阅读 多人点赞 2010-07-13 15:23:00
    1. 多表连接类型 1. 笛卡尔积(交叉连接) 在MySQL中可以为CROSS JOIN或者省略CROSS即JOIN,或者使用',' 如: SELECT * FROM table1 CROSS JOIN table2 SELECT * FROM table1 JOIN table2 SELECT * FROM table1,...
  • MYSQL 多表连接查询优化

    万次阅读 2018-07-12 17:13:44
    交代一下背景,这算是一次项目经验吧,属于公司一个已上线平台的功能,这算是离职人员挖下的坑,随着数据越来越,原本的SQL查询变得越来越慢,用户体验特别差,因此SQL优化任务交到了我手上。 这个SQL查询关联两...
  • 多表查询SQL优化

    千次阅读 2017-08-07 12:49:12
    多表连接LEFT JOIN  是一个例子:三张表,也就是多表联查,使用聚集函数SUM,用到了GROUP BY select C.channel_short_name,u.standby2,u.user_name,bs.enroll_batch,bs.pay_school_year,bs.settlement_amount,bs....
  • MyBatis 多表联合查询优化

    万次阅读 多人点赞 2015-05-25 11:50:27
    序 ...但是吧,就我前几天在做一个多表联合查询的时候,竟然出了很多意想不到的问题,而且这些问题的出现,并不是对 mybatis 不了解,而是在用的过程中会或多或少的忽略一些东西,导致提示各种错误
  • Oracle 多表联合查询优化

    千次阅读 2019-09-19 12:25:32
    假设高速缓冲里已经存储了执行过的sql语句,那就直接匹配执行了,少了步骤,自然就快了,但是经过测试会发现高速缓冲只对简单的表起作用,多表的情况完全没有效果,例如在查询单表的时候那叫一个快,但...
  • sql多表查询语句优化——建立索引

    千次阅读 2019-09-19 19:02:33
    因为是多表查询,因此我们首先来看查询方法是否存在问题,然后再检查是否存在主表数据量远远大于子表的情况,之后再检查是否有建立索引查询。 解决问题 首先我们贴出查询语句 然后我们贴出在navicat中此语句的查询...
  • 继续这一系列,上篇的简单查询优化并没讲完,第二点还有查询注意点以及多表查询优化呢!! 文章结构:(1)多表查询步步优化;(2)查询编写的注意点。 主要是内连接,外连接,交叉连接的详解(配图)。以及罗列出,...
  • mysql 多表查询语句优化

    千次阅读 2019-02-21 11:39:03
    很明显,MySQL选择了小作为驱动,再配合(hotel_id,hotel_type)上的索引瞬间降低了好个数量级。 一个关于left join 的通用法则,即:如果where条件中含有右的非空条件(除开is null),则left join语句等同...
  • @TO关于千万级的大表,单表查询优化问题C 关于面试,千万级大表的单表查询优化问题 前段时间面试,被问到了千万数据级的大表,单表查询操作时如何优化的问题,以下是个人的一些见解及回答内容,如果有不对的地方,...
  • Oracle查询优化-01单表查询

    千次阅读 2017-01-31 22:39:57
    1.1 查询表中所有的行与列1.2 从中检索部分行1.3 查找空值1.4 将空值转换为实际值1.5 查找满足个条件的行1.6 从中检索部分列1.7 为列取有意义的名称1.8 在 WHERE 子句中引用取别名的列1.9 拼接列1.10 在 ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 422,766
精华内容 169,106
关键字:

多表查询如何优化