前言

  很多人认为数据库其实很简单,也没什么大深入的细节去研究,但是真正的一些细节问题决定着你的是否是专家。

  本文主要讲述一下存储过程参数传递的一些小细节,很多人知道参数嗅探,本例也可以理解成参数嗅探的威力加强版++

–如果在存储过程中定义变量,并为变量SET赋值,该变量的值无法为执行计划提供参考(即执行计划不考虑该变量),将会出现预估行数和实际行数相差过大导致执行计划不优的情况

小例子

 1 ---创建测试表
 2 SELECT IDENTITY(INT,1,1) AS RID,
 3 * INTO TB1
 4 FROM sys.all_columns
 5 GO
 6 ---模拟大量数据
 7 INSERT INTO TB1
 8 SELECT *
 9 FROM sys.all_columns
10 GO 100
11  
12  
13  
14 --在 user_type_id列 创建一个索引
15 CREATE NONCLUSTERED INDEX [NonClusteredIndex-20160625-164531] ON [dbo].[TB1]
16 (
17     [user_type_id] ASC
18 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
19 GO
20  
21 --开启IO统计
22 set statistics io on
23  
24 --测试查询执行计划
25 select * from tb1 where user_type_id = 10

图片 1

 

图片 2

图片 3

图片 4

 

注:本例中,语句的执行应该走索引seek + key look up

 

–如果在存储过程中使用SET为存储过程参数重新赋值,执行计划仍采用执行时传入的值来生成执行计划。

–准备测试数据
DROP TABLE TB1
GO
SELECT IDENTITY(INT,1,1) AS RID,
*INTO TB1
FROM sys.all_columns
GO
INSERT INTO TB1
SELECT *
FROM sys.all_columns
GO 100
ALTER TABLE TB1
ADD PRIMARY KEY(RID)
 
 
–测试查询参数使用变量
–例如下列存储过程,由于在生成执行计划时不知道@ID的具体值,因此无法预估满足PID>@ID条件的
CREATE PROCEDURE dbo.USP_GetData
(
  @PIDINT
)
AS
BEGIN
DECLARE @ID INT
SET @ID= @PID
SELECT *
FROM TB1
WHERE RID>@ID
END
GO
EXEC dbo.USP_GetData @PID=606808
–由于预估行数有问题,导致生成不使用索引的查询计划
 图片 5

–================================================= 

–测试修改传入参数的情况
–虽然传入参数在传入后被修改,但是生成执行计划时仍使用传入时的值
CREATE PROCEDURE dbo.USP_GetData2
(
  @PID INT
)
AS
BEGIN
SET @PID=@PID-606800
SELECT*
FROM TB1
WHERE RID>@PID
END
GO
EXEC dbo.USP_GetData2 @PID=606808

 图片 6

–================================================= 
–测试在查询时对传入参数做运算
CREATE PROCEDURE dbo.USP_GetData3
(
  @PID INT
)
AS
BEGIN
SELECT COUNT(1)
FROM TB1
WHERE RID>@PID+600080
END
GO
EXEC dbo.USP_GetData3 @PID=20
 图片 7

 –================================================= 
–测试在查询时对传入参数做运算(复杂运算)
—-对应复杂运算,无法获得准确的值,因此不能准确地预估行数,也不能生成合理的执行计划
CREATE PROCEDURE dbo.USP_GetData4
(
  @PID INT
)
AS
BEGIN
SELECT COUNT(1)
FROM TB1
WHERE RID>@PID+CAST(RAND()*6000800 AS INT)
END
GO
EXEC dbo.USP_GetData4 @PID=20
GO

 图片 8

 

总结:
在存储过程中使用到的变量可以分为内部变量和外部变量
1>对于外部变量,存储过程编译时会使用该变量的真实值依据统计来生成执行计划,无论该外部变量是否在存储过程中发生修改
2>对于内部变量,存储过程编译时无法获取该变量的真实值,因此无法使用统计,从而只能生成”最通用”的执行计划(可能是比较差的执行计划)

补充:
可以使用OPTION(optimize
for(@PID=75124))方式来解决因变量值导致的执行计划不优的问题

 

 

测试一

 1 --测试1:使用定义变量,把参数值传递给变量
 2 
 3 create PROCEDURE dbo.USP_GetData
 4 (
 5   @PID INT 
 6 )
 7 AS
 8 BEGIN
 9 DECLARE @ID INT
10 SET @ID= @PID
11 SELECT *
12 FROM TB1
13 WHERE user_type_id = @ID
14 END
15 GO
16 EXEC dbo.USP_GetData @PID=10

 

图片 9

 

图片 10

 结论:如果在存储过程中定义变量,并为变量SET赋值,该变量的值无法为执行计划提供参考(即执行计划不考虑该变量),将会出现预估行数和实际行数相差过大导致执行计划不优的情况

 

测试二

 1 ---测试2 : 对参数进行运算
 2 create PROCEDURE dbo.USP_GetData2
 3 (
 4   @PID INT
 5 )
 6 AS
 7 BEGIN
 8 SET @PID=@PID-1
 9 SELECT*
10 FROM TB1
11 WHERE user_type_id = @PID
12 END
13 GO
14 EXEC dbo.USP_GetData2 @PID=11

 

 

 图片 11

 

图片 12

结论:如果在存储过程中使用SET为存储过程参数重新赋值,执行计划仍采用执行时传入的值来生成执行计划。

 

测试三

 1 --测试3 :对参数行进拼接
 2 
 3 create PROCEDURE dbo.USP_GetData3
 4 (
 5 @PID INT
 6 )
 7 AS
 8 BEGIN
 9 DECLARE @ID INT
10 set @ID = 2 
11 SET @PID = @ID + @PID
12 SELECT *
13 FROM TB1
14 WHERE user_type_id = @PID
15 END
16 GO
17 EXEC dbo.USP_GetData3 @PID= 8

 

 

 图片 13

 

图片 14

 结论:如果在存储过程中使用新定义的变量与传入参数拼接重新赋值,执行计划仍采用执行时传入的值来生成执行计划。

 

测试四

 1 --测试4 : 对变量进行运算 
 2 create PROCEDURE dbo.USP_GetData4
 3 (
 4   @PID INT
 5 )
 6 AS
 7 BEGIN
 8 SELECT *
 9 FROM TB1
10 WHERE user_type_id = @PID+ 2
11 END
12 GO
13 EXEC dbo.USP_GetData4 @PID=8

 

 图片 15

 

图片 16

  结论:虽然传入参数在传入后被修改,但是生成执行计划时仍使用传入时的值

 

测试五

 1 --测试5 :对变量进行复杂运算 
 2 create PROCEDURE dbo.USP_GetData5
 3 (
 4 @PID INT
 5 )
 6 AS
 7 BEGIN
 8 SELECT *
 9 FROM TB1
10 WHERE user_type_id = @PID+ CAST(RAND()*600 AS INT)
11 END
12 GO
13 EXEC dbo.USP_GetData5 @PID=8
14 GO

 

 图片 17

图片 18

 结论:对参数做复杂运算,无法获得准确的值,因此不能准确地预估行数,也不能生成合理的执行计划

 

测试六

 1 --测试6 : 复杂运算使用变量拼接
 2 create PROCEDURE dbo.USP_GetData6
 3 (
 4 @PID INT
 5 )
 6 AS
 7 BEGIN
 8 DECLARE @ID INT
 9 set @ID = CAST(RAND()*600 AS INT)
10 SET @PID = @ID + @PID
11 SELECT *
12 FROM TB1
13 WHERE user_type_id = @PID
14 END
15 GO
16 EXEC dbo.USP_GetData6 @PID=8
17 GO

 

 

 图片 19

 

 

 图片 20

 

结论:针对测试五可以使用参数拼接的方式,以便准确地预估行数,使用正确的执行计划

 

 

 总结

  技术支持做了比较长的时间了,遇到了很多很多坑,在这些坑中不断反思,慢慢成长!不要说什么数据库更优秀,不要说我们海量数据库需要什么什么高端的技术,其实解决问题的关键只是那么一点点的基础知识。

  注:本例中还有另外一种情况就是查询的数据量很大,那么本身走全表扫描是最优计划,而由于参数传递的问题错误的走了index
seek + key look up 道理是一样的。

 

————–博客地址—————————————————————————–

原文地址: 

如有转载请保留原文地址! 

 

 —————————————————————————————————-

注:此文章为原创,欢迎转载,请在文章页面明显位置给出此文链接!
若您觉得这篇文章还不错请点击下右下角的推荐,非常感谢!

 

Author

发表评论

电子邮件地址不会被公开。 必填项已用*标注