|
|
|
|
SQL Server 2005 中的分区表和索引（1）http://www.sina.com.cn 2006年09月21日 19:24 ChinaByte
为什么要进行分区? 什么是分区?为什么要使用分区?简单的回答是:为了改善大型表以及具有各种访问模式的表的可伸缩性和可管理性。通常，创建表是为了存储某种实体(例如客户或销售)的信息，并且每个表只具有描述该实体的属性。一个表对应一个实体是最容易设计和理解的，因此不需要优化这种表的性能、可伸缩性和可管理性，尤其是在表变大的情况下。 大型表是由什么构成的呢?超大型数据库 (VLDB) 的大小以数百 GB 计算，甚至以 TB 计算，但这个术语不一定能够反映数据库中各个表的大小。大型数据库是指无法按照预期方式运行的数据库，或者运行成本或维护成本超出预定维护要求或预算要求的数据库。这些要求也适用于表;如果其他用户的活动或维护操作限制了数据的可用性，则可以认为表非常大。例如，如果性能严重下降，或者每天、每周甚至每个月的维护期间有两个小时无法访问数据，则可以认为销售表非常大。有些情况下，周期性的停机时间是可以接受的，但是通过更好的设计和分区实现，通常可以避免或最大程度地减少这种情况的发生。虽然术语 VLDB 仅适用于数据库，但对分区来说，了解表的大小更重要。 除了大小之外，当表中的不同行集拥有不同的使用模式时，具有不同访问模式的表也可能会影响性能和可用性。尽管使用模式并不总是在变化(这也不是进行分区的必要条件)，但在使用模式发生变化时，通过分区可以进一步改善管理、性能和可用性。还以销售表为例，当前月份的数据可能是可读写的，但以往月份的数据(通常占表数据的大部分)是只读的。在数据使用发生变化的类似情况下，或在维护成本随着在表中读写数据的次数增加而变得异常庞大的情况下，表响应用户请求的能力可能会受到影响。相应地，这也限制了服务器的可用性和可伸缩性。 此外，如果以不同的方式使用大量数据集，则需要经常对静态数据执行维护操作。这可能会造成代价高昂的影响，例如性能问题、阻塞问题、备份(空间、时间和运营成本)，还可能会对服务器的整体可伸缩性产生负面影响。 分区可以带来什么帮助?当表和索引变得非常大时，分区可以将数据分为更小、更容易管理的部分，从而提供一定的帮助。本文重点介绍横向分区，在横向分区中，大量的行组存储在多个相互独立的分区中。分区集的定义根据需要进行自定义、定义和管理。Microsoft SQL Server 2005 允许您根据特定的数据使用模式，使用定义的范围或列表对表进行分区。SQL Server 2005 还围绕新的表和索引结构设计了几种新功能，为分区表和索引的长期管理提供了大量的选项。 此外，如果具有多个 CPU 的系统中存在一个大型表，则对该表进行分区可以通过并行操作获得更好的性能。通过对各个并行子集执行多项操作，可以改善在极大型数据集(例如数百万行)中执行大规模操作的性能。通过分区改善性能的例子可以从以前版本中的聚集看出。例如，除了聚集成一个大型表外，SQL Server 还可以分别处理各个分区，然后将各个分区的聚集结果再聚集起来。在 SQL Server 2005 中，连接大型数据集的查询可以通过分区直接受益;SQL Server 2000 支持对子集进行并行连接操作，但需要动态创建子集。在 SQL Server 2005 中，已分区为相同分区键和相同分区函数的相关表(如 Order 和 OrderDetails 表)被称为已对齐。当优化程序检测到两个已分区且已对齐的表连接在一起时，SQL Server 2005 可以先将同一分区中的数据连接起来，然后再将结果合并起来。这使 SQL Server 2005 可以更有效地使用具有多个 CPU 的计算机。 分区的发展历史 分区的概念对 SQL Server 来说并不陌生。实际上，此产品的每个版本中都可以实现不同形式的分区。但是，由于没有为了帮助用户创建和维护分区架构而专门设计一些功能，因此分区一直是一个很繁琐的过程，没有得到充分的利用。而且，用户和开发人员对此架构存在误解(由于其数据库设计比较复杂)，低估了它的优点。但是，由于概念中固有的重要性能改善，SQL Server 7.0 开始通过分区视图实现各种分区方式，以此来改进这种功能。现在，SQL Server 2005 为通过分区表对大型数据集进行分区又迈出了最大的一步。 对 SQL Server 7.0 之前的版本中的对象进行分区 在 SQL Server 6.5 及以前的版本中，分区只能通过设计来完成，还必须内置到所有数据访问编码和查询方法中。通过创建多个表，然后通过存储过程、视图或客户端应用程序管理对正确表的访问，通常可以改善某些操作的性能，但代价是增加了设计的复杂性。每个用户和开发人员都必须知道(并正确引用)正确的表。单独创建和管理每个分区，而使用视图来简化访问;但是这种解决方案对性能并没有太大的改善。使用联合视图简化用户和应用程序访问时，查询处理器必须访问每个基础表才能确定结果集所需的数据。如果只需要基础表的有限子集，则每个用户和开发人员都必须了解此设计，以便只引用相应的表。 SQL Server 7.0 中的分区视图 在 SQL Server 7.0 之前的版本中，手动创建分区所面临的挑战主要与性能有关。尽管视图可以简化应用程序设计、用户访问和查询的编写，但却无法改善性能。而在 SQL Server 7.0 版本中，视图结合了约束，允许查询优化程序从查询计划中删除不相关的表(即分区消除)，大大降低了联合视图访问多个表时的总计划成本。 请参见图 1 中的 YearlySales 视图。您可以定义十二个单独的表(如 SalesJanuary2003、SalesFebruary2003 等)，然后定义每个季度的视图以及全年的视图 YearlySales，而不是将所有销售数据放到一个大型表中。
图 1:SQL Server 7.0/2000 中的分区视图 使用以下查询访问 YearlySales 视图的用户只会被引导至 SalesJanuary2003 表。 SELECT ys.* FROM dbo.YearlySales AS ys WHERE ys.SalesDate = '20030113' 只要约束可信并且访问视图的查询使用 WHERE 子句根据分区键(定义?际牧校┫拗撇檠峁琒QL Server 就会只访问必需的基础表。受信任的约束是指 SQL Server 能够确保所有数据符合该约束所定义的属性的约束。创建约束时，默认行为是创建约束 WITH CHECK。此设置将导致对表执行架构锁定，以便根据约束验证数据。如果验证结果表明现有数据有效，则添加约束;一旦解除架构锁定，后续的插入、更新和删除操作都必须符合正在应用的约束。通过使用此过程创建受信任的约束，开发人员无需直接访问(甚至不需要知道)他们感兴趣的表，从而大大降低了使用视图的设计的复杂性。通过受信任的约束，SQL Server 可以从执行计划中删除不需要的表，从而改善性能。 注意:约束可以通过各种方式变得“不可信任”;例如，如果未指定 CHECK_CONSTRAINTS 参数即执行批量插入，或者使用 NOCHECK 创建约束。如果约束不可信任，查询处理器将转而扫描所有基础表，因为它无法确定所请求的数据是否真的位于正确的基础表中。 SQL Server 2000 中的分区视图 尽管 SQL Server 7.0 大大简化了设计并改善了 SELECT 语句的性能，但是并没有为数据修改语句带来任何好处。INSERT、UPDATE 和 DELETE 语句只能针对基础表，而不能直接针对用于联合表的视图。在 SQL Server 2000 中，数据修改语句还可以受益于 SQL Server 7.0 中引入的分区视图功能。由于数据修改语句可以使用相同的分区视图结构，因此，SQL Server 可以通过视图将修改定向至相应的基础表。为了正确配置此设置，需要对分区键及其创建设置额外的限制;但是，基本原理是相同的，因为 SELECT 查询与修改都会直接发送给相应的基础表。有关在 SQL Server 2000 中进行分区的限制、设置、配置和最佳方法的详细信息，请参见 Using Partitions in a Microsoft SQL Server 2000 Data Warehouse。 SQL Server 2005 中的分区表 尽管 SQL Server 7.0 和 SQL Server 2000 中的改进大大改善了使用分区视图时的性能，但是并没有简化分区数据集的管理、设计或开发。使用分区视图时，必须单独创建和管理每个基础表(在其中定义视图的表)。尽管简化了应用程序设计并为用户带来了好处(用户不再需要知道直接访问哪个基础表)，但是由于要管理的表太多，而且必须为每个表管理数据完整性约束，管理工作变得更复杂。因为管理方面的问题，通常只有在需要存档或加载数据时才使用分区视图来分离表。当数据被移动到只读表或从只读表中删除后，操作的代价变得十分高昂，不仅花费时间、占据日志空间，通常还会导致系统阻塞。 另外，由于以前版本中的分区策略需要开发人员创建各个表和索引，然后通过视图将它们联合起来，因此优化程序需要验证并确定每个分区的计划(因为索引可能已发生变化)。这样一来，SQL Server 2000 中的查询优化时间通常会随着处理的分区数增加而直线上升。 在 SQL Server 2005 中，从定义上讲，每个分区都拥有相同的索引。例如，请考虑这样一种方案，即当前月份的联机事务处理 (OLTP) 数据需要移动到每个月末的分析表中。分析表(用于只读查询)是具有一个群集索引和两个非群集索引的表;批量加载 1 GB 数据(加载到已建立索引并激活的一个表中)将使当前用户遭受系统阻塞的情况，因为表和/或索引变得支离破碎和/或被锁定。另外，因为每传入一行都需要维护表和索引，所以加载过程还将耗费大量的时间。虽然可以通过多种方法加快批量加载的速度，但这些方法可能会直接影响所有其他用户，因为追求速度而无法实现并发操作。 如果将这些数据单独放到一个新创建的(空)且未建立索引(堆)的表中，则可以先加载数据，而在加载数据之后建立索引。 不支持Flash
|
