SQL查询优化

记点SQL查询的东西:

1，查询的模糊匹配：

　　尽量避免在一个负债查询里面使用LIKE-"%xx%"，"%"会导致相关列的索引无法使用，最好不好用，解决办法：

　　根据输入条件，先查询和确定符合条件的结果，并把相关记录保存在一个临时表中，然后再用临时表去做复杂关联。

2，索引问题

　　经常发现有很多后台程序的性能问题是因为缺少何时索引造成的，有的表甚至没有索引。这种情况往往是因为在设计表时，没有定义索引，而开发初期，由于表中的数据不多，所以感觉不到索引对性能的影响，但是一旦项目发布，表中数据多了之后，缺少索引对性能的影响便会越来越大。

　　还有不要在建立索引的数据列上进行一下操作：

　　（1）避免对索引字段进行计算操作；

　　（2）避免在索引字段上使用not、<>、!=；

　　（3）避免在索引字段单使用 IS NULL、IS NOT NULL；

　　（4）避免在索引字段单出现数据类型转换；

　　（5）避免在索引字段上使用函数；

　　（6）避免建立索引的列中使用空值。

3，复杂操作

　　部分SQL语句写的很复杂（经常嵌套多级子查询），这样也会对性能造成一定的影响，而且不利于以后的维护工作。可以考虑适当拆成几步来完成，先生成一些临时数据表，再进行关联操作。

4，UNION ALL \ UNION

　　在可以使用UNION ALL的语句中，使用了UNION。UNION 因为会将各查询子集的记录做比较，故比起UNION ALL性能上会差很多。一般来说，如果使用UNION ALL能满足要求的话，务必使用UNION ALL。

　　还有一种情况是，就是虽然要求几个子集作并集需要过滤掉重复记录，但是由于脚本的特殊性，不可能存在重复记录，这是便应该使用UNION ALL。

5，对于WHERE语句的法则：

　　（1）尽量避在WHERE子句中使用in、not in或者having，可以使用exist、not exist代替in、not in，因为in相比exist会有比较操作，参考：http://stackoverflow.com/questions/24929/difference-between-exists-and-in-in-sql；

　　（2）不要以字符格式声明数字，要以数字格式声明字符值，日期同样，否则会使索引无效，产生全表扫面。例如： 

SELECT no,name WHERE no = 1111
而不要使用：
SELECT no,name WHERE no = '1111' 

 

　　（3）尽量避免在WHERE子句中对字段进行null值判断，否则将导致引擎放弃索引而进行全表扫描，如：SELECT name FROM T WHERE no is null;可以在no上设置默认值0，确保表中no列没有null值，然后这样查询SELECT name FROM T WHERE no = 0;

　　（4）尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描；

　　（5）尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如： 

　　　　select id from t where num=10 or num=20
　　　　可以这样查询：
　　　　select id from t where num=10
　　　　union all
　　　　select id from t where num=20

　　（6）in 和 not in 也要慎用，否则会导致全表扫描　　

6，对于SELECT语句，用具体的字段列表代理*

7，排序

　　避免使用消耗资源的操作，例如DISTINCT、UNION、MINUS、INTERSECT、ORDER BY的SQL语句会启动SQL引擎执行消耗资源的排序功能。

　　DISTINCT需要一次排序操作，而其他的至少需要执行两次排序。

8，临时表

　　慎重使用临时表可以极大的提高系统性能。

9，其他

　　（1）在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则索引是不会被使用的，并且应可能让字段顺序与索引顺序相一致。

　　（2）并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引， 如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

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

　　（4）尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。 这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

　　（5）关于nvarchar、varchar、nchar、char的选择：参考文章：http://www.cnblogs.com/lichang1987/archive/2009/03/04/1403166.html，现在是基本上使用nvarchar因为varchar最终也会转成nvarchar？

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

　　（6）尽量避免使用游标，因为游标的效率较差，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效

　　（7）尽量避免使用大事务操作，提高系统并发能力

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