数据库应用基础（SQL Server 2019）（AIGC赋能版） 课件 项目6 创建索引和视图

项目6创建索引和视图《数据库应用基础（SQLServer2019）》项目目标理解索引的概念和类型掌握设计和创建索引的方法理解视图的概念、用途和限制掌握创建、应用和管理视图的方法本章目录6.1理解索引6.2设计索引6.3创建索引6.4理解视图6.5创建视图6.6管理和应用视图理解索引6.1任务6.1理解索引索引是一种特殊类型的数据库对象，它是基于表或视图创建的，可以提高访问表数据的速度，并且能够强制实施某些数据完整性。通过本任务将学习和理解索引的基本概念和类型。任务目标理解索引的概念理解索引的类型任务6.1理解索引索引是一种特殊类型的数据库对象，它是基于表或视图创建的，可以提高访问表数据的速度，并且能够强制实施某些数据完整性。通过本任务将学习和理解索引的基本概念和类型。任务目标理解索引的概念理解索引的类型6.1.1索引的基本概念（1/2）数据存储方式数据以页为单位存储，每页8KB数据行不按特定顺序存放，数据页也无特定顺序数据页的集合称为堆索引的定义与表或视图关联的磁盘上或内存中结构由一列或多列生成键，以B+树结构存储可加快从表或视图中检索行的速度访问数据的两种方式扫描表：表中无索引时使用，读取所有行，适合结果集占比高的情况使用索引：通过索引键列查找行的存储位置，通常比扫描表快很多6.1.1索引的基本概念（2/2）索引相关术语索引列/索引键：在其上创建索引的列键值：索引列中的值索引键可以是单列或多列组合，键值可以唯一也可以重复使用索引的要点通常加速连接表、排序或组合操作的查询定义唯一性时可强制数据行的唯一性按升序排序顺序创建和维护最好在具有唯一性的列或列组合上创建索引索引的成本占用磁盘空间，增加系统开销和维护成本修改索引列数据时，SQLServer会更新相关索引小表上的索引没有多少好处6.1.2索引的类型12种索引类型索引类型说明聚集索引按聚集索引键排序存储数据行，每个表只能有一个非聚集索引索引行包含非聚集键值和行定位符，数据行不保证特定顺序哈希索引通过内存中的哈希表访问数据，内存用量固定内存优化非聚集索引内存用量依赖于行计数及索引键列大小唯一索引确保索引键不包含重复的值列存储索引基于列的数据存储，适用于数据仓库，查询性能最高提升10倍，压缩率最高7倍包含性列索引非聚集索引，扩展后包含键列和非键列计算列索引在从其他列派生值的计算列上创建的索引筛选索引优化的非聚集索引，对表中部分行进行索引，提高性能、减少维护开销空间索引对geometry数据类型的列中的空间对象执行高效操作XML索引xml数据类型列中XMLBLOB的拆分持久表示形式全文索引由全文引擎创建维护，用于在字符串数据中搜索复杂的词设计索引6.2任务6.2设计索引索引设计包括确定要使用的列、选择索引类型以及选择适当的索引选项等。索引设计是一项关键任务，通过创建设计良好的索引可以显著提高数据库查询和应用程序的性能。索引可以减少为返回查询结果集而必须读取的数据量，还可以强制表中的行具有唯一性，从而确保表数据的数据完整性。通过本任务将学习和掌握设计索引的准则和方法。任务目标理解索引设计准则掌握设计聚集索引的方法掌握设计非聚集索引的方法掌握设计唯一索引的方法6.2.1索引设计准则（1/2）数据库准则避免对经常更新的表创建过多索引，索引应保持较窄大量索引可提高更新少、数据量大的查询性能（如SELECT）小表的索引可能不会提升性能，反而增加维护开销视图包含聚合或连接时，视图索引可显著提升性能使用数据库引擎优化顾问分析并生成索引建议查询准则为经常用于谓词和连接条件的列创建非聚集索引涵盖索引（索引包含全部所需列）可减少磁盘I/O将修改多行的操作写入单个语句，利用优化维护6.2.1索引设计准则（2/2）列准则聚集索引键应保持较短，优先选择唯一或非空列不能将ntext、text、image、varchar(max)、nvarchar(max)、varbinary(max)指定为索引键列（但可作为非键列参与非聚集索引）xml列只能在XML索引中用作键列检查列的唯一性和数据分布，避免在极少唯一值的列上创建索引多列索引：将用于等值或范围搜索的列放在最前面，按非重复级别排序可考虑对计算列创建索引6.2.2设计聚集索引（1/2）聚集索引特点按索引键排序并存储数据行，每个表只能有一个创建PRIMARYKEY约束时默认创建聚集索引若无唯一性，SQLServer自动添加4字节标识值使键唯一适合聚集索引的查询返回范围值（BETWEEN、>、>=、<、<=）返回大型结果集使用JOIN子句（特别是外键列）使用ORDERBY或GROUPBY6.2.2设计聚集索引（2/2）适合聚集索引的列特征唯一或包含许多不重复的值（如学号）按顺序访问（如ProductIDBETWEEN980AND999）定义为IDENTITY的列经常用于排序的列不适合聚集索引的列特征频繁更改的列（会导致整行移动）宽键（多列或大列组合，会增大非聚集索引）创建聚集索引时可考虑的选项SORT_IN_TEMPDB、DROP_EXISTING、FILLFACTOR、ONLINE6.2.3设计非聚集索引（1/2）非聚集索引特点包含索引键值和指向数据行的行定位器可对表或索引视图创建多个适合完全匹配查询（如WHERE精确条件）数据库特征低更新、大量数据的数据库可从多个非聚集索引获益联机事务处理（OLTP）系统应避免过多索引，保持索引窄适合非聚集索引的查询使用JOIN或GROUPBY（为连接和分组列创建索引）不返回大型结果集（可考虑筛选索引）包含精确匹配搜索条件的列6.2.3设计非聚集索引（2/2）适合非聚集索引的列特征覆盖查询：索引包含查询所需所有列（减少I/O）大量非重复值（若重复值很少，表扫描更有效）对于低基数列，可考虑筛选索引创建非聚集索引时可考虑的选项FILLFACTOR、ONLINE6.2.4设计唯一索引（1/2）唯一索引特点保证索引键不包含重复值，强制数据唯一性可以是聚集或非聚集为数据本身唯一的列创建唯一索引才有意义多列唯一索引保证组合值唯一唯一索引的优点确保数据完整性为查询优化器提供有用信息，生成更有效执行计划6.2.4设计唯一索引（2/2）创建方式创建PRIMARYKEY或UNIQUE约束会自动创建唯一索引建议通过约束创建，使索引目标明确限制若数据存在重复键值，无法创建唯一索引唯一非聚集索引可包含包含性非键列创建唯一索引时可考虑的选项ONLINE、IGNORE_DUP_KEY创建索引6.3任务6.3创建索引当使用CREATETABLE或ALTERTABLE对列定义PRIMARYKEY或UNIQUE约束时，数据库引擎将自动创建唯一索引，以强制PRIMARYKEY或UNIQUE约束的唯一性要求。也可以使用SSMS图形界面或Transact-SQL语句来创建独立于约束的索引。通过本任务将学习和掌握创建索引、查看索引信息以及删除索引的方法。任务目标掌握使用SSMS创建索引的方法掌握使用SQL语句创建索引的方法掌握查看索引信息的方法掌握删除索引的方法6.3.1使用SSMS创建索引使用SSMS创建索引操作步骤在对象资源管理器中展开数据库和表右键单击“索引”，选择“新建索引”→“聚集索引”或“非聚集索引”在“新建索引”对话框的“常规”页中：指定索引名称选择索引类型指定是否唯一单击“添加”选择索引列及排序顺序在“选项”页中设置索引选项单击“确定”完成创建创建后：索引显示在“索引”节点下，可右键进行修改、重命名、重新生成或重新组织6.3.2使用SQL语句创建索引（1/2）CREATEINDEX语法CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEX索引名称ON表或视图名称(列[ASC|DESC][,...])[WITH(IGNORE_DUP_KEY={ON|OFF}|DROP_EXISTING={ON|OFF})][WHERE<筛选谓词>]参数说明UNIQUE：创建唯一索引，不允许重复键值CLUSTERED：创建聚集索引，数据行物理顺序由键值逻辑顺序决定（默认PRIMARYKEY创建时使用）NONCLUSTERED：创建非聚集索引（默认值）列：可指定多列创建组合索引；ASC/DESC指定排序方向（默认ASC）WITH：DROP_EXISTING=ON：删除已存在同名索引并重新生成IGNORE_DUP_KEY=ON：多行插入时，违反唯一性的行插入失败，其他行成功插入，并发出警告信息；OFF则回滚整个事务WHERE：指定筛选谓词，创建筛选索引6.3.2使用SQL语句创建索引（2/2）例6.1：基于学生表的姓名列创建非聚集索引USE教务管理;GOCREATENONCLUSTEREDINDEXix_stu_nameON学生(姓名);GO6.3.3查看索引信息（1/2）使用sp_helpindex查看索引定义语法 sp_helpindex'表或视图名称'结果集列index_name（索引名称）、index_description（索引说明）、index_keys（索引键列）使用sp_spacesused查看索引空间语法

sp_spacesused[[@objname=]'对象名称'][,[@updateusage=]'更新指示']省略对象名称：返回整个数据库的空间使用情况（两个结果集）指定对象名称：返回该对象的空间使用情况结果集列name、rows、reserved、data、index_size、unused6.3.3查看索引信息（2/2）例6.2：查看学生表的索引信息和空间使用USE教务管理;GOEXECsp_helpindex学生;EXECsp_spacesused学生;GO6.3.4删除索引使用SSMS删除索引展开表→展开“索引”节点右键单击要删除的索引→“删除”在“删除对象”对话框中单击“确定”使用SQL语句删除索引（DROPINDEX）语法：

DROPINDEX表名称.索引名称[,...]可一次删除多个索引（逗号分隔）删除后，原索引占用的空间被释放示例：

DROPINDEXtable1.index1,table2.index1;理解视图6.4任务6.4理解视图与索引一样，视图也是数据库中的一种对象。视图可以视为虚拟表或存储查询。除非是索引视图，否则视图的数据不会作为对象存储在数据库中。创建视图时在数据库中存储的是SELECT语句，其查询结果集构成了视图所返回的虚拟表，可以通过在查询语句中引用视图名称来使用此虚拟表。通过本任务将学习和理解视图的基本概念、用途和限制。任务目标理解视图的基本概念理解视图的用途和限制6.4.1视图的基本概念（1/2）视图的定义视图是一个虚拟表，其内容由选择查询定义包含带有名称的列和行数据，但数据来源于基础表，在引用时动态生成（索引视图除外）视图中引用的表称为基础表视图的特点对基础表而言，视图的作用类似于筛选可基于当前或其它数据库的一个或多个表、其他视图定义分布式查询可用于定义使用多个异类源数据的视图通过视图进行查询没有限制，数据修改的限制也很少6.4.1视图的基本概念（2/2）视图的四种类型视图类型说明标准视图组合一个或多个表中的数据，简化数据操作索引视图已被具体化（已计算并存储），对视图创建唯一聚集索引，显著提高聚合查询性能，不适合经常更新的基础数据集分区视图在一台或多台服务器间水平连接一组成员表中的分区数据；同一实例中为本地分区视图，不同服务器间为分布式分区视图系统视图公开目录元数据，返回与SQLServer实例或对象有关的信息（如sys.databases）6.4.1视图的基本概念（2/2）视图的四种类型视图类型说明标准视图组合一个或多个表中的数据，简化数据操作索引视图已被具体化（已计算并存储），对视图创建唯一聚集索引，显著提高聚合查询性能，不适合经常更新的基础数据集分区视图在一台或多台服务器间水平连接一组成员表中的分区数据；同一实例中为本地分区视图，不同服务器间为分布式分区视图系统视图公开目录元数据，返回与SQLServer实例或对象有关的信息（如sys.databases）6.4.2视图的用途和限制（1/2）视图的用途简化数据操作将复杂的多表联接查询定义为视图，后续查询直接引用视图，不必重复编写复杂的SELECT语句自定义数据允许不同用户以不同方式查看相同数据可根据用户身份（如登录ID）动态决定检索哪些数据提高数据库安全性允许用户通过视图访问数据，不授予直接访问基础表的权限可在视图中排除机密数据列，保护敏感信息6.4.2视图的用途和限制（2/2）创建视图的限制限制项说明权限要求必须具有CREATEVIEW权限，并对基础表/视图有适当权限命名规则视图名称在数据库范围内必须唯一列数限制最多引用1024个列禁止子句定义视图的查询不能包含INTO关键字ORDERBY限制定义视图的查询不能包含ORDERBY，除非使用TOP子句嵌套限制视图可以基于其他视图创建，最多嵌套32层依赖关系删除视图依赖的表后，视图定义仍保留在数据库中索引视图可在视图上定义索引，自动反映基表数据修改禁止绑定不能在视图上绑定规则、默认值和触发器临时视图不能创建临时视图，也不能在临时表上创建视图作用域只能在当前数据库中创建视图，但可引用其他数据库或服务器中的表/视图创建视图6.5任务6.5创建视图如果想使用视图来简化数据操作或提高数据库的安全性，首先要按照需要在数据库中创建视图，这可以使用SSMS图形界面或Transact-SQL语句来实现。通过本任务将学习和掌握创建视图的两种方法。任务目标掌握使用SSMS创建视图的方法掌握使用SQL语句创建视图的方法6.5.1使用SSMS创建视图（1/3）使用SSMS创建视图操作步骤在对象资源管理器中展开数据库右键单击“视图”→“新建视图”在“添加表”对话框中（图6.5）：选择要引用的一个或多个基础表（“表”选项卡）若要引用已有视图，选择“视图”选项卡单击“添加”，完成后单击“关闭”在视图设计器中定义SELECT语句完成定义后，单击“保存”，输入视图名称，单击“确定”6.5.1使用SSMS创建视图（2/3）视图设计器的四个窗格窗格快捷键功能关系图窗格Ctrl+1显示正在查询的表，矩形代表表，连线表示连接关系条件窗格Ctrl+2电子表格形式，设置显示列、筛选行、分组方式SQL窗格Ctrl+3显示/编辑视图的SQL语句，可输入联合查询等复杂语句结果窗格Ctrl+4以网格形式显示视图检索到的数据，可编辑数据（条件允许）6.5.1使用SSMS创建视图（3/3）例6.4：创建“学生成绩视图”并引用创建视图添加学生表、成绩表、课程表（表间已存在关系，自动生成INNERJOIN）选择学号、姓名、课程名称、成绩等列，设置别名保存为“学生成绩视图”引用视图USE教务管理;GOSELECT*FROM学生成绩视图WHERE班级编号='信2401'AND课程名称='英语'ORDERBY成绩DESC;GO6.5.2使用SQL语句创建视图（1/2）CREATEVIEW语法CREATEVIEW[架构名称.]视图名称[(列[,...])][WITH<视图属性>[,...]]ASSELECT语句[WITHCHECKOPTION];视图属性（WITH子句）ENCRYPTION：加密sys.syscomments表中CREATEVIEW语句的文本，防止在复制过程中发布SCHEMABINDING：将视图绑定到基础表的架构VIEW_METADATA：返回视图的元数据信息，而非基表的元数据信息WITHCHECKOPTION：强制针对视图执行的所有数据修改语句必须符合SELECT语句中的条件；确保修改后仍能通过视图看到数据；若SELECT语句中使用TOP，则不能指定CHECKOPTION6.5.2使用SQL语句创建视图（2/2）例6.5：创建“学生视图”并引用USE教务管理;GOCREATEVIEW学生视图ASSELECTs.学号,s.姓名,s.性别,s.入学时间,s.班级编号,c.系部FROM学生ASsINNERJOIN班级AScONs.班级编号=c.班级编号;GOSELECT*FROM学生视图WHERE性别='男'AND系部='信息工程系';GO管理和应用视图6.6任务6.6管理和应用视图在数据库中创建视图后，根据需要可以对其定义进行修改或者进行重命名，也可以查看视图的相关信息；创建一个视图后，不仅可以在SELECT语句中引用它，也可以通过它对基础表中的数据进行修改；对于不再需要的视图则应及时从数据库中删除。通过本任务将学习和掌握管理和应用视图的方法。任务目标掌握修改视图的方法掌握重命名视图的方法掌握查看视图相关信息的方法掌握通过视图修改数据的方法掌握删除视图的方法6.6.1修改视图（1/3）使用SSMS修改视图展开数据库→展开“视图”节点右键单击要修改的视图→选择“设计”在视图设计器中修改SELECT语句保存更改6.6.1修改视图（2/3）使用SQL语句修改视图（ALTERVIEW）语法格式ALTERVIEW[架构名称.]视图名称[(列[,...])][WITH{ENCRYPTION|SCHEMABINDING|VIEW_METADATA}[,...]]ASSELECT语句[WITHCHECKOPTION];WITH子句说明ENCRYPTION：加密视图定义文本SCHEMABINDING：将视图绑定到基础表架构VIEW_METADATA：返回视图元数据而非基表元数据WITHCHECKOPTION：强制通过视图修改数据时满足WHERE条件6.6.1修改视图（3/3）例6.6：创建视图→修改视图（添加列并加密）→查询男教师USE教务管理;GOCREATEVIEW教师视图ASSELECT教师编号,姓名,性别,出生日期FROM教师;GOALTERVIEW教师视图WITHENCRYPTIONASSELECT教师编号,姓名,性别,出生日期,入职时间FROM教师;GOSELECT*FROM教师视图WHERE性别='男';GO6.6.2重命名视图使用SSMS重命名右键单击视图→“重命名”输入新名称→按Enter键使用SQL语句重命名（sp_rename）语法：sp_rename'原名称','新名称','OBJECT'例6.7：将“教师视图”重命名为“vTeacher”USE教务管理;GOEXECsp_rename'教师视图','vTeacher','OBJECT';GO6.6.3查看视图相关信息查看视图常用方法例6.8：查看“学生成绩视图”的定义文本及引用的表和列USE教务管理;GOEXECsp_helptext'学生成绩视图';--查看定义文本EXECsp_d