17. 建立与使用索引
什么是索引？
索引概念
建立索引
重新建置索引
使用索引
设计有效的索引
本章总结
对数据库设计者而言，索引是最有用的工具之一。 索引 （index）是一个让您能增进查询效能的辅助结构，它能减少检索查询数据所必须的I/O动作数量。换言之，索引让Microsoft SQL Server 2000以较少的I/O操作来找出数据，减少搜寻数据的量。当您使用数据库数据表的索引来检索数据列时，SQL Server能很快地判断出数据储存的位置并且立即撷取该数据。因此，数据库数据表的索引与书本所用的索引相当类似－两者均提供快速撷取的功能，特别是我们面对大量信息的时候。
在本章中，您将学到索引的基本观念，包括如何建立索引以及SQL Server中有哪几种不同类型的索引可用。您也会学到何时应使用索引、何时不使用索引－因为索引并非无往不利的工具，在某些情况下，使用索引反而可能有害于系统的效能。
什么是索引？
如前所述，索引是SQL Server用来存取数据的辅助数据结构。依其类型的不同，索引可能是与数据一起储存，也可能与数据分开储存。撇开其类型不谈，索引的运作方式基本上是相同的，这也是本节中您将学到的部分。
在一个没有索引的系统中，所有数据检索的工作都要靠扫描数据表来完成。在扫描数据表的操作中，所有的数据都需要被读取，并且与查询的数据相互比对。一般说来，我们会尽量避免数据表扫描－因为这种操作会造成惊人的I/O数量，尤其是扫描一份庞大的数据表时，可能要花掉不少的时间并吃掉可观的系统资源。如果使用索引的话，您便能具体的降低I/O操作的数量，提升数据存取的速度并尽快将系统资源释放给其它的作业来使用。
数据库索引是以B型树状结构来组织。索引中的每一分页被称作 索引分页 或 索引节点 。索引结构由顶层的根节点开始。 根节点 （root node）标示出索引的起点；当需要寻找数据时，它是最先被存取的资料。根节点也包含了一些索引数据列；这些索引数据列含有一个键值及一个称为中介节点的指针指向另一个索引分页，如图17-1所示。这种组态是必须的，因为在一个平均大小的数据表中，索引可能是由成千上万的索引分页来构成。借着由根节点起始并浏览中介节点，SQL Server能够「放大」您要的数据。
图17-1 根节点与中介节点
当我们使用书本中的索引时，使用的方法可能与下面所述极为类似：假设索引是由一页包含「a」、「b」、「c」等字母项目所在部分之页码的目录页所开始。接着翻到「a」这一页，又会发现进入「aa-ab」、「ac-ad」、「ae-af」等范围所在部分的页码。进入「aa」，里面又列有「aaa-aab」、「aac-aad」、「aad-aaf」等范围所在部分的页码。借着这种排列，您可以很快的找到您要的数据，而不用翻遍整本书。索引以根节点作为第一页，与上述方式相当雷同。
一如根节点，每个中介节点包含有索引分页，分页中包含着索引数据列。每个索引数据列指向其它的中介节点或分叶节点，如图17-2。分叶节点由索引的最底层组成。与根节点不同的是，每个中介节点还包含了一份连接到同一层中介节点的连结清单（Linked List）。换句话说，此节点知道相邻节点以及较低层级的节点。
顾名思义，所谓「B型树」，意味着从根节点展开中介节点后其型态犹如树枝般分布。每一组树状结构中同一层的中介节点称为 索引层级 ，如图17-3所示。到达分叶节点（树状结构中最底层的节点）所需的I/O操作数量要依索引层级的数目而定。如果数据库数据表含有的数据很少，根节点可以直接指向分叶节点，索引将不需要包含任何中介节点（当然这种情况不太可能发生）。
图17-2 显示中介节点与分叶节点的树
图17-3 索引层级
在一个非丛集索引中，分叶节点包含有键值，以及一个指向数据表中所需数据列的数据列识别码（ROW ID），或者会包含一个丛集索引键值－如果数据表中还有一个丛集索引的话。而在丛集索引中，数据本身即位在分叶节点中（丛集与非丛集索引将在本章稍后 〈索引类型〉 一节中有所说明）。分叶节点中数据列的数量要看索引建立时引用的项目大小。而在丛集索引中，则要视数据的大小而定。
________________________________________
说明
数据列识别码（ROW ID）是SQL Server由档案编号、分页码及数据列号码自动建立的指针。借着数据列识别码，您只需一个额外的I/O操作便可撷取数据。因为您知道要撷取的是哪个分页，而且SQL Server知道分页所在的位置，因此只要单一的I/O操作即可将该分页读入内存之中。这种检索过程的简化也是为何索引能更有效率并增进效能的主要原因。
________________________________________
要特别留心的是，由于索引是以排序方式建立的，任何数据的变更都可能造成额外的负担。举例来说，如果插入动作造成一个新的数据列必须被插入到已经满的分叶节点，SQL Server必须挪出空间给新的数据列。此举必须将近乎半数的数据列移至分叶节点中的另一个分页中。此种数据搬移称为 分页分隔 （Page Split）。分页分隔会造成索引树一连串的分隔动作。透过谨慎的设定 填满因子 （fill factor）可以避免分页分隔，我们会在本章稍后 〈使用填满因子避免分页分隔〉 一节中介绍填满因子。
索引概念
现在您已经对索引结构有了基本的了解，接着让我们看一些与索引有关的概念。在本节中，您将学到索引键、索引唯一性以及索引的类型。
索引键
索引键 （index key）指出用于产生索引的一个数据行或数个数据行。索引键是一个让您可以快速找到数据列的值，此数据列包含有您需要的数据。索引键就像是书本中的索引项目，可指向文中特定的主题。要使用索引取得数据列，您必须在SQL陈述式的WHERE子句中包含索引键值，还得要看看索引是一个简单索引还是复合索引。
简单索引
简单索引 （simple index）即为使用单一数据行的索引，如图17-4。如果您要取得索引的效率，则该数据行必须在SQL陈述式的WHERE子句中引用。
图17-4 简单索引
根据数据储存的数据型别、以及数据行中唯一性项目的数量，还有使用何种类型SQL陈述式等等因素，有时使用简单索引便可以很有效率，但有些情况则可能使用复合索引会比较合适。举例来说，如果您索引的是一个由上千个名字与地址构成的通讯簿， state数据行就不应该是一个简单索引的候选键，因为同一州里可能有很多相同的项目。然而，若在索引中增加street与city数据行，使其成为复合索引，您可以让每个项目近乎唯一。这种方法会很有用－若您的查询是依地址来找数据列的话。
复合索引
复合索引 （composite index）是定义超过一个数据行的索引，如图17-5。复合索引可以利用一个多或个索引键来存取。在SQL Server 2000中，索引最多可包含16个数据行，并且键值总数据长度不可超过900个字节。
图17-5 复合索引
当您使用复合索引进行查询时，并不需要在SQL陈述式的WHERE子句中放入所有的索引键，不过使用一个以上的索引键会是比较聪明的做法。举例来说，假设索引是由数据表中a、b、c三个数据行建立的，使用包含有（a AND b AND c）或（a AND b）或a的SQL陈述式均可存取该索引。当然，使用最严格的WHERE子句，例如包含a AND b AND c，效率会比较高些。它能从数据库中检索出较少的数据列，因为它检验资料列时更为明确。如果您使用a AND b或只有a，会开始进行索引扫描的操作。
当超过一个索引项目可以满足搜寻条件时，就会发生索引扫描。在索引扫描中，索引的节点会被扫描以取得多个数据集。此外，索引只部分涵盖了选取的值。举例来说，如果索引是由a、b、c三个数据行建立而查询仅指定了a，所有满足a的值的数据列都会被传回，包括所有b和c的值。
由于使用在索引中的基础数据行是按数字排列，SQL Server查询最佳化器可以判断出大略包含您所需数据的索引分页范围。一旦知道了开始页和结束页，含有数据值的所有分页都将被检索，而且数据将被扫描以选择您所需的数据。
________________________________________
真实世界 CustomerLocation资料表
假设我们现有一份数据表，其中包含了我们公司顾客地址的相关信息。索引是由state、country以及city数据行所建立，并以 state 、 country 、 city 的顺序储存在B型树状结构中。如果一个查询在WHERE子句中指定 state 的值为 Texas ，将会使用索引的功能。由于查询中 country 及 city 的值并未指定，索引将会传回索引数据集中所有state数据行值为 Texas 的数据列。索引扫描会先用来检索出索引分页的范围，随后依state数据行的值撷取资料分页。索引分页将被循序的读取，方式与数据表扫描存取数据分页一样。
________________________________________
________________________________________
说明
如果您要使用索引的特色，只有在SQL查询的WHERE子句中至少用到一个索引键时，索引才会起作用。以刚刚的例子来说，如果查询的WHERE子句里只有名字或电话这类数据行的话，就不会用到索引的功能。
________________________________________
在大部分的情况下，索引扫描十分有效率。不过，若数据表中将有20% 以上的数据列会被存取，使用读取数据表中所有数据列的数据表扫描会更有效率。索引利用的查询效率，要视您如何使用索引（本章稍后 〈使用索引〉 一节会介绍）以及我们接着要讨论的问题：索引的唯一性。
索引的唯一性
您可以将SQL Server的索引定义为唯一索引或非唯一索引。在 唯一性索引 （unique index）中，每个索引键的值必须是唯一的。 非唯一性索引 （nonunique index）则允许索引键的值可以重复。非唯一索引的效力或效率要看索引的选择性而定。
唯一索引
唯一索引的每个索引键只包含一列数据－换句话说，索引键值在数据表中不会出两次。唯一索引非常有效率，因为它们保证要撷取查询所需的数据只需一个额外的I/O操作。SQL Server会强制一个或数个数据行的唯一性以建立索引键。SQL Server将不会允许在数据库中插入重复的键值，如果您试图这么做，则会传回错误讯息。当您在数据表中建立一个PRIMARY KEY条件约束或一个UNIQUE条件约束时，SQL Server便会建立唯一性索引。PRIMARY KEY条件约束与UNIQUE条件约束已经在 第16章 中讨论过。
当数据本身即是唯一的，索引自然可以建立其唯一性。但若数据行中所包含的数据并不是唯一的，您仍然可以利用复合索引来建立唯一索引。举例来说，last name数据行可能并不是唯一的，但是若与first name和middle name数据行组合起来，您便能在数据表上建立唯一索引。
________________________________________
说明
如果您在数据表中插入一数据列的结果可能会使唯一索引出现重复的索引键值，这个插入动作将会失败。
________________________________________
非唯一索引
非唯一性索引的运作方式与唯一性索引并没有什么不一样（除了可以在分叶节点中包含重复的值以外）。只要符合SELECT陈述式中指定的基准，所有重复的值都会被撷取。
非唯一性索引并不如唯一性索引般那么有效率，因为它需要额外的程序（额外的I/O操作）来检索出查询所需的数据。不过有些应用程序需要用到重复的键值，它有可能无法建立唯一索引。在这类情况下，非唯一索引至少比没有索引要来得好些。
索引类型
B型树状结构索引有两种类型： 丛集索引 （clustered indexes）与 非丛集索引 （nonclustered indexes）。丛集索引在其分叶节点中储存实际数据的数据列。非丛集索引则是一个指出数据表中数据位置的辅助结构。在本节中，我们将看一下两种索引类型之间不同的地方。我们也会在本节中介绍 全文检索索引 （full-text index），它实际上更像是一个目录而非索引。
丛集索引
一如前述，丛集索引是一种将数据表的实际数据列数据按顺序储存在其分叶节点的B型树状结构索引，如图17-6。这种系统提供了一些优点与缺点。
图17-6 丛集索引
由于丛集索引的数据是储存在分叶节点上，亦即一旦到达分叶节点便可取用数据，这种方式意味着只要少数I/O操作便可得出结果。任何操作数量的减少都可使单一操作产生较高的效能，并对系统整体的效能有所提升。
丛集索引的另一个优点是，数据将会按索引排序时的顺序来撷取。举例来说，如果现在有一个丛集索引是由state、country、city数据行来建立，而查询选择所有的state ? Texas的值，输出的结果将会以country和city数据行在索引定义时的排序方式来排序。如果应用程序与数据库在设计时考虑过这类问题，这种功能就可以让您避免不必要的排序操作。例如，若您已经确定数据需要的排序方式并用于索引之中，使用丛集索引便可让您在撷取数据之后不需再执行排序的工作。
使用丛集索引的一个缺点是，存取数据表时常需穿梭整个索引，如此将会造成系统额外的负担。SQL Server存取数据时会从根节点开始搜寻索引直到它到达包含数据的分叶节点。如果由于数据数量的缘故建立了许多分叶节点，支持这些分叶节点所需的索引层级数量也相当多，SQL Server从根节点到分叶节点的搜寻过程就会需要更多的I/O操作。
因为实际数据是储存在丛集索引中，在一个数据表上您只能建立一个丛集索引。另一方面，您可以在丛集数据表的顶层建立不只一个非丛集索引。（丛集数据表仅仅是拥有一个丛集索引的数据表。）您应该使用最常用以存取的索引键来建立丛集索引，如此可让您在最佳时机透过丛集索引存取数据，并提供最好的效能。
非丛集索引
不同于丛集索引，非丛集索引并不会把实际数据包含在其分叶节点之中。分叶节点包含着两种数据列的位置信息其中的一种。第一，如果数据表上没有丛集索引，此数据表的非丛集索引的分叶节点储存的是数据列识别码，如图17-7。每个数据列识别码指向数据表中实际数据存在的数据列。构成数据列识别码的值包括数据文件识别码、分页码，以及分页中的数据列编号。这个值精确的指出数据储存的位置以便快速的存取实际数据。
图17-7 在没有丛集索引的数据表上的非丛集索引
如果数据表上有一个丛集索引，非丛集索引的分叶节点将会包含此数据的丛集索引键值，如图17-8。当到达非丛集索引的分叶节点时，可找到丛集索引键值，并且接着被用在丛集索引中继续搜寻，直到在丛集索引的分叶节点中找到资料为止。
图17-8 一个拥有丛集索引的数据表上的非丛集索引
之前已经说过，每个数据表只能拥有一个丛集索引。您可以在每个数据表上建立249个非丛集索引，但那绝不是聪明的做法（本章稍后 〈索引指导原则〉 一节中会解释这一点）。在数据表不同的数据行上拥有几个非丛集索引是很常见的，WHERE子句要用到哪个索引则由查询最佳器来决定。
全文检索索引
如前所述，SQL Server全文检索索引事实上比较像是一个目录而不是索引，并且也不是B型树状结构。全文检索索引允许您以关键词的群组来搜寻数据。全文检索索引是Microsoft Search Services的一部分，并广泛地应用在Web网站搜寻引擎以及其它以文字为基础的操作中。
不同于B型树状结构索引，全文检索索引储存在数据库的外部，但是由数据库来维护。由于它是储存在外部，索引可以自行维护其结构。执行全文检索索引有下列限制：
• 全文检索索引中必须包含一个数据行，并且该数据行在数据表中的每一列都要是唯一的。
• 全文检索索引也必须包含数据表中一个或多个字符数据型别的数据行。
• 每一个数据表只可以允许有一个全文检索索引。
• 全文检索索引并不像B型树状结构索引会自动更新。在B型树状结构索引中，数据表的插入、更新或删除操作将会更新索引。但是在全文检索索引中，数据表上的这些操作将不会自动的更新索引，您必须以排程或手动的方式来更新。
全文检索索引有许多在B型树状结构索引中找不到的丰富功能。由于此索引是设计为一个文字搜寻引擎，它比一般的文字搜寻支持更多的功能。您可以使用全文检索索引搜寻单字、词组、某一个单字或单字的集合，或搜寻类似的单字。在本章稍后的 〈使用全文检索索引精灵〉 一节中您将学会如何建立全文索引。
建立索引
建立索引并不困难。建立丛集与非丛集索引的方法差别不大，都可利用Enterprise Manager所提供的精灵或是利用SQL命令CREATE INDEX来建立。在本节中，您将学到如何利用这两种方法来建立索引，也会学到使用填满因子的相关问题，以及如何使用预存程序来建立全文检索索引。
________________________________________
说明
虽然精灵较易于使用，但是若您的作业需要重复多次或是必须建立多个类似的数据库，您会发现指令文件是很方便的工具。指令文件允许您将建立程序文件化并且可以一再的执行。
________________________________________
使用建立索引精灵
显然地，若您打算在数据表上建立索引，数据表必须是已存在于数据库中。您可以利用 建立索引精灵 在数据表上建立丛集或非丛集索引，请遵循下列步骤：
1. 开启Enterprise Manager，按一下工具列的 执行精灵 按钮，显示 选择精灵 对话框。在本例中，我们将使用 Northwind 数据库。
2. 展开 数据库 ，选择 建立索引精灵 ，按一下 确定 。
3. 出现 欢迎使用建立索引精灵 画面，如图17-9。注意一下您所选择的服务器与数据库的名称会出现在标题列。
图17-9 「欢迎使用建立索引精灵」画面
4. 按一下 下一步 显示 选取数据库与数据表 画面，如图17-10。在这里您可以指定您要建立索引的数据库数据表。预设数据库是您在开启精灵时已选取的数据库，数据库中的数据表也会有一个清单供您选取。
5. 按一下 下一步 继续到 目前的索引信息 画面，如图17-11。本例使用Customers数据表，因为它包含着为数不少的资料列。如您所见，在Customers数据表上已建立了几个索引，包括一个丛集索引及四个非丛集索引。记住，一个数据表上只能建立一个丛集索引。
所有Customers数据表上已经建立的索引均为简单索引（只列出一个数据行），并且每个索引均建立在不同的数据行上。当 查询最佳化器 （Query Optimizer）分析一个查询并要选择查询执行方案时，它会依照目前可用的索引及WHERE子句来决定要使用哪个索引。
图17-10 选取数据库与数据表画面
图17-11 目前的索引信息画面
6. 按一下 下一步 显示 选取数据行 画面，如图17-12。这个画面让您选取索引要包含的数据行。此处不用担心数据行的顺序－稍后您仍然可以变更它们。
图17-12 「选取数据行」画面
7. 要指定打算包含在索引中的数据行，可选取该数据行名称右边的复选框。在本例中，我们将建立一个在CompanyName、ContactName和Region数据行上的复合索引。
8. 按一下 下一步 显示 指定索引选项 画面，如图17-13。这个画面让您可以设定一些重要的选项，以决定要建立何种索引。您可以选取 将此作成丛集索引 复选框，让新索引成为丛集索引。在本例中，用来建立丛集索引的复选框呈现无法选取的状态，因为Customers数据表上已经建立了一个丛集索引。
您也可以选取 将此作成唯一索引 选项，让此索引成为唯一性索引而不是非唯一性索引。您还可以指定填满因子为 最佳化 或 使用固定比率 。由于索引中的数据列是依其顺序储存的，SQL Server有时可能需要搬移数据才能维持其顺序。填满因子选项可让您指定新建立的索引在填满至何种程度时，就应预留空间让将来的插入动作可以使用。预设的填满因子（若您选择 最佳化 便是默认值）是 0 ，亦即指定填满分叶节点但在索引节点的较高层保留一些空间。关于填满因子的详细情形，请参阅本章稍后 〈使用填满因子避免分页分隔〉 一节。
图17-13 指定索引选项画面
9. 选取您索引选项并按 下一步 显示 完成建立索引精灵 画面，如图17-14。在这个画面里，您可以重新安排组成索引的数据行顺序。选取您要移动的数据行，按一下 上移 或 下移 直到数据行到达您想要的位置。在这个画面里您也可以指定索引的名称。
图17-14 「完成建立索引精灵」画面
复合索引的顺序相当重要。SQL陈述式要充分利用索引的优点，陈述式的WHERE子句中必须是索引的重要部分。图17-15显示同样的画面，不过索引已经重新命名为 CustomerAreaIndex ，数据行的顺序也重新安排为Region、CompanyName及ContactName。
图17-15 重新安排索引中数据行的顺序
由于索引数据行以此顺序排列，使用到此索引的SQL陈述式就必须在其WHERE子句中包含Region数据行，因为Region是主要资料行。当然，陈述式也可在WHERE子句中含有Region以及CompanyName，或者甚至Region、CompanyName以及CompanyName都使用。如果您在WHERE子句中使用全部三个值，将可达到最好的效能，因为执行最少的I/O操作。WHERE子句中数据行名称出现的顺序并不会产生任何影响。
10. 一旦您满意了数据行的顺序，按一下 完成 后索引就会开始建立。索引建立的过程可能是几秒钟也可能是几小时，时间的长短依数据量多寡、系统效能、磁盘效能以及系统内存大小而定。要在数据表上建立索引，SQL Server必须读取数据表上所有的数据，因此耗时长短的变化相当大。
________________________________________
注意
如果您建立的是唯一性索引而索引键却发现有重复的值，索引建立程序将会失败。
________________________________________
利用 建立索引精灵 来建立索引相当简单，但是建立过程中会有一些缺点。尤其当您使用 建立索引精灵 时，它并不会留下任何与您工作相关的信息，因此每次您想要建立另一个索引时，就必须依本节介绍的程序从头到尾再次进行一遍。相对地，若建立索引时使用的是指令码档案，您就可以重复地使用。若您打算重建数据库，您必须为数据库中的每个索引再次应用 建立索引精灵 。不过，在您建立索引之后，可以利用Enterprise Manager为它们产生SQL指令码。
使用Transact-SQL
若您使用Transact-SQL（T-SQL）来建立索引，可以将命令储存成指令码并可一再重复地使用，您也可以修改索引建立的指令码来建立其它的索引。此外，这种方法也让您在建立索引时更为有弹性，因为有更多的自变量可以利用。要使用这种技术来建立索引，只需将T-SQL命令储存入一个档案之中，并使用下列的语法将档案读入OSQL：
Osql -Uusername -Ppassword < create_index.sql
此命令假设您建立的文件名称为create_index.sql。您也可以使用Query Analyzer来执行指令码。（关于此一程序的相关信息请参阅 第13章 。）
要使用T-SQL来建立索引，必须利用CREATE INDEX命令。CREATE INDEX的语法如下：
CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED]
INDEX index_name ON table_name
(
column_name [, column_name, column_name, ... ]
)
[ WITH options ]
[ ON filegroup_name ]
中括号内的值是选择性的。您可以将索引建立为唯一性或非唯一性索引、丛集或非丛集索引、使用一个或多个数据行，并且可使用表17-1所列出的选择性自变量。您还可以选择性地指定索引储存的档案群组。
表17-1 用于CREATE INDEX命令中的选择性自变量
自变量 说明
PAD_INDEX 与FILL_FACTOR自变量搭配使用，指定分叶节点以外的较高层节点应预留的空间，而不仅是预留在分叶节点中。
FILL_FACTOR ? number 以百分比的方式（从0到100）指定每个分叶节点填满的程度。
IGNORE_DUP_KEY 当试图对一个唯一性索引插入一个重复的值时，指定忽略其插入动作并发出警告。若未指定IGNORE_DUP_KEY，整个插入将会被复原。
DROP_EXISTING 指定已经存在的同名索引应被卸除，并且重建该索引。当您在一个已经有非丛集索引的数据表上重新建立丛集索引时，这个自变量会很有用，因为不必再执行卸除与重建非丛集索引的其它步骤。
STATISTICS_NORECOMPUTE 指定统计资料不会被重新计算。不建议使用此选项，因为执行方案若是基于过时的数据，它们可能不是最佳化的状态。只有在您计划手动更新统计数据时才使用这个自变量。
________________________________________
相关信息
要了解这些选择性自变量的更多内容，可利用《在线丛书》索引 CREATE INDEX ，并在 找到的主题 对话框中选择 CREATE INDEX （Transact-SQL参阅）。
________________________________________
建立索引时，比起 建立索引精灵 来说使用T-SQL会是较好的选择。虽然刚开始T-SQL比较难用，但就长期而言，您会发现使用T-SQL来建立多个索引比用 建立索引精灵 要来得简单的多。
使用填满因子避免分页分隔
当拥有索引的数据表有更新或插入动作时，索引分页也必须更新。索引分页是靠指针将彼此链接在一起。每一索引分页有两个指标，一个指向下一页而另一个指向前一页。当索引分页填满时，索引的更新将会造成指标链的变更，因为在两个分页之间必须插入新的分页，此一过程被称为 索引分页分隔 （index page split），如此新的信息才能被放入索引链中正确的地点。SQL Server会将已经存在的分页（新数据必须置入的分页）里近乎半数的数据列搬移到新的索引分页之中。原先指向彼此的两个分页如今必须指向新的分页，新的分页也必须指向原先的两页（向前及向后）。现在新的分页在链中的指向次序已经正确，但是数据库中索引分页自然而规律的次序并不会维持太久。由于索引数据列会不断地加进索引当中（假设更新或插入时而发生的话），然而索引分页的大小有限，越来越多的索引分页会被填满。发生这种情况时，新的分页必须找到额外的空间。要建立更多空间，SQL Server就必须一再执行索引分页分隔，结果就是造成系统负荷不断增加，因为这些工作需要更多CPU使用量及额外的I/O操作。这也会造成一个片断的索引。索引数据被散布在整个数据库之中，效能因而缓慢无比。
图17-16 索引分页分隔
要减少分页分隔与片断化的问题，便是调整索引节点的填满因子。当您建立索引的时候， 填满因子 （fill factor）指定节点填满的百分比，让您可以为额外的索引数据列预留空间。您可以利用T-SQL陈述式CREATE INDEX的FILL_FACTOR选项指定一个索引的填满因子，就如之前所说的那样。如果CREATE INDEX命令中并未指定填满因子，系统将会使用默认值。默认值可利用 sp_configure 自变量 fill factor 来设定。当您安装SQL Server时默认值设定为 0 。
________________________________________
说明
只有在建立索引时改变填满因子自变量的设定才会对索引有所影响，当索引已建立后才改变它则不会产生任何效果。
________________________________________
填满因子的值（范围从0到100）指定了索引分页被填满的百分比率。值为 0 时较为特殊。当填满因子指定为0，分叶节点会被完全地填满，但中介节点与根节点会预留一些空间。这个值是SQL Server安装默认值，并且通常不会出什么问题。
若建立索引时指定填满因子值为100，所有的索引节点都会被完全地填满。对一个完全不会有新数据插入也不会有任何更新动作的数据表而言，这个值是它的索引最佳的选择。索引的分叶节点及较高层的节点都会完全地塞满，任何插入都会造成分页分隔。这种设定对只读数据表来说最为理想，即使有数据会被删除也不会有什么问题，因为数据删除不会造成分页分隔。
较低的填满因子值将会预留许多空间给插入动作，但也许要更多额外的空间来建立索引。除非您对数据库会执行固定的插入动作，通常不建议使用较低的填满因子设定值。如果发生太多的分页分隔，试着降低填满因子的设定值并重建索引，看看使否能减少分页分隔的次数。
使用效能监视器的 Page Splits/Sec 计数器可以找出您的系统目前每秒发生多少次分页分隔。这个计数器可以在 SQL Server : Access Methods 物件中找到。
如果分页分隔已经造成，并且您的索引也已变得严重地片断化，解决办法是重建您的索引。只有在您使用填满因子让索引分页预留空间时，才会发生片断化的情况。这些空间迟早也会被填满。要了解更多这一方面的问题，请参阅本章稍后 〈重新建置索引〉 一节。
使用全文检索索引精灵
要使用全文检索索引精灵来建立全文检索索引，遵循下列步骤（下一节会介绍如何利用全文检索索引）：
1. 在Enterprise Manager中选取您要建立全文检索索引的数据表。本例使用Northwind数据库的Customers数据表。
2. 按一下工具列中的 执行精灵 按钮。要不然的话，从菜单里按 工具 ，在下拉式选单中选择 精灵 。出现 选择精灵 对话框。
3. 在 选择精灵 对话框中展开 数据库 。选择 全文检索索引精灵 ，按一下 确定 。出现 欢迎使用SQL Server全文检索索引精灵 画面，如图17-17。
图17-17 「欢迎使用SQL Server全文检索索引精灵」画面
4. 按一下 下一步 进入 选择数据库 画面。我们选择 Northwind 作为范例。（说明 若在按一下 精灵 按钮之前已经选取数据库，则不会出现这个画面。）
5. 按一下 下一步 显示 选择数据表 画面，如图17-18。我们选择 Customers 数据表，然后按一下 下一步 。
6. 出现 选择索引 对话框。这个精灵需要您选择一个现存的唯一性索引，用来与全文检索操作搭配使用。Customers数据表目前只有一个唯一索引PK_Customers可用。
图17-18 「选择数据表」画面
7. 按一下 下一步 显示 选择数据表数据行 画面。此处您可选择适用于全文检索查询的数据行。图17-19显示的是已经选取了数个资料行的情形。
图17-19 选择数据表数据行画面，已选取数个资料行的情形
8. 按一下 下一步 显示 选择全文检索目录 画面，如图17-20。这个画面让您选择使用一个现存的索引目录（如果已经有的话）或是建立一个新目录。若您打算建立一个新目录，请确定目录放在I/O系统可支持的位置并在 名称 文字方块内输入一个足以描述此目录的名称。
图17-20 选择全文检索目录画面
9. 按一下 下一步 显示 选择或建立扩展排程（选择性） 画面，如图17-21。全文检索索引与B型树索引不同，它并不会在数据插入时不停地更新。排程功能让您指定索引每隔多久就必须进行更新的动作。此处您可选择一个已经存在的排程表（如果有的话），或是建立一个新的排程来扩展目录，新排程可以数据表为基础，或依每个全文检索目录（一个目录可包含多个启用全文检索索引的数据表）而定，再不然就是选择不使用排程。若您打算建立一个排程，您可选择 完全扩展 或是 递增扩展 。完全扩展意指系统会为数据表内所有的数据列（或目录中所有的数据表）建立索引项目（如果它们已有索引项目，则会重建）。完全扩展通常只发生在目录建立的时候。递增扩展则是仅为数据表中被修改的数据列更新它们的索引项目。要执行递增扩展，数据表中必须有timestamp数据行。如果没有，则会执行完全扩展。
图17-21 选择或建立扩展排程（选择性）画面
从这里，您可以按 下一步 继续精灵或选择建立一个排程。如果您按一下 下一步 而没有建立任何扩展排程，全文检索索引只会在精灵结束时被建立一次（而不是每隔一段时间便重新建立）。
________________________________________
说明
由于全文检索索引在数据库变更发生时不会不停地更新，您必须周期性地更新它。排程功能允许您让全文检索索引的更新动作可依时程自动执行。一旦建立了排程，索引便会依照排程的设定来进行更新。
________________________________________
10. 按一下 下一步 显示 完成SQL Server全文检索索引精灵 画面，如图17-22。按一下 完成 ，全文检索索引精灵将会为您建立全文检索索引目录。如果您制定了更新排程，排程也会开始进行。一旦建立了目录，您便可开始使用全文检索索引的功能。
图17-22 「完成SQL Server全文检索索引精灵」画面
使用预存程序建立全文检索索引
您也可以使用预存程序来建立全文检索索引。此处只概略的介绍一下如何使用预存程序来建立全文检索索引，完整的语法请参考SQL Server 2000在线丛书。
1. 呼叫sp_fulltext_database预存程序，利用enable自变量启用SQL Server中的全文检索索引。
2. 呼叫sp_fulltext_catalog，利用create自变量建立目录。
3. 呼叫sp_fulltext_table来建立目录与数据表/索引的对应连接。此预存程序必须以create自变量来呼叫，且必须提供全文检索索引要使用的数据表名称以及唯一索引的名称。
4. 呼叫sp_fulltext_column来新增用于全文检索索引的一个数据行。此预存程序必须以add选项以及打算加入目录的数据行名称来执行，且索引中的每个数据行都必须执行一次。
5. 再次呼叫sp_fulltext_table。此次预存过程调用需利用activate自变量来启动含有该数据表的目录。
6. 再次呼叫sp_fulltext_catalog，这一次需利用start_full自变量来启动目录的完全扩展，全文检索索引会撷取与目录相关的每个数据表的每个数据列。
使用预存程序来建立全文检索索引，比起使用T-SQL命令来建立B型树状结构索引要来得复杂。然而，若您必须建立为数不少的全文检索索引目录，最好是设计一个指令码来执行这个任务，虽然有点麻烦，但却相当值得。
使用全文检索索引
一旦全文检索索引建立好了之后，要利用它的功能是很容易的事。您可指定利用全文检索索引的T-SQL关键词： CONTAINS 以及 FREETEXT 。若您在不使用全文检索索引的情况下，使用SQL搜寻字符串，下列陈述式即为您会使用的典型方式。如您所见，查询的WHERE子句将会用到LIKE关键词：
SELECT * FROM Customers WHERE ContactName
LIKE '%PETE%'
这个陈述式可能会无法得到您想要的结果。要使用更为方便的查询方式，并且利用全文检索索引，您可使用CONTAINS述词。CONTAINS述词必须包含数据行名称及想要检索的特定文字：
SELECT * FROM Customers WHERE
CONTAINS(ContactName, '"PETE"')
CONTAINS 述词会利用全文检索索引找出包含特定文字的文字字符串，例如「PETER」或「PETEY」。
您也可以利用 FREETEXT 关键词来搜寻全文检索索引。如同 CONTAINS ， FREETEXT 是在 WHERE 子句中使用。 FREETEXT 可用来找出单字（或相关单字），这些单字与您在 FREETEXT 中指定的单字大略相符，但是找出来的单字并不一定要与您在 FREETEXT 中指定的单字一模一样。在SQL陈述式中使用FREETEXT的方式如下：
SELECT CategoryName FROM Categories WHERE
FREETEXT(Description, 'Sweets candy bread')
这个查询会找出一些含有像是「sweetened」、「candied」或是「breads」这类字的名称。
重新建置索引
SQL Server会保留每个索引的统计数据，这些统计数据描述了它的唯一性或选择性，以及索引键值的离散性。SQL Server查询最佳化器随后会利用这些统计数据来判断使用哪个索引，最能满足特定查询的需求。在预设状况下，索引统计数据会定期地更新。不过，由于分页分隔实际上会分散数据库中的索引分页，索引在经过一段时间后可能会变得极为片断，结果就是恶化了系统的效能。索引也可能变得不平衡，亦即索引树中的某一部份比起其它部分来说有更多填满的索引分页。您可以重新建置索引来回复其平衡与连续性。另外，重新建置索引时其统计数据也会重建。但请特别注意，并不需要先卸除索引再重建索引。我们会在稍后解释这个问题。
________________________________________
说明
在预设状况下，索引统计数据会自动更新，不过您可利用sp_autostats预存程序将这个功能关闭。
________________________________________
对一个已经变得片断化的索引而言，另一个问题是索引会有多于适量需求的索引层级。更多的索引层级意味着每个索引查询需要更多的I/O操作。您可以重新建置索引来减少索引层级的数目，并且因而降低所有索引查询所需的I/O操作量。
重新建置索引的一个方法是先手动移除索引，然后再重新建立该索引。对一个较小的资料表而言，这个方式勉强可以接受。但若处理的是中型或大型的数据表，则不应使用这个方法。最好是选择本节稍后将要介绍的方式来重建索引，该方法并不需要先卸除再重新建立索引。有几个理由支持这个选择。当我们在丛集数据表上建立非丛集索引时，非丛集索引是以丛集索引为基础而建立。若我们卸除了丛集索引，非丛集索引就必须重建一次，因为原先作为根据的丛集索引已不存在于数据表上。假设我们接着又在数据表上建立新的丛集索引，非丛集索引又必须再重建第二次！换言之，如果您先卸除再重新建立丛集索引，您必须重建非丛集索引两次：一次发生在丛集索引卸除时，一次发生在丛集索引重建时。若您使用另一个方法来重新建置丛集索引，非丛集索引将只需要重建一次。
要重建索引而不需先卸除再重新建立，可使用 CREATE INDEX...DROP_EXISTING 或使用 DBCC DBREINDEX 。这两种选择都可以一个步骤来重新建置索引，并告诉SQL Server要重新组织现有的索引。使用这些方法可以让您在重新建置丛集索引时避免非丛集索引的删除与重建。这些只要一个步骤的方法也会利用目前索引中数据排列的顺序，这些数据并不需要再重新排序。
CREATE INDEX...DROP_EXISTING 是用来在数据表上一次只重建一个索引。 DBCC DBREINDEX 则是与数据库名称和数据表名称一起使用，可以重建该数据表上所有的索引，而不必为每个索引执行个别的命令。这两个命令的语法与选项请参阅在线丛书。
更新索引统计数据
如果您没有时间或资源去重建索引，您也可以单独地只更新统计数据。这个技术得到的结果也许不如重新建置索引那般有效，因为索引可能已经片断化，产生的问题远比统计资料过期要来得复杂。这里也假设您已将SQL Server中统计数据自动更新的功能关闭（否则的话，您的统计数据无论如何都会被周期性的更新）。您可使用 UPDATE STATISTICS 命令手动更新索引统计数据，语法如下：
UPDATE STATISTICS table_name
[ index_name | (statistics_name [, statistics_name, ...] ]
[ WITH
[ FULLSCAN | SAMPLE number {PERCENT | ROWS} ]
[ ALL | COLUMNS | INDEX ]
[ NORECOMPUTE]
]
中括号内的值是选择性的，唯一必须的自变量是 table_name 。选择性的自变量在表17-2中说明。
如果您的系统必须处理大量的插入、更新，以及删除的作业，您应偶尔重新建置索引以避免效能如前述状况般降低。若您无法重新建置索引，至少也应定期的更新统计资料。
表17-2 UPDATE STATISTICS命令的选择性自变量
自变量 说明
index_name 指定要重新计算统计数据的索引。在预设状况下，数据表中所有的索引其统计数据都会重新计算。如果指定了index_name，就只有该索引的统计数据会重新计算。
statistics_name 允许您指定那些统计资料要重新计算。如果不指定这个值，所有的统计数据都会重新计算。
FULLSCAN 指定读取数据表中所有的数据列以收集统计数据。使用这个自变量是收集统计数据最好的方式，但它也会消耗最多的系统资源与时间。
SAMPLE number PERCENT | ROWS 指定统计数据要以多少数据列为基础，您可以提供资料列数或百分比率。在预设状况下，SQL Server会自行决定要取样的数据列数。这个选项无法与FULLSCAN选项共存。
ALL | COLUMN | INDEX 指定要收集所有的统计数据、或是数据行统计数据、或只收集索引统计数据。
NORECOMPUTE 指定统计资料在未来不会自动地重新计算。要重新开启统计数据自动重新计算的功能，可再次执行这个命令并且不要使用NORECOMPUTE选项，或是执行sp_autostats 预存程序。
使用索引
现在您已经了解如何建立索引，让我们接着来看一下如何使用索引。即使索引已经存在也不意味着SQL Server一定会使用它。一个索引是否会被SQL Server使用要看索引与SQL陈述式而定。此外，如果存在着多个索引，SQL Server可能就必须去选择要使用哪个索引。在本节中，您会看到SQL Server如何选择索引，您还会学到如何利用 索引提示 来指定使用哪个索引。您也会学到如何利用Query Analyzer来检视一个查询执行计划。
使用提示
当SQL Server查询最佳化器产生一个查询执行计划，它会选择一个能提供最佳效能的索引－通常这个索引会用到最少的I/O操作并检索最少量的数据列。
虽然查询最佳化器通常会为您的查询选择最有效率的查询执行计划与存取路径，但是若您对数据的了解比查询最佳化器知道得更多，您也许可以做出更好的决定。举例来说，假设您想在某一个数据表的名称数据行上检索名称为「Smith」的个人相关资料。索引统计数据是依照该数据行来建立的，并且假设其显示出在该数据行中每个名字平均出现三次。这个信息意味着该数据行具有相当良好的选择性；然而，您却知道「Smith」事实上出现的次数比平均值要高很多。若您知道如何指示SQL作业会更有效率，您可使用提示。 提示 （hint）是您给予查询最佳化器的简单建议，指定其不需进行自动地选择。
有数种型态的提示可用－包括联结提示、查询提示以及数据表提示－但此处我们将焦点放在数据表提示上。数据表提示让您指定如何存取该数据表（其它类型的提示会在 第35章 中介绍）。数据表提示可用来指定下列信息：
• 资料表扫瞄 在某些状况下，您可能会发现一个数据表扫瞄要比一个索引查询或索引扫瞄更有效率。当索引扫瞄需检索数据表中超过20% 的数据列时，数据表扫瞄会更有效率－例如，当数据表中有30% 是「Smith」。
• 要使用哪个索引 您可指定一个特定索引作为唯一要考虑的索引。您可能不知道SQL Server查询最佳化器在没有提示的状况下将会选择哪个索引，但您直觉得了解提示使用该索引会有最佳的效能。
• 要从哪一组索引中作选择 您可以对查询最佳化器建议数个索引，这些索引查询最佳化器都会使用（忽略重复的部分）。当您知道某个索引集合可以运作良好时，这个选项会很有用。
• 锁定方法 当查询最佳化器从特定数据表存取数据时，您可以告诉它要使用哪种类型的锁定。若您直觉到错误的锁定类型可能会被选取用在此数据表上，您可指定查询最佳化器应使用数据列锁定、分页锁定或数据表锁定。
让我们看一下如何利用提示来指定应使用的索引－也就是索引提示。下列范例显示出在T-SQL陈述式中使用索引提示的情形（该查询使用Region索引）：
SELECT *
FROM Customers WITH (INDEX(Region))
WHERE Region = 'OR' AND City = 'Portland'
请注意索引提示是摆在WITH关键词之后。如果您想要指定多个索引给SQL Server使用，在T-SQL陈述式中把它们列出来，方式如下：
SELECT *
FROM Customers WITH (INDEX(Region, City, CompanyName))
WHERE Region = 'OR' AND City = 'Portland'
索引提示的自变量可以是一个索引名称（就如刚刚看到的那样）或是一个索引识别码（Index ID）。在提示中利用索引识别码时有些特殊的情况，如下表所示。
提示 结果
INDEX(0)使用在一个丛集数据表上（此数据表上存在一个丛集索引） 强制执行一个丛集索引扫瞄
INDEX(1)使用在一个丛集数据表上 强制执行一个丛集索引扫瞄或搜寻
INDEX(0)使用在一个非丛集数据表上（此数据表上没有丛集索引） 强制执行一个数据表扫瞄
INDEX(1)使用在一个非丛集数据表上 会像发生错误一样被中断
您可利用SQL Server Query Analyzer来执行您的查询，便可检视使用提示的结果。
使用Query Analyzer
在 第13章 中，您已经学到Query Analyzer是SQL Server 2000中一个很有用的工具。接着我们要再次利用这个工具来看一下它如何显示出查询执行计划使用的是哪一个索引。Query Analyzer还可以用在其它的任务中，如下：
• 执行SQL查询 您可以使用一个便于使用的GUI来执行SQL陈述式并检视它的结果。
• 分析查询的语法 在尚未执行前便分析此SQL陈述式的语法，您可以藉此发现错误并修正它。
• 显示估计的执行计划 透过显示执行计划，您可以看到不同的查询如何影响着执行耗用。这在最佳化SQL陈述式中非常有价值，因为允许您重新编写您的SQL叙述，并看看其执行成本是否有所改变。
• 执行索引分析 索引分析可以告诉您使用索引是否会增加一个查询的执行耗用。
要试验一下Query Analyzer，请将下列T-SQL陈述式载入到Query Analyzer之中：
SELECT *
FROM Customers
WHERE Region = 'OR' AND City = 'Portland'
现在请于 查询 下拉式菜单中选择 显示估计的执行计划 ，便可检查估计的执行计划。在图17-23中您可看到已使用City索引的情形。
图17-23 在没有提示时使用City索引的估计的执行计划
现在让我们新增一个提示来指示SQL Server使用Region索引。查询如下：
SELECT *
FROM Customers With (Index(Region))
WHERE Region = 'OR' AND City = 'Portland'
此查询的估计的执行计划如图17-24所示。请注意现在使用的是Region索引。
图17-24 使用提示指定Region索引的估计的执行计划
在执行SQL陈述式时，SQL Server Query Analyzer是个相当好用且便利的工具，它不仅提供了一个GUI，也让您能从语法上或其它方面来分析SQL陈述式。对于那些可利用指令码来执行的操作，您只要在 档案 下拉式菜单中选择 存盘 便能在Query Analyzer中将您的工作存成指令码。
设计有效的索引
索引的效力－也就是最大效率与效能－是由索引与利用该索引之SQL陈述式的设计来决定。光是把索引建立起来并不足以发挥索引的能力，您的SQL陈述式也必须抓得住索引的重点。只有在SQL陈述式的WHERE子句中包含一个或多个索引键时，索引才会有用。在本节中，您会学到一个好的索引的属性，以及建立索引的最佳与最差时机。
有效索引的特征
一如我们所看到的，一个好的索引能让您在撷取数据时仅需使用较少的I/O操作与系统资源便能解决问题，而不需利用数据表扫瞄。由于索引扫瞄需要巡览索引树以找出个别的值，当您在撷取大量数据时使用索引并不会更有效率。
________________________________________
说明
如果查询存取的数据列超过数据表的2 0%，数据表扫瞄就会比使用索引更有效率。
________________________________________
一个有效的索引仅撷取少量的数据列－事实上，大部分的查询仅使用少量的数据列便可结束。要执行得更有效率，索引必须被设计成具有良好的选择性。索引的 选择性 （Selectivity）奠基于每个索引键值牵涉到的数据列数量。一个选择性差的索引，每个索引键值有多个数据列；选择性较好的索引，每个索引键值仅有数个或一个数据列。唯一性索引具有最高的选择性。索引的选择性储存在索引的离散统计数据之中。您可利用 DBCC SHOW_STATISTICS 命令来检视索引的选择性。查询最佳化器较可能使用具有良好选择性的索引。
您可以使用多个数据行来建立一个复合索引，以增进索引的选择性。复合索引中数个选择性较差的数据行可以被结合起来，构成一个具有良好选择性的索引。虽然唯一索引能提供最好的选择性，但请确定所选的索引类型的确适合您的数据模型。举例来说，如果您的Customers数据表中有不少的项目其 last name 均为「Smith」，您可能就无法在 last name 上建立唯一索引，但您仍然可以发现索引的有用之处。
何时使用索引
在下列工作中使用索引最为合适：
• 指定一个特定搜寻标准的查询。这些查询仅需检索少数符合指定标准的数据列。
• 指定一个数值范围的查询。这些查询应该也只检索少量的数据列。
• 在联结中使用的搜寻。常用来当作联结关键的数据行是索引的良好候选键。
• 以特定顺序检索数据的搜寻。如果结果集的数据必须以丛集索引的顺序来排序，排序动作就不需执行，因为结果集会在传回前预先排序。举例来说，如果丛集索引是在 lastname 和 firstname 数据行之上，而应用程序要求按 lastname 和 firstname 来排序，就不需再增加ORDER BY子句。
当数据表必须执行大量的插入、更新或删除动作时，就应更谨慎并有节制地使用索引，因为每个会造成数据变更的作业也都需要更新索引分页。
索引指导原则
您应遵循下列索引指导原则，以增进系统的效能与效率：
• 适度使用索引 有一些索引可能相当不错，但若索引过多时反而可能有害于系统效能。由于系统必须维护索引，数据表上每一次执行插入、更新或删除操作，索引都需更新。如果这类操作非常多，维护索引的系统负担就会相当沉重。
• 不要对小型数据表使用索引 若数据表并不大（比方说，仅有数百个数据列），有时使用数据表扫瞄会更有效率。使用索引造成的额外负担与其带来的好处相比，其实并不值得。
• 尽可能使用较少的索引键数据行以获得良好的选择性 数据行越少越好，但不是指以牺牲选择性作为代价。拥有较少数据行的索引称为窄索引（narrow index），而拥有许多关键值数据行的索引称为宽索引（wide index）。窄索引比宽索引占用更少的空间。
• 尽可能使用涵盖查询 涵盖查询 （Covering Query）是一种将所有想查询的数据都包含在索引键中的查询方式，也就是说，所有的索引键也是所有选取的数据行。在涵盖查询中，只有索引被存取，而跳过数据表本身。 涵盖索引 （Covering Index）是一个包含了所有数据表数据行的索引。举例来说，如果索引建立在a、b和c数据行上，而SELECT陈述式只从这些数据行上查询数据，那么就只需要存取这个索引。
本章总结
使用索引是改善数据库效能的一个好方法。在本章中，您已经学到SQL Server索引的相关知识，包括索引术语与概念、索引建立程序，以及使用索引的方法。您也学到了丛集与非丛集索引、唯一性与非唯一性索引，以及全文检索索引的相关知识。我们也介绍了分页分隔与填满因子的概念、重新建置索引，以及更新索引统计数据。在 第18章 中，您会学到如何建立与使用检视，这是另一种型态的辅助结构，可用来建立数据表的子集与超集。
