|
其他注意事项
1.尽量避免使用select *
2.尽量使用表连接(join)代替子查询select * from t1 where a in (select b from t2)
3.性能方面,表连接 > (not) exists > (not) in
1)用exists代替in
低效:
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)用not exists代替not in
低效:
SELECT …
FROM EMP
WHERE DEPT_NO NOT IN (SELECT DEPT_NO
FROM DEPT
WHERE DEPT_CAT=’A’);
高效:
SELECT ….
FROM EMP E
WHERE NOT EXISTS (SELECT ‘X’
FROM DEPT D
WHERE D.DEPT_NO = E.DEPT_NO
AND DEPT_CAT = ‘A’);
3)用表连接代替exists
exits:
SELECT ENAME
FROM EMP E
WHERE EXISTS (SELECT ‘X’
FROM DEPT
WHERE DEPT_NO = E.DEPT_NO
AND DEPT_CAT = ‘A’);
表连接:
SELECT ENAME
FROM DEPT D,EMP E
WHERE E.DEPT_NO = D.DEPT_NO
AND DEPT_CAT = ‘A’ ;
4.清除不必要的排序
低效:
select count(*) from (select * from user where id > 40 order by id);
高效:
select count(*) from (select * from user where id > 40);
5.having -> where
避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.
低效:
select * from user group by id having id > 40;
高效:
select * from user where id > 40 group by id;
6.除非确实需要去掉重复的行,否则尽量使用union all而不是union。因为union会自带distinct操作,代价很大
使用explain查看sql性能
1.explain用法:在select之前加上explain即可。
例如:explain select * from test;
注意:explain并不会真正运行语句,而是只返回执行计划。
怎么看执行计划?一个简单的优化原则:令sql读取尽可能少的行。
|
|