mysql 碎片整理速度 sqlserver碎片整理

转载

编程小天匠 2023-12-19 18:36:38

文章标签 mysql 碎片整理速度 sql server microsoft 磁盘工作 文章分类 MySQL 数据库

DSS型工作量

测试中，DSS工作量包含22个由复杂Select语句构成的报表类型的查询。这些查询严格地以批处理方式在服务器端运行。所有查询包含一个或多个多表联接，大多数查询需要扫描很大范围的索引。

表2 记录测试中用到的索引的平均碎片和页密度级别。碎片级别通过下属行为组合得到：

以Bulk Insert方式插入新数据到数据库，并模拟周期性地刷新数据。
删除某个范围内的数据。
按关键值执行一些更新操作，虽然这至少会影响碎片级别，不过和插入和删除操作相比，更新涉及到的记录相对还是比较少。

表2 小规模和大规模环境中平均逻辑碎片和页密度测试

Fragmentation level	Average logical fragmentation (%)	Average page density (%)
Small-Scale Environment
Low (1)	7.8	80.1
Medium (2)	16.6	68.1
High (3)	29.5	69.2
Large-Scale Environment
Low (1)	5.9	84.4
Medium (2)	13.8	70.3

DSS工作类型的测试结果和OLTP工作类型相差很大。在整理索引碎片后，性能提高很明显。因为此时工作量的性能强烈依赖于磁盘吞吐量(大多数查询会包含索引扫描)，因此该结果是可以预料到的。下面的图2和图3显示DSS型工作量在整理索引碎片前后的性能收益情况。如图中所示，性能从碎片整理中显著提升。

在小规模环境中，在碎片较低级别，性能提升了60％，而在碎片较高级别提升了460％。在大规模环境中，在碎片较低级别性能提升13%，在中等级别提升了40%。结果显示大规模环境中，碎片对性能影响影响相对较小，这是因为该环境从磁盘子系统的表现获益更多一些。更详细的讨论见本文后面的“碎片对磁盘吞吐量的影响和SQL Server预读管理器”章节。

图2: 小规模环境下，整个DSS型工作量在不同碎片级别下的运行时间。取值越低表示性能越好。

点击看原图

图3: 大规模环境下，整个DSS型工作量在不同碎片级别下的运行时间。取值越低表示性能越好。

点击看原图

图2从数值上显示，在小规模环境下，DBCC INDEXDEFRAG的结果比DBCC DBREINDEX要好一些。不过，在大多数情况下，完全重建索引应该具有更好的性能。

在解释这些测试结果时，需要牢记以下几点：

图2和图3显示的结果意味着这是应用DBCC INDEXDEFRAG的“最佳”场合。测试中，DBCC INDEXDEFRAG运行在一个禁止的系统上；因此DBCC INDEXDEFRAG可以完全消除碎片。当DBCC INDEXDEFRAG运行在一个动态的系统上，即数据保持在线更新，DBCC INDEXDEFRAG会跳过那些被锁住的页。因此DBCC INDEXDEFRAG也许就无法完全消除碎片。要衡量DBCC INDEXDEFRAG发挥了多大作用，可以在DBCC INDEXDEFRAG后立刻运行DBCC SHOWCONTIG。
数据的分布情况会影响磁盘性能。小规模环境中，数据只分布在两个物理磁盘(磁盘容量加起来一共33.5GB)上，在数据库创建前，这两个磁盘是空的。数据库创建后，数据文件大小在22GB到30GB之间。当数据库创建时，数据分布在磁盘外围部分。DBCC INDEXDEFRAG整理碎片也是从最邻近原始数据的位置开始。因为DBCC DBREINDEX完全重建索引，在释放旧的索引页前，它必须首先给新索引页分配空间。分配新空间使得数据离原始数据位置较远，并且分布在磁盘内侧，因此造成I/O吞吐量有轻微的下降。在小规模环境下的benchmark测试中，这种下降表现为读取数据吞吐量下降15%。
剩余空间容量同样会影响DBCC DBREINDEX。如果没有大量连续的空闲空间，DBREINDEX会强迫使用数据文件中的空闲空间，从而导致索引重建时带有一小部分数量的逻辑碎片。关于DBCC DBREINDEX对剩余空间的需求的信息，见本文后面的"DBCC DBREINDEX"章节。

确定查询的I/O流量

因为具有大量I/O读写的查询从碎片整理中获益最多，所以讨论如何确定某个特定查询的I/O流量是必要的。SET STATISTICS IO命令可以报告完成一个特定查询时，服务器实例上的读取量和读取类型。可以在查询管理器中选择该命令开关为ON和OFF，使用方法如下：

SET STATISTIC IO ON GO Select * FROM table_1 GO SET STATISTIC IO OFF GO

输出示例

Table ‘table_1′. Scan count 1, logical reads 12025, physical reads 0, read-ahead reads 11421.

表3 关于SET STATISTIC IO 输出结果的描述

Value	Description
Scan count	Number of scans performed
logical reads	Number of pages read from the data cache
physical reads	Number of pages read from disk
read-ahead reads	Number of pages placed into the cache for the query

通过physical reads和read-ahead reads值，可以对某个特定查询涉及的I/O量有一个估计。physical reads和read-ahead reads都表示从磁盘读取的页数。多数情况下，read-ahead reads比physical reads数值要大。

注意在通过SQL Profiler 获取信息时，reads列表示的是逻辑读取量(logical reads)，而不是物理读取量(physical reads)。

除了重新对页进行排序，通过增加索引叶级页的页密度，索引的碎片整理还降低执行某个查询的I/O数量。页密度的提高导致完成相同的查询需要读取更少的页，从而提高性能。

理解碎片整理带来的影响和SQL Server预读管理器

碎片对大量读取磁盘边缘的操作带来负面影响。可以使用Windows性能监视器来获取这种影响的衡量。通过性能监视器，可以观测磁盘活动情况，并且有助于决定何时进行碎片整理。

为了理解为什么碎片对DSS型工作具有如此的影响，首先很重要的是要理解碎片是如何影响SQL Server预读管理器的。为了完成需要扫描一个或多个索引的查询，SQL Server预读管理器负责提前对索引页进行扫描，并且将额外的数据页放到SQL Server数据缓存中。根据基础页的物理顺序，预读管理器动态地调整读取量。当碎片较少时，预读管理器即时可以读取较大的数据块，更高效地利用I/O子系统。当数据产生碎片时，预读管理器只能读取较小的数据块。预读的数量虽然和数据的物理顺序无关，不过，因为较小的读取请求消耗更多的CPU/时钟，所以最终就会降低整个磁盘的吞吐量。

在所有情况下，预读管理器能提高性能；但是，当存在碎片，且预读管理器无法读取较大的数据块，整个磁盘的吞吐量就下降。通过检查性能监视器中的 Physical Disk相关的计数器可以发现该现象。下表列举并描述了这些计数器。

表4 性能监视器中物理磁盘计数器

Physical Disk counter	Description
Avg Disk sec/ Read	该计数器用于衡量磁盘延迟。测试显示当碎片出于很高水平(大于等于30%)时，会增加磁盘延迟。
Disk Read Bytes/ sec	该计数器能很好地衡量全面的磁盘吞吐量。一段时间内工作量的下降趋势可以用来表示碎片正在影响性能。
Avg Disk Bytes/ Read	该计数器用于衡量每个读取请求带来的数据读取量。当索引页连续，SQL Server预读管理器可以一次读取较大的数据块，对I/O子系统的利用效率较高。测试显示该计数器值和碎片数量间有着直接联系。当碎片级别上升，该值就下降，从而影响全面的磁盘吞吐量。
Avg Disk Read Queue Length	一般而言，该计数器为每两个物理磁盘上持续的平均数值。测试中，很可能由于较高的延迟和较低的全面磁盘吞吐量，使得该计数器随着碎片的增加而增加。

图4到图7显示在DSS型工作中，性能监视器报告的磁盘吞吐量和平均读取大小。

图4: 小规模环境下，DSS工作流的磁盘吞吐量。数值越高表示磁盘吞吐能力越好。

点击看原图

图5: 小规模环境下，DSS工作流的磁盘吞吐量。数值越高表示磁盘吞吐能力越好。

点击看原图

图6: 小规模环境下，DSS工作中每次磁盘读取的平均大小。数值越高表示每次读取字节数越多。

点击看原图

图7: 小规模环境下，DSS工作中每次磁盘读取的平均大小。数值越高表示每次读取字节数越多。

点击看原图

上面的图显示碎片对磁盘性能的影响趋势。虽然使用DBCC DBREINDEX和DBCC INDEXDEFRAG得到的结果不同，但是注意在所有系统上，都得到了一致的结果，即每次读取的平均大小和整体磁盘吞吐量随着碎片的增加而降低。正如你所见的，整理所有碎片对磁盘吞吐量提高极大。

每次读取的平均大小还可以用来展示，碎片是如何影响预读管理器读取较大数据块的能力的。这点需牢记在心，不过，平均读取数量较大并不总是意味着整体吞吐量较高。平均读取量大表示数据传输中，CPU负荷较小。当索引没有碎片时，读取64KB大小数据的速度和读取256KB大小数据的速度几乎一样。这个结论在那些数据分布在多个磁盘上的大型系统而言，尤其如此。这些普遍和特殊的结论，会根据不同的系统，因为各种各样的原因(例如，不同的I/O子系统，工作类型差异，数据在磁盘的分布特征等等)而不同。当监视系统时，寻找那些持续时间较长的磁盘吞吐量和读取数量的下降阶段。这些阶段以及DBCC SHOWCONTIG命令提供的信息，可以用于帮助来确定什么时候该进行索引的碎片整理了。

测试结果显示碎片也会带来磁盘延迟，不过，至于那些最高级别的碎片才会对磁盘延迟带来巨大的负面影响，并且也只在小规模环境下才有此现象。在大规模环境下，由于SAN提供了很高的I/O性能，因此磁盘延迟值十分小而从来不会带来问题。

DBCC DBREINDEX vs. DBCC INDEXDEFRAG

除了使用Create INDEX命令来删除或者重新创建索引，还可以使用DBCC DBREINDEX和DBCC INDEXDEFRAG命令来帮助维护索引。

DBCC DBREINDEX

DBCC DBREINDEX用于在指定的表上重建一个或多个索引。DBCC DBREINDEX是离线操作方式。当该操作运行时，涉及到的表就无法被用户访问。DBCC DBREINDEX动态地重建索引。没有必要知道参与重建的表结构到底如何，是否用主键或者唯一性约束等信息；重建的时候会自动管理的。DBCC DBREINDEX完全重建索引，也就是说，将页密度级别恢复到最初(默认)的填充因子水平；当然你也可以选择页密度的新值。从内部运行看，DBCC DBREINDEX和手工用T-SQL语句来运行删除然后重新创建索引十分相似。

下面两点是DBCC DBREINDEX比DBCC INDEXDEFRAG优越的地方：

DBCC DBREINDEX在重建索引过程中，自动重建统计；这将显著提高工作性能。
DBCC DBREINDEX可以运行在多处理器环境下，利用多处理器的优势，当重建较大和碎片厉害的索引时，速度可以十分快。

DBCC DBREINDEX的所有工作是一个单一的，原子事务。必须完成创建新的索引并替换旧索引，然后旧索引页被释放。完成重建需要数据文件中有足够的空余空间。如果空余空间不够，DBCC DBREINDEX要么无法重建索引，要么会产生大于0的逻辑碎片。所需空余空间视情况而定，取决于事务中要创建的索引数目。对于聚集索引而言，一个不错的指导公式为：所需空余空间 = 1.2 * (平均行大小) * (行数量)。

对于非聚集索引，可以通过计算非聚集索引包含的每行平均大小(非聚集索引包含关键字长度 + 聚集中的索引关键字或者row ID长度)乘以行数量得到所需空间。如果对整个表进行索引重建，需要为聚集索引和非聚集索引留出足够的空间。同样，如果重建不唯一非聚集索引，也需要为聚集和非聚集索引留出空余空间。因为SQL Server必须为每行创建唯一标识，因此非聚集索引是隐式重建的。使用DBCC DBREINDEX时，较佳的做法是指定那些索引需要整理。这样做可以使得对操作有更多的控制力，以及避免不必要的麻烦。

DBCC INDEXDEFRAG

DBCC INDEXDEFRAG用于对指定的索引进行重建。和DBCC DBREINDEX类似，也不需顾及表的基础结构；不过，DBCC INDEXDEFRAG无法用一个语句对所有的索引进行重建。对于每个希望进行碎片整理的索引，都必须运行一次DBCC INDEXDEFRAG。

和DBCC DEREINDEX不同的是，DBCC INDEXDEFRAG是在线操作的；因此在整理索引碎片时，仍然可以访问被整理到的表和索引。另外一个和DBCC INDEXDEFRAG很不同的地方是，DBCC INDEXDEFRAG可以中止和重新开始而不丢失任何工作信息。整个DBCC DBREINDEX操作作为一个原子事务运行。这意味着如果中止DBCC DBREINDEX操作，整个操作会回滚，继续的话必须重新开始。但是，如果中止DBCC INDEXDEFRAG，任务会立刻中止并不会丢失已经完成的任务，因为DBCC INDEXDEFRAG每个工作单位是独立的事务。

DBCC INDEXDEFRAG包括两个阶段：

压缩页并试图根据索引创建时指定的填充因子来调整页密度。DBCC INDEXDEFRAG会根据最初的填充因子，尽可能提高页密度级别。而DBCC INDEXDEFRAG不会，但是它会将那些高于最初填充因子的页密度降低。
通过移动页来使得物理顺序和索引叶级页的逻辑顺序一致，从而整理索引碎片。这个工作由一系列独立的很小的事务来完成；因此DBCC INDEXDEFRAG对整体系统性能，影响很小。图8显示DBCC INDEXDEFRAG的碎片整理阶段中页移动情况。

图8: DBCC INDEXDEFRAG的数据文件页移动情况

DBCC INDEXDEFRAG并不会帮助整理分散插入到数据文件中的索引，这种分散插入称为Interleave。同样，DBCC INDEXDEFRAG也不对扩展页碎片进行整理。当索引扩展页(扩展页=8页)中的数据并不连续的时候，出现Interleave，此时多个扩展页的数据在文件中是交叉状态。因为即使逻辑顺序和物理顺序一致情况下，所有的索引页也不见得一定是连续的，因此即使没有逻辑碎片情况下，Interleave也会存在。

虽然上面提到了DBCC INDEXDEFRAG的限制，但是测试显示，DBCC INDEXDEFRAG对性能的改善和DBCC DBREINDEX一样有用。实际上，从测试结果可以看出，即使重建了索引，使得Interleave最小，这部分优化并不会对性能带来显著提升。减少逻辑碎片级别这部分优化才对性能提升最多。这就是为什么检查索引碎片时，建议将重点放在逻辑碎片整理和页密度碎片上的原因。表5总结了DBCC DBREINDEX和DBCC INDEXDEFRAG之间的差别。

表5 DBCC DBREINDEX 和 DBCC INDEXDEFRAG的比较

Functionality	DBCC DBREINDEX	DBCC INDEXDEFRAG
Online/Offline	Offline	Online
Faster when logical fragmentation is:	High	Low
Parallel processing	Yes	No
Compacts pages	Yes	Yes
Can be stopped and restarted without losing work completed to that point	No	Yes
Able to untangle interleaved indexes	May reduce interleaving	No
Additional free space is required in the data file for defragmenting	Yes	No
Faster on larger indexes	Yes	No
Rebuilds statistics	Yes	No
Log space usage	High in full recovery mode (logs entire contents of the index), low in bulk logged or simple recovery mode (only logs allocation of space)	Varies based on the amount of work performed
May skip pages on busy systems	No	Yes

性能: DBCC DBREINDEX vs. DBCC INDEXDEFRAG

测试显示，无论是DBCC DBREINDEX还是DBCC INDEXDEFRAG，都可以有效地整理索引碎片，并将页密度恢复到初始填充因子规定的页密度附近。基于这些结果，下面需要决定什么时候应用哪种整理方式。

如果允许有一段时间进行离线索引重建，DBCC DBREINDEX一般来说比DBCC INDEXDEFRAG要快。DBCC DBREINDEX可以充分利用多处理器系统的平行性能。DBCC INDEXDEFRAG用于对生产环境干扰不大，对工作性能影响不大的场合。测试显示，即使同时几个DBCC INDEXDEFRAG并行工作，对性能下降的影响也从来不会超出10%。但是，这也同样使得DBCC INDEXDEFRAG针对较大的索引整理时，需要很长的时间才能完成。而且，工作时间的长短还依赖于当时在服务器上运行的访问工作。

图9为DBCC INDEXDEFRAG和DBCC DBREINDEX的性能比较。图中的数据为小规模环境下，对所有索引进行整理的时间(大规模环境下结果类似，DBCC INDEXDEFRAGY运行时间为DBCC INDEXREINDEX的8倍)。当碎片级别增加，索引大小增加时，DBCC DBREINDEX可以比DBCC INDEXDEFRAG执行得更快。

图9: 小规模环境下，整理所有索引碎片所需时间

点击看原图

日志考虑: DBCC DBREINDEX vs. DBCC INDEXDEFRAG

最后要考察的问题是使用DBCC INDEXDEFRAG和DBCC DBREINDEX时，写入事务日志的数据量差别。DBCC INDEXDEFRAG中写入事务日志的数据量依赖于碎片的级别和完成的工作量。测试显示，当数据库完全恢复模式下，DBCC INDEXDEFRAG写入事务日志的数据量远远小于DBCC DBREINDEX。DBCC INDEXDEFRAG的日志数据量，可以变化很大。这是因为DBCC INDEXDEFRAG完成的碎片整理工作量由页移动数量和必要的页压缩数量决定。因为DBCC INDEXDEFRAG工作由一系列小事务组成，因此可以通过备份来回收DBCC INDEXDEFRAG使用的那部分日志空间。

从日志使用的角度看，DBCC DBREINDEX和DBCC INDEXDEFRAG稍有不同；在大批量(bulk)日志恢复模式下，日志量具有最大的差异。在完全恢复模式下，DBCC DBREINDEX对每个索引页有日志镜像，在日志恢复模式下，就没有。因此，在完全恢复模式下，DBCC DBREINDEX所需的日志空间大约等于索引页数量乘以8KB。可以通过DBCC SHOWCONTIG来确定给定索引的页数量。在大规模环境下，运行DBCC DBREINDEX时，建议将恢复模式改为日志恢复模式。而在运行结束后，再改为完全恢复模式。

注意：由于大规模环境下，回滚事务需要付出巨大的时间代价，因此理解日志的需求很重要。

图10 显示在小规模和中度碎片级别环境下，DBCC INDEXDEFRAG和DBCC DBREINDEX日志空间使用的差别。虽然DBCC INDEXDEFRAG日志空间波动很大，不过测试结果可以体现DBCC DBREINDEX和DBCC INDEXDEFRAG的一般性差别。

图10: 对DSS型数据库所有索引碎片整理时，DBCC INDEXDEFRAG和DBCC DBREINDEX所用的整个日志空间

点击看原图

结论

对于不同的工作类型，索引碎片整理具有十分不同的影响。某些应用可以从碎片整理中获取很大的性能提升。理解应用特征，系统性能和SQL Server提供的碎片统计信息，是正确决定何时进行碎片整理的关键。SQL Server提供一些命令来完成索引碎片整理。本文可以帮助我们来决定何时以及如何整理索引碎片，从而使性能得到最大的改善。

更多信息

要得到关于监视，分析和改善工作性能的更多信息，请访问如下资源：

Microsoft Knowledge Base article 243589: "HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later" at http://support.microsoft.com/default.aspx?scid=KB;en-us;243589&sd=tech
Microsoft SQL Server 2000 Performance Tuning Technical Reference, Microsoft Press, ISBN: 0-7356-1270-6
"Windows 2000 IO Performance" from Microsoft Research at http://research.microsoft.com/BARC/Sequential_IO/Win2K_IO.pdf

附录 A : 测试环境

本文的测试使用下面的硬件和软件环境：

Microsoft Software
Microsoft Windows 2000 Data Center (Service Pack 3)
Microsoft SQL Server 2000 Enterprise Edition (Service Pack 2)

Hardware Platform
Small-Scale Environment:
Dell PowerEdge 6450
4 Intel Pentium III Xeon 550 MHz processors
4 GB RAM

Large-Scale Environment:
Dell PowerEdge 8450
8 Intel Pentium III Xeon 550 MHz processors
16 GB RAM

Storage
Small-Scale Environment:
1 Dell PowerVault 660f, with 2, 18 GB 10,000 RPM disks
Total Disk Space = 36 GB (Raid 0)

Large-Scale Environment:
1 Hitachi Freedom Storage Lightning 9960 system, with 192, 73 GB, 10,000 RPM disks
Total Disk Space = 13 TB (~6 TB after RAID 1+0 and further striping/slicing)

Host bus adapters (HBA)
8 Emulex LP9002L PCI Host Bus Adapters
Firmware 3.82A1
Port Driver v5-2.11a2

Fabric switch
1 McData Switch, 1 GB

Storage management software
Hitachi Command Control Interface (CCI)
Hitachi ShadowImage

Databases
Representative OLTP and DSS databases

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。