最新历史版本 :高性能SQL查询优化取数方案 返回文档
编辑时间: 内容长度:图片数:目录数: 修改原因:

目录:

1. 概述编辑

报表的核心是数据，数据集是否合理决定报表的质量。     

1）每张报表都应该有一个主数据集，为了降低维护时的工作量，尽量将所有字段置于主数据集，除非在某些情况下，不使用多源数据集会导致主数据集异常复杂。

2）在制作报表之前，尽量考虑到所有需要展示的数据字段，在数据库软件中，合理编写 SQL 语句，大数据情况尽量对 SQL 做优化，以及添加索引。

3）拥有高性能 SQL 查询语句，能使查询速度加快，报表展示速度得到较明显的提升！

2. 方案介绍编辑

2.1 SELECT 子句中避免使用 “*”  

当你想在 SELECT 子句中列出所有的 COLUMN 时，使用动态 SQL 列引用‘*’是一个方便的方法。

但这是一个非常低效的方法。 实际上 ORACLE 在解析的过程中, 会将“*” 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间。  

2.2 删除重复记录  

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

2.3 用 TRUNCATE 替代 DELETE

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

2.4 计算记录条数

 和一般的观点相反， count(*) 比count(1)稍快 ，当然如果可以通过索引检索，对索引列的计数仍旧是最快的。 例如：COUNT(EMPNO)

2.5 用 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’)

2.6 用 EXISTS 替换 DISTINCT  

当提交一个包含一对多表信息( 比如部门表和雇员表 )的查询时，避免在 SELECT 子句中使用 DISTINCT， 一般可以考虑用 EXIST 替换。例如:：
低效：
SELECT DISTINCT DEPT_NO,DEPT_NAME  FROM DEPT D,EMP E WHERE D.DEPT_NO = E.DEPT_NO 
高效：
SELECT DEPT_NO,DEPT_NAME  FROM DEPT D WHERE EXISTS ( SELECT ‘X’ FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO)

2.7 用 >= 替代 >

例如 DEPTNO 上有一个索引： 
高效：    
SELECT *  FROM EMP  WHERE DEPTNO >=4    
低效：    
SELECT *  FROM EMP   WHERE DEPTNO >3    
两者的区别在于, 前者 DBMS 将直接跳到第一个 DEPT 等于 4 的记录。而后者将首先定位到 DEPTNO=3 的记录并且向前扫描到第一个 DEPT 大于 3 的记录。

2.8 应尽量避免在 where 子句中对字段判断

例如：
select id from t where num is null
可以在 num 上设置默认值 0，确保表中 num 列没有 null 值，然后这样查询：
select id from t where num=0

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

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

2.10 应避免在 where 子句中使用 or 连接

否则将导致引擎放弃使用索引而进行全表扫描，例如：
select id from t where num=10 or num=20
可以这样查询：
select id from t where num=10 union all select id from t where num=20

2.11 in 和 not in 也要慎用

因为 IN 会使系统无法使用索引，而只能直接搜索表中的数据。例如：
select id from t where num in(1,2,3)
对于连续的数值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3

2.12 应避免在 where 子句中进行表达式操作

这将导致引擎放弃使用索引而进行全表扫描。例如：
SELECT * FROM T1 WHERE F1/2=100
应改为:
SELECT * FROM T1 WHERE F1=100*2

SELECT * FROM RECORD WHERE SUBSTRING(CARD_NO,1,4)=’5378’
应改为:
SELECT * FROM RECORD WHERE CARD_NO LIKE ‘5378%’

SELECT member_number, first_name, last_name FROM members WHERE DATEDIFF(yy,datofbirth,GETDATE()) > 21
应改为:
SELECT member_number, first_name, last_name FROM members WHERE dateofbirth < DATEADD(yy,-21,GETDATE())
注：任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。

2.13 应避免在 where 子句中进行函数操作

这将导致引擎放弃使用索引而进行全表扫描。例如：

1）name 以 abc 开头的 id

select id from t where substring(name,1,3)='abc'

应改为：

select id from t where name like 'abc%'

2）‘2005-11-30’生成的 id

select id from t where datediff(day,createdate,'2005-11-30')=0
应改为:
select id from t where createdate>=’2005-11-30′ and createdate<'2005-12-1'

2.14 不要在 where 子句中的“=”左边运算

进行函数、算术运算或其他表达式运算，系统将可能无法正确使用索引。

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

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

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

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

SELECT name FROM employee WHERE salary > 60000

在这条语句中，如 salary 字段是 money 型的，则优化器很难对其进行优化,因为 60000 是个整型数。我们应当在编程时将整型转化成为钱币型，而不要等到运行时转化。

2.17 充分利用连接条件

在某种情况下，两个表之间可能不只一个的连接条件，这时在 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

第二句将比第一句执行快得多。

2.18 能用 GROUP BY 就不用 DISTINCT

SELECT DISTINCT OrderID FROM Details WHERE UnitPrice > 10

可改为：

SELECT OrderID FROM Details WHERE UnitPrice > 10 GROUP BY OrderID

2.19 能用 UNION ALL 就不要用 UNION

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

2.20 尽量不要用 SELECT INTO 语句

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