首先：我们要了解SQL收到一个指令后所做的事情：具体细节可以查看文章：SqlServer编译、重编译与执行计划重用原理在这里，我简单的表示为：收到指令-编译SQL生成执行计划-选择执行计划-执行执行计划。具体可能有点不一样，但大致的步骤如上所示。接着我们来分析为什么拼接SQL字符串会导致SQL注入的风险呢？首先创建一张表Users:CREATETABLE[dbo].[Users]([Id][uniqueidentifier]NOTNULL,[UserId][int]NOTNULL,[UserName][varchar](50)NULL,[Password][varchar](50)NOTNULL,CONSTRAINT[PK_Users]PRIMARYKEYCLUSTERED([Id]ASC)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON[PRIMARY])ON[PRIMARY]插入一些数据：INSERTINTO[Test].[dbo].[Users]([Id],[UserId],[UserName],[Password])VALUES(NEWID(),1,'name1','pwd1');INSERTINTO[Test].[dbo].[Users]([Id],[UserId],[UserName],[Password])VALUES(NEWID(),2,'name2','pwd2');INSERTINTO[Test].[dbo].[Users]([Id],[UserId],[UserName],[Password])VALUES(NEWID(),3,'name3','pwd3');INSERTINTO[Test].[dbo].[Users]([Id],[UserId],[UserName],[Password])VALUES(NEWID(),4,'name4','pwd4');INSERTINTO[Test].[dbo].[Users]([Id],[UserId],[UserName],[Password])VALUES(NEWID(),5,'name5','pwd5');假设我们有个用户登录的页面，代码如下：验证用户登录的sql如下：selectCOUNT(*)fromUserswherePassword='a'andUserName='b'这段代码返回Password和UserName都匹配的用户数量，如果大于1的话，那么就代表用户存在。本文不讨论SQL中的密码策略，也不讨论代码规范，主要是讲为什么能够防止SQL注入，请一些同学不要纠结与某些代码，或者和SQL注入无关的主题。可以看到执行结果：这个是SQLprofile跟踪的SQL语句。注入的代码如下：selectCOUNT(*)fromUserswherePassword='a'andUserName='b'or1=1—'这里有人将UserName设置为了“b'or1=1–”.实际执行的SQL就变成了如下：可以很明显的看到SQL注入成功了。很多人都知道参数化查询可以避免上面出现的注入问题，比如下面的代码：classProgram{privatestaticstringconnectionString="DataSource=.;InitialCatalog=Test;IntegratedSecurity=True";staticvoidMain(string[]args){Login("b","a");Login("b'or1=1--","a");}privatestaticvoidLogin(stringuserName,stringpassword){using(SqlConnectionconn=newSqlConnection(connectionString)){conn.Open();SqlCommandcomm=newSqlCommand();comm.Connection=conn;//为每一条数据添加一个参数comm.CommandText="selectCOUNT(*)fromUserswherePassword=@PasswordandUserName=@UserName";comm.Parameters.AddRange(newSqlParameter[]{newSqlParameter("@Password",SqlDbType.VarChar){Value=password},newSqlParameter("@UserName",SqlDbType.VarChar){Value=userName},});comm.ExecuteNonQuery();}}}实际执行的SQL如下所示：execsp_executesqlN'selectCOUNT(*)fromUserswherePassword=@PasswordandUserName=@UserName',N'@Passwordvarchar(1),@UserNamevarchar(1)',@Password='a',@UserName='b'execsp_executesqlN'selectCOUNT(*)fromUserswherePassword=@PasswordandUserName=@UserName',N'@Passwordvarchar(1),@UserNamevarchar(11)',@Password='a',@UserName='b''or1=1—'可以看到参数化查询主要做了这些事情：1：参数过滤，可以看到@UserName='b''or1=1—'2：执行计划重用因为执行计划被重用，所以可以防止SQL注入。首先分析SQL注入的本质，用户写了一段SQL用来表示查找密码是a的，用户名是b的所有用户的数量。通过注入SQL，这段SQL现在表示的含义是查找(密码是a的，并且用户名是b的，)或者1=1的所有用户的数量。可以看到SQL的语意发生了改变，为什么发生了改变呢？，因为没有重用以前的执行计划，因为对注入后的SQL语句重新进行了编译，因为重新执行了语法解析。所以要保证SQL语义不变，即我想要表达SQL就是我想表达的意思，不是别的注入后的意思，就应该重用执行计划。如果不能够重用执行计划，那么就有SQL注入的风险，因为SQL的语意有可能会变化，所表达的查询就可能变化。在SQLServer中查询执行计划可以使用下面的脚本：DBCCFreeProccacheselecttotal_elapsed_time/execution_count平均时间,total_logical_reads/execution_count逻辑读,usecounts重用次数,SUBSTRING(d.text,(statement_start_offset/2)+1,((CASEstatement_end_offsetWHEN-1THENDATALENGTH(text)ELSEstatement_end_offsetEND-statement_start_offset)/2)+1)语句执行fromsys.dm_exec_cached_plansacrossapplysys.dm_exec_query_plan(a.plan_handle)c,sys.dm_exec_query_statsbcrossapplysys.dm_exec_sql_text(b.sql_handle)d--wherea.plan_handle=b.plan_handleandtotal_logical_reads/execution_count4000ORDERBYtotal_elapsed_time/execution_countDESC;
创新互联公司专注于平城企业网站建设,响应式网站建设,商城网站定制开发。平城网站建设公司,为平城等地区提供建站服务。全流程按需策划，专业设计，全程项目跟踪，创新互联公司专业和态度为您提供的服务
很多人不知道SQL语句在SQLSERVER中是如何执行的，他们担心自己所写的SQL语句会被SQLSERVER误解。比如：select*fromtable1wherename='zhangsan'andtID10000和执行:select*fromtable1wheretID10000andname='zhangsan'一些人不知道以上两条语句的执行效率是否一样，因为如果简单的从语句先后上看，这两个语句的确是不一样，如果tID是一个聚合索引，那么后一句仅仅从表的10000条以后的记录中查找就行了；而前一句则要先从全表中查找看有几个name='zhangsan'的，而后再根据限制条件条件tID10000来提出查询结果。事实上，这样的担心是不必要的。SQLSERVER中有一个“查询分析优化器”，它可以计算出where子句中的搜索条件并确定哪个索引能缩小表扫描的搜索空间，也就是说，它能实现自动优化。虽然查询优化器可以根据where子句自动的进行查询优化，但大家仍然有必要了解一下“查询优化器”的工作原理，如非这样，有时查询优化器就会不按照您的本意进行快速查询。在查询分析阶段，查询优化器查看查询的每个阶段并决定限制需要扫描的数据量是否有用。如果一个阶段可以被用作一个扫描参数（SARG），那么就称之为可优化的，并且可以利用索引快速获得所需数据。SARG的定义：用于限制搜索的一个操作，因为它通常是指一个特定的匹配，一个值得范围内的匹配或者两个以上条件的AND连接。形式如下：列名操作符或操作符列名列名可以出现在操作符的一边，而常数或变量出现在操作符的另一边。如：Name='张三'价格500050005000如果一个表达式不能满足SARG的形式，那它就无法限制搜索的范围了，也就是SQLSERVER必须对每一行都判断它是否满足WHERE子句中的所有条件。所以一个索引对于不满足SARG形式的表达式来说是无用的。介绍完SARG后，我们来总结一下使用SARG以及在实践中遇到的和某些资料上结论不同的经验：1、Like语句是否属于SARG取决于所使用的通配符的类型--如：namelike'张%'--，这就属于SARG--而：namelike'%张'--,就不属于SARG。原因是通配符%在字符串的开通使得索引无法使用。2、or会引起全表扫描Name='张三'and价格5000符号SARG，而：Name='张三'or价格5000则不符合SARG。使用or会引起全表扫描。3、非操作符、函数引起的不满足SARG形式的语句不满足SARG形式的语句最典型的情况就是包括非操作符的语句，如：NOT、!=、、!、NOTEXISTS、NOTIN、NOTLIKE等，另外还有函数。下面就是几个不满足SARG形式的例子：ABS(价格)5000--SQLSERVER也会认为是SARG，SQLSERVER会将此式转化为：WHERE价格2500/2但我们不推荐这样使用，因为有时SQLSERVER不能保证这种转化与原始表达式是完全等价的。4、IN的作用相当与OR语句：Select*fromtable1wheretidin(2,3)--和Select*fromtable1wheretid=2ortid=3是一样的，都会引起全表扫描，如果tid上有索引，其索引也会失效。5、尽量少用NOT6、exists和in的执行效率是一样的很多资料上都显示说，exists要比in的执行效率要高，同时应尽可能的用notexists来代替notin。但事实上，我试验了一下，发现二者无论是前面带不带not，二者之间的执行效率都是一样的。因为涉及子查询，我们试验这次用SQLSERVER自带的pubs数据库。运行前我们可以把SQLSERVER的statisticsI/O状态打开：（1）selecttitle,pricefromtitleswheretitle_idin(selecttitle_idfromsaleswhereqty30)该句的执行结果为：表'sales'。扫描计数18，逻辑读56次，物理读0次，预读0次。表'titles'。扫描计数1，逻辑读2次，物理读0次，预读0次。（2）selecttitle,pricefromtitleswhereexists(select*fromsaleswheresales.title_id=titles.title_idandqty30)第二句的执行结果为：表'sales'。扫描计数18，逻辑读56次，物理读0次，预读0次。表'titles'。扫描计数1，逻辑读2次，物理读0次，预读0次。我们从此可以看到用exists和用in的执行效率是一样的。7、用函数charindex()和前面加通配符%的LIKE执行效率一样前面，我们谈到，如果在LIKE前面加上通配符%，那么将会引起全表扫描，所以其执行效率是低下的。但有的资料介绍说，用函数charindex()来代替LIKE速度会有大的提升，经我试验，发现这种说明也是错误的：selectgid,title,fariqi,readerfromtgongwen wherecharindex('刑侦支队',reader)0andfariqi'2004-5-5'用时：7秒，另外：扫描计数4，逻辑读7155次，物理读0次，预读0次。selectgid,title,fariqi,readerfromtgongwen wherereaderlike'%'+'刑侦支队'+'%'andfariqi'2004-5-5'用时：7秒，另外：扫描计数4，逻辑读7155次，物理读0次，预读0次。8、union并不绝对比or的执行效率高我们前面已经谈到了在where子句中使用or会引起全表扫描，一般的，我所见过的资料都是推荐这里用union来代替or。事实证明，这种说法对于大部分都是适用的。selectgid,fariqi,neibuyonghu,reader,titlefromTgongwen wherefariqi='2004-9-16'orgid9990000用时：68秒。扫描计数1，逻辑读404008次，物理读283次，预读392163次。selectgid,fariqi,neibuyonghu,reader,titlefromTgongwenwherefariqi='2004-9-16'unionselectgid,fariqi,neibuyonghu,reader,titlefromTgongwenwheregid9990000用时：9秒。扫描计数8，逻辑读67489次，物理读216次，预读7499次。看来，用union在通常情况下比用or的效率要高的多。但经过试验，笔者发现如果or两边的查询列是一样的话，那么用union则反倒和用or的执行速度差很多，虽然这里union扫描的是索引，而or扫描的是全表。selectgid,fariqi,neibuyonghu,reader,titlefromTgongwen wherefariqi='2004-9-16'orfariqi='2004-2-5'用时：6423毫秒。扫描计数2，逻辑读14726次，物理读1次，预读7176次。selectgid,fariqi,neibuyonghu,reader,titlefromTgongwenwherefariqi='2004-9-16'unionselectgid,fariqi,neibuyonghu,reader,titlefromTgongwenwherefariqi='2004-2-5'用时：11640毫秒。扫描计数8，逻辑读14806次，物理读108次，预读1144次。9、字段提取要按照“需多少、提多少”的原则，避免“select*”我们来做一个试验：selecttop10000gid,fariqi,reader,titlefromtgongwenorderbygiddesc用时：4673毫秒selecttop10000gid,fariqi,titlefromtgongwenorderbygiddesc用时：1376毫秒selecttop10000gid,fariqifromtgongwenorderbygiddesc用时：80毫秒由此看来，我们每少提取一个字段，数据的提取速度就会有相应的提升。提升的速度还要看您舍弃的字段的大小来判断。10、count(*)不比count(字段)慢某些资料上说：用*会统计所有列，显然要比一个世界的列名效率低。这种说法其实是没有根据的。我们来看：selectcount(*)fromTgongwen用时：1500毫秒selectcount(gid)fromTgongwen用时：1483毫秒selectcount(fariqi)fromTgongwen用时：3140毫秒selectcount(title)fromTgongwen用时：52050毫秒从以上可以看出，如果用count(*)和用count(主键)的速度是相当的，而count(*)却比其他任何除主键以外的字段汇总速度要快，而且字段越长，汇总的速度就越慢。我想，如果用count(*)，SQLSERVER可能会自动查找最小字段来汇总的。当然，如果您直接写count(主键)将会来的更直接些。11、orderby按聚集索引列排序效率最高我们来看：（gid是主键，fariqi是聚合索引列）：selecttop10000gid,fariqi,reader,titlefromtgongwen用时：196毫秒。扫描计数1，逻辑读289次，物理读1次，预读1527次。selecttop10000gid,fariqi,reader,titlefromtgongwenorderbygidasc用时：4720毫秒。扫描计数1，逻辑读41956次，物理读0次，预读1287次。selecttop10000gid,fariqi,reader,titlefromtgongwenorderbygiddesc用时：4736毫秒。扫描计数1，逻辑读55350次，物理读10次，预读775次。selecttop10000gid,fariqi,reader,titlefromtgongwenorderbyfariqiasc用时：173毫秒。扫描计数1，逻辑读290次，物理读0次，预读0次。selecttop10000gid,fariqi,reader,titlefromtgongwenorderbyfariqidesc用时：156毫秒。扫描计数1，逻辑读289次，物理读0次，预读0次。从以上我们可以看出，不排序的速度以及逻辑读次数都是和“orderby聚集索引列”的速度是相当的，但这些都比“orderby非聚集索引列”的查询速度是快得多的。同时，按照某个字段进行排序的时候，无论是正序还是倒序，速度是基本相当的。12、高效的TOP事实上，在查询和提取超大容量的数据集时，影响数据库响应时间的最大因素不是数据查找，而是物理的I/0操作。如：selecttop10*from(selecttop10000gid,fariqi,titlefromtgongwenwhereneibuyonghu='公室'orderbygiddesc)asaorderbygidasc这条语句，从理论上讲，整条语句的执行时间应该比子句的执行时间长，但事实相反。因为，子句执行后返回的是10000条记录，而整条语句仅返回10条语句，所以影响数据库响应时间最大的因素是物理I/O操作。而限制物理I/O操作此处的最有效方法之一就是使用TOP关键词了。TOP关键词是SQLSERVER中经过系统优化过的一个用来提取前几条或前几个百分比数据的词。经笔者在实践中的应用，发现TOP确实很好用，效率也很高。但这个词在另外一个大型数据库ORACLE中却没有，这不能说不是一个遗憾，虽然在ORACLE中可以用其他方法（如：rownumber）来解决。在以后的关于“实现千万级数据的分页显示存储过程”的讨论中，我们就将用到TOP这个关键词。到此为止，我们上面讨论了如何实现从大容量的数据库中快速地查询出您所需要的数据方法。当然，我们介绍的这些方法都是“软”方法，在实践中，我们还要考虑各种“硬”因素，如：网络性能、服务器的性能、操作系统的性能，甚至网卡、交换机等。
通过对执行计划的跟踪，确定逻辑读最多最乱的，从索引入手解决。
再次跟踪，发现其在执行过程中占用资源最大的百分比，然后分析表的连接。
关于SQL SERVER优化，是个长期学习的过程，基本的优化含有了索引等内容，但深入优化就需要掌握更多的存储引擎方面的知识了。建议学习官方书籍《SQL SERVER2008存储引擎》
重庆分公司
重庆分公司
