索引的一些总结

1.1.1 摘要

  如果说要对数据库进行优化，我们主要可以通过以下五种方法，对数据库系统进行优化。
  1. 计算机硬件调优
  2. 应用程序调优
  3. 数据库索引优化
  4. SQL语句优化
  5. 事务处理调优
  在本篇博文中，我们将想大家讲述数据库中索引类型和使用场合，本文以SQL Server为例，对于其他技术平台的朋友也是有参考价值的，只要替换相对应的代码就行了！
  索引使数据库引擎执行速度更快，有针对性的数据检索，而不是简单地整表扫描（Full table scan）。
  为了使用有效的索引，我们必须对索引的构成有所了解，而且我们知道在数据表中添加索引必然需要创建和维护索引表，所以我们要全局地衡量添加索引是否能提高数据库系统的查询性能。
  本文目录

  

   

• 介绍数据库中的文件和文件组   
  
• 页和区   
  
• 介绍索引的基本结构   
  
• 介绍索引的两个基本类型   
  
• 聚集索引   
  
• 非聚集索引   
  
• 堆表的非聚集索引   
  
• 聚集表的非聚集索引   
  
• 索引的有效性
  

  1.1.2 正文

  在物理层面上，数据库有数据文件组成，而这些数据文件可以组成文件组，然后存储在磁盘上。每个文件包含许多区，每个区的大小为64K由八个物理上连续的页组成（一个页8K），我们知道页是SQL Server数据库中的数据存储的基本单位。为数据库中的数据文件（.mdf 或 .ndf）分配的磁盘空间可以从逻辑上划分成页（从0到n连续编号）。
  页中存储的类型有：数据，索引和溢出。
  文件和文件组

  在SQL Server中，通过文件组这个逻辑对象对存放数据的文件进行管理。
  

  

图1数据库文件组织

  在顶层是我们的数据库，由于数据库是由一个或多个文件组组成，而文件组是由一个或多个文件组成的​​逻辑组，所以我们可以把文件组分散到不同的磁盘中，使用户数据尽可能跨越多个设备，多个I/O 运转，避免 I/O 竞争，从而均衡I/O负载，克服访问瓶颈。
  区和页

  如图2所示，文件是由区组成的，而区由八个物理上连续的页组成，由于区的大小为64K，所以每当增加一个区文件就增加64K。
  

  

图2文件组成

  页中保存的数据类型有：表数据、索引数据、溢出数据、分配映射、页空闲空间、索引分配等，具体如下图所示：
     

页类型	内容
Data	当 text in row 设置为 ON 时，包含除 text、 ntext、image、nvarchar(max)、varchar(max)、varbinary(max) 和 xml 数据之外的所有数据的数据行。
Index	索引条目。
Text/Image	大型对象数据类型：text 、 ntext、image、nvarchar(max)、varchar(max)、varbinary(max) 和 xml 数据。数据行超过 8 KB 时为可变长度数据类型列：varchar 、nvarchar、varbinary 和 sql_variant
Global Allocation Map、Shared Global Allocation Map	有关区是否分配的信息。
Page Free Space	有关页分配和页的可用空间的信息。
Index Allocation Map	有关每个分配单元中表或索引所使用的区的信息。
Bulk Changed Map	有关每个分配单元中自最后一条 BACKUP LOG 语句之后的大容量操作所修改的区的信息。
Differential Changed Map	有关每个分配单元中自最后一条 BACKUP DATABASE 语句之后更改的区的信息。

   

表1页中保存的数据类型

  在数据页上，数据行紧接着页头（标头）按顺序放置；页头包含标识值，如页码或对象数据的对象ID；数据行持有实际的数据；最后，页的末尾是行偏移表，对于页中的每一行，每个行偏移表都包含一个条目，每个条目记录对应行的第一个字节与页头的距离，行偏移表中的条目的顺序与页中行的顺序相反。
  

  

图3数据页

  索引的基本结构

  “索引（Index）提供查询的速度”这是对索引的最基本的解释，接下来我们将通过介绍索引的组成，让大家对索引有更深入的理解。
  索引是数据库中的一个独特的结构，由于它保存数据库信息，那么我们就需要给它分配磁盘空间和维护索引表。创建索引并不会改变表中的数据，它只是创建了一个新的数据结构指向数据表；打个比方，平时我们使用字典查字时，首先我们要知道查询单词起始字母，然后翻到目录页，接着查找单词具体在哪一页，这时我们目录就是索引表，而目录项就是索引了。
  当然，索引比字典目录更为复杂，因为数据库必须处理插入，删除和更新等操作，这些操作将导致索引发生变化。
  叶节点
  假设我们磁盘上的数据是物理有序的，那么数据库在进行插入，删除和更新操作时，必然会导致数据发生变化，如果我们要保存数据的连续和有序，那么我们就需要移动数据的物理位置，这将增大磁盘的I/O，使得整个数据库运行非常缓慢；使用索引的主要目的是使数据逻辑有序，使数据独立于物理有序存储。
  为了实现数据逻辑有序，索引使用双向链表的数据结构来保持数据逻辑顺序，如果要在两个节点中插入一个新的节点只需修改节点的前驱和后继，而且无需修改新节点的物理位置。
  双向链表（Doubly linked list）也叫双链表，是链表的一种，它的每个数据结点中都有两个指针，分别指向直接后继和直接前驱。所以，从双向链表中的任意一个结点开始，都可以很方便地访问它的前驱结点和后继结点。
  理论上说，从双向链表中删除一个元素操作的时间复杂度是O(1)，如果希望删除一个具体有给定关键字的元素，那么最坏的情况下的时间复杂度为O(n)。
  在删除的过程中，我们只需要将要删除的节点的前节点和后节点相连，然后将要删除的节点的前节点和后节点置为null即可。

1. //伪代码
2. node.prev.next=node.next;
3. node.next.prev=node.prev;
4. node.prev=node.next=null;

复制代码

图4索引的叶节点和相应的表数据

如上图4所示，索引叶节点包含索引值和相应的RID（ROWID），而且叶节点通过双向链表有序地连接起来；同时我们主要到数据表不同于索引叶节点，表中的数据无序存储，它们不全是存储在同一表块中，而且块之间不存在连接。
总的来说，索引保存着具体数据的物理地址值。
索引的类型

我们知道索引的类型有两种：聚集索引和非聚集索引。
聚集索引：物理存储按照索引排序。
非聚集索引：物理存储不按照索引排序。
聚集索引

聚集索引的数据页是物理有序地存储，数据页是聚集索引的叶节点，数据页之间通过双向链表的形式连接起来，而且实际的数据都存储在数据页中。当我们给表添加索引后，表中的数据将根据索引进行排序。
假设我们有一个表T_Pet，它包含四个字段分别是：animal，name，sex和age，而且使用animal作为索引列，具体SQL代码如下：

1. -----------------------------------------------------------
2. ---- Create T_Pet table in tempdb.
3. -----------------------------------------------------------
4. USE tempdb
6. CREATE TABLE T_Pet
7. (
8.     animal    VARCHAR(20),
9.     [name]    VARCHAR(20),
10.     sex        CHAR(1),
11.     age        INT
12. )
14. CREATE UNIQUE  CLUSTERED INDEX T_PetonAnimal1_ClterIdx ON T_Pet (animal)

复制代码

 
[code]--------------------------------------------------------------- Insert data into data table.-----------------------------------------------------------DECLARE @i intSET @i=0WHILE(@i