数据库优化工程师必看 第一部分（索引、视图）

数据库优化工程师必看 第一部分（索引、视图）

防伪码：勤劳一日，可得一夜安眠；勤劳一生，可得幸福长眠

       在本章技术详解之前，先分享一个今天晚上和一个做IT的学弟的沟通总结：

我们从八点多聊到十点 ，他主要做Linux高级运维，大部分时间还是做数据库的一些工作， 按理说还是我的小师弟 。哈哈。 他说， 刚毕业从一家外资高新技术企业跳糟到一家国企，工资涨了小2000，现在税后也是8500。 但是，他非常后悔。我说以前的公司该是多么强大，让你宁可每个月少挣好几千也想回到以前的公司， 他说老表，工资不是衡量一个人价值的唯一标准， 给你举例说明你就知道这个公司有多厉害了 ，这家公司叫青牛(北京)技术有限公司，主要做融合网络的 最重要的是大多数的员工，自主的要求无偿加班，回忆起我以前刚毕业在那家软件公司上班的情景，总之效率特别高，例如经理下达一个命令到技术部，要求中午下班之前完成，六七个部门的员工帮我一起完成，而我现在在xx国企，想做个备份，不知道秘钥号码，问经理、测试工程师、问遍了，哎， 等我知道了秘钥号，本来上午十点可以做完的事情。 结果，下午两点才刚刚开始 ，如果给我一次重新选择的机会，我一定选择拥有强大战斗力和文化底蕴的公司，我在青牛仿佛看到了未来的第二个阿里巴巴集团。我其实总结一下 就是：眼光决定格局 选择决定未来 用我恩师的话说：公司文化的力量很大程度上决定他能走多远飞多高！

   现在是凌晨0:54分，我们正式开始索引和视图的详解，希望就像博客昵称“一盏烛光“那样，去帮助更多的人解决实际问题，谢谢各位的支持。我将持续更新更多原创技术文档。

实验案例一：创建数据库并使用索引查询学生考试成绩(多种表格在T-SQL查询语句 第二部分 此处略)

select Student.StudentName,Subject.SubjectName,Result.ExamDate,Result.StudentResult

from Subject,Student,Result  with(INDEX=aaa)  注：INDEX=aaa，即索引=索引名

where Result.SubjectId=Subject.SubjectId and Result.StudentNo=Student.StudentNo and Result.StudentResult between 80 and 90

注：INDEX=aaa，即索引=索引名。虽然可以指定SQL Server按哪个索引进行数据查询，但一般不需要人工指定，SQL Server将会根据所创建的索引，自动优化查询。其实，使用索引可加快数据检索速度，但为每个列都建立检索没有必要。因为检索自身也需要维护，并占用一定资源。

案例二：验证索引的作用

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

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

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

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

如下图所示：

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

我们可以右击，选择前1000行，效果如下：

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

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

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

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

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

注：这里选择第一项 ，跟踪文件。然后保存至桌面，效果如下：

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

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

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

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

9、 再次执行Select * from 学生表Where 学号=900000

10、 打开sql server profiler查看跟踪的时间，发现查询时间大幅提升，说明索引可以提高查询速度。

发现总计时间为1毫秒，几乎忽略不计，以至于几乎不花时间立即查询

案例四：分别练习创建各种索引

首先我们先来了解一下索引的分类以及选择索引列的注意事项：

我们掌握了学术性的理论后，将进行详细的试验操作来进一步巩固：

1、 创建聚集索引

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

为tstudent表创建聚集索引

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

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

2、创建组合索引

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

3、用命令创建聚集索引

创建一个表TS

create TABLE TS(

StudentID varchar(10)NOT NULL,

Sname varchar(10)DEFAULT NULL,

sex char(2)DEFAULT NULL,

cardID varchar(20)DEFAULT NULL,     注意：实际工作中建议从简从快，保证质量，这些语法可拓展练习

Birthday datetime DEFAULT NULL,

Email varchar(40)DEFAULT NULL,

Class varchar(20)DEFAULT NULL,

enterTime datetime DEFAULTNULL

)

Go

用命令创建聚集索引

create clustered index CL_studentID

on TS(studentID)

创建聚集索引不一定创建主键，如下图所示：

4、创建唯一索引

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

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

create unique nonclustered index U_cardID on TStudent(cardID)

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

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

使用命令查看表上的索引

Select * from sys.sysindexes where id=(select object_id from sys.all_objects where

name='Tstudent')

Indid中1代表聚集索引

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

Indidz中3代表非聚集索引

二、视图

在这里，一些举例试验就不再一一演示了，因为，在我看来，作为一名数据库管理员，必须要掌握数据库优化这项技能。

最好掌握一些基本的通用语法，虽说视图是个变量，随时更新变化，用起来很方便简洁，可直接在其基础上直接

执行：

例如

select * from 视图名

where 条件=xxx

很方便，但是视图毕竟有局限性，在性能和修改限制方面有待提高。