3.4  明智地选择：决定何时何地使用何种索引

到现在，你心中可能会想，“哎呀，我总是准备创建聚集索引！”这样想有大量很好的理由。只是要知道，同样还有一些理由不这样想。

选择包含什么索引以及不包含什么索引可能是个困难的过程，而且，如果那样做还不够，则必须做一些关于想让它们成为什么类型的决定。由于只能有一个聚集索引，因此后者的决定同时变得更简单也更难。这说明必须明智地进行选择，以便最有效地使用它。

3.4.1  选择性

索引，特别是非聚集索引，主要受益于在某些情况下索引中有相当高级别的选择性。这里的选择性是指列中为唯一值的百分比。列中唯一值的百分比越高，就认为选择性也越高，因而索引的益处就越大。

回顾前面关于非聚集索引的小节（特别是关于聚集索引上的非聚集索引的部分），会想起在非聚集索引中的查找其实只是开始。要找到真正的数据，还需要在聚集索引中进行再一次浏览。即便使用堆上的非聚集索引，仍然还需要执行多个单独的物理读操作。

如果非聚集索引中的查找将产生聚集索引中的多个另外的查找，那么，可能进行表扫描会更好些。事实上，这里可能产生为数众多的作用效果，这非常令人吃惊。只有当被索引列中的唯一性约为90~95%时，非聚集索引所创造的循环处理才是值得的。

这对于聚集索引的影响微乎其微，因为一旦处于数据（唯一的或不唯一的）范围的起点，就已经到达了目的地。这里不必再读额外的索引页。另外，聚集索引拥有其他事物很可能会更有用处。

选择性规则的一个例外与外键有关。如果表中的一列是外键，那么，在该列上有一个索引，这十有八九是有益的。

为什么是外键而非其他列？外键通常是与它们引用的表之间进行联结的对象。无论索引的选择性如何，在联结的执行中它都是非常帮助的，因为它们允许合并联结。合并联结从每一个表中获取一行并进行比较，以确定它们是否满足联结条件（在什么上进行联结）。由于在两个表的相关列上有索引，因而对两个行的查找会非常快。

这里的要点是，虽然选择性不是一切，但它是需要考虑的大问题。如果讨论中的列不是外键，那么，在你必须考虑的问题方面，选择性几乎仅对于“这将用得有多频繁？”这样的问题才算是次要的。

3.4.2  注意代价：当少变成多时

要记住，虽然索引在读取数据时加快了执行，但事实上在修改数据时，它们的开销非常大。索引不是靠魔力来维护的。每当对数据进行了一次修改，任何与数据相关的索引也需要更新。

当插入一个新行时，必须在表中所有的索引上加入新的条目。另外，也要记住，在更新行时，是作为删除和插入来处理的——再次需要更新索引。但是，等等！还有更多！（感觉这里就像午夜的商业信息片。）当删除记录时，同样也必须更新所有的索引，而不仅仅只涉及删除数据。每创建一个索引，就又创建了一组必须更新的条目。

顺便说一下，我前面提及条目时使用的是复数形式，而不只是单个。记得B树有许多级。每当在叶级进行修改时，可能会发生页拆分，这样一来，为了引用到正确的叶级页，也必须修改一个或多个非叶级页。

有时（实际上很经常），要做的事情是不创建额外的索引。有时，最好是基于对系统至关重要的事务选择索引，并使用讨论中的表。用于该事务的代码中有WHERE子句吗？它使用了什么列？这里需要排序吗？

3.4.3  选择聚集索引

记住，只能有一个聚集索引，因此必须精明地选择它。

默认情况下，主键与聚集索引一起创建。通常，这是聚集索引最好的地方，但并不总是这样（甚至在一些情况下，这会对你极其不利）。并且，如果你顺其自然，则无法在其他任何地方使用聚集索引。此处的要点是，别就这样接受默认安排。在定义主键时请仔细考虑一下——真的想要它成为聚集索引吗？

如果决定确实不想接受默认安排，即不希望声明为聚集索引，只需在创建表时添加NONCLUSTERED关键字即可。例如：

一旦创建了索引，改变它的唯一办法是删除并重建它，因此，希望事情从一开始就是正确的。

要记住，如果修改了聚集索引所在的列，SQL Server将必须对整个表进行彻底的重新排序（记得对于有聚集索引的表而言，表的排序顺序与索引的顺序相同）。那么，考虑一个5000字符宽的表，该表有一百万行——这是必须要进行重新排序的庞大的数据。从这里应当想到几个问题：

l    这会耗时多久？时间可能会很长，而且，实在没有一个好方法来估计这一时间。

l    有足够的空间吗？为了在索引列上重新排序，平均来说，另外需要的空间是表实际占用空间的1.2倍（运行空间加上新的索引）。当处理一个大型的表时，这将会是非常巨大的空间——确定你有足够的空间来进行重新排序。顺便说一下，所有这些活动都将会自己在数据库中发生——因此，所需要的空间还会受到为数据库设置的最大大小和增长选项的影响。

l    应该使用SORT_IN_TEMPDB选项吗？如果tempdb单独位于与数据库不同的物理阵列上，并且有足够的空间，则答案很可能是肯定的。

1．赞成

当讨论中的列经常作为范围查询的对象时，聚集索引对于这样的查询是很有用的。这类查询以使用BETWEEN语句或者<or>符号为代表。使用了GROUP BY和利用了MAX、MIN以及COUNT聚集的查询，也是使用范围并偏好聚集索引查询的重要例子。聚集在这里非常恰当，因为搜索能够直接到达物理数据中的特定位置，并连续读取数据，直至到达范围的末尾，然后停止。它非常高效。

当想要数据基于聚集键排序时（使用ORDER BY），聚集也非常优秀。

2．反对

在两种情况下不希望创建聚集索引。第一种非常显而易见——当有更好的地方使用聚集索引时。是的，听上去我有些重复，但不要因为列看上去将是聚集索引就把它用作聚集索引（主键是常见的罪魁祸首）——要先确定是否有另外的列更适合。

尽管如此，或许关于聚集索引更大的使用禁忌是：如果将要以不连续的顺序进行大量的插入，请不要使用。记得页拆分的概念吗？是的，这里会发生页拆分并消耗大量的时间。

设想这样的场景：你正在创建账目清算系统。想要在交易文件中使用交易号码的概念作为主键，但也想要交易号码能反应出交易的类型（它对于会计人员的排错确实很有帮助）。那么，你有了一种算是方案的想法——在所有的事务上添加前缀，以表明它们源自什么子系统。它们看起来像下面这样：

其中XXXXXX是连续的数值。

这好像是个不错的主意，于是你开始实现它，任由主键默认地成为聚集索引。

乍看上去，这样的设置似乎很好。你将会有唯一的值，会计人员能够基于交易号码推断出交易来自何处，他们也会很满意。由于聚集索引经常被用于查询交易号码的范围，看起来聚集索引是有意义的。

如果真的只是那样简单就好了。考虑一下进行插入的情形。使用聚集索引时，最初有一个良好的机制避免大量页拆分的开销。当插入一条新记录时，将把它接在表中最后一条记录的后面，那么，即使发生了页拆分，也只是让新插入的记录到新的页中——SQL Server不必试图移动之前的数据。不过现在，却陷入到麻烦中了。

从总分类账（General Ledger）中插入的记录还好，将接续在表的最后（按字母顺序GL在后面，而且号码是连续的）。可是，AR和AP交易则有大问题——它们将进行不连续的插入。当插入AP000025且页上没有空间时，SQL Server将在表中发现AR000001并知道这不是连续的插入。在插入AP000025前，原来的页中一半的记录将被复制到新页上。

这样做产生的开销可能是惊人的。要记住正在处理的是聚集索引，而聚集索引就是数据。数据的顺序是聚集索引的顺序。这意味着，当把索引移动到新页上时，也在移动数据。现在，设想一下在典型的OLTP环境中运行这样的账目清算系统（再没有比账目清算系统更具有OLTP特征的了），该环境中有一群数据录入员正在尽可能快地输入供应商发票或客户订单。你的系统将经常发生页拆分，每当发生页拆分时，该表的用户将在系统移动数据时出现短暂的停滞。

幸运的是，有两种方法来避免这种情形：

l    选择在进行插入时是连续的聚集键。可以为此创建标识列，或者也可以使用另一个对任何输入交易（无论来自那个系统）其逻辑上都是连续的列。

l    选择在该表上不使用聚集索引。对于类似本例的情形，这常常是最好的选择，因为在堆上的非聚集索引中的插入比聚集键上的插入快。

尽管前面说过，为避免页拆分倾向于连续的聚集键，但必须意识到这里也有代价。连续聚集键的弊端之一是并发性（两个或更多的人试图在同一时间获得同样的对象）。在需要什么、打算做什么以及这样做在别的地方付出的代价是什么上，全都是关于权衡的问题。

为什么要在有关事情如何进行的问题上进行这样深入的探究，这可能是最好的例子之一。在明白要使用（或不使用）的正确的索引是什么之前，需要思考事情真正是怎样完成的。

3.4.4  列顺序问题

只因为索引中有两个列，就不再意味着索引对所有引用任一列的查询都有用。

仅当查询中使用了索引中第一个列出的列时，才考虑使用索引。好的一面是，不必在所有的列上一对一地匹配——只需第一个列匹配即可。当然，匹配的列越多（按顺序）越好，但是只需第一列就能产生出明确的“请别使用”的情况。

这样考虑一下，假设在使用电话簿，所有的条目都按照先姓氏后名字的方式索引——如果只知道要通电话的人的名字是Fred，这样的排序方式能为你带来任何好处吗？另一方面，如果只知道他的姓是Blake，索引将能为你缩小查找范围。

在索引构建上最常见的错误之一是，认为一个包含所有列的索引将对任何情况都有帮助。事实上，这样做只是把所有的数据再存储了一次而已。如果在查询的JOIN、ORDER BY或WHERE子句中没有提及索引中的第一个列，则将完全忽略索引。

3.4.5  删除索引

如果不断地对情况进行重新分析并添加索引，也请别忘记删除索引。要记住插入时的开销——在考虑需要的索引时，没对插入上的开销太在意，因而也不曾考虑过哪些索引是不需要的。你要总是问自己：“我能从这些索引中去除掉哪些索引？”

删除索引的语法与删除表的非常相似。唯一的不同是这里需要用索引所附着的表或视图来限定索引名：

DROP INDEX <表或视图名>.<索引名>

运行该语句即可删除索引。

3.4.6  使用数据库引擎优化顾问

希望你对于索引的了解已经足够多，可以不需要使用数据库引擎优化顾问，尽管如此，它还是会非常便利的。它的运作如下：取得一个工作负荷文件来考查信息，以确定什么索引对系统最有利，工作负荷文件是使用SQL Server Profiler（在第23章中讨论）生成的。

可以在SQL Server Management Studio的“工具”菜单中找到“数据库引擎优化顾问”。也能够从Windows的“开始”菜单中通过一个单独的程序项来访问它。像使用大多数其他性能调试工具一样，不建议把使用该工具作为决定创建什么索引的唯一方法，但是，它能够在你可能考虑不到的地方提出建议，在这一点上它是非常便利的。