常见SQL优化

一、为什么要对SQL进行优化

我们开发项目上线初期，由于业务数据量相对较少，一些SQL的执行效率对程序运行效率的影响不太明显，而开发和运维人员也无法判断SQL对程序的运行效率有多大，故很少针对SQL进行专门的优化，而随着时间的积累，业务数据量的增多，SQL的执行效率对程序的运行效率的影响逐渐增大，此时对SQL的优化就很有必要。

二、SQL优化的一些方法
1， sql中* 的使用
查询条件中，一定不要使用select * ，因为会返回过多无用的字段会降低查询效率。应该使用具体的自段代替*，只返回使用到的字段。
例如：select * from user where ...
优化：select id,name,age from user where...

2，通配符中Like的使用
以下写法会造成全表的扫描
例如：
select id,name from userinfo where name like '%name%'or
select id,name from userinfo where name like '%name'

优化：使用了索引
select id,name from userinfo where name like 'name%'

3，避免在where子句中对字段进行表达式操作
例如：
select id,name from user where age*2=36;

优化：
select id,name from user where age=36/2;

4，尽量不要使用or，会造成全表扫描
例如：
SELECT * FROM t_order WHERE id = 1 OR id = 3

优化：可以用union代替or
SELECT * FROM t_order WHERE id = 1 UNION SELECT * FROM t_order WHERE id = 3

5，where条件里尽量不要进行null值的判断，null的判断也会造成全表扫描
例如：
SELECT * FROM t_order WHERE score IS NULL

优化：给字段添加默认值，对默认值进行判断
SELECT * FROM t_order WHERE score = 0

6，尽量不要使用where 1=1的条件
例如：会进行全表扫表
SELECT * FROM t_order WHERE 1=1

优化：
如果用代码拼装sql，则由代码进行判断，没where加where，有where加and，如果用mybatis，请用mybatis的where语法。

7，尽量不要使用in 和not in，会造成全表扫描
例如：
SELECT * FROM t_order WHERE id IN (2,3)
SELECT * FROM t_order1 WHERE customer IN (SELECT customer FROM t_order2)

优化：
SELECT * FROM t_order WHERE id BETWEEN 2 AND 3
SELECT * FROM t_order1 WHERE EXISTS (SELECT * FROM t_order2 WHERE t1.customer = t2.customer)

8，每个表都应该设置一个ID主键，最好的是一个INT型。

9，索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有要。

10，尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

11， 二者都能使用尽量使用where （与having比较），因为where 先过滤（数据就少了）再分组 。

12，尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

13，尽量避免大事务操作，提高系统并发能力

14，避免频繁创建和删除临时表，以减少系统表资源的消耗。

15，减少访问数据库的次数。