[草稿]sqlserver调优--语句调优

读懂执行计划

如何得到执行计划

读懂语句执行的统计信息

请参考：《读懂语句执行的统计信息》

表关联的三种算法

常见操作符

其他

用 exists 代替 in

很多时候用 exists 代替 in 是一个好的选择，比如：

select num from a where num in(select num from b)

用下面的语句替换：

select num from a where exists(select 1 from b where num=a.num)

优化where子句

并非周知的SQL调优

场景1：谁说status字段不适合创建索引?

大家都知道“不应该给选择性低的字段建单列索引”，但在特定场景选择性低的字段也适合创建索引。

比如，某个优惠活动记录表，字段FailReason（0成功/1失败/2进行中）的数据分布如下：

SELECT FailReason, COUNT(1) AS cnt
FROM CouponSendRecord WITH(NOLOCK)
GROUP BY FailReason
ORDER BY cnt DESC

--返回结果
FailReason     cnt
0               7102981   --0成功
1               372       --1失败
2               8         --2进行中

与开发讨论得知，程序会根据失败原因（FailReason>0）补发消息，由于异常的数据仅有380行，此时在FailReason字段创建索引也能显著提高效率。

测试语句：

SELECT * FROM CouponSendRecord WHERE FailReason > 0

建索引之前：

表 'CouponSendRecord'。扫描计数 17，逻辑读取 315880 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
SQL Server 执行时间:
   CPU 时间 = 2905 毫秒，占用时间 = 633 毫秒。

创建索引：

CREATE NONCLUSTERED INDEX idx_failreason ON CouponSendRecord(FailReason) WITH(ONLINE=ON,FILLFACTOR=90)

创建索引之后：

表 'CouponSendRecord'。扫描计数 1，逻辑读取 1557 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 281 毫秒。

ps. 附一个背锅经历：某天开发同学清洗数据把FailReason全部重置为0，然后就悲剧了。。。

场景2：允许为NULL的列，查询有坑

单列索引不存null值，复合索引不存全为null的值，如果列允许为null，可能会得到“不符合预期”的结果集。

DECLARE @tb TABLE(id int, name varchar(100) null) 
INSERT INTO @tb VALUES(1,'a') 
INSERT INTO @tb VALUES(2,'b') 
INSERT INTO @tb(id) VALUES(3)

由于name字段允许为null，如下语句可能会得到“不符合预期”的结果集。

SELECT * FROM @tb WHERE name <> 'a'

返回结果(没有id=3的记录)

id	name
2	b

若要得到准确的数据还要借助isnull判断（本无必要），而这又将导致引擎放弃使用索引而进行全表扫描。

SELECT * FROM @tb WHERE isnull(name,'') <> 'a'

返回结果

id	name
2	b
3	NULL

所以，请使用not null约束+默认值来代替null。

最佳实践

为什么要禁止非SARG查询

禁止在索引列上使用函数或计算；

a）解读：WHERE CONVERT(VARCHAR(10),InsertTime,121) > '2017-02-15' 会导致全表扫描；
b）假设在字段col1上建有一个索引，下列场景将可以使用到索引：
col1 = 100
col1 > 100
col1 BETWEEN 0 AND 99
col1 LIKE 'abc%'
col1 IN (4,5,6)
禁止非SARG查询，以及%开头的模糊查询；

a）非SARG查询：NOT、<>、NOT EXISTS、NOT IN、NOT LIKE和内部标量函数，会导致扫描；
b）LIKE子句应尽量前端匹配，避免通配符在前端，因为%开头的模糊查询，会导致索引扫描；
name like 'abc%' --index seek
name like '%abc%' --index scan
name like '%abc' --index scan

where子句调优

整理一些常用的优化方法如下，仅供参考：

不建议	建议	备注
left(pn,3) = '222'	pn like '222%'
amount/30 < 1000	amount < 1000*30
convert(char(8),dt,112)='20100504'	dt >= '2010-05-04 00:00:00' and dt < '2010-05-05 00:00:00'
num in ('10', '20')	num = 10 union all num = 20	无大量重复值，否则用union
num = '10' or num = '20'
num is null	num = 0	设置not null + 默认值0
fname + lname = '张三'	fname = '张' and lname = '三'
phone = 18001833557	phone = '18001833557'	phone为varchar类型，隐式转换

调优常用的几条命令

set statistics time,io on

exec sp_spaceused xxx
exec sp_helpindex xxx

sqlserver调优--语句调优