• 浅析Sql Server参数化查询
    时间:2012-04-29   作者:懒惰的肥兔   出处:cnblogs.com/lzrabbit

    说来惭愧,工作差不多4年了,直到前些日子被DBA找上门让我优化一个CPU占用很高的复杂SQL语句时,我才突然意识到了参数化查询的重要性。相信有很多开发者和我一样对于参数化查询认识比较模糊,没有引起足够的重视

    错误认识1.不需要防止sql注入的地方无需参数化
    参数化查询就是为了防止SQL注入用的,其它还有什么用途不知道、也不关心,原则上是能不用参数就不用参数,为啥?多麻烦,我只是做公司内部系统不用担心SQL注入风险,使用参数化查询不是给自己找麻烦,简简单单拼SQL,万事OK
    错误认识2.参数化查询时是否指定参数类型、参数长度没什么区别
    以前也一直都觉的加与不加参数长度应该没有什么区别,仅是写法上的不同而已,而且觉得加参数类型和长度写法太麻烦,最近才明白其实两者不一样的,为了提高sql执行速度,请为SqlParameter参数加上SqlDbType和size属性,在参数化查询代码编写过程中很多开发者忽略了指定查询参数的类型,这将导致托管代码在执行过程中不能自动识别参数类型,进而对该字段内容进行全表扫描以确定参数类型并进行转换,消耗了不必要的查询性能所致。根据MSDN解释:如果未在size参数中显式设置Size,则从dbType参数的值推断出该大小。如果你认为上面的推断出该大小是指从SqlDbType类型推断,那你就错了,它实际上是从你传过来的参数的值来推断的,比如传递过来的值是"username",则size值为8,"username1",则size值为9。那么,不同的size值会引发什么样的结果呢?且经测试发现,size的值不同时,会导致数据库的执行计划不会重用,这样就会每次执行sql的时候重新生成新的执行计划,而浪费数据库执行时间。
    下面来看具体测试
    首先清空查询计划

    DBCC FREEPROCCACHE


    传值username,不指定参数长度,生成查询计划

    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        conn.Open();
        SqlCommand comm = new SqlCommand();
        comm.Connection = conn;
        comm.CommandText = "select * from Users where UserName=@UserName";
        //传值 username,不指定参数长度
        //查询计划为(@UserName varchar(8))select * from Users where UserName=@UserName
        comm.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar) { Value = "username" });
        comm.ExecuteNonQuery();
    }

     
    传值username1,不指定参数长度,生成查询计划

    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        conn.Open();
        SqlCommand comm = new SqlCommand();
        comm.Connection = conn;
        comm.CommandText = "select * from Users where UserName=@UserName";
        //传值 username1,不指定参数长度
        //查询计划为(@UserName varchar(9))select * from Users where UserName=@UserName
        comm.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar) { Value = "username1" });
        comm.ExecuteNonQuery();
    }

    传值username,指定参数长度为50,生成查询计划

    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        conn.Open();
        SqlCommand comm = new SqlCommand();
        comm.Connection = conn;
        comm.CommandText = "select * from Users where UserName=@UserName";
        //传值 username,指定参数长度为50
        //查询计划为(@UserName varchar(50))select * from Users where UserName=@UserName
        comm.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar,50) { Value = "username" });
        comm.ExecuteNonQuery();
    }

     
    传值username1,指定参数长度为50,生成查询计划

    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        conn.Open();
        SqlCommand comm = new SqlCommand();
        comm.Connection = conn;
        comm.CommandText = "select * from Users where UserName=@UserName";
        //传值 username1,指定参数长度为50
        //查询计划为(@UserName varchar(50))select * from Users where UserName=@UserName
        comm.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar,50) { Value = "username1" });
        comm.ExecuteNonQuery();
    }

    使用下面语句查看执行的查询计划

    SELECT cacheobjtype,objtype,usecounts,sql FROM sys.syscacheobjects
    WHERE sql LIKE '%Users%'  and sql not like '%syscacheobjects%'

     通过结果可以看到指定了参数长度的查询可以复用查询计划,而不指定参数长度的查询会根据具体传值而改变查询计划,从而造成性能的损失。
    这里的指定参数长度仅指可变长数据类型,主要指varchar,nvarchar,char,nchar等,对于int,bigint,decimal,datetime等定长的值类型来说,无需指定(即便指定了也没有用),详见下面测试,UserID为int类型,无论长度指定为2、20、-1查询计划都完全一样为(@UserIDint)select*from Users where UserID=@UserID

    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        conn.Open();
        SqlCommand comm = new SqlCommand();
        comm.Connection = conn;
        comm.CommandText = "select * from Users where UserID=@UserID";
        //传值 2,参数长度2
        //执行计划(@UserID int)select * from Users where UserID=@UserID
        comm.Parameters.Add(new SqlParameter("@UserID", SqlDbType.Int, 2) { Value = 2 });
        comm.ExecuteNonQuery();
    }
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        conn.Open();
        SqlCommand comm = new SqlCommand();
        comm.Connection = conn;
        comm.CommandText = "select * from Users where UserID=@UserID";
        //传值 2,参数长度20
        //执行计划(@UserID int)select * from Users where UserID=@UserID
        comm.Parameters.Add(new SqlParameter("@UserID", SqlDbType.Int, 20) { Value = 2 });
        comm.ExecuteNonQuery();
    }
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        conn.Open();
        SqlCommand comm = new SqlCommand();
        comm.Connection = conn;
        comm.CommandText = "select * from Users where UserID=@UserID";
        //传值 2,参数长度-1
        //执行计划(@UserID int)select * from Users where UserID=@UserID
        comm.Parameters.Add(new SqlParameter("@UserID", SqlDbType.Int, -1) { Value = 2 });
        comm.ExecuteNonQuery();
    }

    这里提一下,若要传值varchar(max)或nvarchar(max)类型怎么传,其实只要设定长度为-1即可

    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        conn.Open();
        SqlCommand comm = new SqlCommand();
        comm.Connection = conn;
        comm.CommandText = "select * from Users where UserName=@UserName";
        //类型为varchar(max)时,指定参数长度为-1
        //查询计划为 (@UserName varchar(max) )select * from Users where UserName=@UserName
        comm.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar,-1) { Value = "username1" });
        comm.ExecuteNonQuery();
    }

    当然了若是不使用参数化查询,直接拼接SQL,那样就更没有查询计划复用一说了,除非你每次拼的SQL都完全一样

    总结,参数化查询意义及注意点

    1.可以防止SQL注入
    2.可以提高查询性能(主要是可以复用查询计划),这点在数据量较大时尤为重要
    3.参数化查询参数类型为可变长度时(varchar,nvarchar,char等)请指定参数类型及长度,若为值类型(int,bigint,decimal,datetime等)则仅指定参数类型即可
    4.传值为varchar(max)或者nvarchar(max)时,参数长度指定为-1即可

    网友留言/评论

    我要留言/评论

    相关文章

    大型网站数据库架构发展升级11个阶段:SQLServer2008在数据的高安全、高性能、高可用方面的技术已经比较成熟,这些技术和方案都是随着很多公司的业务和数据访问压力的增加而不断的升级和变迁的,同时经历了方方面面的考验,证明了它们都是成熟可靠的,下面就这方面的技术方案和变迁过程来做一些分析。
    另一个角度看千万级数据的分页解决方案:想说这个问题,来源来自于CSDN论坛的一个帖子:《sqlserver2005,1690万的数据量怎么快速分页查询》。在之前也有很多人问类似这样的问题,回复这样的问题,我们一般会从索引,水平分区,垂直分区和硬件的升级等方面考虑。
    NoSQL 为什么会崛起:NoSQL产品越来越火,NoSQL产品通常以其高性能,强扩展性和高容错性为大家所称道,我们在问为什么NoSQL会流行起来的时候,或许应该问一下为什么这些功能会变得这么重要,下面本文列举一下6个原因。
    SQL语句查询优化技巧:对于程序开发人员,我们要做到不但会写SQL,还要做到写出性能优良的SQL。不同的SQL对于程序的执行会有很大的区别,特别是对于大并发量的程序。下面本文将给大家提供一些SQL书写以及优化技巧,向往会对大家受益匪浅。
    PHP开发者常犯的MySQL错误总结:数据库是WEB大多数应用开发的基础。如果你是用PHP,那么大多数据库用的是MYSQL–也是LAMP架构的重要部分。
    MongoDB的学习记录:MongoDB是一个基于分布式文件存储的数据库。由C++语言编写。旨在为WEB应用提供可扩展的高性能数据存储解决方案。这么好的东东一定要学习一下了,虽然木有在实际项目中运用,还是记录一下自己的总结。
    MySQL中varchar类型最大长度研究:工作当中经常被问到一个问题:MySQL中varchar最大长度是多少?这不是一个固定的数字。本文简要说明一下Mysql限制规则。
    Php+Mysql注入专题教学:攻击是现今最流行的攻击方式,依靠它强大的灵活性吸引了广大黑迷。 OK,这一期我将给大家伙仔仔细细的吹一吹php+mysql注入,一定让你满载而归哦(谁扔砖头哩!)。 本文主要是为小菜们服务的,如果你已经是一只老鸟呢,可能某些东西会感觉比较乏味,但只要你仔细的看,你会发现很多有趣的东西哦。