高性能SQL查询

1 SELECT 子句中避免使用 *

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

2 删除重复记录

高效删除重复记录方法（使用 ROWID）

DELETE 
FROM
    XEUSER u1 
WHERE
    u1.ROWID > ( SELECT MIN( u2.ROWID ) FROM XEUSER u2 WHERE u2.USERID = u1.USERID );

3 用 TRUNCATE 替代 DELETE

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

4 计算记录条数

COUNT(*) 比 COUNT(1)稍快，如果可以通过索引检索，对索引列的计数是最快的。示例：COUNT(USERID)

5 用 EXISTS 替代 IN

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

低效

SELECT
    * 
FROM
    XEUSER u 
WHERE
    u.DISABLEFLAG = 0 
    AND u.DEPARTMENTID IN ( SELECT d.DEPARTMENTID FROM DEPARTMENT d WHERE d.DISABLEFLAG = 0 );

高效

SELECT
    * 
FROM
    XEUSER u 
WHERE
    u.DISABLEFLAG = 0 
    AND EXISTS ( SELECT d.DEPARTMENTID FROM DEPARTMENT d WHERE d.DEPARTMENTID = u.DEPARTMENTID AND d.DISABLEFLAG = 0 );

6 用 EXISTS 替换 DISTINCT

当执行一对多表数据查询时（比如部门表和用户表），避免在 SELECT 子句中使用 DISTINCT。一般可以考虑用 EXIST 替换。

低效

SELECT DISTINCT
    d.DEPARTMENTID,
    d.DEPARTMENTNAME 
FROM
    DEPARTMENT d,
    XEUSER u 
WHERE
    d.DEPARTMENTID = u.DEPARTMENTID;

高效

SELECT
    d.DEPARTMENTID,
    d.DEPARTMENTNAME 
FROM
    DEPARTMENT d 
WHERE
    EXISTS ( SELECT d.DEPARTMENTID FROM XEUSER u WHERE u.DEPARTMENTID = d.DEPARTMENTID );

7 用 >= 替代 >

如果DEPARTMENTID上有索引。

两者的区别在于，后者将直接定位到 DEPARTMENTID 等于5的记录，而前者首先定位到 DEPARTMENTID 等于4的记录并向前扫描到第一个 DEPARTMENTID 大于4的记录。

低效

SELECT * FROM DEPARTMENT d WHERE d.DEPARTMENTID > 4;

高效

SELECT * FROM DEPARTMENT d WHERE d.DEPARTMENTID >= 5;

8 应尽量避免在 WHERE 子句中对列判断

SELECT USERID FROM XEUSER WHERE AGE IS NULL;

可以在列上设置默认值，确保表中列没有 NULL 值，然后通过默认值查询

SELECT USERID FROM XEUSER WHERE AGE = 0;

9 应避免在 WHERE 子句中使用 != 或 <> 操作符

引擎将放弃使用索引而进行全表扫描。优化器将无法通过索引来确定将要命中的行数，因此需要搜索该表的所有行。

10 应避免在 WHERE 子句中使用 OR

使用 OR 将导致引擎放弃使用索引而进行全表扫描

SELECT USERID FROM XEUSER WHERE GENDER = '男' OR GENDER = '女';

可使用UNION ALL进行查询

SELECT USERID FROM XEUSER WHERE GENDER = '男' UNION ALL SELECT USERID FROM XEUSER WHERE GENDER = '女';

11 慎用 IN 和 NOT IN

因为 IN 无法使用索引，只能直接搜索表中的数据

SELECT USERID FROM XEUSER WHERE GENDER IN ( 1, 2, 3 )

对于连续的数值，能用 BETWEEN 就不要用 IN

SELECT USERID FROM XEUSER WHERE GENDER BETWEEN 1 AND 3;

12 应避免在 WHERE 子句中进行表达式操作和函数操作

会导致引擎放弃使用索引而进行全表扫描

SELECT * FROM T1 WHERE F1/2=100

应改为：

SELECT * FROM T1 WHERE F1=100*2

SELECT * FROM T1 WHERE SUBSTRING(CARD_NO,1,4)=1234

应改为：

SELECT * FROM T1 WHERE CARD_NO LIKE 1234%

SELECT first_name, last_name FROM members WHERE DATEDIFF(yy,datofbirth,GETDATE()) > 21

应改为：

SELECT first_name, last_name FROM members WHERE dateofbirth < DATEADD(yy,-21,GETDATE())

任何对列的操作都将导致表扫描，它包含数据库函数、计算表达式等，查询时要尽可能将操作移至等号右边。

13 尽量避免向客户端返回大数据量

若数据量过大，应该考虑相应的需求是否合理。

14 避免使用不兼容的数据类型

例如float和int、char和varchar。数据类型的不兼容可能使优化器无法执行一些本来可以进行的最佳化操作。

15 充分利用连接条件

在某种情况下，两个表之间可能不止一个连接条件，这时在 WHERE 子句中将连接条件完整的写上，有可能大大提高查询速度。

SELECT SUM(A.AMOUNT) FROM ACCOUNT A,CARD B WHERE A.CARD_NO = B.CARD_NO

SELECT SUM(A.AMOUNT) FROM ACCOUNT A,CARD B WHERE A.CARD_NO = B.CARD_NO AND A.ACCOUNT_NO=B.ACCOUNT_NO

16 能用 DISTINCT 的就不用 GROUP BY

SELECT OrderID FROM Details WHERE UnitPrice > 10 GROUP BY OrderID

应改为：

SELECT DISTINCT OrderID FROM Details WHERE UnitPrice > 10

17 能用 UNION ALL 就不要用 UNION

UNION ALL 不执行 SELECT DISTINCT 函数，这样就会减少很多不必要的资源。

18 尽量不要使用 SELECT INTO 语句

SELECT INTO 语句会导致表锁定，阻止其他使用者访问该表。