1.用exists 代替 in
原理:exists 是存在一个即返回一个 而in是做全盘扫描得出所有条件内的数据
(高效) select *From member where age>18 and exists (select 'x' from Person where member.name =Person.name='cyao') (低效) select *From member where age>18 and name in (select name From Person where name='cyao')
2.用exists 代替DISITNCT
(高效) select name ,age from mamber as d where exists (select 'x' from person as e where e.name = d.name) (低效) select Distinct name ,age from mamber as d ,person as e where e.name = d.name
3.尽量避免在select 后面 加*
4.尽量避免空值查询 所有的字段尽量有设置默认值。例如num列默认值为0;判断由where num is null -> where num=0
5.尽量使用数字型字段,例如status=1 激活 status=2 代表删除
6.避免频繁创建和删除临时表,以减少系统表资源的消耗。