【译】索引进阶（十七）： SQL SERVER索引最佳实践

【译注：此文为翻译，由于本人水平所限，疏漏在所难免，欢迎探讨指正】

原文链接：传送门。

 

在本章我们给出一些建议：贯穿本系列我们提取出了十四条基本指南，这些基本的指南将会帮助你为你的数据库创建最佳的索引架构。

这些指南的格式借鉴了 “框架设计指导”，Krzysztof Cwalina 和Brad Abramszai为.NET 程序开发的标准化方面做了优秀的工作，且他们的文章已由Addison Wesley.出版发行。每一条建议都由如下词语定义：“DO”, 'CONSIDER', "AVOID", "DO NOT", 它们表示了如下的意义：

•   DO: 这条建议应该总是被遵守。
•   CONSIDER: 一般说来这条建议需要被遵守，但是如果你完全理解了指南背后的原因，并深入了解你不遵守这条指南的理由，那么便可以适时的从这条指南上转移你的注意力。
•    AVOID: 与 CONSIDER相对的，一般来说这条指南建议了一些不应该被做的事情，但是，如果你完全理解了为什么它不应该被做，并且你也理解无论如何也要做它的原因， 那么， 就做它。
•  DO NOT 是 AVOID 的强化版本，它预示着绝对不要做的一些事。DO NOT 指南也应该总是被遵守。

 指南

   了解你的程序/用户

           一个索引最主要目的便是提高你的应用程序的数据收集和收据管理操作的性能，除非你知道这些操作是什么，否则你不可能会改进它们。

          在一开始就介入程序，参与到程序的设计和开发是很理想的。但是真实的情况并不总是那样。如果你正在继承一个已经实现了的数据库和应用程序，那么采取两个措施来保证你理解了你继承的东西，外部措施和内部措施。

         外部措施包括从你的用你那边学习，与他们交谈，观察你的用户使用应用程序，阅读任何面向用户的文档和指导，以及查阅现有的表格和报告。

         内部措施涉及了检查应用程序本身，既包含了程序的定义也包含了程序的执行。诸如此类的工具： Activity Monitor, Profiler, sys.dm_db_index usage_stats， sys.dm_db_missing_index_XXX 系列DMV均 提供了关于最常用的查询，运行时间长的查询，最常用的索引，无用的索引，本应存在但却不存在的索引信息。

        检查最常用的以及性能低下的查询的最初的位置，例如，报告服务模板，T-SQL工作步骤，SSIS中的T-SQL任务，以及存储过程，这样以便获取到为什么这些语句对于应用程序来说如此重要的原因。因而便可以被更好的优化。

       拥有了这些信息，你便能更好的做出决策，哪些索引是有益的，哪些却并无益处。

  不要过度索引

      太多的索引和太少的索引一样是个问题。对于一个表来说， 不存在神奇的“最佳索引数”，每张表都是不同的，然而，一旦你对主键进行了索引，任何候选键，可能的外键，以及任何潜在的索引在你添加到数据库之前都需要认真的分析。

 理解： 一样的数据库＋不同的情形 = 不同的索引

      不管是否是一个日常处理还是一个临时处理，不管是运行在OLTP数据库上的事务性处理，还是运行在同一 数据库的拷贝上的报告性处理，不同的情况需要不同的索引。

      一个每晚接受大量新数据注入的数据库在数据注入时应该比正常时间具有更少的索引数。一个具有有限查询需求的易变数据库比一个每晚更新一次的报告数据库需要更少的索引数，后者会处理复杂的报告生成逻辑。

 每张表都应该有一个主键

      虽然主键不是SQL SERVER所必须的，没有主键的表在事务性以及报表数据库中水一件危险的事情，因为它的数据行不能保证是唯一的。如果允许有重复数据，那么它便会发生。并且你不会知道一个对象的相同实例被插入了两次，也不会知道是否具有不同的多个实例他们具有足够的信息来彼此区分。

     虽然SQL SERVER没有强制要求主键，但它却是关系理论的基石，是所有关系型系统的基础建筑块，如果没有主键约束，以及其相关的唯一性索引，关系型操作会导致不可预期的结果以及较低的性能。

    除此之外，许多客户端的开发工具和组件需要你的表具有主键，举例来说， ADO.Net SqlCommandBuilder 组件和Visual Studio’s Entity Data Modeler都依赖于目标数据库表具有主键约束，请记住，主键约束的名称会成为自动创建的用来实现这个约束的索引的名称。

考虑在每张表都有一个聚集索引

        第三章节，聚集索引涵盖了在一个表上具有聚集索引的益处，那就是，使得一张表成为一个聚集索引而不是一个堆，主要的益处是这样的一个简单事实：用户社区作为一个整体趋势以一个默认的序列去查看表的数据，增强了以那种顺序来维护数据的优势。

      如果你遵循这一章节列出的建议，每一张表便会有一个主键，因此，每张表都会包含至少一个索引，很有可能会是多个。因此， 使其中的一个变为聚集索引并不会增加索引的数目，但却会给你的表一个比堆更好的表结构。

     当决策于聚集索引键时，记住在第六章节-书签-给出的指南：聚集索引键应该是唯一的，短的，并且是不易变的。

 考虑在聚集索引的查询键中使用外键

     使用外键作为聚集索引键的最左列，会将相同父节点的子信息聚集起来，这是一个典型的逻辑处理需求。你的信用卡交易信息关联到你的信用卡，我的信用卡交易信息关联到我的卡信息，这种关系比起把一笔交易与出售商品的商家关联起来，或者把这笔交易与处理它的金融机构关联起来更强壮，信用卡号码属于信用卡表聚集索引键，不是商家编号或者银行编号。通过使信用卡号成为聚集索引键的最左列，一个信用卡持有者的所有交易便会聚集到一起，存储在相同的一个或多个数据页中。

    给聚集索引键增加一个额外的不易变的列，以确保聚集索引键的唯一性。

考虑在索引中有一个包含列

  无

避免在只有几个不同值的列上具有非聚集，非过滤索引

      一个老的法则是：不要在性别列上进行索引，一个典型的页会包含一半的男性数据行和一半的女性数据行，并且不管是请求男性或者女性其丢会被访问，对于任何 WHERE GENDER = XXX查询来说，表扫描总是一个最佳的决策，因而这样的一个索引对于查询优化器来说没有任何益处。

 考虑为具有支配值的列创建过滤索引

        对于一个特定的列来说，如果大量的行都具有相同的值，或者都为null，那么就在这个列上创建一个过滤索引，那些查询稀有值的查询便会使用这个小的，高效的索引，查询通用值的数据行的查询将会进行表扫描。　并且SQL SERVER很容易觉得哪个用哪个。

 考虑指定填充因子值以预料未来的数据大小需求

       如果一个相关的新的聚集索引表包含一个月的数据行并且被允许增长至包含一年的数据行，那么用一个填充因子值7~8%来重建这个索引，这会导致这个表现在消耗了与一年后相比相同的页数，对于空间需求和处理性能的潜在问题将在不久之后很快便体现出来--比如一个表扫描所需要的IO数。

考虑指定反映了表稳定状态碎片大小的填充因子值

      如果一个表已经达到了计划的最大大小，那么上一个指南不会被应用。在这种情况下，产生于表正常活动的填充因子应该在开始时候被指定，如同在第十一章-索引碎片中提到的那样，对于一个典型的事务性表来说，其具有持续性的插入但只有阶段性的删除，这个值是75，对于一个具有相同数量的插入和删除活动的表来说，90~95是一个不错的选择。

在创建表的非聚集索引前创建聚集索引

      与这条指南相对的是，在删除它的聚集索引之前删除它的非聚集索引，反之的话会导非聚集索引不必要的重建，把一个表在堆和聚集索引之间来回转换总是导致一个表的非聚集索引进行重建，因为书签的内容必须从行标志更改为聚集索引键。

基于使用场景对你的索引进行碎片整理和重建

        如果你的索引经常被扫描， 那么如第十一章-索引碎片-提到的那样，索引的外部碎片便很重要，因为它决定着用来扫描整个或者部分叶子节点所需要的工作量，如果是处于这种情况，当外部碎片达到10%时候考虑重新整理他，当外部碎片达到30%时候考虑重建它。

     For most transactional environments, the values mentioned above represent the point at which the benefit of performing the reorganization or rebuild of the index outweighs the cost of doing it.【这句话不理解】

     然而，当一个索引被用来查找指定的键值，那么外部碎片对于性能来说几乎微乎其微，遍历从根节点到叶子节点的各个层级的页其所需要的IO数目是相同的，而不管其是否有外部碎片，在这种情况下，重新组织或者重建索引对性能几乎无影响。

有规律的更新索引统计信息

       这儿的关键字是“规律性”，因为只有通过知道了你的应用程序正在做什么你才能决定什么时候统计信息需要被更新，第十四章-索引统计-展示了为什么一些过时的统计信息会变得比其他的更快。

结论

     这些指南从众多工作于SQL SERVER的开发者的经验中逐步提取出来，并在许多环境中服务了多年。遵守它们可以帮助你为数据库创建最好的索引架构。

 

       【完结】