高效SQL语句必杀技

您的位置：
门户
>> 文章精选
>> 软件开发专栏
>> 数据库
>> 查看资讯

发表于：2013-6-05 10:04

作者：RobinsonCheng 来源：51Testing软件测试网采编

　　-->高效：

SELECT deptno, AVG( sal )
FROM emp
WHERE deptno = 20
GROUP BY deptno;

scott@CNMMBO> SELECT deptno, AVG( sal )
2 FROM emp
3 WHERE deptno = 20
4 GROUP BY deptno;

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
          2 consistent gets
          0 physical reads
          0 redo size
        583 bytes sent via SQL*Net to client
        492 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

　　11）最小化表查询次数

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

　　-->低效：

SELECT *
FROM   employees
WHERE department_id = (SELECT department_id
                        FROM   departments
                        WHERE department_name = 'Marketing')
       AND manager_id = (SELECT manager_id
                         FROM   departments
                         WHERE department_name = 'Marketing');

　　-->高效：

SELECT *
FROM   employees
WHERE ( department_id, manager_id ) = (SELECT department_id, manager_id
                                        FROM   departments
                                        WHERE department_name = 'Marketing')

　　-->类似更新多列的情形

　　-->低效：

UPDATE employees
SET job_id = ( SELECT MAX( job_id ) FROM jobs ), salary = ( SELECT AVG( min_salary ) FROM jobs )
WHERE department_id = 10;

　　-->高效：

UPDATE employees
SET ( job_id, salary ) = ( SELECT MAX( job_id ), AVG( min_salary ) FROM jobs )
WHERE department_id = 10;

　　12）使用表别名

　　-->在多表查询时，为所返回列使用表别名作为前缀以减少解析时间以及那些相同列歧义引起的语法错误

　　13）用EXISTS替代IN

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

　　-->低效：

SELECT *
FROM   emp
WHERE sal > 1000
       AND deptno IN (SELECT deptno
                      FROM   dept
                      WHERE loc = 'DALLAS')

　　-->高效：

SELECT *
FROM   emp
WHERE empno > 1000
       AND EXISTS
              (SELECT 1
               FROM   dept
               WHERE deptno = emp.deptno AND loc = 'DALLAS')

　　14）用NOT EXISTS替代NOT IN

　　在子查询中，NOT IN子句引起一个内部的排序与合并。因此，无论何时NOT IN子句都是最低效的，因为它对子查询中的表执行了一个全表遍历。为避免该情形，应当将其改写成外部连接（OUTTER JOIN）或适用NOT EXISTS

　　-->低效：

SELECT *
FROM   emp
WHERE deptno NOT IN (SELECT deptno
                       FROM   dept
                       WHERE loc = 'DALLAS');

　　-->高效：

SELECT e.*
FROM   emp e
WHERE NOT EXISTS
          (SELECT 1
           FROM   dept
           WHERE deptno = e.deptno AND loc = 'DALLAS');

　　-->最高效（尽管下面的查询最高效，并不推荐使用，因为列loc使用了不等运算，当表dept数据量较大，且loc列存在索引的话，则此时索引失效）

SELECT e.*
FROM emp e LEFT JOIN dept d ON e.deptno = d.deptno
WHERE d.loc <> 'DALLAS'

73/7<1 234 5 6 7 >

《2023软件测试行业现状调查报告》独家发布~

搜索风云榜

测试技术了解

2023测试行业调查报告

挣点稿费

AI与软件测试

文章资料精选