（3.10）常用知识-T-SQL优化

关键字：SQL优化

总结：

1.书写问题

2.表连接方式

3.索引的抉择

4.执行计划之参数嗅探，使用提示强制执行计划

5.子查询与表连接的效率

6.临时表、CTE、表变量的选择  

7.常用sp与select的缓存命中

8.锁（善用nolock，注意跳读与重复读）与事务（显示与隐式以及自动）

　　with(nolock) 包括锁定的行
　　with(readpast) 排除锁定的行

9.索引碎片与填充因子

10.慢查询处理

11.统计信息

12.开区并行

1.代码规范建议

　　（1）注意代码格式：代码层次结构清晰，不要一行87个字。使用同一风格，比如都用tab或者空格或者缩进，不要混杂着用。所有关键字及系统对象都用大写，增加可读性。还有一定要有注释（建议一般放在create前面）。

　　（2）使用两部分对象名：不要直接使用对象名等情况，最好加上架构名，以便多个架构相同对象弄混淆。

　　（3）避免select * ：容易导致潜在错误，如果有顺序更改或者表结构变动。可能会导致出很多问题，比如列对应混淆，操作到了错误的列数据。

　　（4）明确对象名：在表连接的时候（我个人认为即使在单表的时候），也一定要记得给表对象起别名，所有的select中都用别名.列名的方式使用。因为如果一旦sql有变动，join了有重复列名的表对象，那么会出问题或者改动非常多。

　　（5）insert中明确列名：在使用Insert插入数据时，必须在insert into table(column1,column2...) 指定好列名，便于字段增删等扩展造成数据不对应，也避免混淆。

　　（6）善用(nolock)：这个句子啊，一般是这么用 select * from table with(nolock)  意思是本次操作不锁表，对于一些不需要实时性数据的操作，应该使用它避免影响正在使用的业务（但可能会造成脏读等一系列问题）。

　　（7）保证事务配对：这个我亲身试过，有一次做调试，走的时候忘记结束调试/运行完，导致生产环境都查不到这个表的数据。所以一定要begin   --   commit/rollback 配对使用。还有，调试完再走，或者加nolock。

　　（8）调试语句注释掉：这个锅我也背过，在调试的时候使用了很多print来查看数据变化记录，但是在生产环境的时候没有去掉，其实会导致输出很多垃圾信息，并且会影响性能。

　　（9）条件中的类型转换：避免做隐式转换，尽量用cast于convert来显式转换。此举是为了避免类型转换溢出问题，以及性能问题。

　　（10）使用union all 替代 union：当查询本身能够去重的时候，用union all就好了，union多了去重的一步，而且还会排序（自动升序排序），你永远都不知道排序的代价有多大，特别是两个大结果集的时候。

　　（11）避免使用负向条件：使用IF时，如果满足条件和不满足条件都会做出相应的处理，不建议使用not做负向判断，负向判断比正向判断效率低。

　　（12）处理null值：使用isnull(可能为null的列，如果为null所给出的值)

　　（13）正确的选择临时表和表变量以及CTE；CTE不能保存，表变量不能建立索引，建议在小数据量的时候用表变量，大数据量用临时表。

　　　　　详情可以参考本系列3.8（https://www.cnblogs.com/gered/p/9146484.html）。

　　（14）使用结构化错误处理：就是用try...catch的方式实现

　　（15）善于使用@@rowcount：@@rowcount用于返回受上一语句影响的行数。

2.T-SQL使用建议

　　（1）为外键建立索引：不建的话，表链接的效率会很差

　　（2）表一定要有聚集主键或聚集索引：没有主键的话，会导致数据重复，没有聚集索引的话，堆扫描会很慢。

　　（3）不要乱建索引：索引不是越多越好，错误的建立索引会导致表索引很大并且扫描效率甚至还不如全表扫。建议不超过5个。

　　（4）考虑select语句的错误处理：select语句也会出错，比如除零错误，或是引用对象出错等等，所以在错误处理（特别是事务处理）的时候，应该考虑进去

　　（5）包含聚集函数的select会返回记录：比如你用IF EXISTS( SELECT COUNT(*) FROM ...)，怎么都会成立的，因为就算空数据也会得到0。

　　（6）声明变量时指定长度：比如 declare @str varchar，也能定义变量，但是不定义长度默认长度为1

　　（7）数据类型的选择：

　　　　【1】尽量使列短：空间少，页少，IO就少

　　　　【2】VarChar(Max),Nvarchar(Max),varbinary(Max)来代替text,ntext和image类型：因为text/ntext/image即使占很小的数据也要占一整个lob页。就如同char与varchar一样，不管用多少char都会占定义大小的空间，而varchar是存多少数据占多少空间。

　　　　【3】对于仅仅存储数据的列使用数字类型：比如1234567使用int就好了4个字节，用varchar的话就要用9个字节.

　　　　【4】如果没有必要，不要使用NVARCHAR 等N开头的Unicode字符编码的数据类型，因为它占的空间是varchar中1个字符的2倍。

　　　　【5】关于char与varchar：参考3.4（https://www.cnblogs.com/gered/p/9141849.html）

　　　　【6】注意NULL：其与任何值做算数运算均为NULL，做逻辑运算避免出现三值逻辑（true,false,unknow）应该只有二值逻辑(true,false)，在表创建时使用not null 或default 来避免。

　　　　【7】考虑使用稀疏列(Sparse)： 稀疏列是对 Null 值采用优化的存储方式的普通列。 稀疏列减少了 Null 值的空间需求，但代价是检索非 Null 值的开销增加。 当至少能够节省 20% 到 40% 的空间时，才应考虑使用稀疏列。（具体可以参考：https://www.cnblogs.com/chenxizhang/archive/2009/04/25/1443287.html）

　　　　【8】时间列的选择：最好使用日期类型而不是字符串类型，会影响性能和内置日期函数的使用。

　　（8）使用schema.对象名：不用schema会多一些解析，而指定Schema.Table这种方式就避免了这种无谓的解析。还有一点是，Schema使用的混乱有可能导致更多的执行计划缓存，换句话说，就是同样一份执行计划被多次缓存

　　（9）大数据量插入：使用CTE，尽量不要用循环，用循环也要包裹在一个事务里。这点其实显而易见。SQL Server是隐式事务提交的，所以对于每一个循环中的INSERT,都会作为一个事务提交。这种效率可想而知，但如果将1000条语句放到一个事务中提交，效率无疑会提升不少。打个比方，去银行存款，是一次存1000效率高，还是存10次100？

　　（10）列值不使用函数或表达式：因为这样会造成数据列做大量计算，并且不会走索引（除非有函数索引或者表达式索引）

　　（11）order by ：不要用order by 1 这种数字的形式，这样当表结构或者Select之后的列变化时，这种方式会引起麻烦，所以老老实实写上列名。

　　（12）使用索引：在t-sql中很多方式都会导致索引无效，比如<>、like '%str'，not in与in也不要用除非值很少，给列加表达式或者函数（除非有表达式或者函数搜因）

3.慢查询其他方面的优化

　　（1）统计信息：就是数据库预估的表/列/索引信息，以便根据它生成好的执行计划。（关于统计信息详情参考：https://www.cnblogs.com/gered/p/9087055.html）

--列出表中所有统计信息
select * from sys.stats where object_id=OBJECT_ID(N'dbo.test103')

--查看统计信息及其列  
SELECT s.name AS statistics_name ,c.name AS column_name ,sc.stats_column_id  
FROM sys.stats AS s  
INNER JOIN sys.stats_columns AS sc ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id  
INNER JOIN sys.columns AS c  ON sc.object_id = c.object_id AND c.column_id = sc.column_id  
WHERE s.object_id = OBJECT_ID(N'[Sales].[SalesOrderDetail]');  
  
--查看所有统计信息更新时间  
exec sp_helpstats N'[Sales].[SalesOrderDetail]', 'ALL' 

--还可以使用命令DBCC SHOW_STATISTICS查看，以下为列。  
DBCC SHOW_STATISTICS('[Sales].[SalesOrderDetail]','IX_SalesOrderDetail_ProductID')  


--查看只索引的统计信息更新时间  
SELECT name AS index_name,STATS_DATE(object_id, index_id) AS update_date  
FROM sys.indexes   
WHERE object_id = OBJECT_ID('[Sales].[SalesOrderDetail]');  
  
--查看所有统计信息更新时间  
select s.name,STATS_DATE(s.object_id, stats_id) AS update_date  
from sys.stats s   
WHERE s.object_id = OBJECT_ID('[Sales].[SalesOrderDetail]');  
  
--查看所有统计信息更新时间  
exec sp_helpstats N'[Sales].[SalesOrderDetail]', 'ALL'  
GO  
  
--创建统计信息  
CREATE STATISTICS [_WA_user_00000001_00000001] ON [Sales].[SalesOrderDetail](ProductID, SalesOrderDetailID)    
  
--查看某个统计信息  
DBCC SHOW_STATISTICS('[Sales].[SalesOrderDetail]','_WA_user_00000001_00000001')  
  
--更新1个统计信息  
UPDATE STATISTICS [Sales].[SalesOrderDetail] [_WA_user_00000001_00000001] WITH FULLSCAN  
  
--更新表的所有统计信息  
UPDATE STATISTICS [Sales].[SalesOrderDetail]   
  
--更新数据库中所有可用的统计信息   
EXEC sys.sp_updatestats   
  
--删除统计信息  
DROP STATISTICS [Sales].[SalesOrderDetail].[_WA_user_00000001_00000001] 

　　（2）索引碎片与高水位问题（关于索引碎片与填充因子详情可以查看3.3：https://www.cnblogs.com/gered/p/9135379.html）

　　　　索引碎片指的是内部更新字段边长或外部插入聚集间隔数据导致页分裂。

　　　　高水位问题指的是删除大量表数据后，数据库不会释放空间，只会重用空间，但是在查询等情况的时候，仍然会把空的页扫描进去，增加了IO开销。

　　　　应对方法：索引碎片重整或重建

　　　　【1】查看索引碎片信息

--查看索引碎片信息
select 
　　db_name(database_id) as '数据库名',
　　object_name(object_id) as '表名',
　　index_id as '索引id',
　　partition_number as '当前索引所在分区',
　　page_count as '页统计',
　　avg_page_space_used_in_percent as '页使用率' ,
　　record_count as '页行记录数',
　　avg_record_size_in_bytes as '平均每条记录大小(B)',
　　avg_fragmentation_in_percent as '索引碎片比率',
　　fragment_count as '索引中的碎片数量',
　　avg_fragment_size_in_pages as '一个索引中碎片的平均页数'
from sys.dm_db_index_physical_stats(db_id('master'),NULL,NULL,NULL,NULL)

　　　　【2】对于碎片的解决办法

　　　　　　基本办法：

　　　　　　【1】当碎片>5% and <30%时： 使用 使用ALTER INDEX REORGANIZE整理碎片。

　　　　　　【2】当碎片>=30%时：　ALTER INDEX REBUILD WITH(ONLINE)　来解决索引碎片。

    基本上所有解决办法都是基于对索引的重建和整理，只是方式不同

    1.删除索引并重建

       这种方式并不好.在删除索引期间，索引不可用.会导致阻塞发生。而对于删除聚集索引，则会导致对应的非聚集索引重建两次(删除时重建，建立时再重建).虽然这种方法并不好，但是对于索引的整理最为有效

    2.使用DROP_EXISTING语句重建索引

       为了避免重建两次索引，使用DROP_EXISTING语句重建索引，因为这个语句是原子性的，不会导致非聚集索引重建两次，但同样的，这种方式也会造成阻塞

    3.如前面文章所示，使用ALTER INDEX REBUILD语句重建索引

       使用这个语句同样也是重建索引，但是通过动态重建索引而不需要卸载并重建索引.是优于前两种方法的，但依旧会造成阻塞。可以通过ONLINE关键字减少锁，但会造成重建时间加长.

    4.使用ALTER INDEX REORGANIZE

       这种方式不会重建索引，也不会生成新的页，仅仅是整理，当遇到加锁的页时跳过，所以不会造成阻塞。但同时，整理效果会差于前三种.

　　（3）锁（详情查看本系列3.11：）

--查看用户级进程
select start_time,command,percent_complete,text,
session_id,blocking_session_id
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(r.sql_handle) s

--有阻塞的可以直接kill其id

　　查看是否有锁等待的问题，或者锁占用（有一个sql长期运行没有结束，其他事务需要相关资源的只好等待）与死锁（就是你需要我的才能做事，我需要你的才能做事，但是大家又都不能给对方，需要等一个人结束，两个人就一直等下去）问题。不过SQL SERVER有死锁机制，会根据情况牺牲掉一个作为祭品。

　　（4）表连接方式（详情参考：https://www.cnblogs.com/gered/p/8568085.html）

　　　　【1】nested loop join: 当一个表很小，另一个表很大时，一般使用loop join。运行过程是基于小表中的每一行，在大表中搜索对应的匹配行。

　　　　　　例如：表A共100行，表B共1000万行。loop join则用表A中的数据一条一条去表B中找匹配的行。100次查找即可完成查询。
　　       【2】merge join: 当两个表都很大，但是关联字段已经经过排序了。运行过程是比较两个input数据集的头，如果相等就输出，如果不相等就丢弃值低的那个。

　　　　　　这个和数据结构中，merge sort是一样的。例如： TableA join TableB on TableA.id = TableB.id … 

　　　　　　如果两表的id字段正好是聚集索引，也就是说id字段已经排序过了，此时就会使用merge join。另外由于子查询等等原因也都可能使字段已经是隐含有序了。

　　　　【3】Hash join: 适用于较大的表，未排序且没有索引的键值的join。由于没有其他提高效率的方法，才退而求其次使用Hash join，因此hash join性能较差。
学过数据结构的同学应该知道，hash join最大的问题是，join前需要计算出所有join key的hash值。最大的优点是一旦有了hash值，查询特定值的时间是常量。Hash join通常是效率最差的join方式，但对于一类特殊情况则是不错的选择：
当表很大且join字段为长字符时，通常即使建立了索引，join的效率也会比较低。此时可在表中新增一个字段，用于存储事先计算好的join字段的hash值。此时就可直接使用hash字段做join，从而提高了效率。该解决方案的限制是，如果join字段值经常发生变化，则需要频繁更新hash字段中的hash值，从而导致效率低下。

　　　　【4】Remote join: remote join适用于参与join的两个表一个在本地，另一个在远程的情况。我们在使用linked server时常常遇到这种情况。

　　　　如何强制使用表连接方式？

--一般形式
<join_hint> ::=   
     { LOOP | HASH | MERGE | REMOTE }  

--loop举例
FROM Sales.SalesPersonQuotaHistory AS spqh  
        INNER LOOP JOIN Sales.SalesPerson AS sp  
        ON spqh.SalesPersonID = sp.SalesPersonID  
      WHERE sp.SalesYTD > 2500000.00;

--其他使用也是放在同一个位置，也可以这样

option(Hash/Loop/Merge Join) 

 　　如何使用强制的执行计划策略？在sql server中使用提示。（详细请参考：https://www.cnblogs.com/gered/p/8833737.html）

1、在计划中看到不是你想要的索引时，看能否在语句中强制使用你想用的索引解决问题，强制使用索引的办法Select CluName1,CluName2 from Table with(index=IndexName)。 

2、看到不是你想要的连接算法时，尝试强制使用你想要的算法解决问题。强制使用连接算法的语句：select * from t1 left join t2 on t1.id=t2.id option(Hash/Loop/Merge Join) 

3、看到不是你想要的聚合算法是，尝试强制使用你想要的聚合算法。强制使用聚合算法的语句示例：select  age ,count(age) as cnt from t1 group by age  option(order/hash group)

4、看到不是你想要的解析执行顺序是，或这解析顺序耗时过大时，尝试强制使用你定的执行顺序。option（force order）

5、看到有多个线程来合并执行你的sql语句而影响到性能时，尝试强制是不并行操作。option（maxdop 1）

6、在存储过程中，由于参数不同导致执行计划不同，也影响啦性能时尝试指定参数来优化。option（optiomize for（@name='zlh'））

 如何强行开启并行

option (querytraceon 8649) 开启强制并行，个人认为这个提示真心是个好东西（2005不支持），sql优化器经常会让一个开销较大的语句使用串行（稍后发文），这个时候当你加上option (querytraceon 8649) 会吓你一跳 30秒变2秒？

　　那么为什么SQL优化器生成一个串行计划，而不是“明显更好的”并行计划，总有一个原因。配置设置被设置为一个最大程度的并行（前面的maxdop 1），或者只有一个逻辑处理器可用的SQL服务器，或并行抑制操作，基数估计错误，成本核算模型的局限性。

　　　　抛开其他因素我们来说一下因为语句写法而造成的优化器不能选择并行计划，大致一下几点：

• 修改表变量操作（查询是可以的）
• 使用标量函数 （这个是最常见查询不能开启并行的原因）
• CLR执行数据访问的标量函数。
• 随机的内在功能，如：object_name，encyptbycert等。
• 使用系统表，如：sys.tables。

　　　　还有一些功能是不能并行完成的，举几个常用情况如：

• top
• row_number
• 多语句表值函数。
• 递归CTE

　　　　提到并行就一定要提一下maxdop了，调整好这个参数也是很必要的，不一定是越大越好哟~ 等待类型CXPACKET很大程度上是因为过度并行导致的等待。

 参考文章：

1.t-sql中的十大注意事项：http://www.cnblogs.com/gered/p/8358762.html

2.https://www.cnblogs.com/gered/p/9528296.html