SqlServer2008R2的索引

SQL Server的索引，是与表或视图关联的磁盘上结构，可以加快从表或视图中检索行的速度，类似我们常见纸质书籍中的目录。

创建索引最常见的注意点：

  1. 记录日志以及记录交易的数据表，应该创建一个记录ID的栏位，并以它为主索引键。
  2. 小的数据表，就不建索引了。如果需要唯一性就建“检查约束”。
  3. 主档类的数据表，例如物项主档，客户主档等，宁可多建几个索引，而不要在一个索引里有过多的栏位。
  4. 默认情况下，索引存储在基表所在的文件组上，为了提高索引的效率，可以把索引存到单独的文件组上，单独文件组与基本文件组(PRIMARY)不要在同一个物料磁盘上，如此，对数据库进行一次操作，就有多个物理磁盘通力合作，效率得到提升。
  5. 创建索引时，填充因子(Fill Factor)默认是0。如果该表该索引会频繁被修改，就应该根据读写比例设定一个百份比，一般建议80% — 95%。意思是，创建索引时，索引页里的资料只占80%，还留有20%的空间给将来插入记录的索引用，如此，避免频繁增加索引页，也减少碎片的产生。需要留意的是，增加填充因子，会导致数据库占用磁盘的空间增大。
  6. 关于填充因子，SQL2008R2还有一点要特别注意。执行重索引命令时，Fill Facor参数指资料占页面百分比，而用维护计划向导创建重新索引任务时，可用空间选项“将每页的可用空间百分比更改为”这个值，却是与Fill Facor相反的，即想要的 FillFactor = 100 – 百分比。

数据库的索引档，需要定期维护，通常借助SqlServer的【维护计划】来实现。除了重建索引，“检查数据库完整性”“收缩数据库”“重新组织索引/重新生成索引”“备份数据库”是维护计划中的常规四件套。

重新组织索引 与 重新生成索引的区别：前者在线执行，前端可以继续访问数据库，后者一般是离线操作，前端不能访问数据库。后者重索引更彻底，没有碎片。所以，夜间定期维护建议用“重新生成索引”，一周一次就够用了。

与索引相关的常用命令，其中 “jhERP”为数据库名，“enBOM”为数据表名：

//重建enBOM表的全部索引
dbcc dbreindex(enBOM)

//以95%为填充因子重建enBOM表的主索引
dbcc dbreindex(enBOM,'PK_enBOM',95)

//以95%为填充因子重建enBOM表的全部索引
dbcc dbreindex(enBOM,' ',95)

//整理当库表中enBOM表的索引碎片，新版SQL将取消
DBCC INDEXDEFRAG (0,enBOM)

//查询数据库里索引的碎片信息
//ScanDensity的值应该接近100%
//Avg.PageDensity越大越好
DBCC SHOWCONTIG WITH ALL_INDEXES

//查询enBOM表的索引的碎片信息
DBCC SHOWCONTIG (enBOM) WITH ALL_INDEXES

查询库中各表的大小（Fill Factor的效应）：

SELECT t.NAME AS TableName,p.rows AS RowCounts,SUM(a.data_pages) * 8 AS DataSpaceKB,SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB   
FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE t.NAME NOT LIKE 'dt%' AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 255 
GROUP BY t.Name, p.Rows
ORDER BY TableName;

发表评论