sqlserver索引的一些总结

如果说要对数据库进行优化，我们主要可以通过以下五种方法，对数据库系统进行优化

1.1.1 摘要
如果说要对数据库进行优化，我们主要可以通过以下五种方法，对数据库系统进行优化。

1. 计算机硬件调优
2. 应用程序调优
3. 数据库索引优化
4. SQL语句优化
5. 事务处理调优

在本篇博文中，我们将想大家讲述数据库中索引类型和使用场合，本文以SQL Server为例，对于其他技术平台的朋友也是有参考价值的，只要替换相对应的代码就行了！

索引使数据库引擎执行速度更快，有针对性的数据检索，而不是简单地整表扫描（Full table scan）。

为了使用有效的索引，我们必须对索引的构成有所了解，而且我们知道在数据表中添加索引必然需要创建和维护索引表，所以我们要全局地衡量添加索引是否能提高数据库系统的查询性能。

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

页中存储的类型有：数据，索引和溢出。

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

1.1.2 正文

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

页中存储的类型有：数据，索引和溢出。

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

图1数据库文件组织

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

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

图2文件组成

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

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

图3数据页

索引的基本结构
“索引（Index）提供查询的速度”这是对索引的最基本的解释，接下来我们将通过介绍索引的组成，让大家对索引有更深入的理解。

索引是数据库中的一个独特的结构，由于它保存数据库信息，那么我们就需要给它分配磁盘空间和维护索引表。创建索引并不会改变表中的数据，它只是创建了一个新的数据结构指向数据表；打个比方，平时我们使用字典查字时，首先我们要知道查询单词起始字母，然后翻到目录页，接着查找单词具体在哪一页，这时我们目录就是索引表，而目录项就是索引了。

当然，索引比字典目录更为复杂，因为数据库必须处理插入，删除和更新等操作，这些操作将导致索引发生变化。

叶节点

假设我们磁盘上的数据是物理有序的，那么数据库在进行插入，删除和更新操作时，必然会导致数据发生变化，如果我们要保存数据的连续和有序，那么我们就需要移动数据的物理位置，这将增大磁盘的I/O，使得整个数据库运行非常缓慢；使用索引的主要目的是使数据逻辑有序，使数据独立于物理有序存储。

为了实现数据逻辑有序，索引使用双向链表的数据结构来保持数据逻辑顺序，如果要在两个节点中插入一个新的节点只需修改节点的前驱和后继，而且无需修改新节点的物理位置。

双向链表（Doubly linked list）也叫双链表，是链表的一种，它的每个数据结点中都有两个指针，分别指向直接后继和直接前驱。所以，从双向链表中的任意一个结点开始，都可以很方便地访问它的前驱结点和后继结点。

理论上说，从双向链表中删除一个元素操作的时间复杂度是O(1)，如果希望删除一个具体有给定关键字的元素，那么最坏的情况下的时间复杂度为O(n)。

在删除的过程中，我们只需要将要删除的节点的前节点和后节点相连，然后将要删除的节点的前节点和后节点置为null即可。

代码如下:

//伪代码 
node.prev.next=node.next; 
node.next.prev=node.prev; 
node.prev=node.next=null; 



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

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

总的来说，索引保存着具体数据的物理地址值。 

索引的类型 

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

聚集索引 

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

----------------------------------------------------------- 
---- Create T_Pet table in tempdb. 
----------------------------------------------------------- 
USE tempdb 
CREATE TABLE T_Pet 
( 
animal VARCHAR(20), 
[name] VARCHAR(20), 
sex CHAR(1), 
age INT 
) 
CREATE UNIQUE CLUSTERED INDEX T_PetonAnimal1_ClterIdx ON T_Pet (animal) 
 
----------------------------------------------------------- 
---- Insert data into data table. 
----------------------------------------------------------- 
 代码如下:

DECLARE @i int 
SET @i=0 
WHILE(@i<1000000) 
BEGIN 
INSERT INTO T_Pet ( 
animal, 
[name], 
sex, 
age 
) 
SELECT [dbo].random_string(11) animal, 
[dbo].random_string(11) [name], 
'F' sex, 
cast(floor(rand()*5) as int) age 
SET @i=@i+1 
END 
INSERT INTO T_Pet VALUES('Aardark', 'Hello', 'F', 1) 
INSERT INTO T_Pet VALUES('Cat', 'Kitty', 'F', 2) 
INSERT INTO T_Pet VALUES('Horse', 'Ma', 'F', 1) 
INSERT INTO T_Pet VALUES('Turtles', 'SiSi', 'F', 4) 
INSERT INTO T_Pet VALUES('Dog', 'Tomma', 'F', 2) 
INSERT INTO T_Pet VALUES('Donkey', 'YoYo', 'F', 3) 


图5聚集索引 

如上图5所示，从左往右的第一和第二层是索引页，第三层是数据页（叶节点），数据页之间通过双向链表连接起来，而且数据页中的数据根据索引排序；假设，我们要查找名字（name）为Xnnbqba的动物Ifcey，这里我们以animal作为表的索引，所以数据库首先根据索引查找，当找到索引值animal = ‘Ifcey时，接着查找该索引的数据页（叶节点）获取具体数据。具体的查询语句如下： 
 代码如下:

SET STATISTICS PROFILE ON 
SET STATISTICS TIME ON 

SELECT animal, [name], sex, age 
FROM T_Pet 
WHERE animal = 'Ifcey' 

SET STATISTICS PROFILE OFF 
SET STATISTICS TIME OFF 
 
当我们执行完SQL查询计划时，把鼠标指针放到“聚集索引查找”上，这时会出现如下图信息，我们可以查看到一个重要的信息Logical Operation——Clustered Index Seek，SQL查询是直接根据聚集索引获取记录，查询速度最快。 


图6查询计划 

从下图查询结果，我们发现查询步骤只有2步，首先通过Clustered Index Seek快速地找到索引Ifcey，接着查询索引的叶节点（数据页）获取数据。 

查询执行时间：CPU 时间= 0 毫秒，占用时间= 1 毫秒。

图7查询结果 

现在我们把表中的索引删除，重新执行查询计划，这时我们可以发现Logical Operation已经变为Table Scan，由于表中有100万行数据，这时查询速度就相当缓慢。 


图8查询计划 

从下图查询结果，我们发现查询步骤变成3步了，首先通过Table Scan查找animal = ‘Ifcey'，在执行查询的时候，SQL Server会自动分析SQL语句，而且它估计我们这次查询比较耗时，所以数据库进行并发操作加快查询的速度。 

查询执行时间：CPU 时间= 329 毫秒，占用时间= 182 毫秒。 


图9查询结果 

通过上面的有聚集索引和没有的对比，我们发现了查询性能的差异，如果使用索引数据库首先查找索引，而不是漫无目的的全表遍历。 

非聚集索引 

在没有聚集索引的情况下，表中的数据页是通过堆(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查找符合条件的数据行；所以说，堆表索引的查询效率不如聚集表，接下来我们将介绍聚集表的非聚集索引。 

聚集表非聚集索引
 
当表上存在聚集索引时，任何非聚集索引的叶节点不再是包含指针值，而是包含聚集索引的索引值。 

以前面的T_Pet表为例，假设T_Pet使用animal列作为非聚集索引，那么它的索引表非聚集索引结构如下图所示： 

图15索引表非聚集索引 

通过上图，我们发现非聚集索引通过双向链表连接，而且叶节点包含索引表的索引值。 

如果我们要查找animal = ‘Dog'的信息，首先我们遍历第一层索引，然后数据库判断Dog属于Cat范围的索引，接着遍历第二层索引，然后找到Dog索引获取其中的保存的索引值，然后根据索引值获取相应数据页中的数据。 

接下来我们修改之前的employees表，首先我们删除之前的堆表非聚集索引，然后增加索引表的非聚集索引，具体SQL代码如下： 
 代码如下:

ALTER TABLE employees 
DROP CONSTRAINT employees_pk 

ALTER TABLE employees 
ADD CONSTRAINT employees_pk PRIMARY KEY CLUSTERED (employee_id) 
GO 

SELECT * FROM employees 
WHERE employee_id=29976 


图16查询计划 

索引的有效性 
SQL Server每执行一个查询，首先要检查该查询是否存在执行计划，如果没有，则要生成一个执行计划，那么什么是执行计划呢？简单来说，它能帮助SQL Server制定一个最优的查询计划。（关于查询计划请参考这里） 

下面我们将通过具体的例子说明SQL Server中索引的使用，首先我们定义一个表testIndex，它包含三个字段testIndex，bitValue和filler，具体的SQL代码如下： 
 代码如下:

----------------------------------------------------------- 
---- Index Usefulness sample 
----------------------------------------------------------- 

CREATE TABLE testIndex 
( 
testIndex int identity(1,1) constraint PKtestIndex primary key, 
bitValue bit, 
filler char(2000) not null default (replicate('A',2000)) 
) 

CREATE INDEX XtestIndex_bitValue on testIndex(bitValue) 
GO 

INSERT INTO testIndex(bitValue) 
VALUES (0) 
GO 20000 --runs current batch 20000 times. 

INSERT INTO testIndex(bitValue) 
VALUES (1) 
GO 10 --puts 10 rows into table with value 1 
 
接着我们查询表中bitValue = 0的数据行，而且表中bitValue = 0的数据有2000行。 
 代码如下:

SELECT * 
FROM testIndex 
WHERE bitValue = 0 

图17查询计划 

现在我们查询bitValue = 1的数据行。 

SELECT *FROM testIndexWHERE bitValue = 1 

图18查询计划 

现在我们注意到对同一个表不同数据查询，居然执行截然不同的查询计划，这究竟是什么原因导致的呢？ 

我们可以通过使用DBCC SHOW_STATISTICS查看到表中索引的详细使用情况，具体SQL代码如下： 
 代码如下:

UPDATE STATISTICS dbo.testIndex 
DBCC SHOW_STATISTICS('dbo.testIndex', 'XtestIndex_bitValue') 
WITH HISTOGRAM 


图19直方图 

通过上面的直方图，我们知道SQL Server估计bitValue = 0数据行行有约19989行，而bitValue = 1估计约21；SQL Server优化器根据数据量估算值，采取不同的执行计划，从而到达最优的查询性能，由于bitValue = 0数据量大，SQL Server只能提供扫描聚集索引获取相应数据行，而bitValue = 1实际数据行只有10行，SQL Server首先通过键查找bitValue = 1的数据行，然后嵌套循环联接到聚集索引获得余下数据行。 

总结 完整实例代码：
 代码如下:

-- ============================================= 
-- Author: JKhuang 
-- Create date: 04/20/2012 
-- Description: Create sample for Clustered and 
-- Nonclustered index. 
-- ============================================= 

----------------------------------------------------------- 
---- 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) 
CREATE UNIQUE CLUSTERED INDEX T_PetonAnimal1_ClterIdx ON T_Pet (animal) 
----------------------------------------------------------- 
---- Insert data into data table. 
----------------------------------------------------------- 
DECLARE @i int 
SET @i=0 
WHILE(@i<1000000) 
BEGIN 
INSERT INTO T_Pet ( 
animal, 
[name], 
sex, 
age 
) 
SELECT [dbo].random_string(11) animal, 
[dbo].random_string(11) [name], 
'F' sex, 
cast(floor(rand()*5) as int) age 
SET @i=@i+1 
END 
INSERT INTO T_Pet VALUES('Aardark', 'Hello', 'F', 1) 
INSERT INTO T_Pet VALUES('Cat', 'Kitty', 'F', 2) 
INSERT INTO T_Pet VALUES('Horse', 'Ma', 'F', 1) 
INSERT INTO T_Pet VALUES('Turtles', 'SiSi', 'F', 4) 
INSERT INTO T_Pet VALUES('Dog', 'Tomma', 'F', 2) 
INSERT INTO T_Pet VALUES('Donkey', 'YoYo', 'F', 3) 

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 

----------------------------------------------------------- 
---- Create employees table in tempdb. 
----------------------------------------------------------- 
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) , 
--PK constraint defaults to clustered 
CONSTRAINT employees_pk PRIMARY KEY (employee_id) 
); 
GO 

----------------------------------------------------------- 
---- Insert data into data table. 
----------------------------------------------------------- 
CREATE VIEW rand_helper AS SELECT RND=RAND(); 
GO 
---- Generates random string function. 
CREATE FUNCTION random_string (@maxlen int) RETURNS VARCHAR(255) 
AS BEGIN 
DECLARE @rv VARCHAR(255) 
DECLARE @loop int 
DECLARE @len int 
SET @len = (SELECT CAST(rnd * (@maxlen-3) AS INT) +3 
FROM rand_helper) 
SET @rv = '' 
SET @loop = 0 
WHILE @loop < @len BEGIN 
SET @rv = @rv 
+ CHAR(CAST((SELECT rnd 
FROM rand_helper) * 26 AS INT )+97) 
IF @loop = 0 BEGIN 
SET @rv = UPPER(@rv) 
END 
SET @loop = @loop +1; 
END 
RETURN @rv 
END 
GO 
---- Generates random date function. 
CREATE FUNCTION random_date (@mindaysago int, @maxdaysago int) 
RETURNS VARCHAR(255) 
AS BEGIN 
DECLARE @rv datetime 
SET @rv = (SELECT GetDate() 
- rnd * (@maxdaysago-@mindaysago) 
- @mindaysago 
FROM rand_helper) 
RETURN @rv 
END 
GO 
---- Generates random int function. 
CREATE FUNCTION random_int (@min int, @max int) RETURNS INT 
AS BEGIN 
DECLARE @rv INT 
SET @rv = (SELECT rnd * (@max) + @min 
FROM rand_helper) 
RETURN @rv 
END 
GO 
---- Inserts data into employees table. 
WITH generator (n) as 
( 
select 1 
union all 
select n + 1 from generator 
where N < 30000 
) 
INSERT INTO employees (employee_id 
, first_name, last_name 
, date_of_birth, phone_number, junk) 
select n employee_id 
, [dbo].random_string(11) first_name 
, [dbo].random_string(11) last_name 
, [dbo].random_date(20*365, 60*365) dob 
, 'N/A' phone 
, 'junk' junk 
from generator 
OPTION (MAXRECURSION 30000) 
----------------------------------------------------------- 
---- Index Usefulness sample 
----------------------------------------------------------- 
CREATE TABLE testIndex 
( 
testIndex int identity(1,1) constraint PKtestIndex primary key, 
bitValue bit, 
filler char(2000) not null default (replicate('A',2000)) 
) 
CREATE INDEX XtestIndex_bitValue on testIndex(bitValue) 
GO 
INSERT INTO testIndex(bitValue) 
VALUES (0) 
GO 20000 --runs current batch 20000 times. 
INSERT INTO testIndex(bitValue) 
VALUES (1) 
GO 10 --puts 10 rows into table with value 1 
SELECT filler 
FROM testIndex 
WHERE bitValue = 1 
UPDATE STATISTICS dbo.testIndex 
DBCC SHOW_STATISTICS('dbo.testIndex', 'XtestIndex_bitValue') 
WITH HISTOGRAM