写有效率的sql

目的：SQL的执行次数、逻辑IO、物理IO、执行消耗CPU时间等

方法：

• where 条件下面使用索引字段，无法估算不同查询计划开销优劣，而只能采用最稳妥的Scan（不管是table scan还是clustered index scan）。
• 尽量不使用不等于（!=）或者NOT逻辑运算符，索引是一颗B树，index seek是查找从B树的根节点开始，一级一级找到目标行。index scan则是从左到右，把整个B树遍历一遍。假设唯一的目标行位于索引树（假设是非聚集索引，树深度2，叶节点占用k页物理存储）最右的叶节点上，index seek引起的IO是4，而index scan引起的IO是K，性能差别巨大。关于索引，可以仔细读读联机文档关于物理数据库体系结构部分
• 查询条件中不要包含运算，

  这些运算包括字符串连接（如：select * from Users where UserName + ‘pig’ = ‘张三pig’），通配符在前面的Like运算（如：select * from tb1 where col4 like ‘%aa’），使用其他用户自定义函数、系统内置函数、标量函数等等（如：select * from UserLog where datepart(dd, LogTime) = 3）。

           在处理以上语句时，一样没办法估算开销。最终结果当然是clustered index scan或者table scan了

• 查询条件中不要包含同一张表内不同列之间的运算，

  所谓的“运算”包括加减乘除或通过一些function（如：select * from tb where col1 – col2 = 1997），也包括比较运算（如：select * from tb where col1 > col2）。这种情况下，一样没办法估算开销。不论col1、col2上都有索引还是创建了col1、col2上的覆盖索引还是创建了col1 include col2的索引。

  但是这种查询有解决办法，可以在表上多创建一个计算字段，其值设置为你的“运算”结果，再在该字段上创建一个索引，就Ok了

• Log类的表，不要有事没事加个自增的Id列。这事相信干过的人很多，哈，而且一般还会顺手在这个Id列上加上个primary key的约束，聚集索引也就被无意识的建上了。就像一个记录用户活动的日志表，一般会有这么几个典型字段：Id、LogTime、UserId。实际上对这种表的查询，大多集中在LogTime和UserId上，Id完全没有实际意义。你的客服系统查找的，可能仅仅是某个用户的操作记录（一般按时间排序的），或者你的报表系统要生成每天的用户操作统计。想想看，如果干脆砍了Id列，并直接把聚集索引创建在LogTime上多爽。

• 不要是个表就给加个primary key约束就像上面的例子，primary完全没必要。呵呵，这条看着简单是简单了，犯这错误的人，那也不比3少

• 在where条件里对同一个表中的列做运算或比较，以为创建某种类型的索引可以提高效率。（这种情况下，任何索引都无法提升性能。）见过了无数的这种写法。最常见的，如：一张用户表里有用户注册时间（t1,YYYYDD），有退订时间（t2,YYYYDD），现在让你获取存活时间大于3天的用户总数：很多人一不注意，就整一个select count(*) from Users where t1 – t2 > 3出来。而且常常会臆测在t1、t2上建个涵盖索引（或者分别在t1、t2上建索引）会让性能提升。

• 在表上创建了col1、col2顺序的涵盖索引（聚集的或非聚集的），但是where条件里就一个col2 > XXX。这种情况下，就不如分别在col1、col2上创建索引

• 存储过程中使用局部变量而不使用参数变量（就是存储过程输入参数）做where条件

• 查询条件中的类型不匹配。所谓的类型不匹配是说，查询条件where AAA = @var，列AAA的定义和@var不同。例如，AAA是varchar(64)，@var是bigint。这种情况下，非常有可能让本来是index seek的运算变成index scan，在大数据量表中，性能差距会非常明显。