sqlserver 索引的一些总结(3)
来源:asp之家 时间:2012-08-21 11:03:31
非聚集索引
在没有聚集索引的情况下,表中的数据页是通过堆(Heap)形式进行存储,堆是不含聚集索引的表;SQL Server中的堆存储是把新的数据行存储到最后一个页中。
非聚集索引是物理存储不按照索引排序,非聚集索引的叶节点(Index leaf pages)包含着指向具体数据行的指针或聚集索引,数据页之间没有连接是相对独立的页。
假设我们有一个表T_Pet,它包含四个字段分别是:animal,name,sex和age,而且使用animal作为非索引列,具体SQL代码如下:
代码如下:
-----------------------------------------------------------
---- Create T_Pet table in tempdb with NONCLUSTERED INDEX.
-----------------------------------------------------------
USE tempdb
CREATE TABLE T_Pet
(
animal VARCHAR(20),
[name] VARCHAR(20),
sex CHAR(1),
age INT
)
CREATE UNIQUE NONCLUSTERED INDEX T_PetonAnimal1_NonClterIdx ON T_Pet (animal)
图10非聚集索引
接着我们要查询表中animal = ‘Cat'的宠物信息,具体的SQL代码如下:
代码如下:
SET STATISTICS PROFILE ON
SET STATISTICS TIME ON
SELECT animal, [name], sex, age
FROM T_Pet
WHERE animal = 'Cat'
SET STATISTICS PROFILE OFF
SET STATISTICS TIME OFF
如下图所示,我们发现查询计划的最右边有两个步骤:RID和索引查找。由于这两种查找方式相对于聚集索引查找要慢(Clustered Index Seek)。
图11查询计划
首先SQL Server查找索引值,然后根据RID查找数据行,直到找到符合查询条件的结果。
查询执行时间:CPU 时间= 0 毫秒,占用时间= 1 毫秒
图12查询结果
堆表非聚集索引
由于堆是不含聚集索引的表,所以非聚集索引的叶节点将包含指向具体数据行的指针。
以前面的T_Pet表为例,假设T_Pet使用animal列作为非聚集索引,那么它的堆表非聚集索引结构如下图所示:
图13堆表非聚集索引
通过上图,我们发现非聚集索引通过双向链表连接,而且叶节点包含指向具体数据行的指针。
如果我们要查找animal = ‘Dog'的信息,首先我们遍历第一层索引,然后数据库判断Dog属于Cat范围的索引,接着遍历第二层索引,然后找到Dog索引获取其中的保存的指针信息,根据指针信息获取相应数据页中的数据,接下来我们将通过具体的例子说明。
现在我们创建表employees,然后给该表添加堆表非聚集索引,具体SQL代码如下:
代码如下:
USE tempdb
---- Creates a sample table.
CREATE TABLE employees (
employee_id NUMERIC NOT NULL,
first_name VARCHAR(1000) NOT NULL,
last_name VARCHAR(900) NOT NULL,
date_of_birth DATETIME ,
phone_number VARCHAR(1000) NOT NULL,
junk CHAR(1000) ,
CONSTRAINT employees_pk PRIMARY KEY NONCLUSTERED (employee_id)
);
GO现在我们查找employee_id = 29976的员工信息。
代码如下:
SELECT *
FROM employees
WHERE employee_id = 29976
查询计划如下图所示:
图14查询计划
首先,查找索引值employee_id = ‘29976'的索引,然后根据RID查找符合条件的数据行;所以说,堆表索引的查询效率不如聚集表,接下来我们将介绍聚集表的非聚集索引。