当系统页面加载较慢,数据库IOPS和负载变大时,这个时候需要考虑优化SQL了。
SQL优化笔记:
NO. 检查项目
1 保证不查询多余的列与行。2 慎用distinct关键字
3 慎用union关键字
4 判断表中是否存在数据的优化
5 连接查询的优化
6 insert插入优化
7 慎用or会引起全表扫描
8 尽量少用NOT
9 字段提取要按照“需多少、提多少”的原则,避免“select *”
10 少用临时表
11 避免在 where 子句中使用 != 或 <> 操作符
12 避免在where子句中对字段进行函数操作
13 新建临时表须知
14 提高Order by语句查询效率
15 子查询中某些情况exists代替in
保证不查询多余的列与行。
尽量避免select * 的存在,使用具体的列代替*,避免多余的列
使用where限定具体要查询的数据,避免多余的行
使用top,distinct关键字减少多余重复的行
慎用distinct关键字
distinct在查询一个字段或者很少字段的情况下使用,会避免重复数据的出现,给查询带来优化效果。
但是查询字段很多的情况下使用,则会大大降低查询效率。
原因是当查询很多字段时,如果使用distinct,数据库引擎就会对数据进行比较,过滤掉重复数据,然而这个比较,过滤的过程则会毫不客气的占用系统资源,cpu时间。
慎用union关键字
满足union的语句必须满足:1.列数相同。 2.对应列数的数据类型要保持兼容。
执行过程:
依次执行select语句-->>合并结果集--->>对结果集进行排序,过滤重复记录。
效率低,所以不是在必要情况下避免使用。
使用union all能对union进行一定的优化。
判断表中是否存在数据的方法
select count(*) from product
select top(1) id from product
很显然下面完胜
连接查询的优化
各种连接的取值大小为:
内连接结果集大小取决于左右表满足条件的数量
左连接取决与左表大小,右相反。
完全连接和交叉连接取决与左右两个表的数据总数量
减少连接表的数据数量可以提高效率。
insert插入优化
insert into select批量插入,明显提升效率。所以以后尽量避免一个个循环插入。
or 会引起全表扫描
Name='张三' and 价格>5000 符号SARG,而:Name='张三' or 价格>5000 则不符合SARG。使用or会引起全表扫描。
IN 的作用相当与OR
语句:
Select * from table1 where tid in (2,3)
--和
Select * from table1 where tid=2 or tid=3
是一样的,都会引起全表扫描,如果tid上有索引,其索引也会失效。
少用临时表
尽量用结果集和Table类型的变量来代替它,Table 类型的变量比临时表好。
避免在 where 子句中使用 != 或 <> 操作符
避免在 where 子句中使用 != 或 <> 操作符,否则将引擎放弃使用索引而进行全表扫描。
避免在where子句中对字段进行函数操作
select id from t where substring(name,1,3) = ’abc’ -–name以abc开头的id
select id from t where datediff(day,createdate,’2005-11-30′) = 0 -–‘2005-11-30’ --生成的id
应改为:
select id from t where name like 'abc%'
select id from t where createdate >= '2005-11-30' and createdate < '2005-12-1'
新建临时表须知
如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;
如果数据量不大,为了缓和系统表的资源,应先create table,然后insert。
如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先 truncate table ,然后 drop table ,这样可以避免系统表的较长时间锁定。
提高Order by语句查询效率
order by/group by字段如果包含在索引列当中,效率会有所提高。
通过数据库活动找到特别耗时间的查询语句,如果Where条件语句与Order By条件语句一起使用的话,可以考虑需要通过建立索引(order by字段)来提高查询效率。
子查询中某些情况exists代替in
exists会针对子查询的表使用索引. not exists会对主子查询都会使用索引. in与子查询一起使用的时候,只能针对主查询使用索引. not in则不会使用任何索引.
即:
not exists 可以替代 not in
exists代替in分情况:
如果查询的两个表大小差不多,那么用exists和in差别不大。
如果两个表中一个大表一个小表,则子查询表大的用exists,子查询表小的用in:
比如:A表(小表),B表(大表)
select * from A where exists(select cc from B where cc=A.cc) 的效率比 select * from A where cc in (select cc from B) 高, 因为前者用到了B表上cc列的索引,后者用的是A表cc列的索引。
反之(A表大,B表小),则相反。