蝈蝈俊.net

-- 用随笔来记录自己的技术感触

随笔 - 655, 评论 - 4839, 引用 - 276

关于

这里是我的技术Blog，下一代CSDN社区Blog在 http://blog.csdn.net/ghj1976/

每月存档

前几天给同事培训了聚集索引，非聚集索引的知识后，在一个同事新作的项目中，竟然出现了滥用聚集索引的问题。看来没有培训最最基础的索引的意义，代价，使用场景，是一个非常大的失误。这篇博客就是从这个角度来罗列索引的基础知识。

使用索引的意义

索引在数据库中的作用类似于目录在书籍中的作用，用来提高查找信息的速度。
使用索引查找数据，无需对整表进行扫描，可以快速找到所需数据。

使用索引的代价

索引需要占用数据表以外的物理存储空间。
创建索引和维护索引要花费一定的时间。
当对表进行更新操作时，索引需要被重建，这样降低了数据的维护速度。

创建索引的列

主键
外键或在表联接操作中经常用到的列
在经常查询的字段上最好建立索引

不创建索引的列

很少在查询中被引用
包含较少的惟一值
定义为 text、ntext 或者 image 数据类型的列

Heaps是staging data的很好选择，当它没有任何Index时

Excellent for high performance data loading (parallel bulk load and parallel index creation after load)
Excellent as a partition to a partitioned view or a partitioned table

聚集索引提高性能的方法，在前面几篇博客中分别提到过，下面只是一个简单的大纲，细节请参看前面几篇博客。

何时创建聚集索引？

Clustered Index会提高大多数table的性能，尤其是当它满足以下条件时：

独特, 狭窄, 静止：最重要的条件
持续增长的，最好是只向上增加。例如：
- Identity
- Date, identity
- GUID (only when using newsequentialid() function)

聚集索引唯一性（独特型的问题）

由于聚集索引的B+树结构的叶子节点必须指向具体数据。如果你要建立聚集索引的列不唯一，并且你指定的创建的聚集索引是非唯一的聚集索引，则会有以下情况：
如果未使用 UNIQUE 属性创建聚集索引，数据库引擎将向表自动添加一个四字节 uniqueifier 列。必要时，数据库引擎将向行自动添加一个 uniqueifier 值，使每个键唯一。此列和列值供内部使用，用户不能查看或访问。

参看我的这篇博客：

SQL Server 索引基础知识(4)----主键与聚集索引
http://blog.joycode.com/ghj/archive/2008/01/04/113373.aspx

聚集索引持续向上增长的需求

具体来说下面两个问题要求建立聚集索引的列最好是持续向上增长的

1、缓存的命中率问题。（需要从B+树的结构分析）
2、连续和不连续的磁盘 I/O 操作对性能的影响。

细节参看我的这篇博客：

SQL Server 索引基础知识(5)----理解newid()和newsequentialid()
http://blog.joycode.com/ghj/archive/2008/01/08/113521.aspx

至于，如果你的数据已经存在重复了，而且是不应该出现的，则可以参看下面这篇KB ：

如何删除 SQL Server 表中的重复行
http://support.microsoft.com/kb/139444/zh-cn

非聚集索引提高性能的方法

非聚集索引由于B+树的节点不是具体数据页，有时候由于这个原因，会导致非聚集索引甚至不如表遍历来的快，参看我在下面这篇博客中给的例子
http://blog.joycode.com/ghj/archive/2008/01/02/113291.aspx

但是，非聚集索引有个特性，如果你要查询的内容，在非聚集索引中以及被覆盖到了，则不需要继续到聚集索引，或者RID中去寻找数据了，这时候就可以很大的提高性能，这就是覆盖面(Covering) 的问题。

由于聚集索引叶子节点就是具体数据，所以聚集索引的覆盖率是 100%，

通过提高覆盖面来提高性能的问题也就只有非聚集索引（Nonclustered Indexes）才存在。

当查询中所有的columns 都包括在index上时，我们说这个 index covers the query. Columns的顺序在此不重要

(Select 时候的顺序不重要,但是Index 建立的顺序可得小心了)。

在 SQL Server 2005 中，为了提高这种 Covering 带来的好处，甚至可以通过将非键列添加到非聚集索引的叶级别来扩展非聚集索引的功能。

比如下面的脚本, 虽然我们是对 Title, Revision 建立的非聚集索引，但是这个非聚集索引的叶子节点上还包含 FileName 字段的信息。

USE AdventureWorks;
GO
CREATE INDEX IX_Document_Title
ON Production.Document (Title, Revision)
INCLUDE (FileName);

下面的代码就是测试 Covering 的.

我已经在每个查询使用的方式，逻辑读的个数都标在每个查询前面了。

SET STATISTICS IO ON
-- Turn Graphical Showplan ON (Ctrl+K)

USE CREDIT
go
-- 逻辑读取144 次    Clustered Index Scan
SELECT m.LastName, m.FirstName, m.Phone_No
FROM dbo.Member AS m WITH (INDEX (0))
WHERE m.LastName LIKE '[S-Z]%'
go

--CREATE INDEX MemberLastName ON Member(LastName)
go
-- 逻辑读取6354 次 BookMark Lookup

SELECT m.LastName, m.FirstName, m.Phone_No
FROM dbo.Member AS m WITH (INDEX (MemberLastName))
WHERE m.LastName LIKE '[S-Z]%'
go

--CREATE INDEX NCLastNameCombo ON Member(LastName, FirstName, Phone_No)
go
-- 逻辑读取21 次   Index Seek
SELECT m.LastName, m.FirstName, m.Phone_No
FROM dbo.Member AS m
WHERE m.LastName LIKE '[S-Z]%'
go

--CREATE INDEX NCLastNameCombo2 ON Member(FirstName, LastName, Phone_No)
go
-- 逻辑读取59 次     Index Scan
SELECT m.LastName, m.FirstName, m.Phone_No
FROM dbo.Member AS m WITH (INDEX (NCLastNameCombo2))
WHERE m.LastName LIKE '[S-Z]%'
go

-- If you want to clean up the indexes:
--DROP INDEX Member.MemberLastName
--DROP INDEX Member.NCLastNameCombo
--DROP INDEX Member.NCLastNameCombo2

参考资料

Teched 2007 上吴家震主讲的"微软SQL服务器Always-On Tech-nologies: 高级索引策略" 录像下载地址:
http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032364059&Culture=zh-CN
注意, 这个页面标示的是 "SharePoint 2007 网站性能调优" ,但是其实是高级索引策略,微软弄错文件了,害得我一个个下下来看,哪个是需要的录像.

打印 | 张贴于 2008-01-16 14:33:00 | Tag:数据库开发管理心得