SQLServer查询优化和事务处理

对于了解掌握SQL的增、删、改、查的语句操作是最基本的，实际生产环境中，我们还会用到一些比较高级的数据处理和查询，包括索引、视图、存储过程和触发器。本篇博文主要如何更好的实现对数据库的操作、诊断及优化。

创新互联专注于企业成都全网营销推广、网站重做改版、秦都网站定制设计、自适应品牌网站建设、html5、成都商城网站开发、集团公司官网建设、外贸网站制作、高端网站制作、响应式网页设计等建站业务，价格优惠性价比高，为秦都等各大城市提供网站开发制作服务。

博文大纲：
一、索引；
二、视图；
三、存储过程；
四、触发器；
五、事务；

一、索引

索引提供指针以指向存储在表中指定列的数据值，然后根据指定的次序排列这些指针，再跟随指针到达包含该值的列。

1.什么是索引

数据库中的索引与书籍中的目录相似。在一本书中，无需阅读整本书，利用目录就可以快速的查找到所需的信息。在数据库中，索引使数据库程序无须对整个表进行扫描，就可以在其中找到所需的数据。书中的目录就是一个词语列表，其中注明了包含各个词的页码。而数据库中的索引是某个表中一列或若干列值的集合，以及物理表示这些值得数据业的逻辑指针清单。

索引是SQL Server编排数据的内部方法，它为SQL Server提供一种方法来编排查询数据的路由。

索引页是数据库中存储索引的数据页。索引页存放检索数据行的关键字页以及该数据行的地址指针。通过使用索引，可以大大提高数据库的检索速度，改善数据库性能。

2.索引分类

在SQL Server中，常用的索引有：

（1）唯一索引

唯一索引不允许两行具有相同的索引值。

如果现有数据中存在重复的键值，则一般情况下大多数数据库不允许创建唯一索引。当新数据使表中的键值重复时，数据库也拒绝接收此数据。

创建了唯一约束，将自动创建唯一索引。尽管唯一索引有助于找到信息，但是为了获得最佳性能，建议使用主键约束。

（2）主键索引

在数据库关系图中为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。

主建索引要求主键中的每个值都是唯一的。当在查询使用主键索引时，它还允许快速访问数据。

（3）聚集索引

在聚集索引中，表中各行的物理顺序与键值的逻辑（索引）顺序相同。

一个表中只能包含一个聚集索引。

（4）非聚集索引

非聚集索引建立在索引页上，在查询数据是可以从索引中找到记录存放的位置。
非聚集索引使表中各行数据存放的物理顺序与键值的逻辑顺序不匹配。聚集索引比非聚集索引有更快的数据访问速度。

在SQL Server中，一个表只能创建一个聚集索引，但可以有多个非聚集索引。设置某列为主键，该列就默认为聚集索引。

（5）复合索引

在创建索引时，并不是只能对其中一列创建索引，与创建主键一样，可以将多个列组合作为索引，这种索引称为复合索引。

注意：只有用到复合索引的第一列或整个复合索引列作为条件完成数据查询时才会用到该索引。

（6）全文索引

全文索引是一种特殊类型的基于标记的功能性索引，由SQL Server中全文引擎服务创建和维护。

全文索引主要用于在大量文本中搜索字符串，此时使用全文索引的效率将大大高于使用T-SQL的LIKE关键字的效率。

3.创建和使用索引

创建索引的方法有两种：使用SSMS和T-SQL语句。

使用SSMS创建索引，如下：



使用SSMS创建索引完成！

select * from chengji
WITH (INDEX=IX_chengji)
where name LIKE '孙%'
//使用创建的索引查询数据

虽然可以指定SQL Server按哪个索引进行数据查询，但一般不需要人工指定。SQL Server将会根据所创建的索引，自动优化查询。

使用索引可加快数据检索速度，但为每个列都建立索引没有必要。因为索引本身也是需要维护，并占用一定的资源，可以按照以下标准选择建立索引的列。

• 频繁搜索的列；
• 经常用于查询选择的列；
• 经常排序、分组的列；
• 经常用于连接的列（主键/外键）；

不要使用下面的列创建索引：

• 仅包含几个不同值的列；
• 表中仅包含几行；

在工作中的经验：

• 查询时减少使用“*”返回全部列，不要返回不需要的列；
• 索引应该尽量小，在字节小的列上建立索引；
• where子句中有多个条件表达式时，包含索引列的表达式应置于其他条件表达式之前；
• 避免在order by子句中使用表达式；
• 根据业务数据发生频率，定期重新生成或重新组织索引，进行碎片整理；

二、视图

视图是保存在数据库中的select查询。因此，对查询指定的大多数操作也可以在视图上进行。

使用视图的原因有：

• （1）处于安全考虑，用户不必看到整个数据库结构，而隐藏部分数据；
• （2）符合用户日常业务逻辑，使其对数据更容易理解；

1.什么是视图

视图是另一种查看数据库中一个或多个表中的数据的方法。视图是一种虚拟表，通常是作为来自一个或多个表的行或列的子集创建的。当然，视图也可以包含全部的行和列。但是，视图并不是数据库中存储的数据值的集合，它的行和列来自查询中引用的表。在执行时，视图直接显示来自表中的数据。

视图充当着查询中指定的表的筛选器。定义视图的查询可以基于一个或多个表，也可以基于其他视图、当前数据库或其他数据库。

视图通常用来进行以下三种操作：

• 筛选表中的行；
• 防止未经许可的用户访问敏感的信息；
• 将多个物理数据表抽象为一个逻辑数据表。

（1）使用视图的好处：

（1）对最终用户的好处：
结果更容易理解；
获得数据更容易；
（2）对开发人员的好处：
限制数据检索更容易；
维护应用程序更方便；

（2）创建视图

语法：

create view aa
as
SELECT     dbo.基本信息表.学号, dbo.成绩表.学号 AS Expr1, dbo.基本信息表.姓名, dbo.成绩表.成绩
FROM         dbo.基本信息表 INNER JOIN
                      dbo.成绩表 ON dbo.基本信息表.学号 = dbo.成绩表.学号

查看视图

select * from aa

（3）使用视图的注意事项

（1）每个视图可以使用多个表；
（2）与查询相似，一个视图可以嵌套另一个视图，最好不要超过三层；
（3）视图定义中的select语句不能包含以下内容：

• order by子句，除非子啊select语句的选择列表中也有一个TOP子句；
• into关键字；
• 引用临时表或表变量；

三、存储过程

SQL Server使用存储过程来避免远程发送并执行SQL代码带来的安全隐患。

1.为什么需要存储过程

当今的软件大多应用于网络中，而一般应用程序所运用的数据保存在数据库中。在没有使用存储过程的数据库应用程序中，用户大多从本地极端及客户端通过网络向服务器端发送SQL代码编写的请求，服务器端对接收到SQL代码进行语法编译后执行，并经指定结果传送回客户端，再由客户端的应用软件处理后输出。如果开发者对服务器的安全性考虑不全面，就会为非法者提供盗取数据的机会。如图：

未经授权的非法者在网络中截取用户想服务器发送的SQL代码，改写后的恶意SQL代码提交到服务器编译并执行，最后非法者就比较容易地获得他所需的数据。

从图中，我们可以看到应用程序执行的过程是不安全的，主要在于以下几个方面：

• 数据不安全：网络传送SQL代码，容易被未经授权者截取；
• 每次提交SQL代码都要经过语法编译后再执行，影响应用程序的运行性能；
• 网络流量大：对于反复执行的相同SQL代码，将会在网络上多次传送，增加网络传输流量。

为了解决这些问题，我们可以采用存储过程把对数据库操作的SQL代码预先编译好并保存在服务器端，用户只需在本机上输入要执行的存储过程名称和必要的数据就可以直接调用执行存储过程完成行管的操作。这样。既减少了网络传输流量，又能保证应用程序的运行性能，同时也防止了未经授权者想截获SQL代码的行为。

2.什么是存储过程

存储过程是SQL语句和控制语句的预编译集合，保存在数据库中，可由应用程序调用执行，而且允许用户声明变量，逻辑控制语句及其他强大的编程功能。

使用存储过程的优点：

• 1.模块化程序设计；
• 2.执行速度快、效率高；减少网络流量；
• 3.减少网络流量；
• 4.具有良好的安全性；

3.系统存储过程

SQL Server提供系统存储过程，它们是一组预编译的T-SQL语句。系统存储过程提供了管理数据库和更新表的机制，并充当从系统表中检索信息的快捷方式

（1）常用的系统存储过程

SQL Server的系统存储过程的名称以“sp-”开头，并存放在Resource数据库中。如图：

比如：

exec sp_databases
#列出当前系统中的数据库
exec sp_helptext aa
#查看视图的语句文本

若xp_cmdshell作为服务器安全配置的一部分而被关闭，请使用如下语句启用：

exec sp_configure 'show advanced options',1
#显示高级配置信息
go
reconfigure
#重新配置
go
exec sp_configure 'xp_cmdshell',1
#打开xp_cmdshell选项
go
reconfigure
#重新配置
go

（2）常用的扩展存储过程

比如使用这些语句在系统中创建某些文件：

exec xp_cmdshell 'md c:\bank',no_output
#创建文件夹c:\bank
exec xp_cmdshell 'dir c:\'
#列出c盘下的文件等内容

create   proc  oo
as
select 姓名, SUM(成绩)  as 总成绩
from   基本信息表  left join  成绩表  on  基本信息表.学号=成绩表.学号
group  by  姓名
#创建存储过程qq
exec qq
#查看存储过程qq
create   proc  ww
@shuo  varchar(10)
as
select 姓名, SUM(成绩)  as 总成绩
from   基本信息表  left join  成绩表  on  基本信息表.学号=成绩表.学号
group  by  姓名
having  姓名=@shuo
#创建针对每个同学查看的记录
exec ww 张三
#查看ww存储过程但是只查看张三

四、触发器

触发器分为以下几种
INSERT触发器：当向表中插入数据时触发，自动执行触发器定义的SQL语句；
UPDATE触发器：当更新表中某列、多列时触发，自动执行触发器所定义的SQL语句；
DELETE触发器：当删除表中记录时触发，自动执行触发器定义的SQL语句。
两个特殊的表由系统管理：

创建触发器的语句：
第一种

create   trigger   删除
on  科目表
for  delete
as
begin
delete  from 成绩表  
end
#删除之后不会同步从表中的数据

第二种

create   trigger   自动同步成绩
on  科目表
after delete
as
begin
delete  from 成绩表  where 科目id=(select   科目id  from deleted)
end
#删除之后自动同步成绩

第三种

create  trigger  禁止删除
on 基本信息表
for  delete
as
print  '禁止删除'
rollback  transaction
#禁止删除数据，如果删除数据则执行回滚、撤回操作

五、事务

事务：保证数据库的原子性、一致性、隔离性、持久性，简称ACID。
一个小实例

begin transaction
declare @errorsum int
set @errorsum=0
#定义 内部变量，用来保存前一条的执行结果，执行成功为0，执行不成功为非0.
/*--转帐：张三的帐户少1000，李四的帐户多1000元--*/
update bank set currentmoney=currentmoney-1000
where name='zhangsan'
set @errorsum=@errorsum+@@ERROR 
update bank set currentmoney=currentmoney+1000
where name='lisi'
set @errorsum=@errorsum+@@error
print '查看转账事务中的余额'
select * from bank
if @errorsum<>0
 begin
  print '交易失败，回滚事务'
  rollback transaction
 end
else
 begin
   print '交易成功，提交事务，写入硬盘，永久地保存'
   commit transaction
  end
 go
 print '查看转账事务后的余额'
 select * from bank
 go

———————— 本文至此结束，感谢阅读 ————————