SQL优化

1. 全列查询，坚持不用“*”

   --不推荐
   select * from user
   --推荐
   select id
          ,name
          ,password
          ,status
          ,address
          ,sex
   from user
   

2. 能用exists ，尽量不用in

   --不推荐
   select id,
          name
   from user 
   where id in (select id from usernew)
   --推荐
   select id,
          name
   from user
   where exists(select 1 from usernew where user.id=usernew.id)
   

3. CURSOR游标能不用就不用，效率差

   --游标示例
   --声明我们需要读取的变量（id ,name) 
   DECLARE @id INT , @name NVARCHAR(50)
   --声明静态游标
   DECLARE cur CURSOR STATIC
       --需要游标的数据
       SELECT  id,name FROM   user
   --打开游标
   OPEN cur
   --取数据
   FETCH NEXT FROM cur INTO @id, @name
   --判断是否还有数据
   WHILE ( @@fetch_status = 0 )            
       BEGIN
          UPDATE #user SET name='山行者' WHERE id=@id
           FETCH NEXT FROM cur INTO @id, @name   --取下一条数据
       END
   --关闭游标
   CLOSE cur
   --释放游标
   DEALLOCATE cur
   

4. 尽量union all 代替 or （or：放弃引擎，全表索引）

   --不推荐
   select id,
          name
   from user
   where id = 1 or id =2
   --推荐
   select id,
          name
   from user 
   where id=1
   union all
   select id,
          name 
   from user 
   where id=2
   

5. in、not in 慎用，属全表扫描，能采用between ，就采用这个。

   --不推荐
   select id,
          name
   from user where id in (1,2,3,4,6)
   --条件允许，推荐
   select id,
          name
   from user 
   where between 1 and 4
   

6. count(1)比count（*），更高效

   --不推荐
   select count(*)
   from user
   --推荐
   select count(1)
   from user