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查询

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

   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)
   

2. 禁止非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