数据库性能优化第二部分（ 索引维护）
4.96/5 (53投票s)
在本系列文章的第二部分，关于数据库性能优化，我将讨论索引维护。
引言
在我关于数据库性能调整的第一篇文章中，我描述了索引策略，并提到了在设计索引时应牢记的一些方面。现在，我将继续讨论数据库调整。在这第二篇文章中，我将告诉您关于索引维护的内容。
索引在 SQL Server 中有其存在的价值，这一点并不新鲜。实施索引并非易事，因为设计良好的索引策略可以快速提高数据库性能，但另一方面，糟糕的索引可能会导致性能下降。就像数据库中的几乎所有事物都需要维护一样，索引也需要。索引维护是一项不常见且非常困难的任务，因为某些操作可能耗时很长，您在计划维护时必须牢记这一点。
作为 DBA，您必须确保数据库的最佳性能。如上所述，索引维护是关键任务之一。创建良好的索引意味着最大限度地减少 I/O 操作。作为 DBA，您应该执行一些活动：
- 重组和重建索引。此过程涉及索引碎片整理和磁盘空间优化。
- 使用 Fill factor 选项微调索引数据存储和性能。
- 通过使用 ONLINE 索引选项在线执行索引操作，以便在索引操作期间向用户提供数据访问。
- 通过使用 max degree of parallelism 选项配置并行索引操作。
- 使用查询优化器根据统计信息开发最佳查询计划。
- 提高具有聚集索引和非聚集索引的表上的批量复制操作的性能。
- 选择合适的数据库恢复模型用于索引操作，并列出可用的日志记录类型。
索引选择
在索引选择领域，理解聚集索引非常容易。聚集索引基本上是唯一引用每一行的键。即使您定义了聚集索引并且没有将其声明为唯一，SQL Server 仍然会在后台通过添加一个 4 字节的“uniqueifier”来使聚集索引成为唯一的。额外的“uniqueifier”增加了聚集索引的宽度，导致维护时间增加和搜索速度变慢。由于聚集索引是标识每行的键，因此它们在每个查询中使用。
当我们开始谈论非聚集索引时，情况就变得复杂了。查询可能会因为以下原因而忽略非聚集索引：
- 高碎片率 – 如果索引碎片率超过 40%，优化器可能会忽略该索引，因为搜索碎片索引比执行表扫描成本更高。
- 唯一性 – 如果优化器确定非聚集索引的唯一性不高，它可能会认为表扫描比尝试使用非聚集索引更快。例如：如果查询引用了一个位列（bit column，其中 bit = 1），并且该列上的统计信息显示 75% 的行是 1，那么优化器可能会认为表扫描比尝试扫描非聚集索引更快地获得结果。
- 过时的统计信息 – 如果列上的统计信息过时，SQL Server 可能会误导非聚集索引的优势。自动更新统计信息不仅会减慢您的数据修改脚本，而且随着时间的推移，它还会与行的实际统计信息不同步。有时，运行 sp_updatestats 或 UPDATE STATISTICS 是个好主意。
- 函数使用 – 如果条件中存在函数，SQL Server 无法使用索引。如果您引用了非聚集索引列，但使用了诸如 convert(varchar, Col1_Year) = 2004 之类的函数，那么 SQL Server 就不能使用 Col1_Year 上的索引。
- 错误的列 – 如果非聚集索引定义在 (col1, col2, col3) 上，而您的查询有一个 where 子句，例如“where col2 = 'somevalue'”，则该索引不会被使用。只有当索引中的第一个列在 where 子句中被引用时，才能使用非聚集索引。where 子句，例如“where col3 = 'someval'”，将不会使用该索引，但 where 子句，如“where col1 = 'someval'”或“where col1='someval and col3 = 'someval2'”将能够选中该索引。
- 由于该列在索引定义中不是 col1 之后的，因此该索引不会对 col3 进行查找。如果您希望在这种情况发生时 col3 进行查找，那么最好定义两个独立的非聚集索引，一个在 col1 上，另一个在 col3 上。
页面拆分
为了存储数据，SQL Server 使用 8 KB 的数据块页面。填充页面的数据量称为填充因子，填充因子越高，8 KB 页面就越满。更高的填充因子意味着需要的页面更少，从而减少 IO/CPU/RAM 使用。此时，您可能希望将所有索引的填充因子设置为 100%；但是，这里有一个陷阱：一旦页面填满，并且有一个值进来，该值适合已填满的索引范围，那么 SQL Server 将通过“页面拆分”来在索引中创建空间。
本质上，SQL Server 将满页拆分成两个单独的页面，这两个页面此时有更多的空间。您可以通过将填充因子设置为 70% 左右来解决此问题。这允许 30% 的可用空间用于传入值。这种方法的问题在于，您必须不断地“重新索引”索引，以便它保持 30% 的可用空间百分比。
碎片
虽然索引可以使查询执行速度提高数倍，因为它们可以使查询过程更快，但它们也有相关的开销。它们会占用额外的磁盘空间，并在每次更新、删除或追加表中的数据时需要额外的时间进行更新。此外，当您执行任何数据修改操作（INSERT、UPDATE 或 DELETE 语句）时，可能会发生索引碎片，并且索引中的信息可能会在数据库中分散。碎片化的索引数据会导致 SQL Server 执行不必要的数据读取和跨页面切换，因此对高度碎片化的表的查询性能可能非常差。在本文中，我将讨论碎片化以及确定碎片化程度的不同查询。碎片主要有两种类型：逻辑碎片（外部碎片）和 SQL Server 碎片。
逻辑碎片（外部）
逻辑碎片发生在索引叶页面不按逻辑顺序时，换句话说，当索引的逻辑顺序与索引的物理顺序不匹配时发生。这会导致 SQL Server 执行额外的操作来返回有序结果。对于返回很少记录的特定搜索或不需要排序结果集的查询，外部碎片在很大程度上不成问题。在以下情况下可能会发生页面拆分：
- 插入或更新
- 大量删除可能导致页面从页面链中删除，从而导致页面链不连续
SQL Server 碎片（内部）
当索引页面中有过多的可用空间时，会出现此类碎片。通常，需要一些可用空间，尤其是在创建或重建索引时。您可以在创建或重建索引时指定 Fill Factor 设置，以指示创建时索引页面的填充百分比。如果索引页面碎片过多，查询将花费更长的时间（因为需要额外的读取来查找数据集），并且您的索引将比必要时更大。如果索引数据页面中没有可用空间，数据更改（主要是插入）将导致页面拆分，如上所述，这也需要额外的系统资源来执行。内部碎片通常发生在：
- 随机删除导致数据页面上出现空隙
- 由于插入或更新而发生的页面拆分
- 行收缩，例如将大值更新为小值
- 使用小于 100 的填充因子
- 使用大行大小
区碎片
当表或索引的区与数据库不连续时，会发生区碎片，导致一个或多个索引的区在文件中交错。这可能由于以下原因发生：
- 随机删除，这可能导致区中的某些页面未被使用，而区本身仍被保留为表空间分配的一部分。将其想象成内部碎片，但发生在区而不是页面上。
- 在表中的连续行范围内删除，导致一个或多个整个区被释放，从而在表或索引的周围区之间留下了空隙。
- 表的数据区与其他对象的区交错。
如何检查碎片
高度碎片化的索引会严重降低查询性能，并导致访问它的应用程序响应缓慢。为了帮助您识别碎片量，SQL Server 2005 和 2008 提供了 sys.dm_db_index_physical_stats 动态管理函数。
以下查询返回 AdvendureWorks2008R2 数据库碎片级别的详细信息。
select
OBJECT_NAME(stats.object_id) as [Object_Name],
idx.name as [Index_Name] ,
stats.avg_fragmentation_in_percent,
stats.avg_page_space_used_in_percent
from
(select OBJECT_ID, index_id, avg_fragmentation_in_percent, avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats (DB_ID('AdventureWorks2008R2'),null,null,null,'DETAILED')
where index_id<>0) as stats
join sys.indexes idx
on idx.object_id=stats.object_id
and idx.index_id = stats.index_id
您需要关注两个值。第一个是 avg_fragmentation_in_percent 列，用于识别外部碎片；第二个是 avg_page_space_used_in_percent，用于识别内部碎片。当索引的 avg_fragmentation_in_percent 值大于 10 时，表示该索引高度碎片化。当 avg_page_space_used_in_percent 值低于 75 时，表示内部碎片。解决碎片
当您想解决某些碎片问题时，SQL Server 为您提供了两种解决问题的方法：ALTER INDEX ... REORGANIZE 或 ALTER INDEX ... REBUILD。
重组索引
在 SQL Server 2005 中，ALTER INDEX REORGANIZE 语句取代了 DBCC INDEXDEFRAG 语句。可以使用此语句重组分区索引的单个分区。
此语句重组表和视图上的聚集索引和非聚集索引的叶级别，并重新排序以匹配逻辑顺序——即从左到右的叶节点。索引在分配的页面内进行组织，如果它们跨越多个文件，则一次重组一个文件。不会在文件之间迁移页面。此外，页面将被压缩，并且由此产生的空页面将被删除并释放磁盘空间。压缩由 sys.indexes 目录视图中的填充因子值确定。如果存在 LOB 子句，聚集索引或底层表中包含的大对象数据类型也将默认被压缩。
好消息是重组过程对系统资源来说是经济的，并且是自动在线执行的。没有长期阻塞锁可以妨碍工作！
建议 DBA 在索引碎片最少时重组索引。高度碎片化的索引需要重建。Microsoft 建议当 60 < avg_page_space_used_in_percent < 75 或 5 < avg_fragmentation_in_percent < 30 时重组索引。
重建索引
当重建索引时，它会被删除并创建一个新的。这个过程非常消耗资源，并消除外部和内部碎片。外部碎片通过重新排序索引行到连续页面来消除。内部碎片通过填充因子来消除。您可以使用 ONLINE 选项，该选项使表及其索引可用于选择和数据修改。
重建索引期间的表锁
当您重建索引时，默认情况下 SQL Server 会锁定表以防止任何数据修改。要覆盖此行为，您可以使用 ONLINE 选项。在这种情况下，SQL Server 会分 3 个阶段执行索引重建。
- 准备阶段
- 构建
- 完成
在准备阶段，收集所有系统元数据以创建新的空索引结构。定义表的快照，并使用行版本控制来提供事务级别的读一致性。表上的任何并发写操作都会被阻止很短时间。
在构建阶段，数据被扫描、排序、合并并插入到目标中。并发用户选择、插入、更新和删除操作会同时应用于现有索引和任何正在构建的新索引。
在完成阶段之前，所有未提交的更新事务必须完成。所有读写操作都会被阻止很短时间，直到此阶段完成。系统元数据将被更新以用目标对象替换源对象。
并行索引重建
SQL Server 2005 企业版支持通过以下索引创建和重建语句使用多个处理器：
CREATE INDEXDROP INDEX（仅用于聚集索引）ALTER TABLE ADD CONSTRAINT（仅用于索引约束）ALTER TABLE DROP CONSTRAINT（仅用于聚集索引）
MAXDOP 查询提示对索引操作施加进一步的处理器使用限制，例如，以下语句将 ALTER INDEX 语句限制为 2 个处理器：
ALTER INDEX PK_DimProduct_ProductKey ON DimProduct REBUILD WITH (MAXDOP = 2)
如果 MAXDOP 提示指定 1，则所有索引操作将按顺序进行，而不是并行进行；0 是默认值，不限制处理器数量。任何大于 1 的值将被解释为要考虑的处理器数量，但是，实际用于构建索引的处理器数量可能会根据系统负载而较低。
并行索引操作可能对非对齐分区索引特别消耗资源。这是因为 SQL Server 必须为每个分区构建一个排序表（根据是否使用了 SORT_IN_TEMPDB 选项，在存储分区的相应文件组或在 tempdb 中）。如果索引与表分区方案或聚集索引对齐，则为每个分区按顺序构建排序表。但对于非对齐索引，除非将最大并行度设置为 1，否则所有排序表将一次性构建。并行度越高，非对齐分区索引需要同时构建的排序表越多，因此需要的内存也越多。如果系统没有足够的内存来为具有多个分区的表的索引创建足够的排序表，操作将失败。如果是这样，您通常可以通过指定较低的并行度来解决此问题。
如何重建数据库中的所有索引
正如我之前提到的，DBA 的关键任务之一是维护索引。有时索引需要重建。在索引数量很多的情况下，这项任务可能非常耗时。ALTER INDEX 语句允许您重建索引，但您必须指定要为其重建索引的对象名称。使用 ALTER INDEX ALL 选项允许您为指定表或视图重建所有索引，但如果您有很多表怎么办？这可能是一个问题，因为您必须为所有要重建索引的表或视图创建 ALTER 语句。在这里，我为您提供一个用于重建所有表和数据库的所有索引的脚本。
DECLARE @Database VARCHAR(255)
DECLARE @Table VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 90
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','msdb','tempdb','model','distribution')
ORDER BY 1
OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +
table_name + '']'' as tableName FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES
WHERE table_type = ''BASE TABLE'''
-- create table cursor
EXEC (@cmd)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@@MICROSOFTVERSION / POWER(2, 24) >= 9)
BEGIN
-- SQL 2005 or higher command
SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' +CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@cmd)
END
ELSE
BEGIN
-- SQL 2000 command
DBCC DBREINDEX(@Table,' ',@fillfactor)
END
FETCH NEXT FROM TableCursor INTO @Table
END
CLOSE TableCursor
DEALLOCATE TableCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
此脚本将重建所有表和数据库的所有索引。如果您只想为某些数据库重建索引，只需重写
WHERE name NOT IN ('master','msdb','tempdb','model','distribution')
toWHERE name IN ('myDB1','myDB2')
此脚本由 Greg Robidoux 创建，您可以在此处找到更多信息。
索引使用信息
索引可以快速影响数据库性能。太少的索引会导致扫描，从而急剧增加应用程序的响应时间。太多的索引会导致数据更新期间的索引维护开销以及数据库膨胀。很难知道哪些索引正在被使用以及如何被使用。有时摆脱未使用的索引非常好。对于此类任务，SQL Server 提供了许多新的动态管理视图。提供索引使用数据的两个新视图是 sys.dm_db_index_operational_stats 和 sys.dm_db_index_usage_stats。
sys.dm_db_index_operational_stats
此动态管理视图为您提供有关特定索引的插入、更新和删除操作的信息。此 DMV 还提供有关锁定、闩锁和访问方法的信息。从这类视图返回的几个列是：
leaf_insert_count- 叶级别插入的总数leaf_delete_count- 叶级别删除的总数leaf_update_count- 叶级别更新的总数row_lock_count- 行锁的总数row_lock_wait_count- 数据库引擎等待行锁的总次数page_latch_wait_count- 由于闩锁争用，数据库引擎等待的总次数
SELECT OBJECT_NAME(A.[OBJECT_ID]) AS [OBJECT NAME],
I.[NAME] AS [INDEX NAME],
A.LEAF_INSERT_COUNT,
A.LEAF_UPDATE_COUNT,
A.LEAF_DELETE_COUNT
FROM SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL,NULL,NULL,NULL ) A
INNER JOIN SYS.INDEXES AS I
ON I.[OBJECT_ID] = A.[OBJECT_ID]
AND I.INDEX_ID = A.INDEX_ID
WHERE OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1
这是上述查询的输出。从这个视图中，我们可以大致了解每个表和索引上执行了多少插入、更新和删除操作。
sys.dm_db_index_usage_stats
此视图为您提供有关索引的整体访问方法的信息。此 DMV 返回几个列，但以下是一些有关索引使用的有用列：
user_seeks- 索引查找的数量user_scans- 索引扫描的数量user_lookups- 索引查找的数量user_updates- 插入、更新或删除操作的数量
SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
I.[NAME] AS [INDEX NAME],
USER_SEEKS,
USER_SCANS,
USER_LOOKUPS,
USER_UPDATES
FROM SYS.DM_DB_INDEX_USAGE_STATS AS S
INNER JOIN SYS.INDEXES AS I
ON I.[OBJECT_ID] = S.[OBJECT_ID]
AND I.INDEX_ID = S.INDEX_ID
WHERE OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1
这是上述查询的输出。从这个视图中，我们可以大致了解发生了多少查找、扫描、检索以及总共多少更新（插入、更新和删除）。
以下示例返回所有未使用的索引。
SELECT
DB_NAME() AS DatabaseName,
OBJECT_NAME(idx.OBJECT_ID) AS TableName,
idx.NAME AS IndexName,
idx.index_id
FROM
sys.objects obj
JOIN sys.indexes idx ON obj.OBJECT_ID = idx.OBJECT_ID
WHERE
not exists (SELECT
stats.index_id
FROM
sys.dm_db_index_usage_stats stats
WHERE idx.OBJECT_ID = stats.OBJECT_ID
AND idx.INDEX_ID = stats.INDEX_ID) AND obj.TYPE <> 'S'
ORDER BY DatabaseName, TableName, IndexName
在此查询中，我们列出了每个用户表、它的所有索引以及构成索引的列。此查询的问题在于，索引中的每个列都会有一行，如果您有很多索引，这可能会令人困惑。
SELECT
obj.name as TableName,
idx.name as IndexName,
idxcols.key_ordinal,
cols.name as ColumnName
FROM
sys.objects obj
join sys.indexes idx
ON obj.object_id = idx.object_id
join sys.index_columns idxcols
ON idx.object_id = idxcols.object_id
and idx.index_id = idxcols.index_id
join sys.columns cols
ON idxcols.object_id = cols.object_id
and idxcols.column_id = cols.column_id
WHERE
obj.TYPE <> 'S'
ORDER BY TableName, IndexName, key_ordinal
在此查询中，我们使用了大部分查询 3，但我们进行了 PIVOT 操作，以便在一行中看到索引和索引列。这只处理了 7 个索引列，但可以在 PIVOT 操作中轻松增加以处理更多。这里有另一个与 PIVOT 使用相关的技巧，SQL Server 2005 中的 PIVOT、Crosstab 查询。如果您想更好地理解 PIVOT 的工作原理。
SELECT TABLENAME, INDEXNAME, INDEXID, [1] AS COL1, [2] AS COL2, [3] AS COL3,
[4] AS COL4, [5] AS COL5, [6] AS COL6, [7] AS COL7
FROM (SELECT A.NAME AS TABLENAME,
B.NAME AS INDEXNAME,
B.INDEX_ID AS INDEXID,
D.NAME AS COLUMNNAME,
C.KEY_ORDINAL
FROM SYS.OBJECTS A
INNER JOIN SYS.INDEXES B
ON A.OBJECT_ID = B.OBJECT_ID
INNER JOIN SYS.INDEX_COLUMNS C
ON B.OBJECT_ID = C.OBJECT_ID
AND B.INDEX_ID = C.INDEX_ID
INNER JOIN SYS.COLUMNS D
ON C.OBJECT_ID = D.OBJECT_ID
AND C.COLUMN_ID = D.COLUMN_ID
WHERE A.TYPE <> 'S') P
PIVOT
(MIN(COLUMNNAME)
FOR KEY_ORDINAL IN ( [1],[2],[3],[4],[5],[6],[7] ) ) AS PVT
ORDER BY TABLENAME, INDEXNAME;
在此查询中，我们将上述 PIVOT 查询与 sys.dm_db_index_usage_stats 结合起来，以便我们只能查看自上次重置统计信息以来已使用的索引。
SELECT TABLENAME, INDEXNAME, INDEX_ID, [1] AS COL1, [2] AS COL2, [3] AS COL3,
[4] AS COL4, [5] AS COL5, [6] AS COL6, [7] AS COL7
FROM (SELECT A.NAME AS TABLENAME,
A.OBJECT_ID,
B.NAME AS INDEXNAME,
B.INDEX_ID,
D.NAME AS COLUMNNAME,
C.KEY_ORDINAL
FROM SYS.OBJECTS A
INNER JOIN SYS.INDEXES B
ON A.OBJECT_ID = B.OBJECT_ID
INNER JOIN SYS.INDEX_COLUMNS C
ON B.OBJECT_ID = C.OBJECT_ID
AND B.INDEX_ID = C.INDEX_ID
INNER JOIN SYS.COLUMNS D
ON C.OBJECT_ID = D.OBJECT_ID
AND C.COLUMN_ID = D.COLUMN_ID
WHERE A.TYPE <> 'S') P
PIVOT
(MIN(COLUMNNAME)
FOR KEY_ORDINAL IN ( [1],[2],[3],[4],[5],[6],[7] ) ) AS PVT
WHERE EXISTS (SELECT OBJECT_ID,
INDEX_ID
FROM SYS.DM_DB_INDEX_USAGE_STATS B
WHERE DATABASE_ID = DB_ID(DB_NAME())
AND PVT.OBJECT_ID = B.OBJECT_ID
AND PVT.INDEX_ID = B.INDEX_ID)
ORDER BY TABLENAME, INDEXNAME;
此查询还使用了 PIVOT 查询以及 sys.dm_db_index_usage_stats，因此我们还可以查看已使用索引的统计信息。
SELECT PVT.TABLENAME, PVT.INDEXNAME, [1] AS COL1, [2] AS COL2, [3] AS COL3,
[4] AS COL4, [5] AS COL5, [6] AS COL6, [7] AS COL7, B.USER_SEEKS,
B.USER_SCANS, B.USER_LOOKUPS
FROM (SELECT A.NAME AS TABLENAME,
A.OBJECT_ID,
B.NAME AS INDEXNAME,
B.INDEX_ID,
D.NAME AS COLUMNNAME,
C.KEY_ORDINAL
FROM SYS.OBJECTS A
INNER JOIN SYS.INDEXES B
ON A.OBJECT_ID = B.OBJECT_ID
INNER JOIN SYS.INDEX_COLUMNS C
ON B.OBJECT_ID = C.OBJECT_ID
AND B.INDEX_ID = C.INDEX_ID
INNER JOIN SYS.COLUMNS D
ON C.OBJECT_ID = D.OBJECT_ID
AND C.COLUMN_ID = D.COLUMN_ID
WHERE A.TYPE <> 'S') P
PIVOT
(MIN(COLUMNNAME)
FOR KEY_ORDINAL IN ( [1],[2],[3],[4],[5],[6],[7] ) ) AS PVT
INNER JOIN SYS.DM_DB_INDEX_USAGE_STATS B
ON PVT.OBJECT_ID = B.OBJECT_ID
AND PVT.INDEX_ID = B.INDEX_ID
AND B.DATABASE_ID = DB_ID()
ORDER BY TABLENAME, INDEXNAME;
所有这些示例均摘自 http://www.mssqltips.com/tip.asp?tip=1545
历史
- 8月22日 - 发布原始版本