SQL优化（二）

对于sql的优化，从前面也可以知道一些相关的知识，包括对left join和笛卡尔乘积等的运用。现在就从一些基本的数据库设置和sql
语句的优化下手。
①、对于查询的优化，要尽量的去避免全表的扫描，应该考虑在where和order by涉及到的表上建立索引。
②、要尽量避免在where字句中对字段进行null判断，要不然的话会导致引擎放弃使用索引从而进行全表扫描。
例如：  select id from student where name is null；
能做到话就尽量不要给数据库留null值，可以使用not null来设置数据。
因为在数据库中null也是需要空间来存储的，如：char（100），创建字段的时候，这个空间就会创建好，不管你是插入什么值，都是
会占用100个字符的空间，但是varchar和char不同，如果插入的值是null的话，则varchar是不会让null占用空间的。
③、要尽量避免在where字句中使用！=或<>等操作符,和使用模糊查询的like，这样也是会导致引擎放弃使用所以而进行全表扫描，如果想要
提高效率的话，可以建议索引来实现全文检索。
④、应该尽量避免在where字句中使用or来连接条件，如果一个字段有索引，一个字段没有索引，将会导致引擎放弃使用索引而全表扫描。
如： select id from student where num = 10 or Name = 'admin';
使用or的操作可以使用union all来替代
如：

		select  id from  student where num = 10
		nion all
		select  id  from student where Name = 'admin';

⑤、在语句中，in和not in也要小心使用，否则会导致全表扫描。
如： select id from s where num in(1,2,3);
如果in里面的数据是连续的话，建议使用between来使用
select id from s where num between 1 and 3;
在使用in后面连接的是表数据时，可以使用exists来替代in
例如： select id from a where id in (select id from b);
替换为：
select id from a where exists (select id from b where id = a.id);
⑥、如果在where子句中使用参数，也是会导致全表扫描的。因为sql只有在运行的时候才会解析局部的变量，但是优化程序不能将访问
计划的选择推迟到运行时；所以它必须在编译时进行选择。然而，如果在编译时候建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。
如： select id from t where num = @num；
可以修改为强制查询使用索引；
select id from t with (index(索引名)) where num = @num
还有就是在where子句中应尽量避免对字段进行表达式的操作，这样是会放弃索引进行全表扫描的。
如： select id from t where num/2=100;
修改为： select id from where num = 100*2;
⑦、尽量避免在where子句中对字段进行函数操作，这也是会进行全表扫描的。
例如：

	select id from t where substring(name,1,3) ;
	select id from t where datediff(day,createdate,'2005-11-30');

可以修改为：

	select id from t where name like 'abc%';
	select id from t where createdate>='2005-11-30' and  createdate<'2005-12-1';

⑧、在where子句中的“=“左边进行函数、算术运算或者其他表达式运算，这些操作也会导致索引的运用不当。
在使用索引字段作为条件的时候，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，
否则索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
⑨、使用update语句的时候，如果是更新一两个字段，则不要选择update全部字段，这样的话会带来对性能的消耗，同时会带来大量的日志。
10、对于大数据量的查询，可以先分页后在join连接，这样的逻辑读取会比较高，性能更好。
对于select count（*） from table ；这样不带任何条件的count是全表的扫描，这是不好的。
11、.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。
一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有 必要。
12、应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引，尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。
这是因为引擎在处理查询和连 接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
13、尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要些。
14、任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
15、如果使用了临时表，在存储过程中的最后要讲所有的临时表显式的删除，先truncate table，然后在drop TABLE，这样可以避免系统表的较长时间的锁定。
同时也要尽量避免的使用游标，游标的效率较差，如果游标操作的数据太大，则需要考虑改写。
16、使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时 间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。