sql 查询语句优化

1.选择最有效率的表顺序(只在基于规则的优化器中有效)

Oracle的解析器按照从右到左的顺序处理FROM子句中的表名,FROM子句中写在最后的表(基础表d)将被最先处理,在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.如果有3个以上的表连接查询,那就需要选择交叉表作为基础表,交叉表是指那个被其他表所引用的表。

2.WHERE子句中的连接顺序.

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

3. 尽量避免在where子句中对字段进行null判断.

NULL对于大多数数据库都需要特殊处理,MySQL也不例外,它需要更多的代码,更多的检查和特殊的索引逻辑.有些开发人员完全没有意识到,创建表时NULL是默认值,但大多数时候应该使用NOTNULL,或者使用一个特殊的值,如0/-1作为默认值。

4. 应尽量避免在where子句中使用!=或<>操作符 .

MySQL只有对以下操作符才使用索引:<,<=,=,>,>=,BETWEEN…IN,以及某些时候的LIKE.可以在LIKE操作中使用索引的情形是指另一个操作数不是以通配符(%或者_)开头的情形.例如:

SELECT id FROM  t WHERE col LIKE 'Mich%'; #  这个查询将使用索引，
SELECT id FROM  t WHERE col  LIKE '%ike'; #  这个查询不会使用索引。

5.用>=替代> .

高效：SELECT * FROM EMP WHERE DEPTNO >=4;
低效: SELECT * FROM EMP WHERE DEPTNO >3;

6. 应尽量避免在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;# 高效

7. 应尽量避免在where子句中对字段(等号左边)进行表达式操作,函数操作或其他表达式运算操作.

select id from t where num/2=100; # 低效
select id from t where num=100*2; # 高效
select id from t where substring(name,1,3)='abc'; # 低效  
select id from t where name like 'abc%'; # 高效
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'; # 高效

8. 很多时候用exists代替in是一个好的选择.

select num from a where num in(select num from b);
select num from a where exists(select 1 from b where num=a.num) 

9. 最好不要使用”*”返回所有字段.

10. count(*) 优于count(1)和count(primary_key)

很多人为了统计记录条数,就使用count(1)和count(primary_key)而不是count(*),他们认为这样性能更好,其实这是一个误区。对于有些场景,这样做可能性能会更差,应为数据库对count(*)计数操作做了一些特别的优化. count(column)和count(*)是一个完全不一样的操作,所代表的意义也完全不一样.count(column)是表示结果集中有多少个column字段不为空的记录,count(*)是表示整个结果集有多少条记录

11.删除重复记录：

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

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

12.SQL语句用大写的；因为Oracle总是先解析SQL语句，把小写的字母转换成大写的再执行。

13.避免在索引列上使用NOT通常，我们要避免在索引列上使用NOT, NOT会产生在和在索引列上使用函数相同的影响。当Oracle“遇到”NOT,他就会停止使用索引转而执行全表扫描。