提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  数据库

当前栏目

SQL参数化查询的另一个理由命中执行计划

SQL执行 查询 一个 参数 计划 理由 命中
2023-06-13 09:14:35 时间
1概述

SQL语言的本质就是一串伪代码，表达的是做什么，而不是怎么做的意思。如其它语言一样，SQL语句需要编译之后才能运行，所以每一条SQL是需要通过编译器解释才能运行的(在这之间还要做SQL的优化)。而这些步骤都是需要运行成本，所以在数据库中有一个叫做执行计划的东西，编译器会将编译过后的SQL存入执行计划当中，当遇到同样的SQL时，就直接调用执行计划来执行，而不需要再次编译。
通过对上面执行计划的认识，为了提高数据库运行的效率，我们需要尽可能的命中执行计划，这样就可以节省运行时间。

2相关SQL

2.1查看当前数据库中所有的执行计划：
复制代码代码如下:

SELECTcp.usecountsAS"使用次数"
,objtypeAS"类型"
,st.[text]AS"SQL文本"
,plan_handleAS"计划句柄"
FROMsys.dm_exec_cached_planscp
CROSSAPPLYsys.dm_exec_sql_text(plan_handle)ASst
WHEREst.textnotlike"%sys%"

2.2删除执行计划
复制代码代码如下:

--删除所有计划
DBCCFREEPROCCACHE

2.3测试脚本（创建员工表，并向其插入1000条数据）
复制代码代码如下:
IFEXISTS(SELECT*FROMsys.objectsWHEREobject_id=OBJECT_ID(N"[dbo].[Employee]"))
DROPTABLE[dbo].Employee
GO
--人员表
CREATETABLEdbo.Employee
(
idint,
namenvarchar(50)
);

--插入测试数据
DECLARE@IINT=0,@ENDIINT=1000;
WHILE(@I<@ENDI)
BEGIN
SET@I+=1;
INSERTdbo.Employee(id,name)VALUES(@I,"蒋大华"+CAST(@IASNVARCHAR(20)));
END;

3测试执行计划

3.1先执行删除所有执行计划，然后执行SELECT*FROMEmployee，最后查看执行计划（2.1中的查看执行计划脚本）如下图

  即SQLSERVER会为每一条SQL建立一个执行计划，并将它缓存起来

3.2再运行一次SQL:SELECT*FROMEmployee,并查看执行计划

    可以看到这个计划的重用次数为2，即这个计划被重用了；

3.3修改SQL：SELECT *FROMEmployee（在SELECT后多加一个空格），执行并查看执行计划

    结果又新添加一个执行计划，即SQLSERVER认为这是两个不同的SQL语句并分别建立了执行计划；

4重用执行计划——使用参数化查询方法

4.1未参数化SQL

复制代码代码如下:
stringselectCmdText=string.Format(@"SELECT*FROMEmployeeWHEREname="{0}"",”蒋大华1”);
SQLHelper.ExecuteNonQuery(SQLHelper.DefaulConnectiontString,System.Data.CommandType.Text,selectCmdText,null);

查看执行计划：

   即当执行一个未参数化SQL时，SQLSERVER需要先将其转换成一个参数SQL并执行它。一共需要两执行计划

   然后再执行下面的代码（查询的条件变了）

复制代码代码如下:
stringselectCmdText=string.Format(@"SELECT*FROMEmployeeWHEREname="{0}"",”蒋大华2”);
SQLHelper.ExecuteNonQuery(SQLHelper.DefaulConnectiontString,System.Data.CommandType.Text,selectCmdText,null);

查看执行计划

   此时不需要再准备一个准备的SQL，但还是需要再产生一个执行计划，并缓存下来；

4.2参数化SQL

复制代码代码如下:
SqlParameter[]param={newSqlParameter("@name",txtEmployeeName.Text.Trim())};
stringselectCmdText=string.Format(@"SELECT*FROMEmployeeWHEREname=@name");
SQLHelper.ExecuteNonQuery(SQLHelper.DefaulConnectiontString,System.Data.CommandType.Text,selectCmdText,param);

输入参数并执行，然后查看执行计划：

只需要一个准备SQL，然后，输入不同的参数，并执行，再查看执行计划

重用执行计划，perfect...

5总结

总的来说，SQL语句在执行时，会生成执行计划并将它缓存起来，我们可以通过提高使用缓存中的执行计划次数，来减少数据库的压力。而使用参数化的SQL是一个很好的选择，参数化查询的作用不仅只有防止SQL注入，还可以提高缓存中执行计划使用次数。

相关文章