sqlserver调优--语句调优
[TOC]
读懂执行计划
如何得到执行计划
读懂语句执行的统计信息
请参考：《读懂语句执行的统计信息》
表关联的三种算法
常见操作符
其他
用 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。
最佳实践
大表频繁count的改进
当大表频繁需要count的查询，考虑使用汇总表。
比如，某个日志表记录了某个页面的点击记录，执行count可以得到总的点击数，由于数据量已经2.3亿条，每次count都要耗时6秒：
SELECT COUNT_BIG(1) FROM bigtable
SQL Server 执行时间:
CPU 时间 = 14 毫秒，占用时间 = 6002 毫秒。
根据时间字段InsertTime统计发现，当天数据仅16852行，而历史数据约2.3亿。
SELECT COUNT_BIG(1) FROM bigtable WHERE inserttime < '2020-01-01 00:00:00' --行数 234287003
SELECT COUNT_BIG(1) FROM bigtable WHERE inserttime >= '2020-01-01 00:00:00' --行数 16852
这种情况下，我们可以创建一个汇总表：
CREATE TABLE bigtable_summary(id int, dtime datetime, summ bigint)
第一次需要初始化数据，比如把2019年12月31日之前的数据全部初始化为一条记录：
DECLARE @cnt bigint
SET @cnt = (SELECT COUNT_BIG(1) FROM bigtable WHERE inserttime <= '2019-12-31 23:59:59'）
INSERT INTO bigtable_summary(dtime, summ) VALUES('2019-12-31 59:59:59',@cnt)
以后每天00:00（比如2020-01-02）插入一条前一天的汇总记录，语法参考如下：
DECLARE @cnt bigint
SET @cnt = (SELECT COUNT_BIG(1) FROM bigtable WHERE inserttime >= '2020-01-01 00:00:00' AND inserttime < '2020-01-02 00:00:00'）
INSERT INTO bigtable_summary(dtime, summ) VALUES('2020-01-01 00:00:00',@cnt)
这样可以大表的count就可以改为：汇总表sum汇总记录 + 大表当天的count记录，由于大表InsertTime字段有索引，只查询当天的数据，效率自然很高。
$cnt1 = SELECT SUM(cnt) FROM bigtable_summary WHERE dtime < '2020-01-02 00:00:00'
$cnt2 = SELECT COUNT(1) FROM bigtable WHERE inserttime >= '2020-01-02 00:00:00'
$cnt = $cnt1 + $cnt2
为什么要禁止非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类型，隐式转换 |
select语句执行顺序
SELECT语句的完整语法为（语法前面的序号为SELECT执行顺序）：
7) SELECT
8) DISTINCT <select_list>
1) FROM <left_table>
3) <join_type> JOIN <right_table>
2) ON <join_condition>
4) WHERE <where_condition>
5) GROUP BY <group_by_list>
6) HAVING <having_condition>
9) ORDER BY <order_by_condition>
10) LIMIT <limit_number>
下面我们来具体分析一下查询处理的每一个阶段：
- 1、FORM：对FROM左边的表和右边的表计算笛卡尔积，产生虚表VT1
- 2、ON：对虚表VT1进行ON过滤，只有那些符合
的行才会被记录在虚表VT2 - 3、JOIN：如果指定了OUTER JOIN（比如left join、 right join），那么保留表中（left join的保留表为table1）未匹配的行就会作为外部行添加到虚拟表VT2中，产生虚拟表VT3
- 4、WHERE：对虚拟表VT3进行WHERE条件过滤，只有符合
的记录才会被插入到虚拟表VT4 - 5、GROUP BY：根据group by子句中的列，对VT4中的记录进行分组操作，产生VT5
- 6、HAVING：对虚拟表VT5应用having过滤，只有符合
的记录才会被 插入到虚拟表VT6中 - 7、SELECT：执行select操作，选择指定的列，插入到虚拟表VT7
- 8、DISTINCT：对VT7中的记录进行去重，产生虚拟表VT8
- 9、ORDER BY：将虚拟表VT8中的记录按照
进行排序操作，产生虚拟表VT9 - 10、LIMIT：取出指定行的记录，产生虚拟表VT10，并将结果返回
举个例子：
SELECT a.customer_id, COUNT(b.order_id) as total_orders
FROM table1 AS a
LEFT JOIN table2 AS b ON a.customer_id = b.customer_id
WHERE a.city = 'hangzhou'
GROUP BY a.customer_id
HAVING count(b.order_id) < 2
ORDER BY total_orders DESC
LIMIT 10;
如何删除完全重复的数据
场景1：一条命令删除完全重复的数据
if object_id('test1') is not null drop table test1
go
create table test1(a int, b int, c int, d int)
go
insert into test1 values
(1, 2, 3, 4),
(1, 2, 3, 4),
(5, 6, 7, 8),
(5, 6, 7, 8)
go
with cte as ( select *, row_number() over(partition by a,b,c,d order by a) rn from test1 )
delete cte where rn > 1
go
select * from test1
go
场景2：删除b和c完全重复(保留a最大)的数据
if object_id('test2') is not null drop table test2
go
create table test2(a int, b int, c int, d int)
go
insert into test2 values
(1, 2, 3, 4),
(2, 2, 3, 5),
(3, 6, 7, 8),
(4, 6, 8, 8)
go
with cte as ( select *, row_number() over(partition by b,c order by a desc) rn from test2 )
delete cte where rn > 1
go
select * from test2
go
调优常用的几条命令
set statistics time,io on
exec sp_spaceused xxx
exec sp_helpindex xxx