讲师中心 微信公众号

工具推荐

视频效率加速
SoundView
SoundView

字幕擦除翻译、配音口型同步

Laper
Laper

剧本策划到成片协作

Seko
Seko

AI短剧一键生成成片

立刻MV
立刻MV

低成本歌曲 MV、视频生成

T-SQL动态查询(1)简介_MySQL

老伟姑娘_7274

老伟姑娘_7274

发布时间:2016-05-27 19:11:39

|

1448人浏览过

|

来源于php中文网

原创

 

起因:


由于最近工作需要及过去一直的疑问,所以决定着手研究一下动态sql。由于离开一线开发有点年头了,很多技巧性的东西没有过多研究,作为dba和《sql server性能优化与管理的艺术》一书的独立作者,更多的是关注在满足功能要求前提下的性能问题。但是我认为本文不仅对dba有用,对数据库开发人员甚至设计师、架构师等都有一定的参考价值。

 

前言:


读者是否遇到过类似功能:一个应用程序(不管是B/S还是C/S结构),有某个/些功能,允许用户选择不同的条件来进行查询(为了简便期间,这里仅针对查询功能)。极端情况下,用户可以通过界面选择结果集的不同的展示列、列展示顺序、排序形式甚至二次查询等。这些功能使用前端编程语言实现未尝不可,有时候甚至更佳,但是前端实现毕竟需要后台数据库的支持,所以这里更加集中在数据库层面。

上面的例子中,用户看到的可能就是这样,但是作为一个开发人员,你需要考虑的东西就不仅仅是这些了,假设我们使用存储过程来实现这个功能的数据支持,那么你需要考虑的是:不同的查询条件可能需要关联不同的表,比如需要同时筛选日期和货号,这两个条件是分布在A、B两表,那么你在查询时就要进行关联。但是当用户选择筛选货号和客户名时,这些数据可能分布在B、C两表,那么你需要在同一个存储过程中进行B、C表的关联。

由于关系数据库理论的限制,你很难做到灵活构建这种逻辑的同时保持代码的简洁及高效。除了用游标加大量判断这种几乎能实现任何功能但又往往极其低效的方式之外,动态SQL就出现在我们的考虑范围里面。我们需要借助某些工具来实现两个目的:得到正确数据和获得合理的性能。

简介:


在现代信息系统中,上面的例子广泛存在。但是随之引发的一个核心问题是:在SQL Server(估计在所有RDBMS中均是),没有一个单一的执行计划可以很好地支持所有的可能的查询条件。同时你也希望SQL Server针对用户的输入进行优化。

通常在数据库层面实现这种要求的方式有两种:

1. 编写带有查询提示(如OPTION(RECOMPILE))的静态SQL,强制SQL Server每次都对查询进行编译。

2. 使用动态SQL 创建一个可以覆盖用户定义的查询条件的字符串并执行语句。

这两种方式都有适用常见,各有优缺点,不能简单地说哪个好哪个不好,本系列文章将介绍两类的相关内容。

注意:本系列将使用SQL 2008 R2并用微软示例数据库AdventureWorks2008 R2作为演示。

基础知识:

为了让读者有一些知识准备,本问先介绍一些基础知识,这些基础知识有:

执行计划执行计划缓存统计信息参数嗅探(简介)

注:为了控制文章的篇幅,本系列文章不打算深入探讨上面的知识,更详细的信息可查阅本人书籍《SQL Server性能优化与管理的艺术》中相关信息。

执行计划:

当一个SQL语句(SQL语句/批 或存储过程)提交给SQL Server之后,SQL Server会先分析语句的语法,若不符合要求马上停止后续操作并返回错误信息给客户端。

当SQL语句语法分析通过之后,SQLServer会根据语句的文本在内存的计划缓存中(Plan Cache,属于内存的Buffer Pool一部分)查找是否有可用的执行计划,若有,则直接执行,若无,则进行编译阶段。编译后生成执行计划提交给存储引擎进行执行并缓存。大概流程如下:

\

 

执行计划是性能的核心之一,存储引擎根据执行计划访问和返回数据给客户端,好的执行计划能高效、快速地完成数据请求,而不合理的执行计划会使一个本来很简单的请求的运行时间从几秒上升到数小时。而且执行计划也是分析性能问题的核心工具。在SQL Server Management Studio(SQL 2000叫查询分析器,SQL2005开始简称SSMS),查看执行计划可以通过快捷键:Ctrl+M(实际执行计划)和Ctrl+L(预估执行计划)来获取,也可以通过图形化界面中的下图来获取:

\

 

执行计划缓存:

在SQL Server优化器对语句进行编译和优化之后,提交给存储引擎执行,随即把执行计划缓存到内存的Plan Cache中以便后续重用。

计划缓存的最重要的目的是为了减少不必要的编译和重编译,这两个操作在负载很大的系统中会带来明显的CPU压力。如果在一个一秒钟某个存储过程会被调用超百次的系统中,避免一个只需要5ms的编译过程,可以使存储过程的执行计划从5分钟降到100ms。

另外计划缓存与语句的文本有密切关系,后续会介绍,对于下面两个语句,他们的执行计划是可以被重用的:

 

SELECT ID,A,B FROM TB WHERE ID=1
SELECT ID,A,B FROM TB WHERE ID=10

 

但是下面两个语句会被认为两个不同的语句,导致第二个语句被认为新语句而进行编译,通常会导致性能问题和资源浪费:

 

SELECT ID,A,BFROM TB WHERE ID=1
SELECT ID,A,B FROM     TB WHERE ID=10  --注意空格


 

对于这类问题,通常的解决方案是合理的编码规范和使用参数化查询,后续将介绍。

简单的CSS点动画加载特效
简单的CSS点动画加载特效

简单的CSS点动画加载特效

下载

另外,计划缓存有先进先存的特性,假设下面一个存储过程:

 

CREATEPROC TEST (@ID INT)
AS
  SELECT ID,A,B FROM TB WHERE ID=@ID
GO

同时假设表TB上ID列的数据分布极其不均匀,比如ID=1的数据在列上占了90%,而ID=10的数据在列上只有0.01%,数据行超百万,并且有合适的索引支持。在这种情况下,针对ID=1的查询,合理的应该是进行索引扫描,而对于ID=10的查询,合理的应该是索引查找。当第一次运行存储过程并以@ID=1为参数时,执行计划按索引扫描的方式产生并缓存,这是对的。但是当第二次执行是以@ID=10为参数时,可能因为某些原因执行计划被重用,依旧使用索引扫描方式,此时你可能会明显感觉到性能降低。这种情况反之亦然。关于这个情况在后续会整理一个关于参数嗅探的系列文章中详述。

最后,计划缓存是在内存中,也就是说,它是不稳定的,重启服务/服务器或某些命令和操作都可能导致计划被刷出内存。如有必要可以定期收集并存储在专门的监控数据库中或把执行计划另存为文件。

 

统计信息:


统计信息是一个描述表数据的“表”,简单来说就是描述数据的数据。我们常说的数据分布,对于SQL Server来说,就是存储在统计信息中。统计信息可以是自动创建或者手动创建的,并且通常在索引创建时就会附带创建对应的统计信息。关于统计信息的详述可见《SQL Server性能优化与管理的艺术》第六章。

统计信息也是性能优化的核心,SQLServer在分析语句语法之后,会根据统计信息的内容,选择执行计划中使用什么索引及表关联的算法,统计信息的过时和不准确会严重影响执行计划生成和服务器的整体性能。

同时,在优化过程中,如果统计信息足够准确,优化器可以通过判断数据是否有需要关联从而避免不必要的表关联。系列文章的后续部分也会做演示。

提醒:现行统计信息的算法最晚从SQL2000开始沿用至今,但是从SQL 2014开始做了大改动,算法更加合理,所以如有条件,建议使用SQL 2014或后续版本。

 

参数嗅探:


很多地方把这个功能视为性能杀手或者一个贬义词,但是存在即合理,我们应该具体情况具体分析。那么参数嗅探是什么呢?在执行计划缓存部分已经介绍了那个例子。在一些存储过程或者其他对象中,使用了传参数的方式,但是SQL Server并不知道你在实际执行时会传入什么参数,此时SQL Server就会使用一个预估值用于生成执行计划,但是在某些情况下,SQL Server也会“嗅探”实际传入的参数,并判断缓存中的执行计划针对当前语句及其参数是否不合理,是否有必要进行重编译。

所以这种功能其实是必要的,并且很多情况是有用的。但是如果因为某些原因,在本来不需要使用的时候使用了这个功能,会导致原来很合理的执行计划被抛弃,选择不合理的执行计划,其结果就是查询运行好好的,但是突然变得奇慢无比。

总的来说,需要了解参数嗅探的本质,才能下定论。后续会专门写一个关于参数嗅探的文章,一旦发布会在本文中加入链接。

 

环境准备:


软件环境:SQL Server 2008 R2,最好企业版。操作系统随意。

示例数据库:AdventureWorks 2008 R2,下载地址:http://msftdbprodsamples.codeplex.com/releases/view/59211

 

除了上面环境,还要在数据库中构建一个存储过程的模版,说是模版,其实就是后续演示过程中,在这个模版里面添加某些语句而已:

 

CREATE PROCEDURE sp_Get_orders
                 @salesorderid     int     = NULL,
                 @fromdate    datetime     = NULL,
                 @todate      datetime     = NULL,
                 @minprice    money        = NULL,
                 @maxprice    money        = NULL,
                 @custid      nchar(5)     = NULL,
                 @custname    nvarchar(40) = NULL,
                 @prodid      int          = NULL,
                 @prodname    nvarchar(40) = NULL,
                 @employeestrvarchar(MAX) = NULL,
                 @employeetblintlist_tbltype READONLYAS
 
SELECT o.SalesOrderID, o.OrderDate, od.UnitPrice, od.OrderQty,
       c.CustomerID, per.FirstName as CustomerName,p.ProductID,
       p.Name as ProductName,  per.BusinessEntityID as EmpolyeeID
FROM   Sales.SalesOrderHeader o
INNER JOIN   Sales.SalesOrderDetail od ON o.SalesOrderID = od.SalesOrderID
INNER JOIN   Sales.Customer c ON o.CustomerID = c.CustomerID
INNER JOIN   Person.Person per on c.PersonID=per.BusinessEntityID
INNER JOIN   Production.Product p ON p.ProductID = od.ProductID
WHERE  ???
ORDER  BY o.SalesOrderID
GO


 

注意上面WHERE ???的意思是后续例子中将会通过增减这个 WHERE条件并创建新命名的存储过程来演示。

简要说明一下存储过程的参数:

 

@salesorderid 订单ID
@fromdate 起始日期
@todate 结束日期
@minprice 最低价
@maxprice 最高价
@custid 客户ID
@custname 客户名
@prodid 产品ID
@prodname 产品名
@employeestr 雇员ID的字符串组合,以逗号分割
@employeetbl 雇员ID的表值参数

 


 

 

如果用户不使用任何查询条件,那么查询条件将不起筛选作用,因此需要一个单纯的EXEC SP_GET_ORDERS的方式返回所有数据库订单的功能。

通过对存储过程的改写,可以应对业务中的下面需求:

 

用户可以选择如何对结果排序。依赖于输入的参数,语句可以访问不同的表或列。用户可以选择比较操作符,如@custname=’xxxx’或@custname !=’xxxx’。用户可以从输出结果中添加或移除列,也可以在聚合查询中选择聚合什么。其他你能想到甚至你不能想到但客户会想到的需求。

下一篇文章:T-SQL动态查询(2)——关键字查询

热门AI工具

更多
墨刀AI
墨刀AI Hot

一款AI图像与设计工具,主要用于产品经理的专属智能体,适合需要提升相关任务效率的用户。

WorkBuddy

一款AI办公效率工具,主要用于腾讯云推出的AI原生桌面智能体工作台,适合需要提升相关任务效率的用户。

火山引擎

火山引擎是一款面向企业的云计算与AI服务平台。

立刻MV
立刻MV Hot

立刻MV是一款AI文本写作工具,AI 音乐视频(MV)创作工具。

豆包大模型

豆包大模型是一款由字节跳动推出的企业级大语言模型服务平台。

超级麦吉

一款通过数字员工协作完成调研、数据分析与文档制作的AI办公平台。

音述AI
音述AI Hot

一款AI音频处理工具,主要用于音述AI是一个以“用声音述说故事”为核心的 AI 音乐创作与声音分享社区,适合需要提升相关任务效率的用户。

DeepSeek

DeepSeek是一款面向对话、写作、编程和推理场景的AI大模型工具。

小橡皮AI

一款AI工具,主要用于小橡皮一键去AI味,将机器味重的内容改得更有活人感,防止因为 AI 内容被平台打上 AI 标签,以及限流,适合需要提升相关任务效率的用户。

相关专题

更多
GoFrame框架Web开发与路由中间件
GoFrame框架Web开发与路由中间件

本专题聚焦GoFrame框架的Web开发,拆解路由注册与匹配规则、请求参数获取、参数校验与中间件执行顺序,说明统一响应、JWT鉴权、文件上传与接口文档生成的落地方式,并补充优雅重启与测试实践。

0

2026.11.17

GoFrame框架安装与gf命令行工具
GoFrame框架安装与gf命令行工具

本专题围绕GoFrame框架的安装与工具链配置展开,讲清Go版本要求、gf命令行的安装方式、代理镜像与依赖准备,说明项目初始化、热编译、交叉编译打包的做法,并整理安装阶段的常见报错与验证步骤。

0

2026.11.17

GoFrame框架零基础入门
GoFrame框架零基础入门

本专题整理GoFrame框架的入门知识,从模块化设计理念、环境准备到跑通第一个Web服务,讲清目录分层、配置加载、日志与中间件的用法,并对比Gin、Go-Zero的差异,帮助Go开发者建立整体认知。

0

2026.11.17

SQLAlchemy 一对多关系怎么建立
SQLAlchemy 一对多关系怎么建立

SQLAlchemy 一对多关系怎么建立?本专题整理父子表外键、relationship 双向映射、关联查询、级联删除和加载优化等相关文章,帮助你理解并实现常见的 ORM 关系建模。

0

2026.11.17

SQLAlchemy 如何增删改查
SQLAlchemy 如何增删改查

SQLAlchemy 如何增删改查?本专题整理 ORM 新增记录、条件查询、更新和删除、Session 事务提交与批量处理等实用文章,帮助你掌握 Python 数据库操作流程。

0

2026.11.17

SQLAlchemy ORM 模型怎么写
SQLAlchemy ORM 模型怎么写

SQLAlchemy ORM 模型怎么写?本专题整理模型类定义、Mapped 字段映射、主键与约束、表结构创建和常见报错的相关文章,帮助你从基础配置到实际建模逐步掌握 SQLAlchemy。

0

2026.11.17

Go-Zero API开发使用教程
Go-Zero API开发使用教程

本专题聚焦Go-Zero的API开发,拆解.api描述文件的语法结构、路由与分组定义、参数绑定与校验规则,说明统一响应体、JWT鉴权、跨域与接口文档生成的落地方式,并给出网关聚合与接口测试的实践建议。

180

2026.11.12

Go-Zero安装与goctl环境配置教程
Go-Zero安装与goctl环境配置教程

本专题围绕Go-Zero的安装与工具链配置展开,讲清Go版本要求、goctl安装方式、代理镜像、protoc依赖与环境变量设置,说明多版本共存、升级回退与CI流水线安装的做法,并整理安装阶段的常见报错与验证步骤。

120

2026.11.12

Go-Zero零基础入门教程
Go-Zero零基础入门教程

本专题整理Go-Zero的入门知识,从框架定位、环境准备到跑通第一个API服务,讲清目录分层、配置加载、日志与中间件的基本用法,并对比Gin、Kratos的差异,帮助Go开发者建立整体认知,判断它是否适合当前项目。

100

2026.11.12

热门下载

更多
网站特效
/
网站源码
/
网站素材
/
前端模板

精品课程

更多
相关推荐
/
热门推荐
/
最新课程
关于我们 免责申明 举报中心 意见反馈 讲师合作 广告合作 最新更新
php中文网:公益在线php培训,帮助PHP学习者快速成长!
关注服务号
PHP中文网订阅号
每天精选资源文章推送

Copyright 2014-2026 https://www.php.cn/ All Rights Reserved | php.cn