T-SQL动态查询（2）——关键字查询

接上文：T-SQL动态查询（1）——简介

 

前言：

在开发功能的过程中，我们常常会遇到类似以下情景：应用程序有一个查询功能，允许用户在很多查询条件中选择所需条件。这个也是本系列的关注点。

但是有时候你也许会发现，有些条件或多或少是互相排斥的。比如用户通过下面其中一个条件查找信息：

1．  客户名

2．  客户ID

3．  客户身份标识号（如国内身份证、美国社保号等）。

并且这三列上都有适当的索引。本系列主要研究动态SQL和OPTION(RECOMPILE)查询提示来处理需求，但是前面已经提到过，频繁编译、重编译会给服务器带来严重压力，特别是对于那些操作特别频繁的功能。另外对于一些简单的问题使用动态SQL也略微浪费。

针对上面的问题，我们可以使用一个较为轻量的方法：使用IF语句。

本文出处：http://blog.csdn.net/dba_huangzj/article/details/49929669

IF语句：

针对上面问题，我们来看看下面例子：

IF @custno IS NOT NULL  --客户号   SELECT ... FROM customers WHERE custno = @custnoELSE IF @idno IS NOT NULL  --身份标识号   SELECT ... FROM customers WHERE natregno = @natregnoELSE IF @custname IS NOT NULL --客户名   SELECT TOP 200 ...   FROM   customers   WHERE  custname LIKE @custname + '%'   ORDER  BY custnameELSE   RAISERROR('没有提供查询条件！', 16, 1)

注意：不要过于纠结里面的表和列是否存在，这里只是个大概演示。

另外，上面的TOP 200是为了避免由于用户输入一个非常短的字符串查询客户名时，由于过于模糊的查询导致返回大量数据导致性能问题。

如果这时候你系统同时返回其他表的数据，并且不喜欢重复JOIN，可以把所有匹配的客户数预存到一个表变量或临时表，然后最后再JOIN：

IF @custno IS NOT NULL   INSERT @cust(custno) VALUES (@custno)ELSE IF @natregno IS NOT NULL   INSERT @cust(custno) SELECT custno FROM customers WHERE natregno = @natregnoELSE IF @custname IS NOT NULL   INSERT @cust(custno)      SELECT TOP (200) custno      FROM   customers      WHERE  custname LIKE @custname + '%'      ORDER  BY custnameELSE   RAISERROR('没有提供查询条件！', 16, 1) SELECT ...FROM   @cust cJOIN   customers cst ON cst.custno = c.custnoJOIN   ...

本文出处：http://blog.csdn.net/dba_huangzj/article/details/49929669

这种写法有一个潜在的性能问题，不管用户如何选择查询条件，我们都希望优化器能使用查询列上的索引。但是由于SQL Server创建执行计划的方式导致这种情况很难总是如愿。前文提到过参数嗅探的问题，当存储过程被执行并在缓存中没有找到可重用的执行计划时，SQL Server会对整个存储过程及当前值进行“嗅探”，产生一个对当前值最优的执行计划。

换句话说，如果第一个用户选择以客户号作为参数查询，那么优化器会对客户号进行优化，而底层处理中会对客户名附以NULL的形式，如果后续用户使用客户名进行搜索，会导致表扫描，这种情况肯定不是你期望的。

为了避免这种情况，可以使用一些预防措施。其中一种是把存储过程中原有的SELECT语句拆成三种针对性的SELECT语句，但是无可否认的是，这种方式会使得语句越来越庞大。另外一种是在语句中使用索引提示指定索引，但是这样语句就被绑死了，如果由于某些原因导致索引重命名，那么语句会运行失败。

在某种程度上，使用适当的OPTIMIZEFOR提示可能是更好的选择：

SELECT TOP 200 custnoFROM   customersWHERE  custname LIKE @custname + '%'ORDER  BY custnameOPTION (OPTIMIZE FOR (@custname = N'ZZZZZZZ'))

这种提示会触发SQL Server针对你指定的值（如上面的ZZZZZZ）进行查询计划的创建，而不会在乎你实际传入什么值。但是这时你就要挑选一个有足够选择度和代表性的值，如果指定了一个很少会用到的值，那比不指定可能更惨。

但是不管使用什么方式，都应该在生产环境规模的数据（最好能模拟生产环境的行为）上进行测试以确保执行计划和性能都能符合你的期望。

基于参数嗅探的原因，你需要做类似下面的测试：

[email protected] = 123[email protected] = '1234567890'[email protected] = 'ABC'EXEC sp_recompile你的SP   -- 清空存储过程缓存[email protected] = '1234567890'[email protected] = 123[email protected] = 'ABC'EXEC sp_recompile你的SP[email protected] = 'ABC'[email protected] = 123[email protected] = '1234567890'

也就是说，你需要测试所有参数在单独作为首次执行计划生成时的参数的情况，如果参数很多，你能想象需要测试的次数也很多。注意上面的sp_recompile，是为了通过重编译清空计划缓存以便减少计划缓存带来的影响。

在上面例子中，[email protected]%，这种情况下，扫描可能是更好的方式。如果你需要支持查询字符串前面有%的查询，最好的方式是拆成两个分支，如：

IF left(@custname, 1) <> '%'   -- 上面的查询ELSE   -- 带有OPTIMIZE FOR的查询语句

小结：

目前为止，我们看到使用IF来实现一定程度的动态查询。这种方式不是很适合条件非常多的情况，正如前面所示，你要测试和编写的条件非常多，可能导致代码庞大不便于维护。但是对于简单的情况，比如2、3种参数时，这种方式却很有效，后续会介绍其他情况下的应对措施。

 本文出处：http://blog.csdn.net/dba_huangzj/article/details/49929669

OR：

如果你不想使用多个IF判断，并且可以忽略前面提到的字符串前加%的情况，那么语句完全可以改写成类似下面的方式：

SELECT TOP 200 ...FROM   customersWHERE  (custno = @custno AND @custno IS NOT NULL) OR       (natregno = @natregno AND @natregno IS NOT NULL) OR       (custname LIKE @custname + '%' AND @custname IS NOT NULL)ORDER  BY custname

这里的WHERE子句实际上是：

custno = @custno OR natregno = @natregno OR custname [email protected] + '%'

但是分别加上IS NOT NULL是有目的的，加上之后，优化器可以把三个条件上的索引通过索引连接的方式来生成执行计划。由于IS NOT NULL条件，SQL Server可以在运行时添加一个名为“启动表达式（startup expression）”的筛选操作符，这个操作符可以根据实际情况仅访问所需的索引，而不会像代码中的那样。

不过这种策略需要所有的查询条件都作用在一个表上，并且有相对合理的索引。如果查询条件涉及不同的表，那么性能上并不一定满足你的期望。

 

小结：

很多编码和优化资料上显示不要过多使用OR，因为会导致非SARG的出现。从而影响性能。但是在上面情况中，OR表现得还可以，所以我们不要因为某些“铁律”、“军规”而不去考虑和尝试其他方式。最后还要强调一下，不要盲目使用这种策略，你需要验证每种策略的执行计划和性能是否满足期望。

 本文出处：http://blog.csdn.net/dba_huangzj/article/details/49929669

全文检索：

除了上面两种方式之外，当你需要查找一个表或者一个固定集合（也就是说不会根据条件动态添加移除数据表）进行不同条件的查询时，使用大量的索引来支持各种查询往往不能得到什么好处。

从SQL 2005开始引入了全文检索（Fulltext），从一定程度上解决了这种问题。但是从应用经验上来说，由于计算机和SQL Server这类RDBMS是老外开发的，所以对中文的支持并不如人意。所以这里只是提一下，对于英文环境的系统，这种方式是可以考虑的。

 本文出处：http://blog.csdn.net/dba_huangzj/article/details/49929669

总结：

本文演示了对于一些简单的关键字查询的处理方案。主要使用了IF/OR两种方式，正如文中多处写到的，任何一种方式都应该做充分的验证和测试，特别是在一定数据量下，否则你在小数据库上运行得很好，说不定到正式环境下就奔溃了。

下一篇将专门介绍静态SQL的相关知识。

1楼landingbj昨天 18:00

看到您的博文，非常希望您能来 联动北方技术论坛 发表您的观点，在您发表后，我们会及时反馈到后台管理员将您的文章推到首页，并且会发表到官方公众微信号及微博，您的到来一定能活跃论坛的氛围并推动大家的学习与思考哒~~在此，非常诚挚的邀请您,如果您的时间充足是否还可以考虑加盟论坛版主呢~~期待您的回复，谢谢^^n论坛：http://bbs.landingbj.com/n论坛专注于中间件技术、Oracle/MySql/Sql Server数据库技术、Unix/Linux技术、Java和企业信息化等领域