索引的重要性

索引的重要性

数据库性能优化中索引绝对是一个重量级的因素，可以说，索引使用不当，其它优化措施将毫无意义。

聚簇索引(Clustered Index)和非聚簇索引(Non- Clustered Index)

最通俗的解释是:聚簇索引的顺序就是数据的物理存储顺序，而对非聚簇索引的索引顺序与数据物理排列顺序无关。举例来说，你翻到新华字典的汉字“爬”那一页就是P开头的部分，这就是物理存储顺序（聚簇索引）；而不用你到目录，找到汉字“爬”所在的页码，然后根据页码找到这个字（非聚簇索引）。

下表给出了何时使用聚簇索引与非聚簇索引:

聚簇索引的唯一性

正式聚簇索引的顺序就是数据的物理存储顺序，所以一个表最多只能有一个聚簇索引，因为物理存储只能有一个顺序。正因为一个表最多只能有一个聚簇索引，所以它显得更为珍贵，一个表设置什么为聚簇索引对性能很关键。

初学者最大的误区：把主键自动设为聚簇索引

因为这是SQLServer的默认主键行为，你设置了主键，它就把主键设为聚簇索引，而一个表最多只能有一个聚簇索引，所以很多人就把其他索引设置为非聚簇索引。这个是最大的误区。甚至有的主键又是无意义的自动增量字段，那样的话Clustered index对效率的帮助，完全被浪费了。

刚才说到了，聚簇索引性能最好而且具有唯一性，所以非常珍贵，必须慎重设置。一般要根据这个表最常用的SQL查询方式来进行选择，某个字段作为聚簇索引，或组合聚簇索引，这个要看实际情况。

事实上，建表的时候，先需要设置主键，然后添加我们想要的聚簇索引，最后设置主键，SQLServer就会自动把主键设置为非聚簇索引（会自动根据情况选择）。如果你已经设置了主键为聚簇索引，必须先删除主键，然后添加我们想要的聚簇索引，最后恢复设置主键即可。

记住我们的最终目的就是在相同结果集情况下，尽可能减少逻辑IO。

我们先从一个实际使用的简单例子开始。

一个简单的表：

∙CREATE TABLE [dbo].[Table1](

∙ [ID] [int] IDENTITY(1,1) NOT NULL,

∙ [Data1] [int] NOT NULL DEFAULT ((0)),

∙ [Data2] [int] NOT NULL DEFAULT ((0)),

∙ [Data3] [int] NOT NULL DEFAULT ((0)),

∙ [Name1] [nvarchar](50) NOT NULL DEFAULT (''),

∙ [Name2] [nvarchar](50) NOT NULL DEFAULT (''),

∙ [Name3] [nvarchar](50) DEFAULT (''),

∙ [DTAt] [datetime] NOT NULL DEFAULT (getdate()))

复制代码

来点测试数据（10w条）：

∙

∙declare @i int

∙set @i = 1

∙while @i < 100000

∙begin

∙insert into Table1 ([Data1] ,[Data2] ,[Data3] ,[Name1],[Name2] ,[Name3])

∙values(@i, 2* @i,3*@i, CAST(@i AS NVARCHAR(50)), CAST(2*@i AS NVARCHAR(50)), CAST(3*@i AS NVARCHAR(50)))

∙set @i = @i + 1

∙end

∙update table1 set dtat= DateAdd (s, data1, dtat)

复制代码

打开查询分析器的IO统计和时间统计：

∙SET STATISTICS IO ON;

∙SET STATISTICS TIME ON;

复制代码

显示实际的“执行计划”：

我们最常用的SQL查询是这样的：

∙SELECT * FROM Table1 WHERE Data1 = 2 ORDER BY DTAt DESC;

复制代码

先在Table1设主键ID，系统自动为该主键建立了聚簇索引。

然后执行该语句，结果是：

∙Table 'Table1'. Scan count 1, logical reads 911, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

∙SQL Server Execution Times:

∙CPU time = 16 ms, elapsed time = 7 ms.

复制代码

然后我们在Data1和DTat字段分别建立非聚簇索引：

∙CREATE NONCLUSTERED INDEX [N_Data1] ON [dbo].[Table1]

∙(

∙[Data1] ASC

∙)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

∙CREATE NONCLUSTERED INDEX [N_DTat] ON [dbo].[Table1]

∙(

∙[DTAt] ASC

∙)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

复制代码

再次执行该语句，结果是：

∙Table 'Table1'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

∙SQL Server Execution Times:

∙CPU time = 0 ms, elapsed time = 39 ms.

复制代码