数据库同步工具
sqlserver,Mysql数据同步软件

SQL Server索引基础

购买事宜请联系QQ:1793040

问题

SQL Server提供两种类型的传统索引,即群集索引和非群集索引。我需要了解为什么我需要索引,两者之间的区别以及何时选择一个而不是另一个。一旦我了解了这一点,就需要知道如何实际创建它们。

本技巧将说明索引如何帮助SQL Server操作,群集索引和非群集索引之间的差异,如何选择要使用的类型以及给出每种类型的示例脚本。

为什么需要SQL Server索引?

想象一下走进一家杂货店,这些杂货店没有在过道中整理货物。所有待售物品都随机放在随机走道的架子上。如果要购买蛋糕粉,他们将不得不在每个过道上上下寻找蛋糕粉,直到找到为止。找到一件物品需要很长时间。与此形成对比的是,杂货店组织过道,并有高架标牌将顾客引导到包含“烘烤需求”的过道。现在,同一位客户将能够直接转到需要烘烤的通道,以找到他们的蛋糕粉。与第一家商店相比,客户在该商店中找到他们的商品所花的时间要少得多。

SQL Server的操作与杂货店中的顾客几乎相同。索引是SQL Server用来对表中的行进行排序和组织以使其更容易找到所需行的方法。在缺乏组织的情况下,SQL Server将查看每一行–称为扫描的操作。通过引入索引,SQL Server可以更快地找到所需的行。使用索引跳至所需表部分的操作称为查找。

SQL Server索引类型

SQL Server提供的第一种索引类型是聚集索引。聚集索引是SQL Server对实际数据表进行排序的方法。因为整个表只在磁盘上存储一次,并且该单个副本只能以一种方式排序,所以每个表只能有一个聚集索引。该索引允许包含多个列。

如果需要对同一个表进行多种排序怎么办?例如,对于某些查询,可能需要通过政府标识号,在其他查询中通过姓氏,在另一组查询中通过登录名来对其中每一行代表一个人的表进行排序。如果聚集索引按政府识别号对表格进行排序,而又无法建立另一个聚集索引,该如何完成?答案是非聚集索引。非聚集索引是表中列的子集的副本,该副本是由那些复制的列而不是聚集的索引列排序的。单个表上可以有许多非聚集索引。在此示例中,可以创建两个非聚集索引,其中一个按姓氏排序,另一个按登录名排序。

两种类型的优缺点

聚集索引本质上是表。它包含表的所有列,而不仅仅是列的子集。这意味着使用聚簇索引执行搜索的查询将能够返回它们需要的任何列,因为它们都存储在索引中。不利的一面是,构成聚簇索引的列的任何更新都可能导致整个行必须移至聚簇索引中的另一个位置。在宽表(许多列)上,这可能是非常耗费资源的操作。

非聚集索引仅包含列的子集,因此将其用于查找的查询将快速找到所需的行,但可能不得不针对聚集索引进行第二次查询(称为键查找)以返回请求的所有不属于索引的列。发生第二次查询的想法听起来很吓人,但是所有查询都自动在SQL Server引擎内发生,并且开发人员无需付出任何努力。

由于非聚集索引的列数少于聚集索引的列数,因此需要将行副本在非聚集索引内移动的更新不会占用大量资源。

SQL Server群集和非群集索引的用例

每个表(几乎没有有意义的例外)应该具有聚集索引。由于只能有一个聚集索引,因此在选择哪个列或一组列作为聚集索引时必须格外小心。在选择了聚集索引之后,为了将查询扫描转换为查找而需要排序的所有其他列将必然需要是非聚集索引。

当选择一列或一组列作为表的聚集索引时,有一些注意事项。首先,聚簇索引应该是唯一的。这不是SQL Server强制执行的绝对要求,但是在设计表时,聚集索引应该是数据架构师强制执行的非常严格的规则。

良好的聚集索引的其他一些注意事项是仅包含在创建值后永远不会更改值的列。这样的列称为不可变的,一些示例可以是身份列,订单号,政府ID号或GUID。对于具有许多新行的表,不断增加的内容(例如标识列)可能是明智的选择。聚集索引的另一个良好特性是使其尽可能窄。如果在两个或多个唯一且不可变的选项(可能是员工ID号和政府ID号)之间存在争议,请选择较窄的列。

现在选择了聚集索引,现在可以创建一系列非聚集索引。尝试在可能要搜索的列上创建非聚集索引。如果人员表的聚集索引位于EmployeeID上,则这可能意味着(FamilyName,GivenName),(GovernmentID)和(HomeOffice)上的索引。确保在系统加载一段时间后检查索引,以查看索引的性能如何。  本技巧说明了如何使用SQL Server DMV来确定当前索引结构的有效性,并获得有关如何解决问题的建议。

SQL Server索引的性能注意事项

索引编制的第一法则是要了解索引不是提高性能的神奇方法。新的SQL Server专家通常会意识到,放置良好的索引可以提高查询速度,并且可以为所有列建立索引。当然,现在所有查询都会很快,对吗?错误。错了

索引不是免费的。必须创建,存储,更新和维护它们-此过程将在本技巧后面部分简要介绍。每次在表中插入,删除或更新行时;包括任何受影响的列的所有非聚集索引也必须更新。这称为写惩罚。值得为经常使用的索引付出代价,但不要为不经常用于执行查找操作的索引付出代价。创建新索引时,请牢记此写惩罚,因为拥有过多索引可能会比拥有索引所带来的任何性能提升对性能的影响更大。如果一个表有很多索引或重复的索引,那么删除一些不经常使用的索引可以帮助提高性能。

用于创建集群索引和非集群索引的示例脚本

在本节中,将创建2个表,并将针对它们创建一些样本索引。随时将这些代码复制并粘贴到SSMS或ADS中,以查看其工作原理。更改一些列,并在无风险的环境中进行实验。

请注意,两个表上的聚集索引都使用唯一约束,并且位于单个窄列上。其他表上的索引是允许的,但不需要考虑这些选项。非聚集索引放置在可能要搜索的列上,而不是表上的每一列上。

使用TempDB ; GO CREATE TABLE 客户(CustomerCode CHAR (6 ),FamilyName NVARCHAR (50 ),给定名称NVARCHAR (50 ),AddressLine1 NVARCHAR (100 ),AddressLine2 NVARCHAR (100 ),城市NVARCHAR (64 ),StateProvince NVARCHAR (64 ),

 






POSTALCODE CHAR (8 ),EMAILADDRESS NVARCHAR (255 ),SalesYTD MONEY );



CREATE TABLE 订单(订单编号INT IDENTITY (1000 ,1 ),订购日期DATETIME2 (0 ),CustomerCode CHAR (6 ),InvoiceTotal MONEY );   




CREATE UNIQUE CLUSTERED INDEX CX_CUSTOMER ON 客户(CustomerCode ); CREATE NONCLUSTERED INDEX IX_NAME ON 客户(FamilyName ,给定名称); CREATE NONCLUSTERED INDEX IX_EMAIL ON 客户(EMAILADDRESS );   
  
  
 
CREATE UNIQUE CLUSTERED INDEX CX_ORDERS ON 订单(订单编号); 在订单上创建NONCLUSTERED INDEX IX_DATE (OrderDate ); CREATE NONCLUSTERED INDEX IX_CUSTOMER ON 订单(CustomerCode );   
  
  			

SQL Server群集索引和非群集索引维护最佳实践

首次创建索引时,它们将所有行整齐地连续排列在磁盘上。随着时间的流逝,某些行肯定会更改,这可能需要将它们移到索引的新部分。例如,当某人移至新城市并且跟踪城市的索引需要将该行移至磁盘上的新位置时,就会发生这种情况。显示新行,删除旧行。SQL Server尽最大努力使这些行适合组成索引的许多数据页中的开放空间,但这并非总是如此。随着时间的流逝,大多数索引最终将导致页面无处不在,而不是处于最佳顺序。这称为索引的碎片。索引维护是对索引进行碎片整理的过程。

本文将重点介绍如何使用维护计划向导执行索引维护的3个任务。有很多 技巧可以更详细地介绍此过程,这可能很有用,尤其是在较大的环境中。

首先打开SSMS并浏览到“维护计划”,右键单击该对象,然后从上下文菜单中选择“维护计划向导”。维护计划向导

第一步之一是要求创建时间表。将要调用的某些操作是阻塞操作。其他的不会阻塞,但是它们仍然很忙,可能会影响性能。由于这些原因,需要在低使用率期间安排作业。

繁忙的SQL Server可能需要每天查看一次运行。其他人可能只能每周运行一次。请考虑允许的维护时段,服务器的繁忙程度并进行选择。作业计划将看起来完全像 SQL Server代理作业,因为它将由SQL Server代理运行。

单击“下一步”,直到出现“选择维护任务”屏幕。选择这3个选项。前两个与碎片整理有关,第三个与更新有关索引的元数据有关,这是索引维护的最后一步。 

维护任务

在下一个屏幕上,作者喜欢这种操作顺序。

维护计划向导

在下一个屏幕上选择目标数据库或数据库组。本示例显示“所有数据库”。此示例还显示了10%的采样率,对于大多数常见场景而言已足够,但在其他场景中可能太高或太低。从10%开始,并在您逐渐熟悉该过程时考虑对其进行微调。

定义更新统计信息任务

在下一个屏幕上,选择与上一个屏幕相同的数据库列表。选中屏幕上的所有4个框以及“快速”单选按钮。作者更喜欢从10、1000和7以及数值开始,但这是另一种情况,随着您对过程的更多了解,微调将很有价值。

重组索引任务

下一个屏幕比上一个屏幕有一些其他选项,但是可以忽略这些选项并将其保留为默认设置。使最后3个选项与上一步中选择的选项相同,除了碎片百分比需要比上一步中选择的更大。25可能是一个很好的起点。

仅优化索引

单击下一步或完成,直到向导关闭。

下一步

  • MSSQLTips.com有许多有关索引的提示
  • SQL Server索引教程
  • SQL Server维护技巧

未经允许不得转载:数据库同步软件|Mysql数据同步软件|sqlserver数据库同步工具|异构同步 » SQL Server索引基础

分享到:更多 ()

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

syncnavigator 8.6.2 企业版

联系我们联系我们