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

1概述

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

通过对上面执行计划的认识,为了提高数据库运行的效率,我们需要尽可能的命中执行计划,这样就可以节省运行时间。

2相关sql

2.1查看当前数据库中所有的执行计划:


复制代码 代码如下:

select cp.usecounts as ‘使用次数’

,objtype as ‘类型’

,st.[text] as ‘sql文本’

,plan_handle as ‘计划句柄’

from sys.dm_exec_cached_plans cp

cross apply sys.dm_exec_sql_text(plan_handle) as st

where st.text not like ‘%sys%’

2.2删除执行计划


复制代码 代码如下:

–删除所有计划

dbcc freeproccache

2.3测试脚本(创建员工表,并向其插入1000条数据)


复制代码 代码如下:

if exists (select * from sys.objects where object_id = object_id(n'[dbo].[employee]’))

drop table [dbo].employee

go

–人员表

create table dbo.employee

(

id int,

name nvarchar(50)

);

–插入测试数据

declare @i int=0,@endi int=1000;

while(@i<@endi)

begin

set @i+=1;

insert dbo.employee(id,name) values(@i,’蒋大华’+cast(@i as nvarchar(20)));

end;

3测试执行计划

3.1 先执行删除所有执行计划,然后执行select * from employee ,最后查看执行计划(2.1中的查看执行计划脚本)如下图

   即sql server会为每一条sql建立一个执行计划,并将它缓存起来

3.2 再运行一次sql: select * from employee,并查看执行计划

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

3.3 修改sql:select  * from employee(在select后多加一个空格),执行并查看执行计划

     结果又新添加一个执行计划,即sql server认为这是两个不同的sql语句并分别建立了执行计划;

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

4.1 未参数化sql

复制代码 代码如下:

string selectcmdtext = string.format(@”select * from employee where name='{0}'”,” 蒋大华1”);

sqlhelper.executenonquery(sqlhelper.defaulconnectiontstring, system.data.commandtype.text, selectcmdtext, null);

查看执行计划:

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

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

复制代码 代码如下:

string selectcmdtext = string.format(@”select * from employee where name='{0}'”,” 蒋大华2”);

sqlhelper.executenonquery(sqlhelper.defaulconnectiontstring, system.data.commandtype.text, selectcmdtext, null);

查看执行计划

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

4.2 参数化sql

复制代码 代码如下:

sqlparameter[] param = { new sqlparameter(“@name”, txtemployeename.text.trim()) };

string selectcmdtext = string.format(@”select * from employee where name=@name”);

sqlhelper.executenonquery(sqlhelper.defaulconnectiontstring, system.data.commandtype.text, selectcmdtext, param);

输入参数并执行,然后查看执行计划:

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

重用执行计划,perfect…

5总结

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

(0)
上一篇 2022年3月21日
下一篇 2022年3月21日

相关推荐