首页 > 其他分享 >第08章 索引和视图

第08章 索引和视图

时间：2022-11-16 09:37:37浏览次数：46  
标签：INDEX 创建 08 视图 Server 索引 student SQL

在SQL Server中，设计有效的索引(Index)是影响数据库性能的重要因素之一，合理的索引可以显著提高数据库的查询性能。
视图是一个虚拟表，视图中数据来源于由定义视图所引用的表，并且能够实现动态引用，即表中数据发生变化，视图中的数据随之变化。
统计信息是查询优化器进行查询优化的依据，及时更新统计信息对优化的效果至关重要。SQL Server提供了自动和手动两种方式实现对统计信息的创建及更新功能。
规划索引

  1. 索引的用途
    SQL Server的索引是为了加速对表中数据检索而创建的一种分散的、物理的数据结构。数据库中的索引的形式与图书的目录相似，键值就像目录中的标题，指针相当于页码。
    索引是一个逻辑文件，包含从表或视图中一个或多个列生成的键，以及映射到指定数据行的存储位置指针。当SQL Server执行查询时，查询优化器会对可用的多种数据检索方法的成本进行估计，从中选用最有效的查询计划。
    在数据库中使用索引的优点如下。
    加速数据检索：索引能够以一列或多列值为基础实现快速查找数据行。
    优化查询：查询优化器是依赖于索引起作用的，索引能够加速连接、排序和分组等操作。
    强制实施行的唯一性：通过给列创建唯一索引，可以保证表中的数据不重复。
  2. 索引的类型
    SQL Server 2016中常用的有聚集索引、非聚集索引和唯一索引3种类型。聚集索引和非聚集索引是按照索引的存储结构划分的，而唯一索引和非唯一索引是按照索引取值划分的。这是两种截然不同的索引类型划分方法。
    聚集索引。在聚集索引中，索引键值的顺序与数据表中记录的物理顺序相同，即聚集索引决定了数据库表中记录行的存储顺序，每个表只能创建一个聚集索引。聚集索引按 B 树索引结构实现，B 树索引结构支持基于聚集索引键值对行进行快速检索。
    非聚集索引。非聚集索引存储的数据顺序一般与表中记录的物理顺序不同。非聚集索引具有独立于数据行的结构，但非聚集索引的每一个键值项都含有指向该键值数据行的指针。
    唯一索引。唯一索引可确保所有表中任意两行的索引列值（不包括NULL）不重复，如果在多列创建唯一索引，则该索引可以确保索引列中每个值组合都是唯一的。
    在表中创建主键约束时，如果表上还没有创建聚集索引，则SQL Server将自动在创建主键约束的列或组合上创建聚集唯一索引。
    其他索引类型如表8-1所示。

创建索引
SQL Server 2016中创建索引的方法包括：使用SSMS创建索引和利用CREATE INDEX语句创建索引。还可以在CREATE TABLE或ALTER TABLE语句中定义或修改表结构时创建索引。
创建索引之前，应该考虑到权限问题，只有表的拥有者才能在表上创建索引，每个表最多可以创建249个非聚集索引。
在创建聚集索引时还要考虑到数据库剩余空间的问题，创建聚集索引时所需要的可用空间是数据库表中数据量的120%。如果空间不足会降低性能，甚至导致索引操作失败。
1.利用SSMS创建索引
使用SQL Server Management Studio创建独立于约束的聚集索引的操作步骤如下：
启动SQL Server Management Studio，展开“资源管理器”窗口中teaching数据库“表”子目录。
选择student表并展开，右击“索引”项，如图8-1所示。在快捷菜单中选择“新建索引”“聚集索引”命令。
使用SQL Server Management Studio创建独立于约束的聚集索引的操作步骤如下：
在弹出的“新建索引”对话框中，选择“常规”选项卡，输入所引名称Idx_student，取代默认名称。如图8-2所示。其中各项说明如下。
① 表名：指出创建索引的表的名称，用户不可更改。
② 索引名称：输入所创建索引的名称，由用户设定。
③ 索引类型：本例在索引类型组合框中选择“聚集”。
④ 唯一：选中表示创建唯一性索引。本例唯一复选框为选中状态。
设置完成后，按照提示，单击索引键列的“添加”按钮，出现如图8-3所示的从Student表中选择列对话框。在“表列”列表中选中要建立索引的一列或多列，如选择studentno列。
索引键列设置完毕，单击“确定”按钮，返回到“新建索引”对话框，在“索引键列”中的“排序顺序”组合框中可以选择的“升序”或“降序”。如图8-4所示。
在“新建索引”对话框中查看“选项”选项卡，如图8-5所示。
在“新建索引”对话框中查看“存储”选项卡，如图8-6所示。
在“新建索引”对话框中查看“扩展属性”选项卡，如图8-7所示。进行必要的设置后，单击“脚本”，可以查看创建本索引Idx_student的代码。代码如下：
单击“确定”按钮，即完成了创建聚集索引的操作。此时，就可以在当前索引子目录中查看创建的索引文件。
2.利用CREATE INDEX命令创建索引
SQL Server 2016提供的创建索引的Transact-SQL语句是CREATE INDEX，其基本语法格式如下：
CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED] INDEX index_name
ON { table_or_view_name }
(column [ ASC|DESC ][ ,...n ] )
  [ INCLUDE (column_name[ ,...n])]
  [ ON { filegroup_name |default  } ][ ; ]
例8.1】在 teaching 数据库中的 student 表的Email列上创建唯一索引IDX_Email。
CREATE UNIQUE INDEX IDX_Email
ON student(Email)
执行如下插入语句：
INSERT INTO student(studentno,sname,sex,birthdate,classno,Email)
VALUES('18125121105','梁欣','女','1999-6-3',
'180802','[email protected] ')
唯一性约束确保索引列不包含重复的值，则插入操作出现重复键值时会发出错误消息，如

【例8.2】在 teaching 数据库中 student 表的 studentno 和 classno 列上创建组合索引 IDX_sc。
IF EXISTS(SELECT name FROM sysindexes WHERE name='IDX_sc')
DROP INDEX student.sc
GO
CREATE INDEX IDX_sc ON student(studentno,classno)
本例首先在系统表sysindexes中查找是否存在名称为IDX_sc的索引，如存在则将其删除，然后在student表上创建非聚集非唯一索引。本索引键值由列studentno和classno的值组合而成。
维护索引
在SQL Server 2016中修改索引的方法有两种：使用SQL Server Management Studio图形工具和Transact-SQL语句。
1 .在SSMS中修改索引
使用SSMS修改索引的参考操作步骤如下：
（1）启动SQL Server Management Studio，展开“资源管理器”窗口中的“teaching数据库” “表”student 子目录。
（2）选择并展开“索引”项，右击Idx_student索引，在快捷菜单中选择“属性”命令。
（3）出现“索引属性”对话框，在各选项卡中可以修改索引的设置。在“常规”选项卡中可以添加或删除索引键列、改变键列排序。
（4）在“选项”选项卡中可实现对于在访问索引时是否使用行锁和页锁、填充因子等索引选项的修改。如选中“设置填充因子”和“填充索引”复选框，并设置填充因子为80%。
（5）在“存储”选项卡中可实现对于索引的文件组和分区属性的修改。切换至“扩展属性”选项卡，可以修改与索引相关的扩展信息。
（6）切换至“碎片”选项卡，该选项卡用于查看索引碎片数据以确定是否需要重新组织索引，如图8-9所示。
（7）修改完毕，单击“脚本”按钮，可以查看修改索引的代码。单击“确定”按钮，即可完成操作。
2 .利用ALTER INDEX命令修改索引
SQL Server 2016提供的创建索引的Transact-SQL语句是ALTER INDEX，其语法基本格式如下：
ALTER INDEX { index_name | ALL }
    ON
    { REBUILD
    [ [ WITH ( <rebuild_index_option> [ ,...n ] ) ] ]
    | DISABLE
    | REORGANIZE   }[ ; ]
3 .索引碎片检测
SQL Server 2016的索引数据是随着表数据的插入、更新或删除操作而自动维护的。这些修改可能会导致索引中的信息分散在数据库中，本来可以存储在一个页中的索引却不得不存储在两个或更多的页上，这样的情况称为索引中存在碎片。
当索引包含的页中基于键值的逻辑排序与数据文件中的物理排序不匹配时，就会存在碎片。碎片非常多的索引可能会降低查询性能，导致应用程序响应缓慢。
SQL Server可以通过重新组织索引或重新生成索引来修复索引碎片，以解决上述问题。决定使用哪种碎片整理方法的前提是检测索引碎片并分析以确定碎片程度。
SQL Server 2016提供了查看和检测有关索引碎片信息的方法，并且可以通过对检测结果的分析，确定处理碎片的最佳方法。在检测结果中，逻辑碎片的百分比属性中的取值可用来决定下一步的处理方法。
一般情况下，如该属性值<=30%，推荐采用索引重组，如果该属性值>30%，推荐采用索引重建。也可以使用sys.dm_db_index_physical_stats()函数获取索引平均碎片。
【例8.3】使用sys.dm_db_index_physical_stats()函数获取score表中所有索引的平均碎片。
SELECT avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_id(‘score’),null,null,null)
4 . 索引重组
索引重组是通过对索引的叶级页进行物理重新排序，使其与叶结点的逻辑顺序相匹配，从而对表或视图的聚集索引和非聚集索引的叶级别进行碎片整理。
索引重组需要注意的问题。
索引在分配给它的现有页内重新组织。如果索引跨越多个文件，则将一次重新组织一个文件。
重新组织还会压缩索引页。如果还有可用的磁盘空间，将删除此压缩过程中生成的所有空页。压缩基于设置的填充因子值。
重新组织进程使用最少的系统资源，且是自动联机执行的。索引碎片不太多时，可以重新组织索引。如果索引碎片非常多，重新生成索引则可以获得更好的结果。
使用ALTER INDEX REORGANIZE语句可实现对索引的重新组织。
【例8.4】重新组织 teaching 数据库中 student 表上的 IDX_sc 索引。
ALTER INDEX IDX_sc ON dbo.student
REORGANIZE
5 .索引重建
索引重建将删除已存在的索引并创建一个新索引。此过程中将删除碎片，通过使用指定的或现有的填充因子设置压缩页来回收磁盘空间，并在连续页中对索引行重新排序。这样可以减少获取所请求数据所需的页读取数，从而提高磁盘性能。
使用ALTER INDEX REBUILD语句重建索引
其基本语法格式为：
ALTER INDEX { index_name | ALL } ON
    REBUILD
    [ WITH ( <rebuild_index_option> [ ,...n ] ) ]

【例8.5】重新生成 teaching 数据库中 student 表上的 IDX_Email 索引，设置填充索引，将填充因子设置为80%，设置将中间排序结果存储在tempdb中。

ALTER INDEX IDX_sname ON dbo.student
REBUILD
WITH(PAD_INDEX = ON,FILLFACTOR = 80,SORT_IN_TEMPDB = ON)
使用带DROP_EXISTING子句的CREATE INDEX语句重建索引
ALTER INDEX语句不能通过添加或删除键列、更改索引类型、更改列顺序或更改列排序顺序来更改索引定义，如需完成此类操作，可通过带DROP_EXISTING子句的CREATE INDEX语句实现。
【例8.6】 重新生成 teaching 数据库中 student 表上的 Idx_student 索引，指定该索引的填充因子为70%。

CREATE UNIQUE CLUSTERED INDEX Idx_student
ON dbo.student(studentno)
WITH(PAD_INDEX = ON,FILLFACTOR = 70,DROP_EXISTING = ON)
6.索引的分析
数据库表创建索引之后，由于数据的添加、删除和修改会导致索引中的信息分散到不同的数据页，形成索引碎片，需要对索引进行分析和维护。
SHOWPLAN_ALL 命令
SHOWPLAN_ALL 命令可用于在SQL Server中显示查询计划。查询计划包括显示在执行查询的过程中、连接表时所采取的步骤，以及是否选择、选择了哪个索引，从而帮助用户分析有哪些索引被系统采用。通常在查询语句中设置SHOWPLAN_ALL选项，可以选择是否让SQL Server显示查询计划。SHOWPLAN_ALL 命令的使用格式如下：
SET SHOWPLAN_ALL ON ︳OFF

【例8.7】 使用SHOWPLAN_ALL命令对Transact_SQL语句进行分析。

SET SHOWPLAN_ALL ON
GO
SELECT studentno, sname, birthdate, phone FROM student
WHERE YEAR(birthdate)>=2000
GO
SET SHOWPLAN_ALL OFF
GO
STATISTICS IO命令
STATISTICS IO命令用于数据检索语句所花费的磁盘活动量，这也是用户比较关心的性能之一。通过设置STATISTICS IO选项，可以是SQL Server显示磁盘IO信息。
设置是否显示磁盘IO统计的命令格式如下：
SET STATISTICS IO ON| OFF     
【例8.8】 利用STATISTICS IO分析Transact_SQL语句执行过程中的磁盘使用情况。

SET STATISTICS IO ON
GO
SELECT studentno, sname, birthdate, phone
FROM student
WHERE birthdate BETWEEN ’1999-01-01’ AND ’2000-09-30’
GO
SET STATISTICS IO OFF
GO
7 .删除索引
当一个索引不再需要时，可将其从数据库中删除，以回收它当前使用的磁盘空间。删除索引之前，必须先删除PRIMARY KEY或UNIQUE约束，才能删除约束使用的索引。
如果数据已经排序，则重新生成索引的过程无需按索引列对数据排序，重新生成索引有助于重新创建聚集索引。
另外，删除视图或表时，系统将自动删除为永久性和临时性视图或表创建的索引。
使用SQL Server Management Studio删除索引
使用SSMS删除索引的操作步骤如下：
（1） 启动SQL Server Management Studio，展开“资源管理器”窗口中的“teaching数据库” “表”student 子目录。
（2）选择并展开“索引”项，右击索引IDX_sc，在快捷菜单中选择“删除”命令。
（3）在弹出的“删除对象”对话框中，其中显示要删除的索引，单击“确定”按钮即可完成删除操作。
使用Transact-SQL语句删除索引
使用DROP INDEX语句可从当前数据库中删除一个或多个索引。
【例 8.9】删除 teaching 数据库中 student 表上的聚集索引 Idx_student 和非聚集索引IDX_Email。
程序代码如下：
DROP INDEX student. Idx_student,student. IDX_Email

标签：INDEX,创建,08,视图,Server,索引,student,SQL
From： https://www.cnblogs.com/kingwz/p/16894779.html

相关文章

  • Springboot上传文件出现tomcat报错问题：/tmp/tomcat.8013579853364800617.8080/work/To
    1、线上的项目上传文件的时候突然出现一个tomcat的错误，说是tomcat里面的项目路径上传文件临时路径找不到，搞得莫明其妙 2、网上百度，其他人解决办法又是在application.yml......
  • SpringBoot 08: SpringBoot综合使用 MyBatis, Dubbo, Redis
    业务背景Student表CREATETABLE`student`(`id`int(11)NOTNULLAUTO_INCREMENT,`name`varchar(255)COLLATEutf8_binDEFAULTNULL,`phone`varchar(11)......
  • Prism通过反射机制自动注册对话视图模型
    摘要说明在使用WPF+Prism开发中，有时会需要使用到一些弹窗服务，而在Prism当中，我们使用Dialog是需要注入到IOC容器当中的，传统的写法如下：而当Dialog过多时或者需要新增一个......
  • address localhost8080 is already in use
    参考声明：https://blog.csdn.net/qq_45720042/article/details/120854916在学习编程的过程中，我们或许会遇到端口被占用的情况，因而导致程序启动不了。这种情况只需要找到......
  • SpringBoot 08: SpringBoot综合使用Mybatis + Dubbo + Redis
    业务背景Student表CREATETABLE`student`(`id`int(11)NOTNULLAUTO_INCREMENT,`name`varchar(255)COLLATEutf8_binDEFAULTNULL,`phone`varchar(11)......
  • 分布式搜索引擎01-- elasticsearch基础
    分布式搜索引擎01--elasticsearch基础0.学习目标1.初识elasticsearch1.1.了解ES1.1.1.elasticsearch的作用elasticsearch是一款非常强大的开源搜索引擎，具备非常多强......
  • 分布式搜索引擎02-elasticsearch的数据搜索功能-DSL和RestClient实现搜索
    分布式搜索引擎02在昨天的学习中，我们已经导入了大量数据到elasticsearch中，实现了elasticsearch的数据存储功能。但elasticsearch最擅长的还是搜索和数据分析。所以今天，我......
  • 分布式搜索引擎03-数据聚合
    分布式搜索引擎030.学习目标1.数据聚合聚合（aggregations）可以让我们极其方便的实现对数据的统计、分析、运算。例如：什么品牌的手机最受欢迎？这些手机的平均价格、最高......
  • 080_阻塞队列 BlockingQueue
    目录简介演示代码抛出异常add()添加元素队列已满时抛出异常remove()移除元素为空时抛出异常有返回值，不抛出异常offer()添加元素队列已满时返回false不抛异常poll()移除......
  • MySQL视图
    准备工作，新建名为students的数据，三张表分别是student，courses，stu_cou，并创建外键约束，级联删除更新，插入数据。/*创建数据库*/createdatabaseifnotEXISTSstudentscha......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99