ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 写有效率的SQL查询(IV)

写有效率的SQL查询(IV)

原创 Linux操作系统 作者:sunger_chen 时间:2009-03-13 13:31:39 0 删除 编辑

本文主要介绍写SQL的另外两个误区:
1、 存储过程中使用局部变量而不使用参数变量(就是存储过程输入参数)做where条件
2、 查询条件中类型不匹配

这两种错误都是非常非常容易犯且非常发指的错误,特别是2,太多次见过了。

 

 一、关于存储过程使用局部变量,我们举例说明。

有这么一张表

存储过程:

create proc test

(

    @id int

)

as

select * from charge where charge_no > @id

 

那么exec test 99998,执行计划为:

请注意上图中的估计行数。

 

但是如果把存储过程修改为:

alter proc test

(

    @id int

)

as

declare @local int

set @local = @id

select * from charge where charge_no > @local

 

再次观察exec test 99998的查询计划:

请再次注意估计行数,现在是30000了。而我们都知道,修改前存储过程和修改后的输出结果集都没有任何变化,为2

由于charge_no是聚集索引,而我们的查询条件是where charge_no > XXX,不论SQLServer估计行数有多大,伊都会使用相同的clustered index seek查找到XXX,然后直接顺序遍历基础表剩下的叶节点。

但是,若charge_no是非聚集索引,由于估计结果集行数大小由两行变成了总行数的百分之三十(使用局部变量做查询条件,这种where AAA > BBBSQLServer无法估计结果集大小,所以它使用默认估计值:30%),nonclustered index seek变成nonclustered index scan(SQL2k5中若不是覆盖查询,会是clustered index scan),这是巨大的性能损耗,必须避免。

 

在这里顺带着再次强调另外一个问题:缓存的查询计划可能会强力的伤害性能。为了更详细的说明它,我们把存储过程test改为:

alter proc test

(

    @id int

)

as

select * from charge where charge_no > @id

 

然后看看执行计划exec test 99998(见上面的图,不重复贴了)。再来看看exec test 1的执行计划:


我们可以注意到,尽管真实的结果集变动非常巨大,但是查询计划还是完全不变,SQLServer在使用缓存。这种情况在使用聚集索引时不会让查询变得更糟,但是使用非聚集索引就会差上十万八千里,IO开销会差上n个数量级(n取决于真实的结果集)。

所以如果你的查询由于输入参数的不同,选择性变动剧烈,最好在创建存储过程的时候使用 WITH RECOMPILE选项。即:

create proc test

(

    @id int

)

with recompile

as

select * from charge where charge_no > @id

 

OK,但并不是所有的情况下在查询条件中使用局部变量都有问题。如果查询条件中涉及的索引,SQLServer发现伊的分布密度非常小(比如一个identity(1,1)列或者一个unique),那么在where AAA = XXX的情况下,SQLServer仍然会认为结果集相对总行数很小,而选择index seek类的查询计划。

 

二、  查询条件中的类型不匹配。

    所谓的类型不匹配是说,查询条件where AAA = @var,列AAA的定义和@var不同。例如,AAAvarchar(64)@varbigint。这种情况下,非常有可能让本来是index seek的运算变成index scan,在大数据量表中,性能差距会非常明显


    从我的经验来看,并不是所有的隐式转换都会带来这样的问题。但是这样的问题大量的存在,并且在分析性能瓶颈、做索引调优时,会给你带来极大的困扰。必须分析缓存中查询计划对应的原始语句,看那玩意属于慢性自杀。

我们写SQL一般都会类型匹配,但是通过应用程序就非常容易出错。比方说一个表有个MobileNo字段用来存储手机号码,表中是varchar。但是应用程序你这么写:

SqlConnection conn = ...;

           SqlCommand cmd = new SqlCommand("select * from Users where MobileNo = @mo", conn);

           cmd.Parameters.Add(new SqlParameter("@mo", 13511223344));

           SqlDataReader reader = cmd.ExecuteReader();

            //....

那么你挂了……

 

    到现在为止,我没有看到任何资料说哪种形式的隐式转换会让SQL无法判定结果集大小或者可以不去爬整棵索引树。所以我的建议是,使用最强类型去匹配查询列。查询列是啥,就写啥。是varchar(64)就别简单的new SqlParamerer(“@mo”, “13511223344”),要精确指定它的类型、长度。这样做有另外一个好处,偶将在下一篇blog——比较拼SQL、参数化SQL、使用存储过程执行DB指令的优劣时说明(btw:我相信那是一个好坑:))。


==加个总结=====================================
1、存储过程中,能不使用本地变量就不使用,尽可能的使用参数变量(也就是输入参数)。如果不得不使用本地变量,那也得只用在分布密度足够小的索引上使用。
2、写查询条件时,应该尽可能的使类型匹配。使用诸如SqlCommand执行DB指令时,一定要让输入参数从类型到长度严格匹配相应的列。尽管DB端不是所有的隐式转换都会引起性能损耗。
================================================


       稍微提一句,在msdn中SQL Server Database Engine>Troubleshooting the Database Engine > Troubleshooting Queries下有一篇《Troubleshooting Poor Query Performance: Constant Folding and Expression Evaluation During Cardinality Estimation》,尽管说的粗糙无比外带模棱两可,但还是推荐一读。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16998571/viewspace-567161/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-01-09

  • 博文量
    28
  • 访问量
    23960