某某茶叶有限公司欢迎您!
金沙棋牌在线 > 必赢棋牌官网 > SQL Server 索引

SQL Server 索引

时间:2019-12-29 06:38

背景

在第一篇中我介绍了如何访问元数据,元数据为什么在数据库里面,以及如何使用元数据。介绍了如何查出各种数据库对象的在数据库里面的名字。第二篇,我选择了触发器的主题,因为它是一个能提供很好例子的数据库对象,并且在这个对象中能够提出问题和解决问题。

本篇我将会介绍元数据中的索引,不仅仅是因为它们本身很重要,更重要的是它们是很好的元数据类型,比如列或者分布统计,这些不是元数据中的对象。

索引对于任何关系数据库表都是必不可少的。然而,就像吐司上的黄油一样,过度使用它们可能会在数据库中产生问题。有时,可以对表进行过度索引或缺失索引,或者构建重复索引。有时问题是选择一个坏的填充因子,错误地设置ignore_dup_key选项,创建一个永远不会被使用(但必须被维护)的索引,丢失外键上的索引,或者将GUID作为主键的一部分。简而言之,任何频繁使用的数据库系统中的索引都需要定期维护和验证,而目录视图是完成这些工作的最直接的方式之一。

简介

  在数据库中,我们除了存储数据外,还存储了大量的元数据。它们主要的作用就是描述数据库怎么建立、配置、以及各种对象的属性等。本篇简单介绍如何使用和查询元数据,如何更有效的管理SQLServer 数据库。

  对一些有经验的数据库开发和管理人员而言,元数据是非常有价值的。下面我会介绍一下简单的原理,然后尽量用代码的方式直接说明,毕竟“talk is cheap show me the code ”。

背景

  上一篇中,我介绍了SQL Server 允许访问数据库的元数据,为什么有元数据,如何使用元数据。这一篇中我会介绍如何进一步找到各种有价值的信息。以触发器为例,因为它们往往一起很多问题。

 

索引是数据库规划和系统维护的一个关键部分。它们为SQL Server(以及任何其他的数据库系统)提供了查找数据和定位到数据物理位置的快捷方式的其他方法。通过添加正确的索引可以大大减少查询的执行时间。但是,许多设计很差的索引实际上会增加运行所花费的时间。事实上,索引正逐步成为SQL Server中最容易令人误解的对象,因此也是最容易管理不当的对象。

都有哪些索引可以查到?

让我们通过下面的简单语句来看一下都有哪些索引在你的数据库上,代码如下:

SELECT  convert(CHAR(50),object_schema_name(t.object_ID)+'.'
    +object_name(t.object_ID)) AS 'The Table', i.name AS index_name
FROM sys.indexes AS i
  INNER JOIN sys.tables t
    ON t.object_id=i.object_id
  WHERE is_hypothetical = 0 AND i.index_id <> 0;

结果如下:

图片 1

为什么要去引用sys.tables?这是因为它是确保只获得用户表的最简单方法。我们选择index_id 的values大于0,因为如果不为表创建集群索引,在sys中仍然有一个条目。索引,但它指向的是堆,不代表索引。每个表在sys中都有一行。索引值为0或1的索引。如果该表有一个聚集索引,则有一行数据且index_id值为1;如果该表是一个堆(这只是表示该表没有聚集索引的另一种方式),则会有一行的index_id值为0。此外,无论该表是否有聚集索引,每个非聚集索引都有一行,其index_id值大于1。我们过滤了的索引,这些索引是由数据库引擎优化顾问(DTA)创建的,目的仅仅是测试一个可能的索引是否有效。以防它们积累起来,最好把它们去掉。

如果你过一个多个指定的表,下面的这个查询是更为合理的,需要在上面的例子中增加对象的指定:

AND t.object_id = OBJECT_ID('Production.BillOfMaterials');

 

什么是动态在线目录?

  每一个关系型数据库系统,比如SQL Server 一定要提供关于它的结构的信息,这些信息往往需要通过sql语法来查询。通常这些信息被保存在指定数据表的结构中。这意味着数据库中有两种不同的表:一是用户自定义的表和系统表或者视图(包含元数据)。从SQL Server 2005开始,只有视图可以查询了,不能直接看到数据表了。

 图片 2

系统视图

这种系统表或者视图的结合通常参考关系型数据库理论的文献叫做作为系统目录或者数据字典。

在数据库内部,有一些系统表一直追踪数据库中发生的每一件事情。系统表存储像表、活动、列、索引等事情。这些完全符合Edgar Codd 的关系型数据库试试的十三条准则直译。这个准则就是定义动态在线目录,它就是“关于数据的数据”,也叫作元数据。

 Edgar Codd  准则4, 描述如下:

‘The database description is represented at the logical level in the same way as ordinary data, so that authorized users can apply the same relational language to its interrogation as they apply to the regular data.’

翻译:像普通数据一样,在逻辑层的数据表达了对数据库的描述,以便于授权用户能应用相同的SQL语言来查询元数据,就如同查询常规数据一样。

在SQL Server中,可以通过系统视图或者架构视图直接访问动态在线目录,方便用户更为快捷的开发和管理数据库。

那么如何找到触发器的数据?

*  以sys.system_views*is表开始。让我们查询出数据库中使用触发器的信息。可以告知你当前SQL Server版本中有什么触发器。

SELECT schema_name(schema_ID)+'.'+ name

  FROM sys.system_views WHERE name LIKE '%trigger%'

 ----------------------------------------

sys.dm_exec_trigger_stats              

sys.server_trigger_events              

sys.server_triggers                    

sys.trigger_event_types                

sys.trigger_events                     

sys.triggers                           



(6 row(s) affected)

  其中sys.triggers看起来信息很多,它又包含什么列?下面这个查询很容易查到:

 SELECT Thecol.name+ ' '+ Type_name(TheCol.system_type_id)

  + CASE WHEN TheCol.is_nullable=1 THEN ' NULL' ELSE ' NOT NULL' END as Column_Information

FROM sys.system_views AS TheView

  INNER JOIN sys.system_columns AS TheCol

    ON TheView.object_ID=TheCol.Object_ID

  WHERE  TheView.name = 'triggers'

  ORDER BY column_ID;

结果如下:

 Column_Information

----------------------------------------

name nvarchar NOT NULL

object_id int NOT NULL

parent_class tinyint NOT NULL

parent_class_desc nvarchar NULL

parent_id int NOT NULL

type char NOT NULL

type_desc nvarchar NULL

create_date datetime NOT NULL

modify_date datetime NOT NULL

is_ms_shipped bit NOT NULL

is_disabled bit NOT NULL

is_not_for_replication bit NOT NULL

is_instead_of_trigger bit NOT NULL

 

因此我们多这个信息有了更好的理解,有了一个目录的目录。这个概念有点让人头晕,但是另一方面,它也是相当简单的。我们能够查出元数据,再找个查询中,需要做的就是改变这个单词‘triggers’来查找你想要的视图名称。.

在2012及其以后版本,可以使用一个新的表值函数极大地简化上述查询,并可以避免各种连接。在下面的查询中,我们将查找sys.triggers 视图 中的列。可以使用相同的查询通过更改字符串中的对象名称来获取任何视图的定义。

 SELECT name+ ' '+ system_type_name

  + CASE WHEN is_nullable=1 THEN ' NULL' ELSE ' NOT NULL' END as Column_Information

FROM sys.dm_exec_describe_first_result_set

  ( N'SELECT * FROM sys.triggers;', NULL, 0) AS f

  ORDER BY column_ordinal;

查询结果如下:

 Column_Information

----------------------------------------

name nvarchar(128) NOT NULL

object_id int NOT NULL

parent_class tinyint NOT NULL

parent_class_desc nvarchar(60) NULL

parent_id int NOT NULL

type char(2) NOT NULL

type_desc nvarchar(60) NULL

create_date datetime NOT NULL

modify_date datetime NOT NULL

is_ms_shipped bit NOT NULL

is_disabled bit NOT NULL

is_not_for_replication bit NOT NULL

is_instead_of_trigger bit NOT NULL

 

sys.dm_exec_describe_first_result_set函数的最大优势在于你能看到任何结果的列,不仅仅是表和视图、存储过程或者贬值函数。

为了查出任何列的信息,你可以使用稍微修改的版本,只需要改变代码中的字符串'sys.triggers'即可,如下:

 Declare @TheParamater nvarchar(255)

Select @TheParamater = 'sys.triggers'

Select @TheParamater = 'SELECT * FROM ' + @TheParamater

SELECT

  name+ ' '+ system_type_name

  + CASE WHEN is_nullable=1 THEN ' NULL' ELSE ' NOT NULL' END as Column_Information

FROM sys.dm_exec_describe_first_result_set

  ( @TheParamater, NULL, 0) AS f

  ORDER BY column_ordinal;

SQL Server的存储机制

区段

区段(extent)是用来为表和索引分配空间的基本存储单元。它由8个连续的8KB数据页组成,共计64KB大小。关于区段的要点包括以下两方面:

一旦区段已满,那么下一记录将要占据的空间不是记录的大小,而是整个新区段的大小。
通过预先分配空间,SQL Server节省了为每个记录分配新空间的时间。

页是特定区段中的分配单元。每个区段包含8页。页是在达到实际数据行之前所能达到的最后一个存储级别。尽管每个区段中的页数据是固定的,但每一页中的行数不是固定的,这完全取决于行的大小,而行的大小是可以变化的。可以把页看作是表行和索引行数据的容器。通常不允许行跨页。

页拆分

当页已满时,它会进行行拆分。这意味着多个新页被分配,也以为着现有页上有近半的数据被移到新页上。

在使用群集索引时,该过程会有例外。如果有一个群集索引,并且下一个插入的记录在物理上作为表中的最后一个记录,那么创建一个新页,然后将该新行添加到这个新页中,而不需要重新定位任何现有数据。在研究索引时将介绍有关拆分的内容。

每个表中有多少个索引,并展示他们的名字

前面的表并不特别有用,因为无法一眼看到每个表有多少索引,以及它们是什么。下面这个语句可以实现:

SELECT  convert(CHAR(20),object_schema_name(t.object_ID)+'.'
    +object_name(t.object_ID)) AS 'The_Table',
sum(CASE WHEN i.object_ID IS NULL THEN 0 ELSE 1 END) AS The_Count,
coalesce(stuff(( 
     SELECT ', '+i2.name
       FROM sys.indexes i2
       WHERE t.object_ID = i2.object_ID
       ORDER BY i2.name
     FOR XML PATH(''), TYPE).value(N'(./text())[1]',N'varchar(8000)'),1,2,''),'') AS Index_List
  FROM sys.tables AS t
  LEFT OUTER JOIN sys.indexes i
    ON t.object_id=i.object_id
      AND is_hypothetical = 0 AND i.index_id > 0 
GROUP BY t.Object_ID;

 

我在老的测试数据库上执行这个测试,对象名称比较短。

The_Table            The_Count   Index_List
-------------------- ----------- --------------------------------------------------
dbo.publishers       1           UPKCL_pubind
dbo.titles           2           titleind, UPKCL_titleidind
dbo.titleauthor      3           auidind, titleidind, UPKCL_taind
dbo.stores           1           UPK_storeid
dbo.sales            2           titleidind, UPKCL_sales
dbo.roysched         1           titleidind
dbo.discounts        0           
dbo.jobs             1           PK__jobs__6E32B6A51A14E395
dbo.pub_info         1           UPKCL_pubinfo
dbo.employee         2           employee_ind, PK_emp_id
dbo.authors          2           aunmind, UPKCL_auidind

(11 row(s) affected)

如何获得以上信息?

因为我们不能直接访问,需要使用视图和函数来看这些信息。只能看到你权限内的数据。有更好的方法在用户数据库中使用数据定义语言(DDL),这些DDL语句包括CREATE, DROP, ALTER, GRANT, DENY, REVOKE 和sp_rename statements 等。总有一种方法可以使用DDL来修改视图中的任何信息,即使并不总是显而易见的。

关系型数据库使用动态的系统视图中的数据描述数据库,但是目前还有没有标准化。但是有一个包含在每个数据库内的架构可以读取这些信息:就是Information Schema

不走运的是,这个架构不足以提供足够信息,这意味着我们需要使用SQL Server 系统数据库的视图和函数来补充信息。接下来需要解释一些术语和技术,我会尽可能少的细节足以让大家轻松地理解这些示例

如图所示,如何访问元数据,及其接口

 图片 3

 

* *

但是当然一个触发器是首先是一个对象,因此一定在sys.objects?

  在我们使用sys.triggers的信息之前,需要来重复一遍,所有的数据库对象都存在于sys.objects中,在SQL Server 中的对象包括以下:聚合的CLR函数,check 约束,SQL标量函数,CLR标量函数,CLR表值函数,SQL内联表值函数,内部表,SQL存储过程,CLR存储过程,计划指南,主键约束,老式规则,复制过滤程序,系统基础表,同义词,序列对象,服务队列,CLR DML 触发器,SQL表值函数,表类型,用户自定义表,唯一约束,视图和扩展存储过程等。

  触发器是对象所以基础信息一定保存在sys.objects。不走运的是,有时我们需要额外的信息,这些信息可以通过目录视图查询。这些额外数据有是什么呢?

 

  修改我们使用过的查询,来查询sys.triggers的列,这次我们会看到额外信息。这些额外列是来自于sys.objects。

 SELECT coalesce(trigger_column.name,'NOT INCLUDED') AS In_Sys_Triggers,

       coalesce(object_column.name,'NOT INCLUDED') AS In_Sys_Objects

FROM

 (SELECT Thecol.name

  FROM sys.system_views AS TheView

    INNER JOIN sys.system_columns AS TheCol

      ON TheView.object_ID=TheCol.Object_ID

  WHERE  TheView.name = 'triggers') trigger_column

FULL OUTER JOIN

 (SELECT Thecol.name

  FROM sys.system_views AS TheView

    INNER JOIN sys.system_columns AS TheCol

      ON TheView.object_ID=TheCol.Object_ID

  WHERE  TheView.name = 'objects') object_column

ON trigger_column.name=object_column.name

查询结果:

In_Sys_Triggers                In_Sys_Objects

------------------------------ ----------------------

name                           name

object_id                      object_id

NOT INCLUDED                   principal_id

NOT INCLUDED                   schema_id

NOT INCLUDED                   parent_object_id

type                           type

type_desc                      type_desc

create_date                    create_date

modify_date                    modify_date

is_ms_shipped                  is_ms_shipped

NOT INCLUDED                   is_published

NOT INCLUDED                   is_schema_published

is_not_for_replication         NOT INCLUDED

is_instead_of_trigger          NOT INCLUDED

parent_id                      NOT INCLUDED

is_disabled                    NOT INCLUDED

parent_class                   NOT INCLUDED

parent_class_desc              NOT INCLUDED

 

以上这些让我们知道在sys.triggers的额外信息,但是因为它始终是表的子对象,所以有些不相关信息是不会展示在这些指定的视图或者sys.triggers中的。现在就要带大家去继续找找这些信息。

理解索引

韦氏字典中将索引定义为“通常按字母顺序排列的一些指定数据(入作者、主题或者关键字)的列表(如目录信息或者著作正文的引用)”。换种简单的表述,即索引是一种能快速访问数据的方法。

索引排序规则

索引中的存储顺序取决于为数据建立的排序规则信息。可以在数据库或列级设置排列规则,因此有相当细粒度的控制级别。一旦设置了排序规则顺序,改变它是非常困难的(但也是可能的),因此在设置之前要确定所需的排序规则顺序。

平衡树(B-Tree)

平衡树仅提供了一种以一致相对低成本的方式查找特定信息的方法。其名称中的“平衡”是自说明的。平衡树是自平衡的(只有很少的例外的情况),这意味着每次树进行分支时都有接近一半的数据在一边,而另一半数据在另一边。而名称中的“树”的概念也是很清楚的,如果绘制该结构,然后倒置它,会发现该结构具有树的一般形状,因此称为树。

图片 4

平衡树

更新平衡树:页拆分简介

所有这些页在读取方面工作良好,但在插入时会有点棘手。回顾一下,“B-Tree”中的“B”表示平衡(Balanced)。而且前面提到每次遇到树种的分支时,因为每一边都大约有一半的数据,所以B-Tree是平衡的。另外,由于添加新数据到树上的方法一般可避免出现不平衡,所以B-Tree有时被认为是自平衡的。

通过将数据添加到树上,节点最终将变满,并且将需要拆分。因为在SQL Server中一个节点相当于一个页,所以这称为页拆分。如下图所示:

图片 5

更新平衡树:页拆分

当发生页拆分时,数据自动地四处移动以保持平衡。数据的前半部分保留在旧页上,而数据的剩余部分添加到新页,这样就形成对半拆分,使得树保持平衡。

如果稍微考虑下这个拆分过程,将认识到它在拆分时增加了大量系统开销。不只是插入一页,而是进行下列操作:

  • 创建新页
  • 将行从现有页移动到新页上
  • 将新行添加到其中一页上
  • 在父节点中添加另一个记录项

但是,系统开销远不止这些。因为在进行树的排列,就可能有级联操作。创建新页时(因为拆分的缘故),需要在父节点中建立另一个记录项。在父节点中的这个及记录项在该级别也可能导致页拆分,而且整个过程会重新开始。实际上,这种可能性扩展到所有节点,甚至影响到根节点。

如果根节点拆分,那么最终实际会创建两个额外的页。由于只能有一个根节点,所以之前作为根节点的页被拆分为两页,而且成为树的新中间级别。然后创建全新的根节点,并且将有两个记录项(一个指向旧的根节点,另一个指向拆分的页)。

显然,页拆分会对系统性能产生非常负面的影响,其表现是在服务器上的处理会暂停几秒(此时页被拆分并改写)。

虽然页级的页拆分时很常见的,但是在中间节点进行页拆分却很少发生。当表增长时,索引的每一层将进行页拆分,但是,因为中间节点对于下一级节点的几个记录只有一个记录项,所以当向树的上层移动时,页拆分的数量将变得越来越少。尽管如此,对于发生在页级以上的拆分来说,在下一个较低级别上一定有一个,这意味着沿树而上的页拆分在本质上是累计的(而且严重影响性能)。

SQL Server 中的数据访问方式

从广义上讲,SQL Server检索所需数据的方法只有两种:

  • 使用表扫描:表扫描是相当直观的过程。当执行表扫描时,SQL Server从表的物理起点处开始,浏览表中的每一行。当发现和查询条件匹配的行,就在结果集中包含它们。
  • 使用索引:当SQL Server采用索引时,该过程实际上与表扫描的工作方式相类似,但是有一些捷径。在查询优化过程中,优化器查看所有可以那个的索引结构并选择最好的一个索引(这主要基于在连接和WHERE字句中所指定的信息,以及SQL Server在索引结构中保存的统计信息)。一旦选择了索引,SQL Server将在树结构中导航至与条件匹配的数据位置,并且提取它所需要的记录。区别在于,因为数据是排序的,所以查询引擎知道它何时到达正在查找的当前范围的下界。然后它可以结束查询,或者根据需要移至下一数据范围。

SQL Server使用何种方法来执行特定查询取决于可用的索引、所需的列、使用的连接以及表的大小。

索引类型

尽管表面上在SQL Server中有两种索引结构(群集索引和非群集索引),但是在实际撒很高,就内部而言,有3种不同的索引类型。

  • 群集索引
  • 非群集索引,该索引又包括以下两种:
    • 堆上的非群集索引
    • 群集索引上的非群集索引

物理数据的存储方式在群集索引和非群集索引中是不同的。而SQL Server遍历平衡树以到达末端数据的方式在所有3中索引类型中也是不同的。

所有的SQL Server索引都有叶级和非页级页。正如讨论平衡树所提到的那样,叶级是保存标志记录的“键”的级别,非页级是叶级的引导者。

索引在群集表(如果有群集索引)或者堆(用于没有群集索引)上创建。

  • 群集表:群集表示在其上具有U群集索引的任意表。它们对于表而言意味着以指定顺序物理存储数据。通过使用群集键唯一地标志独立的行,群集键即定义群集索引的列。
  • :堆是在其上没有群集索引的任意表。在这种情况下,基于行的区段、页以及行偏移量(偏移页顶部的位置)的组合创建唯一的标识符,或者成为行ID(Row ID, RID)。如果没有可用的群集键(没有群集索引),那么RID是唯一必要的内容。

** 群集索引**

群集索引对于任意给定的表而言是唯一的,每个表只能有一个群集索引。不一定要有非群集索引,但是如果查看索引类型,你会发现由于多种很显然那的原因,它正成为最常被使用的一种类型。

使群集索引变得特殊的方面是,群集索引的页级是实际的数据。也就是说,数据重新排序,按照和索引排序条件声明的相同相同物理顺序存储。这意味着,一旦到达索引的页级,就到达了数据。任何新纪录都根据其正确的物理顺序插入到群集索引中。创建新页的方式随需要插入的记录的位置而变化。

如果新纪录要插入到索引结构中间,就会发生正常的页拆分。来自旧页的后一半记录被移到新页,并且适当的时候,将新纪录插入到新页或旧页。

如果新纪录在逻辑上位于索引结构末端,那么创建新页,但是只将新纪录添加到新页,如下图。

图片 6

新纪录在逻辑上位于索引结构末端

堆上的非群集索引

页级不是数据,相反,它是一个可从中获得指向该数据的指针的级别。该指针以RID的形式出现,如同在本章前面描述的那样,这种RID由索引指向的特定行的区段、页以及偏移量构成。即使叶级不是实际的数据(相反,它具有RID),使用叶级也仅仅比使用群集索引多一个步骤。因为RID具有行的位置的全部信息。所以可以通过RID直接达到数据。

然而,不要误以为“这个额外步骤”只有少量的系统开销,并且堆上的非群集索引将和群集索引几乎一样快的运行。使用群集索引,数据在物理上是按照索引的顺序排列的。这意味着,对于一定范围的数据,当找到在其上具有数据范围起点的行时,那么很可能有其他行 在同一页上(也就是说,因为它们存储在一起,所以在物理上已几乎到达下一个记录)。使用堆,数据并为通过除索引外的其他方法链接在一起。从物理上看,绝对没有任意种类的排序。这意味着,从物理读取的角度看,系统可能不得不从整个文件中检索记录。实际上,很可能最终多次从同样的页中取出数据。SQL Server没有方法知道它需要回到该物理位置,因为在数据之间没有链接。使用群集索引,它知道这是物理上的排序,因此仅仅通过访问页一次就完全获得数据。

图片 7

堆上的非群集索引查找

如上图所示。主要通过索引导航,但一切都按以前的方式工作。服务器从相同的根节点开始,并且遍历树,处理越来越聚焦的页,直到到达索引的叶级。这里就有了区别。采用群集索引的方式,能够正好在这里停止,而采用非群集索引的方式则需要做更多的工作。如果非群集索引是在堆上,那么只要再进入一个级别。获得来自叶级页的RID,并且定位到该RID,直到这时才可以直接获得实际的数据。

群集表上的非群集索引

和堆上的非群集索引一样,索引的非叶级节点的工作与使用群集索引时相比几乎一样。区别出现在叶级。

在叶级,与使用其他两种索引结构所看到的内容有相当明显的区别:有另外一个索引来查找。使用群集索引,当服务器达到叶级时,它可以找到实际的数据。使用堆上的非群集索引,不能找到实际的数据,但是可以找到能够直接获得数据的标识符(仅仅多了一步)。使用群集表的非群集索引,可以找到群集键。也就是说,服务器找到足够的信息来利用群集索引。

如下图。服务器首先执行范围搜索,这一点与前面相同。在索引中执行一次单独的查找,并且可以浏览非群集索引以找到满足条件(LIKE'T%')的连续数据范围。这种能够直接到达的索引中的特定位置的查找称为seek。

图片 8

查找FName类似“T%”的EmployeeID

然后开始第二个查找,使用群集索引的查找。第二种查找非茶馆迅速:问题在于它必须执行多次。可以看到,SQL Server从第一个索引查找中索引列表(所有名称以“T”开始的列表),但是该列表在逻辑上并没有以任意连续的方式与群集键相匹配,每个记录需要单独地查找,如下图。

![Uploading image_980906.png . . .]

查找没有聚集索引的表

关于索引,您可以找到很多有趣的东西。例如,这里有一种快速查找表的方法,无需使用聚集索引(堆)

-- 展示所有没有聚集索引的表名称
SELECT  object_schema_name(sys.tables.object_id)+'.'
    +object_name(sys.tables.object_id) AS 'Heaps'
      FROM sys.indexes /* see whether the table is a heap */
      INNER JOIN sys.tables ON sys.tables.object_ID=sys.indexes.object_ID
      WHERE sys.indexes.type = 0;

系统视图

触发器的问题

  触发器是有用的,但是因为它们在SSMS对象资源管理器窗格中不是可见的,所以一般用来提醒错误。触发器有时候会有些微妙的地方让其出问题,比如,当导入过程中禁用了触发器,并且由于某些原因他们没有重启。

下面是一个关于触发器的简要提醒:

  触发器可以在视图,表或者服务器上,任何这些对象上都可以有超过1个触发器。普通的DML触发器能被定义来执行替代一些数据修改(Insert,Update或者Delete)或者在数据修改之后执行。每一个触发器与只与一个对象管理。DDL触发器与数据库关联或者被定义在服务器级别,这类触发器一般在Create,Alter或者Drop这类SQL语句执行后触发。

  像DML触发器一样,可以有多个DDL触发器被创建在同一个T-SQL语句上。一个DDL触发器和语句触发它的语句在同一个事务中运行,所以除了Alter DATABASE之外都可以被回滚。DDL触发器运行在T-SQL语句执行完毕后,也就是不能作为Instead OF触发器使用。

  两种触发器都与事件相关,在DML触发器中,包含INSERT, UPDATE, 和DELETE,然而很多事件都可以与DDL触发器关联,稍后我们将了解。

创建、修改和删除索引

CREATE INDEX语句

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED]
INDEX <index name> ON <table or view name>(<column name> [ASC | DESC] [, ...n]
[WHERE <condition>])
[WITH
[PAD_INDEX = { ON | OFF }]
[[, ] FILLFACTOR = <fillfactor>]
[[, ] IGNORE_DUP_KEY = { ON | OFF }]
[[, ] DROP_EXISTING = { ON | OFF }]
[[, ] STATISTICS_NORECOMPUTE = { ON | OFF }]
[[, ] SORT_IN_TEMPDB = { ON | OFF}]
[[, ] ONLINE = { ON | OFF }]
[[, ] ALLOW_ROW_LOCKS = { ON | OFF }]
[[, ] MAXDOP = <maxinum degree of parallelism>]
[[, ] DATA_COMPRESSON = { NONE | ROW | PAGE }]
]
[ON {<filegroup> | <partition scheme name> | DEFAULT}]

随约束创建的隐含索引

当向表中添加如下两种约束之一时,就会创建隐含索引:

  • 主键约束
  • 唯一约束(也称为替换键)

当创建一个索引作为约束的隐含索引时,除{CLUSTERED | NONCLUSTERED}和FILLFACTOR外,所有选项都不允许使用。

创建XML索引

XML索引是SQL Server 2005中的新增功能。除了WHERE、IGNORE_DUP_KEY和ONLINE外,XML索引创建语法支持在前面的CREATE语句中所看到的所有相同选项。

在SQL Server中,可以在类型为XML的列上创建索引。这样做的主要要求如下:

  • 在包含需要索引的XML的表上必须具有群集索引。
  • 在创建“辅助”索引之前,必须现XML数据列上创建“主”XML索引。
  • XML索引只能在XML类型的列上创建(而且XML索引是可以在该类型的列上创建的唯一一种索引)。
  • 主索引必须是基表的一部分,不能在视图上创建索引。

主XML索引:在XML索引上创建的第一个索引必须声明为“主”索引。当创建主索引时,SQL Server创建一个新的群集索引,这个群集索引将基表的群集索引和来自任何指定的XML节点的数据组合在一起。

辅助XML索引:这里没有任何特别之处,非常类似指向群集索引的群集键的非群集索引,辅助XML索引以相似的方法指向主XML索引。一旦创建了主XML索引,就能在XML列上创建多达248个以上的XML索引。

在稀疏列和地理空间列上创建索引
由于其复杂度,这里不过多说明。但事实是可在稀疏列和地理空间类型数据上创建特殊索引。因此,如果要应用这些特殊需求类型的列,就需要将此谨记在心。

每个索引中有多少行在表里面?

通过连接sys.partitions视图,我们可以计算出索引中大约有多少行。我修改了一些代码,关联了sys.extended_properties,这样可以把备注的信息带出来。

--列出每个索引/堆的行数
SELECT 
  OBJECT_SCHEMA_NAME(t.object_id)+'.'+OBJECT_NAME(t.object_id) as 'Table',
  coalesce(i.NAME,'(IAM for heap)') as 'Index',
  Coalesce(
   (SELECT SUM(s.rows) FROM sys.partitions s WHERE s.object_id = i.object_id
        AND s.index_id = i.index_ID    
    ), 0) 'Rows',coalesce(ep.Value,'') as comments
 FROM sys.tables t
   INNER JOIN sys.indexes i ON i.object_id = t.object_id
   LEFT OUTER JOIN sys.Extended_Properties ep
   ON i.Object_Id = ep.Major_Id AND i.Index_Id = Minor_Id AND Class = 7;

 

图片 9

然后,你可以修改这个代码,让其只是展示每个在索引表中的表有多少行。

SELECT
  OBJECT_SCHEMA_NAME(t.object_id)+'.'+OBJECT_NAME(t.object_id) AS 'Table',  
  sum(rows) AS row_count
FROM sys.partitions p INNER JOIN sys.tables t 
   ON p.object_ID=t.object_ID 
WHERE index_id < 2 GROUP BY t.object_ID,Index_ID;

Information Schema

这个架构是一套视图,视图中是当前数据库的信息。每一个数据库中都有这个架构,只能看到当前数据库的对象信息。可以直接访问这些架构的数据在主要的关系型数据中。其中架构视图不包含数据库部署信息。

对于不同的关系型数据库之间的处理工作这个架构尤其重要。它们非常适合日常工作,例如在访问钱检查是否存在,但是如果需要详细报告则会受到限制。他们还使用一种稍有不同的标准命名法:例如,数据库被称为目录,用户定义的数据类型被称为“domain”。

之前看到MSDN上有人警告说不要使用INFORMATION_SCHEMA视图来确认对象架构,我理解是因为SQL Server允许在不同的架构中有相同的表名字,因此当只有表名称的时候会有混淆。所以我认为尽管放心使用就好了。

 

在数据库中列出触发器

那么怎么获取触发器列表?下面我在AdventureWorks数据库中进行查询,注意该库的视图中没有触发器。

第一个查询所有信息都在sys.triggers 的目录视图中。

SELECT

  name AS TriggerName,

  coalesce(object_schema_name(parent_ID)+'.'

    +object_name(parent_ID),'Database ('+db_name()+')') AS TheParent

FROM sys.triggers;



TriggerName                    TheParent

------------------------------ ----------------------------------------

ddlDatabaseTriggerLog          Database (AdventureWorks2012)          

dEmployee                      HumanResources.Employee                

iuPerson                       Person.Person                          

iPurchaseOrderDetail           Purchasing.PurchaseOrderDetail         

uPurchaseOrderDetail           Purchasing.PurchaseOrderDetail         

uPurchaseOrderHeader           Purchasing.PurchaseOrderHeader         

iduSalesOrderDetail            Sales.SalesOrderDetail                 

uSalesOrderHeader              Sales.SalesOrderHeader                 

dVendor                        Purchasing.Vendor                      

iWorkOrder                     Production.WorkOrder                   

uWorkOrder                     Production.WorkOrder   

  我使用元数据函数db_name()使SQL保持简单。db_name()告诉我数据库的名称。object_schema_name()用来查询object_ID代表的对象的架构,以及object_name**()**查询对象名称。这些对对象的引用指向触发器的所有者,触发器可以是数据库本身,也可以是表:服务器触发器有自己的系统视图,稍后我会展示。

如果想要看到所有触发器,那么我们最好使用sys.objects 视图:

SELECT name as TriggerName, object_schema_name(parent_object_ID)+'.'

    +object_name(parent_object_ID) AS TheParent

            FROM   sys.objects

           WHERE  OBJECTPROPERTYEX(object_id,'IsTrigger') = 1

 

注意,输出不包含数据库级别的触发器,因为所有的DML触发器都在sys.objects视图中,但是你会漏掉在sys.triggers视图中的触发器。

上面查询结果:

name                           TheParent

------------------------------ -------------------------------

dEmployee                      HumanResources.Employee

iuPerson                       Person.Person

iPurchaseOrderDetail           Purchasing.PurchaseOrderDetail

uPurchaseOrderDetail           Purchasing.PurchaseOrderDetail

uPurchaseOrderHeader           Purchasing.PurchaseOrderHeader

iduSalesOrderDetail            Sales.SalesOrderDetail

uSalesOrderHeader              Sales.SalesOrderHeader

dVendor                        Purchasing.Vendor

iWorkOrder                     Production.WorkOrder

uWorkOrder                     Production.WorkOrder

 

明智地选择——在何时何地使用何种索引

选择性

索引,特别是非群集索引,主要在其中有相当高级别的选择性的情况下是有益的。所谓选择性,指的是列中唯一值得百分比。列中唯一值得百分比越高,选择性就越高,从而索引的益处就越大。

** 注意成本:少即是多**

记住,虽然索引在读取数据方面可提高性能,但是在修改数据时,它们实际上花费很高。索引没有通过魔法来维护,每次对数据进行修改时,任何与该数据相关的索引也需要更新。可以将执行更新时花费的时间认为是投资的金钱。每次通过索引读取数据时,您 可以将节省的时间视为投资回报:但是对于没有使用的每个索引,投资就没有任何回报。

选择群集索引

记住,只可以有一个群集索引,所以需要明智地选择它。

默认情况下,主键是和群集索引一起创建的。这通常是个不错的选择,但并不总是如此(实际上,在有些情况下,这回带来严重的危害),并且如果这样做,那么 将不能在其他任何地方使用群集索引。这里的要点在于不要接受默认方式。在定义主键时要考虑一下,确实想要它作为群集索引吗?

如果确实想要改变,也就是说,不想声明为群集索引,那么在创建表时只需要添加NONCLUSTERED关键字。例如:

CREATE TABLE MyTableKeyExample
(
  Column1 int IDENTITY
    PRIMARK KEY NONCLUSTERED,
  Column2 int
)

如果讨论的列常作为范围查询的对象,那么群集索引对于这类查询是很用的。这类查询通常使用BETWEEN语句或者<or>符号。使用GROUP BY以及可利用MAX、MIN和COUNT聚合函数额查询也是使用范围和偏好群集索引的查询的重要示例。群集索引适用此处,这是因为搜索可以直接到达物理数据中的特定点,可一直读数据,直到到达范围的末端,然后停止。这种方法非常有效。当想要数据基于群集排序(使用ORDER BY)时,群集也是极好的方法。

在将要以非连续的顺序进行大量插入时不适合使用群集索引。还记得页拆分的概念吗?这里会进行叶拆分,并且会消耗大量时间。

列顺序问题

仅仅因为索引中有两个列,这不能说明索引对于任何引用其中一列的查询是有用的。

如果查询中使用了索引中列出的第一个列,那么可考虑使用该索引。好的方面是不必在每一列上一对一匹配——只需要第一个列匹配。当然,(按照顺序)匹配的列越多越好,但只需要通过第一个列就可确定“不要使用”某索引。

可以这样考虑一下,假设在使用电话薄。所有项都按先姓后名的方式进行索引。如果知道要通话电话的人的名是Fred,那么这种排列顺序能带来任何好处吗?另一方面,如果只知道他的姓是Blakc,那么索引将可以用来缩小查找范围。

索引构造过程中较为常见的一种错误是认为一个包含所有列的索引将对任何情况都是有帮助的。实际上,这样做只是将所有数据又存储了一次。如果索引的第一个列没有在查询中的JOIN、ORDER BY或者WHERE子句中提及,那么索引将完全被忽略。

覆盖索引

简单来说,覆盖索引包含查询所需要的所有数据。如果前面看到的那样,一旦SQL Server发现它需要的数据,就会停止查找。建立在索引基础的几乎所有最终查找都采用这种处理方式。如果只是仅需要在一个索引键中解析查询,或者需要将其包含在叶子中,就没有理由执行这种查找。

最基础和最显而易见的覆盖索引是群集索引。表中的所有数据都在叶子中,因此群集覆盖了所有查询。

您可以在索引的叶子中包含(INCLUDE)非键列。尽管这看起来可以解决群集键查找的任何问题,但是它需要一定的开销。你的索引键保持相同的大小,因此查找速度依然很快,但是叶子必须增大以容纳额外的数据。此外,每次插入、更新或删除数据时,必须抛弃更多的位数进行补偿。当然,对于必须快速执行的常见查询,这是极好的工具。

过滤索引

到目前为止看到的所有索引有一个共同点:每个索引都针对表中的每一行在叶子中有一个条目。然而,这并不是严格的必要条件,并且有时需要限制出现在索引中的行。

创建过滤索引只需要包括WHERE字句。

您可以在运行包括兼容WHERE表达式的查询时使用该索引。关于过滤索引需要注意一下几点:

  • 索引深度远小于全表索引。您只是索引几千行,而不是索引超过十万行,因此遍历索引的速度更快。
  • 因为索引只包含条件过滤后的结果集,所以通过插入、更新、删除操作维护该索引的开销较低。改变不存在过滤后的结果集中的数据完全不影响索引。

过滤索引的一个相对常见用途是有效地允许在可为NULL的列上设置唯一约束。通过使用WHERE <column> IS NOT NUL 字句创建唯一索引,您可以阻止重复的实际数据,并且仍然允许存在的NULL值。

修改索引

如果修改索引的组成,那么仍然需要DROP(删除)然后CREATE(创建)索引,或者用DROP_EXISTING=ON选项CRAETE(创建)并使用索引。

ALTER INDEX的语法如下。

ALTER INDEX { <name of index> | ALL }
  ON <table or view name>
  { 
    REBUILD
    [  [  WITH (
          [ PAD_INDEX = { ON | OFF }  ]
          | [ [, ] FILLFACTOR = <fillfactor> ]
          | [ [, ] SORT_IN_TEMPDB = { ON | OFF } ]
          | [ [, ] IGNORE_DUP_KEY = { ON | OFF } ]
          | [ [, ] STATISTICS_NORECOMPUTE = { ON | OFF } ]
          | [ [, ] ONLINE = { ON | OFF } ]
          | [ [, ] ALLOW_ROW_LOCKS = { ON | OFF } ]
          | [ [, ] ALLOW_PAGE_LOCKS = { ON | OFF } ]
          | [ [, ] MAXDOP = <max degree of parallelism> ]
          | [ [, ] ONLINE = { ON | OFF } ]
        )]
        | [ PARTITION = <partition number>
            [ WITH ( 
              <partition rebuild index option>
              [, ...n]
           )]
          ]
        ]
        | DISABLE
        | REORGANIZE
          [ PARTITION = <partition number> ]
          [ WITH ( LOB_COMPACTION = { ON | OFF } ) ]
        | SET (
          [ ALLOW_ROW_LOCKS = { ON | OFF }] 
          | [ [, ] ALLOW_PAGE_LOCKS = { ON | OFF } ]
          | [ [, ] IGNORE_DUP_KEY = { ON | OFF } ]
          | [ [, ] STATISTICS_NORECOMPUTE = { ON | OFF } ]
          )
  }  [ ; ]
  • 索引名:如果项维护一个特定的索引,可以指定该索引,或者使用ALL表明项要维护与指定的表相关联的所有索引。

  • 表明或视图名: 从字面可知,这是想要在其上执行维护的特定对象(表或视图)的名称。注意,必须是一个特定的表(可以给它提供一个列表,然后说“请处理所有这些”)。

  • REBUILD:这是用来修复索引的“工业级强度”的方法。如果使用该选项运行ALTER INDEX,那么将完全丢弃旧的索引并重新生成新的索引。结果是真正优化的速印,其中所有叶级和非叶级的页都按照定义进行了重新构建(使用个默认值或者开关修改填充因子等)。如果讨论中的索引是群集索引,那么也会重新组织物理数据。
    要小心使用该选项。一旦开始REBUILD,在完成索引重建之前,正在使用的索引实际就没有了。依赖该索引的所有查询可能变得异常缓慢(可能会降几个数量级)。对于这类事情,首先需要在离线系统上测试,以了解整个过程将花多少时间。然后,计划在非高峰时段运行(最好有人监控,以确保它在高峰时段来临时恢复联机状态)。

  • DISABLE:该选项名副其实,只是方式有点过激。如果该命令的全部作用只是为了让索引离线,直至您决定了进一步要做什么,则它是不错的选择,但它实际上会把索引标记为不可用。一旦禁用了某个索引,在重新激活之前,必须重建索引(不是重新组织,而是重建)。ALTER INDEX...DISABLE的相反操作并不是ENABLE,这样的操作并不存在。你必须执行ALTER INDEX...REBUILD。
    如果对表禁用了群集索引,那么也会禁用表。数据仍会保留,但在重建群集索引之前,不能被所有索引(因为它们都依赖于群集索引)访问。

REORGANIZE

从开发人员的角度看,这一选项特别好。如果重新组织索引,就得到了比完全重建索引稍逊一点的完全优化,但这种方法可以联机进行(用户仍能使用索引)。

如果仔细琢磨,那么上面的描述可能会让你想到一个问题,“稍逊一点”到底是指什么。它其实指,REORGANIZE只是在索引的叶级起作用,而不触及非叶级。这意味着未获得完全优化。但是,对于大部分的索引而言,那不是真正产生碎片的地方(尽管可能会发生这种情况,并且遇到的情况也不尽相同)。

由于该选项对用户的影响非常小,通常您会希望该工具作为常规维护计划的一部分来使用。后面讨论碎片时将更进一步讨论它。

删除索引

如果正在不断地重新分析情况和添加索引,那么也不要忘记删除索引。记住插入索引需要系统开销。由于在考虑需要的索引时并没有对插入的开销太在意,因此也没有考虑过那些索引是不需要的。总是要自问一下:“可以从中去掉那些索引?”

删除索引的语法非常类似于删除表的语法。唯一的不同在于需要使用索引所附着的表或视图来限定索引名称:

DROP INDEX <table or view name>.<index name>

或者

DROP INDEX <index name> ON <table or view name>

这样就可以删除索引。

从查询计划中获取提示

SQL Server 2008提供了一个新功能:查询计划信息中的索引提示,它们将给出查询优化器所认为有用的、但不存在的索引(一次给出一个索引)。现实情况是,在创建建议的索引之后,您不需要严格地检查查询是否使用该索引;即使该索引不会被任何其他的查询再次使用,他也会用于查询。如果您在执行某个重要的查询时获得该提示,则在大多数情况下需要采取该提示的建议。

索引未被使用的原因

反复的测试!检查您的索引是否被使用。如果它们未被使用,则开始查找原因,比如无序的WHERE子句、缺乏选择性、建议的索引或不可索引条件。

当你的WHERE子句中过滤的是某个函数而不是列时,就不会用索引。