一概述
sql语句的运行时间一般会主要花在3步：语句编译，语句执行，结果集返回.
结果集返回的速度一般和sql server 自身没有太大关系，所以一般不会在语句调优的时候考虑。
所以语句调优时，focus在于编译和语句执行各花了多少时间，哪一段时间有优化空间，以及怎么优化
二常见优化套路
1 表关联的时候 最好缩小范围之后再关联，这样耗费内存少。
2 条件都出在一张表，为什么不把刷选都做掉，再join呢？——这样join的数据集小，
3 搜索条件多的，可以考虑使用动态sql——考虑根据是否传参，拼接where条件以及join表——case when
4 order by 排序，表关联on——考虑索引
5 尽量不用视图，启用物理表，表关联的时候 最好缩小范围之后再关联，这样耗费内存少。
6 单表——索引
7 多表——join 顺序,关联条件加索引，尽量不要join视图三 优化指北
1 索引 待续：《索引的本质》 《数据库内部存储》
业务系统大多数时间基本上是排序和查找。索引=隐式排序+有序查找
缺点：索引建太多的话，会占存储空间。索引越多统计信息越多。写入会变慢。2 事务与隔离级别 待续 ：《事务与隔离级别》
1 READ_COMMITTED_SNAPSHOT隔离级别 可以解决读写互斥
缺点：一次update，tempdb存一份之前的数据，这是update的性能取决于tempdb的IO。如果tempdb在一个烂磁盘上，那么就凉凉了
2 with nolock： 是一种不请求锁的机制。
缺点： 查询有脏数据。不过呢，如果不care脏数据，可以使用。3 T-sql 存储过程化
减少重编译
遇到逻辑修改，直接修改db，不需要程序再次发布，节约人力和时间双重成本4 科学设计表结构， 表结构是根本.
一个好的表结构：
1 避免数据倾斜，进而避免参数嗅探
2 后期易n次迭代开发
3 易维护与优化5 适当反范式
我们遵守数据库第三范式的同时，应该考虑适当反范式，减少过多的表关联6 热点表的优待
1 热点表应放在吞吐好的磁盘
2 同时设置文件组，将数据文件分配到不同磁盘，以避免IO瓶颈7 tempdb的优待 待续《tempdb的作用》
同理，tempdb数据库也应该放在性能好的磁盘，避免tempdb文件头争用四例子
经典查询：分页
1 多条件搜索，应该使用动态sql。拼接sql
2 使用动态sql 需要注意：sql注入
3 根据参数是否传入，拼接join表，以及where条件——应使用case when 应付多变性
CREATE PROC [dbo].[SP_GET_Person]
@ZoneID INT=NULL,
@Name VARCHAR(50)=NULL,
@RoomNo VARCHAR(50)=NULL,
@StartNum INT=0,
@EndNum INT=10
AS
BEGIN
DECLARE @Sql NVARCHAR(MAX);
DECLARE @Sql_TotalCount NVARCHAR(MAX); --检索返回的总数
DECLARE @Sql_Paged NVARCHAR(MAX); --分页
DECLARE @Sql_Common NVARCHAR(MAX);--总数和分页逻辑相同的sql脚本(表join和where条件)
-----总数-----
SET @Sql_TotalCount=N'
DECLARE @TotalCount INT;
SELECT @TotalCount=COUNT(*)'
SET @Sql_Common=N' FROM [dbo].[EST_House] HH'
+CASE WHEN @ZoneID IS NOT NULL OR @Name IS NOT NULL
THEN '
JOIN [dbo].[EEEE] E
ON HH.ID=E.ID'
ELSE '' END
+CASE WHEN @ZoneID IS NOT NULL THEN
'JOIN [dbo].[Zone] K
ON E.ZoneID=K.ZoneID'
ELSE '' END
+' WHERE HH.IsDelete = 0 AND HH.IsRecycleBin = 0'
+CASE WHEN @ZoneID IS NOT NULL THEN
' AND K.ZoneID =@VAR_ZoneID'
ELSE '' END
+CASE WHEN @Name IS NOT NULL THEN
' AND E.Name LIKE ''%''+@VAR_Name+''%'''
ELSE '' END
+CASE WHEN @RoomNo IS NOT NULL THEN
' AND HH.RoomNo LIKE ''%''+@VAR_RoomNo+''%'''
ELSE '' END
-----分页-----
SET @Sql_Paged=N'
SELECT TOP(@VAR_EndNum)
RowNumber=row_number() OVER (ORDER BY HH.updatetime DESC)
,HH.[House]
INTO #Temp1 '+@Sql_Common+N'
SELECT * INTO #Temp2 FROM #Temp1 WHERE RowNumber>=@VAR_StartNum;
SELECT
TotalCount=@TotalCount
,RowNumber
,E.[ZoneID]
,K.[ZoneName]
,E.[Name]
,B.[BuildingName]
,HH.[RoomNo]
FROM #Temp2 H
JOIN [dbo].[House] HH
ON H.[HouseID]=HH.[HouseID]
JOIN [dbo].[EEEE] E
ON HH.EID=E.EID
JOIN [dbo].[Zone] K
ON E.ZoneID=K.ZoneID
ORDER BY H.RowNumber;
'
SET @Sql=@Sql_TotalCount+@Sql_Common+@Sql_Paged
PRINT @Sql
EXEC SP_EXECUTESQL @Sql,
N'
@VAR_ZoneID INT,
@VAR_Name VARCHAR(50),
@VAR_RoomNo VARCHAR(50),
@VAR_StartNum INT,
@VAR_EndNum INT',
@VAR_ZoneID=@ZoneID,
@VAR_Name=@Name,
@VAR_RoomNo=@RoomNo,
@VAR_StartNum=@StartNum,
@VAR_EndNum=@EndNum
END
GO
