SQL优化以及反驳网上的SQL优化内容

优化：

1、用EXISTS替代IN、用NOT EXISTS替代NOT IN
例子：

//高效
SELECT * FROM EMP (基础表)
 WHERE EMPNO > 0 
 AND EXISTS 
 (SELECT "X" FROM DEPT 
 WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = "MELB")

//低效
SELECT * FROM EMP
WHERE EMPNO > 0
AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = "MELB")

    使用in查询时，数据库首先会执行子查询，然后将结果保存在临时表中，然后扫描整个临时表，很多情况下非常耗费资源。
反之EXISTS不会产生临时表。

2、SQL用大写
    因为oracle会先解析sql语句，把小写的字母转换成大写的再执行

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

4、用>=替代>

//高效
select * from emp where deptno >=4

//低效
select * from emp where deptno > 3

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

5、在含有子查询的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)

    因为子查询里查表也会产生一个临时表进行储存数据。

6、模糊查询尽量避免前%查询

//低效
select id from a where name like ‘%abc’

//高效
select id from a where name like ‘abc%’

    因为前%模糊查询会导致全表扫描。

7、能用between不用in

select id from t where num in(1,2,3)

对于连续的数值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

  因为in也会造成全表扫描。

8、where的使用
  将能够过滤掉最多的条件放在where的最后面，因为where条件是从后往前执行的。

反驳：

1、计算记录条数
  和一般的观点相反, count(*) 比count(1)稍快 , 当然如果可以通过索引检索,对索引列的计数仍旧是最快的. 例如 COUNT(EMPNO)
我建了一个表，插入了一百万的数据，分别用COUNT(*)、COUNT(1)执行了50次算出他们的平均耗时。结果COUNT(*)平均用时0.018ms，COUNT(1)平均用时0.015ms。

2、选择最有效率的表名顺序
  在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并.例如:
表 TAB1 16,384 条记录
表 TAB2 1 条记录
选择TAB2作为基础表 (最好的方法)
select count() from tab1,tab2 执行时间0.96秒 选择TAB1作为基础表 (不佳的方法)
select count() from tab2,tab1 执行时间26.09秒
我建了俩个表，一个插入了一百万的数据，一个插入一条数据，用上述方法，结果显示并没太大差别。

3、分解关联查询

select * from tag
left join tag_post on tag_post.id = tag.id
where tag.tag = 'abc'

分解为：

select * from tag where tag = 'abc'
select * from tag_post where tag_post in (xx,xx,xx,xx);

由于Oracle的IN的参数最多只能放1000个参数，所以如果是大数据量的话，就必须分批次查，这样就会导致效率不如关联查询，如果是1000个参数以内的话，俩者又没有多大差别。如果是1000条以内，可以进行分解，因为可以减少锁定竞争，在代码上 也能相对的清晰一些。而且有准确的ID进行查询的话会比用SQL关联查询少一些数据量的查询，因为SQL关联查询有可能会重复查询某一些数据。