mssqlserver 聚集索引 sql server建立聚集索引

1. Sqlserver中索引分为聚集索引和非聚集索引:

聚集索引：表中数据的物理存储的顺序与索引顺序完全相同(字典的按拼音查法)，检索效率比非聚集索引高，但对数据更新影响较大。

非聚集索引：表中数据的存储和索引存储在不同的位置，索引带有指针指向数据的存储位置(字典的按偏旁部首查法)，非聚集索引检索效率比聚集索引低，但对数据更新影响较小。

2. 索引的建立原则：

　　A.每个表中只可以创建一个聚集索引（数据库一次只能按一个规则进行排序）但最多可以创建多个非聚集索引；

　　B.系统一般会给主键字段自动建立聚集索引；

　　C.有大量重复值且经常有范围查询和排序、分组的列，或者经常频繁访问的列，或者返回大量数据记录时考虑建立聚集索引；

　　D.有大量非重复值或者精确匹配的查询的搜索条件(where)中经常使用的列或者返回少量数据记录时考虑建立非聚集索引；

　　E.在一个经常做插入操作的表中建立索引，应使用fillfactor(填充因子)来减少页分裂，同时提高并发度并降低死锁的发生。如果该表为只读表，填充因子可设为100.

　　F.在选择索引键时，尽可能采用小数据类型的列作为键以使每个索引页能容纳尽可能多的索引键和指针，通过这种方式，可使一个查询必需遍历的索引页面降低到最小，此外，尽可能的使用整数做为键值，因为整数的访问速度最快。

　　G.不要索引大型字段和小型表；

3. 索引的创建语句：

USE [CMC]
GO

IF EXISTS (SELECT * FROM SYSINDEXES WHERE NAME='IX_CMC_INDEX_NAME')
BEGIN
    DROP INDEX [dbo].[Products].IX_CMC_INDEX_NAME
END

CREATE NONCLUSTERED INDEX IX_CMC_INDEX_NAME
ON [dbo].[Products](ID)
WITH FILLFACTOR = 100
GO

--用指定的索引查询
SELECT * FROM [dbo].[Products] WITH (INDEX = IX_CMC_INDEX_NAME) WHERE ProductName = 'A'
GO

View Code

4. 深入理解（查询表中数据所在的数据页）：
dbcc ind--查看数据库中数据页的的情况，--数据页默认分为表数据页和IAM数据页（索引数据页）

数据表Products：

dbcc traceon(2588)--为当前连接打开跟踪标记2588
DBCC IND (CMC,PRODUCTS,1)--DBCC IND (DB_Name,TableName,1)

dbcc traceon(2588,3604)--为当前连接打开跟踪标记2588和3604
dbcc page(CMC,1,79,2)--dbcc page(CMC,IAMFID,PagePID,2)

View Code

结果预览：