Windows server 2016——查询优化与事务处理

• 作者简介：一名云计算网络运维人员、每天分享网络与运维的技术与干货。 
• 公众号：网络豆
•  座右铭：低头赶路，敬事如仪
• 个人主页： 网络豆的主页​​​​​

写在前面

本系列文章将会讲解SQL server 中 查询优化与事务处理，了解使用索引工具，使用视图，存储过程，触发器等操作。

视频教程：Windows server 2016 查询优化与事务处理

介绍

SQL Server是由微软公司开发的关系型数据库管理系统，现在是全世界主流数据库之一。它具备方便使用、可伸缩性好、相关软件集成程度高等优势，能够从单一的笔记本上运行或以高倍云服务器集群为基础，或在这两者之间任何一种方式上运行。

 一.索引

1.索引是什么

索引是SQL Server编排数据内部方法，通过索引可以快速查找数据，而无需扫描整个表。

类似于字典的音节索引页似的，可以快速查找到，你所想要找到的字而无需一页一页翻。

 2.索引的分类

• 唯一索引：不允许两行具有相同的索引值，如果尝试插入一个已经存在的值，数据库将会拒绝这个操作。
• 主键索引：要求主键中的每个值是唯一的，主键索引通常用于唯一标识每一行，以便于快速查找和连接数据。
• 聚集索引：数据存放的物理顺序与索引顺序相同，聚集索引可以加快基于索引列的数据检索操作
• 非聚集索引：数据存放的物理顺序与索引顺序不相同
• 复合索引：将多个列组合而成的索引
• 全文索引：一种特殊类型的基于标记的功能性索引，全文索引通常用于处理大量文本数据，如文章、博客帖子或文档

3.唯一索引和主键索引的区别：

      主键索引一定是唯一索引，唯一索引不一定是主键索引。主键索引一定是聚集索引。唯一索引不一定是聚集索引。

4.选择索引的标准

• 频繁搜索的列
• 经常用作查询选择的列
• 经常排序、分组的列
• 经常用作连接的列（主键/外键）
• 大量数据

5.创建索引

1.选中一张表，展开表，找到索引，右击索引——新建索引

2.在新建索引对话框中，指定索引名称，可以选择给那一列创建索引，创建什么类型的索引，完成后点击确定，就创建好了一个新的索引。

二.视图

1.什么是视图

• 视图是一种虚拟表，通常是作为来自一个或多个表的行或列的子集创建的。
• 视图直接显示来自表中的数据，只供查看，无法修改。

 视图的作用

• 筛选表中的数据
• 防止未经许可的用户访问敏感数据
• 将多个物理数据表抽象

使用视图的好处

• 对于最终的用户：结果更容易理解，获取数据更容易。
• 对于开发：限制数据检索更容易，维护应用程序更方便

创建视图：

   1.展开数据库——找到视图，右击视图——新建视图

 2.选择要添加到视图中的表，选择表然后点击添加。可以添加多张表。添加完成后点击关闭。

手动选择表之间的连接条件，并选择最终要在视图中显示的列。（在要显示的列前面打钩）

配置完后，点击保存

 三.存储过程

1.什么是存储过程

存储过程是SQL语句和控制语句的预编译集合,保存在数据库里，可由应用程序调用执行。

2.为什么需要存储过程

• 数据不安全
• 网络流量大
• 影响应用程序的运行性能
• 从客户端到网络服务器发送SQL代码并执行不妥当

3.使用存储过程的优点

• 模块化程序设计
• 执行速度快，效率高
• 减少网络流量
• 具有良好的安全性

4.存储过程分类

（1）系统存储过程

常用的系统存储过程（以“sp_”开头，存放在Resource数据库中）

   2.常用的扩展存储过程（以“xp_”开头，以DLL形式单独存在）

  扩展存储过程默认被关闭，需要启用

（2）用户自定义的存储过程

调用执行存储过程：

EXEC[UTE]  存储过程名 [参数值]

创建存储过程：
    （使用T-SQL语句创建）
Create proc 存储过程名称
[@参数1 数据类型]
[@参数1 数据类型]
As
SQL 语句

使用SSMS创建

 四. 触发器

1.什么是触发器

• 是在对表进行插入、更新或删除操作时自动执行的存储过程
• 用于强制业务规则，可以定义比用 CHECK 约束更为复杂的约束
• 通过事件触发而被执行的

2.分类

INSERT触发器：当向表中插入数据时触发

UPDATE触发器：当更新表中某列、多列时触发

DELETE触发器：当删除表中记录时触发

触发器涉及到两张表（delete表和inserted表）（由系统管理，用户不可以修改，仅做了解）

3.触发器的作用

     强化约束（实现比CHECK语句更为复杂的约束）

     跟踪变化（侦测数据库内的操作，从而不允许数据库中未经许可的指定更新和变化）

     级联运行（侦测数据库内的操作，并自动地级联影响整个数据库的各项内容）

 4.创建触发器

（1）使用SSMS创建

（2）使用T-SQL语句创建触发器

Create  trigger 触发器名称
      On 执行触发器的表
      [with encryption]        #加密syscomments 表中包含create trigger语句文本的条目。
      From [delete,insert,update]
      As
      SQL 语句

实战案例

素材：SQL server 2008 素材

创建视图

方法一：在图形界面下创建视图（以Myschool数据库为例）

创建一个视图，分别来自三个表的三个列，并重命名列，生成的视图名为student_info，如下图所示：

实验案例一：验证索引的作用

1. 创建数据库test，再创建一个数据量大的表，名称为“学生表”，分别有三列，学号，姓名和班级，如下图所示，学号为自动编号，班级为默认值“一班”。

1. 向表中插入大量数据，数据越多，验证索引的效果越好。

使用语句完成：While 1>0  Insert into 学生表(姓名)  values(‘张三’)

上面语句是一个死循环，除非强制结束，如果1大于0就会一直向表中插入姓名

如下图所示：

等待5分钟左右，打开表的属性，查看表的行数，当前为1032363，如下图所示：

使用语句查询第900000行的数据，Select * from 学生表 Where 学号=900000

4、打开“sql server  2016  profiler ”工具进行跟踪，如下图所示：

打开“sql server profiler ”工具查看跟踪的信息，发现查询时间很长，cpu工作了265毫秒，reads：读了8649次，writes：写了10次，duration：总计花费2336毫秒完成查询。

为了下面分析文件更准确，多执行几次Select * from 学生表 Where 学号=900000

然后把跟踪的结果保存在桌面上：

1. 打开“SQL server 2016数据库引擎优化顾问”，添加跟踪文件，进行分析，发现索引建议，需要建立索引。

注意选择test数据库中的学生表，然后点击“开始分析”

索引类型为clusterd（聚集索引），索引列为“学号”。

1. 按照“数据库引擎优化顾问”的索引建议建立聚集索引，并且选择“唯一”

1. 再次执行Select * from 学生表Where 学号=900000
2. 打开sql server profiler查看跟踪的时间，发现查询时间大幅提升，说明索引可以提高查询速度。

发现总计时间为1毫秒，几乎忽略不计

实验案例二：分别练习创建各种索引

1. 创建聚集索引

目前tstudent表中没有任何索引也没有主键

为tstudent表创建聚集索引

选中studentID,单击左上侧的主键按钮

为Tstuden表的studentID创建主键就同时创建了聚集索引

2、创建组合索引

为成绩表创建组合索引，因为一个学生不能为一门学科录入两次成绩，所以将成绩表中的studentID和subjectID创建组合索引

解决办法：

菜单栏----工具----选项

找到设计器（designers）,将标记处的勾去掉，单击“确定”

这样组合索引就创建成功了。

3创建唯一索引

创建唯一性约束的时候就会创建唯一性索引，不能有重复值

为Tstudent表创建唯一非聚集索引

create unique nonclustered index U_cardID on TStudent(cardID)

4、创建非聚集索引---可以有重复值

为Tstudent表的姓名列创建非聚集索引

create  nonclustered   index  non_sname  on  tstudent(sname)

使用命令查看表上的索引

Select * from sys.sysindexes where id=(select object_id from sys.all_objects where name='Tstudent')

Indid中1代表聚集索引

Indid中2代表唯一非聚集索引

Indidz中3代表非聚集索引

使用sp_help  Tstudent也可以查看到相关表的信息

sp_help  Tstudent

实验案例一：验证索引的作用

1. 创建数据库test，再创建一个数据量大的表，名称为“学生表”，分别有三列，学号，姓名和班级，如下图所示，学号为自动编号，班级为默认值“一班”。

1. 向表中插入大量数据，数据越多，验证索引的效果越好。

使用语句完成：While 1>0  Insert into 学生表(姓名)  values(‘张三’)

上面语句是一个死循环，除非强制结束，如果1大于0就会一直向表中插入姓名

如下图所示：

等待5分钟左右，打开表的属性，查看表的行数，当前为1032363，如下图所示：

使用语句查询第900000行的数据，Select * from 学生表 Where 学号=900000

4、打开“sql server  2016  profiler ”工具进行跟踪，如下图所示：

打开“sql server profiler ”工具查看跟踪的信息，发现查询时间很长，cpu工作了265毫秒，reads：读了8649次，writes：写了10次，duration：总计花费2336毫秒完成查询。

为了下面分析文件更准确，多执行几次Select * from 学生表 Where 学号=900000

然后把跟踪的结果保存在桌面上：

1. 打开“SQL server 2016数据库引擎优化顾问”，添加跟踪文件，进行分析，发现索引建议，需要建立索引。

注意选择test数据库中的学生表，然后点击“开始分析”

索引类型为clusterd（聚集索引），索引列为“学号”。

1. 按照“数据库引擎优化顾问”的索引建议建立聚集索引，并且选择“唯一”

1. 再次执行Select * from 学生表Where 学号=900000
2. 打开sql server profiler查看跟踪的时间，发现查询时间大幅提升，说明索引可以提高查询速度。

发现总计时间为1毫秒，几乎忽略不计

实验案例二：分别练习创建各种索引

1. 创建聚集索引

目前tstudent表中没有任何索引也没有主键

为tstudent表创建聚集索引

选中studentID,单击左上侧的主键按钮

为Tstuden表的studentID创建主键就同时创建了聚集索引

2、创建组合索引

为成绩表创建组合索引，因为一个学生不能为一门学科录入两次成绩，所以将成绩表中的studentID和subjectID创建组合索引

解决办法：

菜单栏----工具----选项

找到设计器（designers）,将标记处的勾去掉，单击“确定”

这样组合索引就创建成功了。

3创建唯一索引

创建唯一性约束的时候就会创建唯一性索引，不能有重复值

为Tstudent表创建唯一非聚集索引

create unique nonclustered index U_cardID on TStudent(cardID)

4、创建非聚集索引---可以有重复值

为Tstudent表的姓名列创建非聚集索引

create  nonclustered   index  non_sname  on  tstudent(sname)

使用命令查看表上的索引

Select * from sys.sysindexes where id=(select object_id from sys.all_objects where name='Tstudent')

Indid中1代表聚集索引

Indid中2代表唯一非聚集索引

Indidz中3代表非聚集索引

使用sp_help  Tstudent也可以查看到相关表的信息

sp_help  Tstudent

实验案例三：创建视图  

方法一：在图形界面下创建视图（以Myschool数据库为例）

创建一个视图，分别来自三个表的三个列，并重命名列，生成的视图名为student_info，如下图所示：

通过查询语句查看视图：select   *    from   student_info

方法二：使用语句创建视图（以schoolDB数据库为例）

进行数据库设计的时候，一个表有很多列，我们可以在表上创建视图，只显示指定的列。

Select语句可以作为一个视图

select Sname,sex,Class from TStudent where Class='网络班'

1. 创建视图，筛选行和列

（1）创建视图

create view netstudent

as

select Sname,sex,Class from dbo.TStudent where Class='网络班'

（2）从视图中查找数据：

select * from netstudent where sex='男'

2、创建视图，更改列的表头

（1）创建

create view V_Tstudent1

as

select StudentID 学号,Sname 姓名,sex 性别,cardID 身份证号码,Birthday 生日,Class 班级 from TStudent

（2）从视图中查找数据：

select * from V_Tstudent1

以后再去查询的时候就非常方便了。

实验案例四：存储过程

1、常用的系统存储过程

exec  sp_databases      --列出当前系统中的数据库

use  MySchool

go

exec sp_tables                       --当前数据库中可查询对象的列表

exec sp_columns student            --查看表student中列的信息

exec  sp_help student               --查看表student的所有信息

exec sp_helpconstraint student       --查看表student表的约束

exec sp_stored_procedures      --返回当前数据库中的存储过程列表

2、常用的扩展存储过程（在C盘下创建一个文件夹bank）

exec  xp_cmdshell  'mkdir  c:\bank',no_output  --创建文件夹c:\bank

exec  xp_cmdshell  'dir c:\bank\'               --查看文件

如果执行不了上面的语句，请开启下面的功能。然后再次执行上面的两条语句。

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

exec sp_configure  'show advanced options', 1   --显示高级配置选项（单引号中的只能一个空格隔开）

go

reconfigure                                    --重新配置

go

exec sp_configure  'xp_cmdshell',1                 --打开xp_cmdshell选项

go

reconfigure                                    --重新配置

go

3、用户自定义的存储过程（以schoolDB数据库为例，计算网络管理专业的平均分）

use schoolDB

go

if exists  (select  *  from  sysobjects where name='usp_getaverageresult')

drop  procedure  usp_getaverageresult

go

create  procedure  usp_getaverageresult

as

declare  @subjectid  nvarchar(4)

select  @subjectid=subjectid  from  dbo.TSubject where subJectName='网络管理'

declare  @avg decimal (18,2)

select  @avg=AVG(mark)  from  dbo.TScore where subJectID=@subjectid

print  '网络管理专业平均分是:'+convert(varchar(5),@avg)

go

执行验证效果：

exec  usp_getaverageresult

实验案例五：触发器

（Myschool数据库为例）

创建触发器（禁止修改admin表中数据）：

create  trigger  reminder

on  admin

for  update

as

print  '禁止修改，请联系DBA'

rollback  transaction

go

执行语句，查看错误信息：

update Admin set  LoginPwd='123'  where  LoginId='benet'

select  *  from  Admin

实验案例六：事务

案例：完成转账的过程，如果转账1000会回滚，提示失败，如果转账800，提示成功。（参看书140页）

 要求：

1. 创建表名为bank,如图所示：

1. Currentmoney列的Check约束：

（2）插入两条数据：

INSERT  INTO  bank(customerName,currentMoney)  VALUES('张三',1000)
INSERT  INTO  bank(customerName,currentMoney)  VALUES('李四',1)

1. 转账的过程代码参考书140页。（先执行转账1000，查看结果；之后转账800，查看结果）

print   '查看转账事务前的余额'
select  *  from  bank
go
begin   transaction
declare @errorsum  int
set  @errorsum=0
update  bank  set  currentmoney=currentmoney-1000
where  customername='张三'
set @errorsum=@errorsum+@@ERROR
update  bank  set  currentmoney=currentmoney+1000
where  customername='李四'
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