数据库优化常用的几种小技巧
注:(不全面需要更新补充)
1.尽量不使用select * ,查询什么字段select什么字段;
2.尽量不使用 in 效率很低;
3.冗余字段,减少关联查询,提高查询效率;
4.建立索引,提高查询效率;
5.用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')