数据库索引的简单了解与使用

目录

1. 索引的概念、特点及类型
2. 索引的结构与原理
3. 索引的创建及使用

---

一、索引的概念、特点及类型

  Q: 为什么使用索引？
  A：  在学生关系中查询学号为“BX15236”的学生，查询语句为：  
     Select * From 学生 Where 学号=' BX15236'               
    实现该查询最基本的方式是遍历关系中全部元组,逐行比较 每个元组的学号值是否匹配where子句中的条件。
 显然，这种查询方式极其耗时，并造成大量的磁盘I/O操作。           
 如果在该关系的学号属性列上建立索引，则无需对整个表进行扫描，就能快速定位到查询的内容，检索出需要的信息。 

1．索引的概念

索引（Index）是关系中一列或几列值的列表及相应的指向关系中标识 这些值的 数据页的逻辑指针。

2．索引的特点

1）极大地提高数据查询的速度，这也是其最主要优点。
2）通过创建唯一性索引，可以保证数据库中各行数据的唯一性。
3）建立在外码上的索引可以加速多表之间的连接，有益于实现数据的参照完整性。
4）查询涉及到分组和排序时，也可显著减少分组和排序的时间。
5）通过使用索引可以在查询过程中使用优化隐藏器，提高系统的性能。

使用索引能够提高系统性能，但是索引为查找到带来的性能好处是有代价的

1）物理存储空间中除了存放数据表之外，还需要一定的额外空间来存放索引。
2）对数据表进行插入、修改和删除操作时，相应的索引也需要动态维护更新， 消耗系统资源。

3．索引的类型

微软的SQL Server中，根据其索引记录的结构和存放位置可分为聚簇索引 （Clustered Index, 也叫聚集索引）、非聚簇索引（Nonclustered Index, 也叫非聚集索引）和其它索引。

聚簇索引:把聚簇码上具有相同值的元组集合存放在连续的物理块中。 聚簇索引能够确定表中数据的物理存储顺序，即表中数据是按照索引 列的顺序进行物理排序的。

非聚簇索引中，数据与索引的存储位置可以完全独立,索引存储在一个 地方，数据存储在另一个地方。索引中带有指向数据存储位置的逻辑 指针。 可以在一个表上建立多个非聚簇索引.

索引类型	简单说明
聚簇索引	创建索引时，索引键值的逻辑顺序决定表中对应行的物理顺序。聚簇索引的底层（或称为叶子）包含该 表的实际数据行，因此要求数据库具有额外的可用工作空间来容纳数据的排序结果和原始表或现有聚簇 索引数据的临时副本。一个表或者视图只允许同时有一个聚簇索引。
非聚簇索引	创建一个指定表的逻辑排序的索引。对于非聚簇索引，数据行的物理排序独立于索引排序。一般来说， 先创建聚簇索引，后创建非聚簇索引。
唯一索引	唯一索引不允许两行具有相同的索引值，如果存在唯一索引，数据库引擎会在每次插入操作添加数据时 检查重复值。可生成重复键值的插入操作将被回滚，同时数据库引擎显示错误消息。
分区索引	为了改善大型表的可管理性和性能，常会对其进行分区，对应的可以为已分区表建立分区索引，但是有 时亦可以在未分区的表中使用分区索引。
筛选	筛选索引是一种经过优化的非聚集索引，适用于从表中选择少数行的查询。筛选索引使用筛选谓词对表 中的部分数据进行索引。设计良好的筛选索引可以提高查询性能，降低存储成本和维护成本。
空间	空间索引是一种扩展索引，允许对数据库中的空间数据类型（如geometry或geography）的列编制索引。
XML	可以对 xml 数据类型列创建 XML 索引。它们对列中 XML 实例的所有标记、值和路径进行索引，从而提高 查询性能。
计算列上的索引	从一个或多个其它列的值或者某些确定的输入值派生的列上建立的索引。
带有包含列的索引	可以将非键列（称为包含列）添加到非聚集索引的叶级别，从而通过涵盖查询来提高查询性能。也就是 说，查询中引用的所有列都作为键列或非键列包含在索引中。这样，查询优化器可以通过索引扫描找到 所需的全部信息，而无需访问表或聚集索引数据。
列存储	在常规索引中，所有每行的索引数据都被一起保持在一页中，每列数据在一个索引中是跨所有页保留的。 而在列存储索引中，每列数据被保存在一起，这样每个数据也都只包含来自单个列的数据。
全文	一种特殊类型的基于标记的功能性索引，有SQL Server全文引擎生成和维护，用于帮助在字符串数据中搜索复杂的词。

---

二、索引的结构与原理

       索引作为一个数据库对象，存储在数据库中。一条索引记录中包含的基 本信息包括：索引键值（key，即定义为索引的字段值）和逻辑指针（ pointer，指向数据页)。

       在SQL Server中,索引按照B树结构组织。索引B树中的每一个索引页(Index Page)称为一个索引结点。B树的顶端称为根结点,最底端的结点称为叶层结点或叶结点,中间层的结点称为非叶层结点,其结构如图所示。当对该表执行查询操作时,系统会先在其索引页面(Index Page)进行查找。先从B树的根结点出发,对结点内的已排好序的索引关键字(key)序列进行二分查找,如果命中,则进入查询关键字所属范围的子结点,重复查找,直到所对应的子结点指针(Pointer)为空,或者已经是叶子结点,最后根据指针从数据页面1四知识拓展 (Data Page)读取到相应的数据。因此, B树的查询性 :B树结构及特点能等价于二分查找,效率较高。

       此外,多数情况下索引记录中仅包含索引关键字和较小的指针链接,比完整的数据行所占的空间要小很多,因此索引页相对数据页来说要密集许多,一个索引页可以存储数量更多的索引记录,这意味着在索引中执行查询操作在10上占很大的优势,这也是索引本质上的优势所在。

1．聚簇索引的结构

        聚簇索引的非叶层节点中，只包含下一节点的第一个键值及指向下一节点的指 针，指针的格式为：文件编号+数据页编号，而聚簇索引的叶子节点就是表中的数据 行。所有数据行的存储顺序与索引的存储顺序一致。

 例：在姓名字段上建立聚簇索引，当需要根据姓名字段查找记录时，数据库系统会先找到该索引的根节点，然后根据指针查找下一个，直到找出需要的某个记录。
 
 如，现在要查询“Gail”，由于它介于[Bill,Kate]之因此系统先找到Bill对应的索引页1007；
 在该页中Gail介于[Gabby,Hank]之间，因此找到1133（也是数据节点），并最终在此页中找到了Gail对应的记录行。

2.非聚簇索引的结构

        非聚簇索引中的叶子节点并非数据节点，叶子节点存储的是值+指针，指针中则包括 页指针和指针偏移量，用于定位到具体的数据行。
        在除叶节点外的其他索引节点，存储的也是类似的内容，只不过它是指向下一级的 索引页的。

例：在姓名字段上建立的非聚簇索引。假设要查询“ Gill ”，系统从根节点出发，考虑到 Gill 位于 [Bill ，Kate] 之间，则先定位到索引页 1007 ；
下一步判断 Gill 位于 [Gabby ，Hank] 之间，再定位到索引页 1133 ，找到Gill 。
此时索引页 1133 为索引的叶子节点，Gill指针中指示数据页为1421 ，偏移量为 2 。
 根据指针内容，在数据页 1421 第2行中找到 Gill 对应的记录行。

创建索引的策略

一般来说，可以考虑在以下属性列上建立索引：

1）在查询条件中常用的列上建立索引，可以加快查询速度；

2）在连接条件中常出现的列上建立索引，可以加快连接的速度；

3）在经常使用排序的列上建立索引，利用索引的排序，可加快查询的速度；

4）在经常需要搜索连续范围值的列上建立聚簇索引，找到第一个匹配行后，满 足要求的后续行在物理上是连续且有序的，因此只要将数据值直接与查找的终 止值进行比较即可连续提取后续行。

---

三、索引的创建及使用

索引的创建

T-SQL语言中，建立索引使用CREATE Index语句，其语法格式为：

CREATE  [UNIQUE][CLUSTERED|NONCLUSTERED] INDEX  <索引名> ON  <表名>  ( <列名> [<次序>] ,[<列名> [<次序>]] …)

其中的参数说明如下：
1）UNIQUE表明要创建的是唯一索引;
注意 ：只有当数据本身具有唯一特征时，建立唯一索引才有意义。如果必须 要实施唯一性来确保数据的完整性，则应在列上建立唯一约束，而不要创建 唯一索引。当在表上创建唯一约束时，系统会自动在该列建立唯一索引。

2）CLUSTERED为要创建的是聚簇索引，若无显示声明，默认创建的是非聚簇索引；

3）<表名>是要创建索引的基本表的名称。

4）索引可以建立在一个列上，也可以建立在多个列上，各<列名>之间用逗号分隔开。建立在多个列上的索引称为复合索引。

5）<次序>指定索引值的排序方式，包括ASC（升序）和DESC（降序），默认为 ASC

 例：
   1.在学生表的学号上建立唯一的聚簇索引，按照升序排列。 
   CREATE UNIQUE CLUSTERED INDEX SNO_Index ON 学生(学号)
   2. 在教师表中按照姓名建立非聚簇索引，按照姓名降序排列。         
   CREATE INDEX Tname_Index ON 教师 (教师姓名 DESC) 
   3.在学生选课表按课程代码升序和学号升序建立唯一索引。 
     CREATE UNIQUE INDEX SC_Index ON 学生选课 (课程代码 , 学号) 

索引的查看与使用

（1）索引的查看
查看指定表的索引信息，可通过以下语句执行存储过程SP_HELPINDEX：

EXEC  SP_HELPINDEX <表名> 

该语句执行结果可返回指定表上所有索引的名称、类型和建立索引的列。 当然也可以执行以下语句：

 DBCC SHOW_STATISTICS（表名，索引名）

该命令也可以用来查看指定表中某个索引的统计信息。

（2）聚簇索引与非聚簇索引的比较

如图所示，学生表中原有3条记录，在 学生表的学号属性上建立非聚簇索引后，插入新元组（BX15240，测试，男，软件工程， Null，Null），新元组在学生表中最后一行。

当在学生表的学号属性上建立的是聚簇 索引时，表中原有元组以及插入的新元 组都自动按照学号的大小顺序重新排序， 结果如图所示。

索引的更新与删除

1．索引的更新
随着数据库中数据的不断插入、修改和删除，索引对系统的优化性能有可能出现降低。此时数据库管理员应该定期对索引进行分析和更新。
2．索引的删除
删除索引使用DROP INDEX语句，其语法格式为：

   DROP  INDEX  <索引名> ON <表名或视图名>      或者 DROP INDEX <表名或视图名>.<索引名>

删除索引时，系统会同时从数据字典中删除该索引的相关描述。