30种简单的sql优化方法

同样的数据表，同样的查询条件，为什么有的执行时间2、3秒甚至更多，有的却只需要2毫秒。就是因为sql语句的差异。

以下是我在网上搜集的一些sql语句优化方式，暂做笔记。

1. 合理的建立索引。首选在where查询条件和order by字段上建立索引。
2. 尽量避免在 where 子句中使用!=或<>操作符，否则将使用全表扫描，索引作废。
3. 尽量避免在where中进行null值的判断，否则将使用全表扫描，索引作废。例如：select  * from table_name where name = null  ；   解决办法就是将表中的name字段默认值设置为“”（空字符串），剔除null值的存在，查询时使用select  * from table_name where name = ‘’。
4. 不在查询条件中使用or关键词，否则将使用全表扫描，索引作废。例如：select * from table_name where age = 18 or age = 19 ;  解决办法是使用 union all 关键词，如下：select * from table_name where age = 18  union all  select * from table_name where age =19 。
5. 尽量不使用模糊查询。例如：select *  from table_name where name like '张%' 。
6. 尽量不使用in 和not  in进行判断，连续的数值可以使用between。例如：select * from table_name where age in(18,19,20,21); 优化成：select * from table_name where age between 18 and 21 ;
7. 尽量不在where 条件语句中使用参数。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下语句将进行全表扫描： select id from t where age = ${age}。 可以改为强制查询使用索引：  select id from t with ( index(索引)) where age=${age} 。 暂时还没理解什么意思。
8. 尽量避免在 where 子句中对字段进行表达式操作，否则将使用全表扫描，索引作废。如下 ： select * from user where salary/2 = 2000 ;   可以优化成select * from user where salary = 2000*2 ; 
9. 尽量避免在where子句中对字段进行函数操作，否则将使用全表扫描，索引作废。例如：select * from user where  substring(name,0,2)='abc' 。 这个语句可以查询出所有姓名以abc开头的信息。优化为 select * from user where  name like 'abc%' ; (好像函数操作的问题比模糊查询的问题更严重，<> ! 操作符也是如此。)
10. 不要在 “=”  进行判断以及函数操作和计算。
11. 在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
12. 学会用exists 替代in。 select * from a where age in (select age from b) 用下面的语句替换： select age from a where exists(select age  from b where age =a.age)。
13. 不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段name，age 、id，nation几乎各一半，这时即使在name上建了索引也对查询效率起不了作用。
14. 索引并不是越多越好，索引在提高 select语句效率的同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以建索引需要视具体情况而定。建议一张表的索引不超过6个。
15. 应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。
16. 尽量使用数字型字段。若一个字段只含数字形式的信息，则尽可能的设计成数值类型，否则会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
17. 尽可能的使用 varchar/nvarchar 而不是 char/nchar ，因为变长字段存储空间小，可以节省存储空间。而且对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
18. 尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。
19. 避免频繁创建和删除临时表，表结构越稳定，越能减少对系统资源的消耗，sql 效率越高。
20. 但对于一次性事件，最好使用导出表。尽量不使用临时表。
21. 在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。
22. 如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。
23. 尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。
24. 使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。
25. 与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。
26. 在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。
27. 尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
28. 尽量避免大事务操作，提高系统并发能力。
29. 查询时尽量返回具体的字段，避免返回无用信息。不要出现select  * from user，要多使用 select name，age from user。本文只是为了举例方便才用到了* 。

总而言之，就是尽可能的避免全表扫描。