此文章由机器翻译。

SQL Server

Don Mackenzie

业务系统通常包括具有挑战性的报告要求。业务用户需要访问历史交易记录和当前的活动。他还需要用很多方式查看数据。用户将如作出具体要求：

• 按月由客户去年美元
• 单位产品的最后六个月一周
• 单位和美元的订单和产品 （包括今天和现在） 在过去的 10 天

满足这些不同的请求可以是系统设计师，尤其对于高交易量与企业面临的一个挑战。考克斯数字解决方案 （CDS） 是一个很好的例子。该公司处理业务量约达 20000 次每秒。

CD 我系统设计支持向上到小时报告一年的历史在一个 SQL Server 数据库表的更新每隔 10 分钟。溶液共混物两个 SQL Server 功能 — — 分区和 columnstore 索引 — — 以实现此响应时间查询大量数据时。

第一印象

CD 提供的互联网广告服务。由广告支持的"自由"的电视节目和"自由"的 Web 站点支持的显示这些页面上的广告。CDS 的帮助网站出版商正确显示广告。该公司记录了每个显示屏 （称为印象） 每个广告的信息。它还记录其他信息 （如点击有关的广告。每一天，这将生成近 20 亿的记录 （1.5 tb 压缩后容量）。

每个印象或交易记录，包括许多组件。有两个客户 — — 广告商和网站出版商。它还涉及的产品，就是广告。广告客户下订单，因此也是订单 id 作为记录的一部分。从广告客户收集的价格，并向出版商支付的价格。交易记录包含很多字段，反映出许多小的细节，对这项交易。

超过一年的交易被归档，以便用户可以比较本个月至一年以前或感恩节 （一家大型广告天） 今年到去年。CDS 会生成报告，账单广告商和出版商支付。这些报告还帮助工作人员监控广告订单交货，他们帮助监察成效，他们的广告，广告商和出版商可以跟踪活动和收入。

业务用户往往不能等待每个新的一天的数据。由于广告传递了一整天，他们会让调整和更新。这导致了我将描述在这里，你能适应那里的新的连续数据流被添加到已经很庞大的数据集的许多应用程序的设计。

数据过载

你不会想要把 5000 亿行 （和解压缩的 500 TB） 放在一个 SQL Server 表中。原始交易记录都保存在 Hadoop 分布式文件系统 (HDFS)。Hadoop 是一个好的工具，为存储和分析大量的数据，但有可怕的响应时间 （了解更多在 hadoop.apache.org) 查询。

对此数据的查询需要五分钟至 20 小时，当然，取决于数据量、 时间窗口和查询的复杂性。直接对 HDFS 的查询是为 Web 应用程序用户的速度太慢。

大多数用户需要可以会见聚合数据。总结按小时 （和广告、 网站、 广告和其他关键属性） 交易并将其放置在 SQL Server 数据库表中。时间很重要，因为广告活动是完全不同于 2 上午比中午 此过程概述了向 50,000 行每小时 7000 万交易。

这些行放在一个名为收入的 SQL Server 表中。一个交互式的 Web 应用程序使用户可以查询此表来检查对性能的一项命令，看到一则广告在不同网站上的表现如何，审查预算，等等。更新此表，一小时数据 （50,000 行） 取代每 10 分钟一班。

将一个 SQL 服务器表分区

SQL Server 2005 引入了对分区表，一直与后续版本好级别支持。分区将一个大表分成几个 （或多个） 的规模较小的内部表。SQL Server 存储和索引的每个小表或分区，分别 （见图 A)。

图在 SQL Server 中的分区的表

分区函数 （本质上的不同的磁盘分区的边界值的列表） 会告诉 SQL Server 如何将表划分为分区。经常是 datetime 列 （如我收入表），可用于分离的分区。表每月分区的数据超过一个月大、 日常分区为较新的数据超过一个星期岁，并且每小时对非常最近的数据分区。所有这些小的分区有一个单个表的名字，一个单一的架构和似乎是一个大表到应用程序。

当 SQL Server 处理查询的 WHERE 子句包括日期列，它确定哪些分区不需要，而忽略其余。所有查询的 WHERE 子句中包括的日期。这可以提高查询性能，通过限制到一个或多个较小的数据存储的查询。

举办一个大型的数据库表

这仍然是一个巨大的表，有 5 亿行和消费 60 GB 的存储空间。那么大一个普通表将会缓慢更新和慢的查询。添加索引可以帮助，但是他们进一步使更新变慢。当表变得更大，它能产生倍数层的索引 （索引深度），从而降低了他们乐于助人。

多维 OLAP 多维数据集是一种方法查询大量的数据，但他们需要大量的设计和部署之前规划。并在部署之后，您将无法更新它们。每天，或甚至更少，通常都会重新生成 OLAP 多维数据集。

我的方法来管理这个庞大的表相结合两种技术：分区和 columnstore 索引。SQL Server 2012 中的 columnstore 索引表的一个明显的限制是你不能更新这些索引处于活动状态时。你必须禁用索引更新的过程中，完全更新后重建它。这是一个繁琐的过程，当处理大型表格。我将描述为克服这种限制的技术。在 SQL Server 中 2014 （见"分区 SQL Server 表"） 已经放松这些限制。

各分区大小 — — 月和天和小时 — — 匹配的业务用户常见的查询模式。它们也符合常见的更新和维护模式。您的应用程序可能需要不同的分区大小和"日期"是最有用的分区以外的值。在其他应用程序中，可能是更好地保持只是每小时或每天的分区。SQL Server 2012 支持高达 15,000 的分区，每个表，允许每小时分区为 20 个月。请参阅 SQL Server 文档在 bit.ly/1mtZkfl 的分区更权威、 更深入的解释。

Columnstore 索引

最令人兴奋的新功能介绍了 SQL Server 2012 是 columnstore 指标。他们是强大的工具，用于提高查询性能。对于像我这样的大规模表，见过 100 倍的性能提高。

SQL Server 2012 的严重问题是 columnstore 索引的表，像一个 OLAP 多维数据集为只读。您将无法更新它没有滴 （或至少禁用） 索引和重建后的更新。经过验证的性能改进，我寻找一种方式来克服的只读模式的局限性和发现分区是答案。

虽然我认为作为进 （基于时间在我的例子） 的水平切片切片表分区的我想按列成垂直的薄片切片表 columnstore 指标。像所有的非聚集索引，该索引从主表数据分开存储。每个 columnstore 索引的列单独存储在其自己的内部的 blob 容器中。这是相当不同的从具有多个索引或包含列的复合索引在一起。

因为基础表为只读模式，columnstore blob 中的数据不需要处理更新，他们可以压缩使用 SQL Server 所选择的几种算法之一来匹配的数据。所以，索引通常是大大小于原始数据 （请参阅图 1)。

图 1 使用 Columnstore 索引来管理大型数据存储区

我的 columnstore 指数包括表中的每一列。每个列分别存储。引用只有几列的查询只会看那些列，而忽略其余。查询不需要读取基表中的行。所有数据值在索引中，每个索引中的列的数据压缩以减少磁盘读取。

记住我的表也被分区，所以每个分区和分区中的每一列有其自己的存储空间 （见图 2)。SQL Server 是聪明到可以只阅读它需要 （分区和列） 的一个特定的查询与令人印象深刻的查询响应时间的改进相比，扫描"正常"的表行的行，或甚至通过正常指标所需的数据检索的数据。

图 2 使用分区和 Columnstore 索引来解析数据

一个重要的积极副作用的这一切都是有非正常化表没有性能损失。前执行遭受因为针对收入表通常包含前奏的查询联接到其他表以获得次要属性。在那个时候，想要保持苗条的表中的行，并避免不必要的数据从磁盘中读取，每次有人问，这张大桌子。

有时联接导致 SQL Server 要做全表扫描的查询中，尤其是当辅助属性是在 where 子句中。现在，许多这些次要 （和一些专上） 的属性存储在宽得多的收入表知道使用 columnstore 索引，他们不会被访问，除非他们需要由该特定的查询。我已经设计了收入表有 25 列。请参阅 SQL Server 文档在 bit.ly/1zbsju1 columnstore 指标更权威、 更深入的了解。

更新

分区和 columnstore 索引的组合已经大规模表分成便于管理的中等规模的网段。然而，由于 columnstore 指数，在 SQL Server 2012 禁止更新。 尽管每个分区单独存储的它们都视为一个表中，所以禁用、 删除或重新生成索引涉及整个表。重建 columnstore 指数可要花上一个半小时，在那段时间，这个桌子还没有可用于查询。表需要每隔 10 分钟用新数据更新，并且可用的时间。

解决这一问题的关键是分区相关的语句：更改表......交换机分区...此 DDL 语句将数据分区从一个表移到另一个。它不会复制数据，但只是重新排列内部架构信息，所以用现在属于一个表分区存储属于一个不同的表。有一些规则这一进程，但它们是可控。TechNet 库阅读文章，"传输数据有效地通过使用分区切换"（bit.ly/1ts04Xv)，有关的分区切换彻底说明。

让我们再次回到原来的问题。还有大量涌入的数据 （业务量达 20000 次/秒） 从广告服务机器去 Hadoop 24 x 7。我可以使用 Hadoop 来汇总当前小时数据到约 50,000 SQL Server 行那一个小时。Hadoop 查询需要大约 5 分钟。我不能更新我主的收入表，但我可以把那些行入新表中 （分区和 columnstore 索引），我打电话给 RevenueIn。它具有相同的架构作为收入表中，但它是空的禁用其 columnstore 索引，这样我就可以插入行。

插入后小时行，我可以重建 columnstore RevenueIn 表上的索引。一个小时的数据精确的吻合到一个分区。所以，少于一分钟，我只索引 50,000 行。现在我可以使用交换机分区移动那个分区，已建立了索引，拨入政府的主要收入表和它的立即可用于报告查询。注意交换机分区语句仅适用是否目标分区为空。为了解决这个问题，我使用了称为 RevenueOut，它是空的第三个表。

现有的数据，从收入分区切换到 RevenueOut （保留收入分区为空），然后将 RevenueIn 分区 （包含新的数据和索引） 切换到收入。两个 SWITCH 语句在我的环境中采取小于 5 毫秒。RevenueOut 中的旧数据然后被截断 （请参见图 3)。这是一个变体所述的滑动窗口划分问题 bit.ly/1wgPVkR。

图 3 刷新分区中的数据

详细信息详细信息

总是有详细信息。应用程序重复此过程六次或以上每小时 （一样快，可以运行 Hadoop 查询）。早在小时，有只有几分钟的数据，每次更新时替换为新的数据。不久后的小时顶部，还有了整整一个小时的巨大的收入表中的数据，它可以在下一小时开始。用户在他的指尖是容易和快速查询到的一个表中有奢侈的新鲜的、 当前的数据和超过一年的历史。

开发商也赢得与单个数据库表。以前的解决方案用于"当前"数据，另一个用于历史数据的一个表。C# 代码必须决定哪些表使用并结合数据库查询的结果，如果使用两个表的查询。单个表和联接 （因为由 columnstore 索引启用而不受处罚的非规范化的列） 的最小使用简化了数据库查询以用于报告。

另一个细节合并。我把较旧的数据在每月的分区。这简化了维护，因为我可以丢弃超出了要求一个月。少分区还简化了 SQL Server 内部的逻辑，来选择分区的查询，因为大多数查询为旧数据包括整月。Columnstore 指数也获得了一些具有较大的分区，由于其压缩算法的效率­仿真。我用一种类似于插入数据汇总小分区成大分区没有重建数据。

还有一些 SQL Server 水暖涉及。在一篇网上文章 msdn.microsoft.com/magazine/dn800596 为所有必需的 SQL Server 数据库对象提供的分步说明和示例的 T-SQL。看看那篇文章，从那里复制代码。

Don Mackenzie 是软件体系结构在考克斯数字化解决方案，考克斯传媒集团与考克斯企业互联网广告子公司董事。他喜欢将新技术应用到业务应用程序。联系到他在 don@coxds.com。

衷心感谢以下 Microsoft 技术专家对本文的审阅：Remus Rusanu