SQL Server索引进阶:第十五级,索引的最佳实践

时间:2022-02-14 02:47:40

在本文中我们将推荐14条贯穿本系列的规则,这些规则帮助你为数据库创建最好的索引结构。

格式来自于《Framework Design Guidelines》。每条推荐用四个词来总结:Do做,Consider考虑,Void避免,Do Not不要做。

  • 做。总是要遵守的规则。
  • 考虑。通常来说应该遵守,但是如果你完全理解了规则背后的原因,并且有你不遵守的原因。
  • 避免。和考虑相反,通常建议不这么做,但是如果你完全理解了为什么不应该这么做,你有这么做的原因,你可以这么做。
  • 不要做。比避免语气要强,表面有些事永远不要做。

指导原则

Do know your Application/Users

索引的主要目的是提高查询和操作数据的性能,除非你知道这些操作是什么,否则你没有希望改进他们。

最好是在应用的开始就考虑,在设计和开发中加入。如果你继承了一个已经存在的数据库和应用,从两方面理解你继承的是什么:内部和外部。

外部包括从用户角度,和他们聊天,观察他们使用应用,阅读用户文档和手册,查看当前的表格和报表。

内部包括检查应用本身,应用的定义和应用的执行。使用工具,Activity Monitor,Profiler,sys.dm_db_index usage_stats动态视图,以及sys.dm_db_missing_index_XXX系列的动态视图观察常用查询,慢查询,常用索引,未使用的索引,应该存在但却没有建立的索引。

检查常用查询和慢查询的源头,例如,报表服务的模板。TSQL作业的步骤,SSIS应用中的TSQL任务,存储过程,都应该被优化。

在了解了这些信息之后,对于那些索引是好的,那些索引是不好的,你就可以做出更好的决定。

Do Not Over Index

索引太多和太少都是不好的。对表来说没有“最佳索引个数”这种说法。每张表的情况都不同。但是如果你要在主键,候选键,合适的外键,潜在的查询列上建立索引,请在建立之前做一些分析。

Do Understand that:Same Database + Different Situation = Different Indexing

不管是白天处理,还是非高峰期处理;不管是联机处理,还是对数据库拷贝的报表处理;不同的情况,建立的索引是不同的。

Do Have a Primary Key on Every Table

尽管主键不是SQL Server必须的,但是没有主键的表在事务的时候是非常危险的,因为不能保证行是唯一的。如果允许重复行,就会发生,你不知道是同一个实体重复插入了两次,还是没有足够的信息来区分这两个实体。

尽管SQL Server没有要求,主键是关系理论的基础,所有关系系统的基本构成。没有主键的约束,或者是唯一索引,可能会导致意外的结果,或者不好的性能。

另外,很多客户端开发工具和组件都需要你的表有主键。主键约束的名称就是索引的名称。

Consider Having a Clustered Index on Every Table

第三级,聚集索引介绍了聚集索引的好处。让表成为聚集索引表而不是堆表。主要的好处是一个简单的事实,用户在查看表数据的时候肯定会以一个默认的顺序,所以就以哪个顺序来维护表。

如果你按照本文的规则,每张表都有主键,每张表至少有一个索引,甚至更多。因此,一个聚集索引不会增加索引的数量,但是相比堆表,会给你的表带来一个很好的结构。

在决定聚集索引列的时候,要记住第六级,标签中的指导原则:一个聚集索引应该唯一,短小,不变的。

Consider Using a Foreign Key in the Search Key of the Clustered Index

考虑将外键作为聚集索引键中最左面的列,这样可以将子item的信息聚集在父的周围,这是一个典型的需求。你的信用卡的消费信息和信用卡关联,我的消费信息和我的卡关联。这个关系要比消费记录和商家,或者消费记录和处理消费记录的金融机构,要比这些关系强。卡号是包含在消费表的聚集索引键中的外键,而不是商家编号和银行编号。将卡号放在聚集索引的最左边,同一个持卡人的消费记录就会聚集在相同的页中。

Consider Having Included Columns in your Indexes

考虑在你的非聚集索引中添加包含列。

因为一般的非聚集索引都是从某种角度查看表,或者是建立的外键的基础上,但是除了非聚集索引的键列,还会需要一些其他列,但是这些列不作为查询条件,只是需要显示或者统计它们,这时候,这些列就可以添加为包含列,就不用再去访问数据行了,直接在非聚集索引中就可以完成请求。

Avoid Nonclustered, Unfilterd Indexes on Columns that have few Distinct Values

有句老话:“不要在性别列上建立索引”。表中的一页将会有一半的值为男,一半的值为女,不管是请求男还是女,扫描表都是最好的决定。因此,这样的一个索引永远不会被查询优化器使用。

Consider Create a Filtered Index for Columns that Have a Dominate Value

如果表中有一列,大部分行的值都相同,或者都是NULL,那么就在这列上创建一个过滤索引。那些查询小部分值的时候,就会使用索引;查询大部分值的时候就扫描表。

Consider Specifying Fill Factor Values that Anticipate Future Size Requirements

Consider Specifying Fill Factor Values that Reflect the Table's Steady-state Page Fragmentation Value

Do Create a Table's Clustered Index Before Creating its Nonclustered Indexes

这条规则的一个推论就是:删除聚集索引之前,先删除非聚集索引。否则会导致非聚集索引出现不必要的重建。表从堆表,转变成聚集索引表,总是会导致非聚集索引的重建,因为非聚集索引的书签的内容会从行号变成聚集索引的键。

Do Plan Your Index Defragmenting and Rebuilding Based Upon Usage

如果一个索引经常被扫描,索引的外部碎片是很重要的,对于全扫描或者扫描部分叶子层会产生重要的影响。如果是这种情况,在外部碎片达到10%的时候,考虑重新组织索引,当达到30%的时候,考虑重建索引。

但是,如果,索引只是通过一个键来查询,外部碎片对性能的影响很小,甚至没有影响。从根页到叶子层的一页所需要的IO,将会忽略外部碎片,将会是相同的。这时候,重新组织和重建索引对于性能没有提升。

Do Update Index Statistics On a Regular Basis

关键字是“规律的”,因为只有知道你的应用在做什么,你才能决定什么时候统计信息需要更新。在第十四级中有这部分的介绍。

结论

这些指导原则来自于很多在SQL Server上工作过多年的开发人员,根据这些指导原则,你可以在你的数据库上创建最好的索引。