第七章 索引

    小天：在创建索引之后，由于频繁地对数据进行增加、删除、修改等操作使得索引页发生碎块，因此，必须对索引进行维护。我想这个应该不会是简单的删除和属性修改哦，最起码得有些可以对已有索引优化的工具吧？

    老田：说的好，我们来看看针对维护索引的一些办法。这里说维护不是指单纯地修改、删除哦。

    7.6.1  查看索引碎片

    使用DBCC SHOWCONTIG语句可以显示表的数据和索引的碎块信息。当执行DBCC SHOWCONTIG语句时，SQL Server浏览叶级上的整个索引页，来确定表或者指定的索引是否严重碎块。DBCC SHOWCONTIG语句还能确定数据页和索引页是否已经满了。当对表进行大量的修改或者增加大量的数据之后，或者表的查询非常慢时，应该在这些表上执行DBCC SHOWCONTIG语句。当执行DBCC SHOWCONTIG语句时，应该考虑这些因素：当执行DBCC SHOWCONTIG语句时，SQL Server要求指定表的ID号或者索引的ID号，表的ID号或者索引的ID号可以从系统表sysindexes中得到；应该确定多长时间使用一次DBCC SHOWCONTIG语句，这个时间长度要根据表的活动情况来确定，每天、每周或者每月都可以。 执行后效果如图7-9所示。

图7-9  显示表的数据和索引的碎块信息

    另外还有一种办法就是，直接在SQL Server Management Studio的“对象资源管理器”下面的“指定实例”展开“指定数据库”，再展开“表”目录，展开“指定的表”下面的“索引”目录，在要查看的索引上双击或者单击鼠标右键，在弹出的快捷菜单中选择“属性”命令，然后切换到碎片选项界面，如图7-10所示。

图7-10  查看索引碎片

    7.6.2  重建索引

    使用DBCC DBREINDEX语句重建表的一个或者多个索引。当希望重建索引和当表上有主键约束或者唯一性键约束时，执行DBCC DBREINDEX语句。除此之外，执行DBCC DBREINDEX语句还可以重新组织叶级索引页的存储空间、删除碎块和重新计算索引统计。当使用DBCC DBREINDEX语句时，应该考虑以下因素。

    *根据指定的填充度，系统重新填充每一个叶级页。

    *使用DBCC DBREINDEX语句重建主键约束或者唯一性键约束的索引。

    *使用SORTED_DATA_REORG选项可以更快地创建聚集索引，如果没有排列关键值，那么不能使用DBCC DBREINDEX语句。

    *DBCC DBREINDEX语句不支持系统表。

    另外，还可以使用数据库维护规划向导自动地进行重建索引的进程。

    下面是一个使用DBCC DBREINDEX重建ZONE表中索引的语句，执行效果如图7-11所示。

图7-11  执行重建索引命令

    7.6.3  统计信息

    统计信息是存储在SQL Server中列数据的样本。这些数据一般被用于索引列，并且还可以为非索引列创建统计。SQL Server维护某一个索引关键值的分布统计信息，并且使用这些统计信息来确定在查询进程中哪一个索引是有用的。查询的优化依赖于这些统计信息的分布准确度。查询优化器使用这些数据样本来决定是使用表扫描还是使用索引。当表中数据发生变化时，SQL Server周期性地自动修改统计信息。索引统计被自动地修改，索引中的关键值显著变化。统计信息修改的频率由索引中的数据量和数据改变量确定。例如，如果表中有10000行数据，当1000行数据修改了，那么统计信息可能需要修改。然而，如果只有50行记录修改了，那么仍然保持当前的统计信息。除了系统自动修改之外，用户还可以通过执行UPDATE STATISTICS语句或者sp_updatestats系统存储过程来手工修改统计信息。使用UPDATE STATISTICS语句既可以修改表中的全部索引，也可以修改指定的索引。

    --针对指定表更新所有关键值分布的信息

    UPDATE STATISTICS ZONE

    --针对指定表中的一个索引更新关键值分布的信息

    UPDATE STATISTICS ZONE USER_ID_INDEX

    使用SHOWPLAN_ALL和STATISTICS IO语句可以分析索引和查询性能。使用这些语句可以更好地调整查询和索引。SHOWPLAN_ALL语句显示在连接表中使用的查询优化器的每一步以及标明使用哪一个索引访问数据。使用SHOWPLAN_ALL语句可以查看指定查询的查询规划。当使用SHOWPLAN语句时，应该考虑这些因素。SET SHOWPLAN_ALL语句返回的输出结果比SET SHOWPLAN_TEXT语句返回的输出结果详细。然而，应用程序必须能够处理SET SHOWPLAN_ALL语句返回的输出结果。SHOWPLAN语句生成的信息只能针对一个会话。如果重新连接SQL Server，那么必须重新执行SHOWPLAN_ALL语句。

    以下两个语句使用了SET SHOWPLAN_ALL设置，用于显示SQL Server在查询中分析和优化索引的方式。

    执行如下Transact-SQL语句：

    执行效果如图7-12所示。

图7-12  显示 SQL Server 在查询中分析和优化索引的方式

    第一个查询在WHERE子句中使用针对索引列的等于比较运算符（=）。从而在LogicalOp列内得到Clustered Index Seek值，在Argument列内产生索引名。

    第二个查询在WHERE子句中使用LIKE运算符。这将强制SQL Server使用聚集索引扫描并查找满足WHERE子句条件的数据。从而在LogicalOp列内得到Clustered Index Scan值，在Argument列内生成索引名；在LogicalOp列内产生Filter值，在Argument 列内出现WHERE子句条件。

    第一个索引查询的EstimateRows和TotalSubtreeCost属性中的值较小，这表示与非索引查询相比，该查询的处理速度快得多且使用的资源更少。

    STATISTICS IO语句表明输入/输出的数量，这些输入/输出用来返回结果集和显示指定查询的逻辑的和物理的I/O信息。可以使用这些信息来确定是否重写查询语句或者重新设计索引。使用STATISTICS IO语句可以查看用来处理指定查询的I/O信息。

    以下示例显示SQL Server处理语句时，进行了多少次逻辑读和物理读操作。

    执行如下Transact-SQL代码：

    执行后切换到“消息”选项卡，看到效果如图7-13所示。

图7-13  查看执行查询的过程中进行了多少次逻辑读和物理读操作

    就像SHOWPLAN语句一样，优化器隐藏也用来调整查询性能。优化器隐藏可以对查询性能提供较小的改进，并且如果索引策略发生了改变，那么这种优化器隐藏就毫无用处了。因此，限制使用优化器隐藏，这是因为优化器隐藏更有效率和更有柔性。当使用优化器隐藏时，需要考虑这些规则：指定索引名称、当index_id为0时表示使用表扫描；当index_id为1时表示使用聚集索引。优化器隐藏覆盖查询优化器，如果数据或者环境发生了变化，那么必须修改优化器隐藏。