sqlserver几个可提高性能和适应大数据量的办法(存储优化)

索引自不用说了，几乎是必须要考虑到的。select的时候尽量把使用索引的字段放前面，数据更新都会影响索引。查询上聚集索引要快一些，关乎物理存储也就知道有一个聚集索引。非聚集索引可以有多个，但是因为更新的同时也会更新索引的缘故所以有太多的非聚集索引是个负担。

这篇文章的很多细节并非出自我手，很多是网络收集过来，所以对于版权，归原作者，数据量大了之后，就必须做一些日常的计划任务了，比如过一段时间做一些备份，做一些分区，把一些不常用到的历史数据放到其他地方，比如按某字段分区存储。压缩数据等。

执行SQL查询时，主要的几个瓶颈在于：CPU运算速度、内存缓存区大小、磁盘IO速度。而对于大数据量数据的查询，其瓶颈则一般集中于磁盘IO，以及内存缓存。那么为了提高SQL查询的效率，一方面我们需要考虑尽量减少查询设计的数据条目数——建立索引，设立分区；另一方面，我们也可以考虑切实减少数据表物理大小，从而减少IO大小。

在SQL Server 2008中，最新提供了一项功能“压缩（Compression）”，就是定位于减少数据表、索引物理大小。

这里可以看到几点：

1. 下方列表里列出了该表所有的分区，也就是可以同一张表的不同分区应用不同的压缩策略。
2. 压缩方式（Compression Type）分为Row和Page两种。
   
   行级压缩（Row）：
   一方面减少了动态长度字段元数据的大小（varchar、varbinary等），比如之前存储字段实际长度需要2bytes，压缩后只需要3bits。
   另一方面也直接减少各字段存储内容的大小，比如存储数值1在一个int类型字段中，压缩后只占用了一个字节。

    

   页级压缩（Page）：能在各行间共享相同的数据，这里面包含两项技术：列前缀（Column Prefix）、页字典（Page Dictionary）。
   列前缀可以让拥有同样前缀的字段值拥有类似外键一样的结构来存储相同的前缀和各自的其余部分。比如一张存储了一个网站所有页面URL的表，URL字段存储的值分别是‘www.example.com/a.html’，‘www.example.com/b.html’，‘www.example.com/c.html’，‘www.example.com/d.html’。则压缩后，它们同样的前缀‘www.example.com/’会被提取出来，而其余部分会被类似如下的形式存储‘1a.html’，‘1b.html’，‘1c.html’，‘1d.html’。
   页字典则可以将在应用列前缀基础上的其余部分再次聚合存储，比如同样是一张存储了一个网站所有页面URL的表，假设有在表里里有多条URL字段的值相同，比如‘1a.html’，‘1b.html’，‘1c.html’，‘1b.html’，‘1a.html’，‘1a.html’，则通过页字典技术压缩后，实际存储在字段中的值会进一步减少为‘2’，‘3’，‘1c.html’（没有重复的字段值不会被压缩），‘3’，‘2’，‘2’。

3. 点击“Calculate”后，会计算出表当前占用的空间大小，以及压缩需要的空间大小。注意这里与一般预想的不同，如果要对一张预存有数据但尚未压缩的表进行压缩，首先需要的是额外的空间大小。

执行压缩

设置好之后，就可以选择是生成脚本还是立即执行，一般压缩的执行时间受表原有数据多少以及选择压缩方式的影响。笔者对一张有上千万条记录的表做页级压缩，耗时在10分钟左右。

压缩完成之后查看数据库大小，会发现数据库的大小变大了！这也和在设置阶段计算出来的额外空间相关。但实际上这里大部分空间是预占的空间，并没有实际数据。如果需要节省磁盘空间，需要进一步执行收缩（Shrink）操作。

与Compression不同，Shrink用来释放数据库占据的没有利用的空间，一般用来对无用的日志文件收缩（如果操作频繁，日志文件很有可能大于数据库实际数据的大小）。这里我们对数据库文件（mdf）做Shrink操作，完成之后再看数据库的大小，果然减少了很多。笔者做压缩、Shrink之后，一般都能将数据库的大小减为原来的1/3~1/2左右。当然，具体压缩比率取决于压缩方式、压缩表的字段特点、压缩表占整个数据库数据的比重等。

注意事项
•既然对表行了压缩，那么在执行查询时必然会有解压缩的过程。而这一过程会占用CPU时间，也就是我们在通过压缩减少了磁盘占用空间以及IO时间的同时，增大了CPU的消耗。所以在压缩前需要考虑清楚查询的瓶颈到底是磁盘IO还是内存还是CPU。而且如果表应用了压缩，类似建立索引，对于增删改等操作也会有一定的影响。所以同样要考虑应用在表上的操作到底以哪种为主。
•各页面的压缩是独立进行的，页字典和列前缀也分别存储于各页内。而且压缩仅在数据页快满的时候进行，因为一个页的大小是固定的，压缩半页不会有性能上的提升。
•数据库备份中也有Compression的选项，但这利用的是系统的文件压缩技术，而且只能应用于整个数据库上。
•容易被忽略的是，索引也能被压缩，而且和表压缩独立，同样也会提升所有应用到索引的查询的性能。
•如果对表进行压缩，聚集索引会自动应用与表同样的压缩模式，而非聚集索引不会。
•在Shrink阶段，可能会造成大量的索引碎片，所以可以在Shrink完成之后重建或者重组织索引，但同时，这些操作也会造成数据库的体积变大……也就是，最小的数据库体积和最小碎片比率的索引是鱼与熊掌，不可兼得。

Partition（创建分区）

和压缩（Compression）相比，数据库分区（Partition）的操作更为复杂繁琐。而且与Compression一次操作，终身保持不同，分区是一项需要长期维护周期变更的操作。

分区的意义在于将大数据从物理上切割为几个相互独立的小部分，从而在查询时只取出其中一个或几个分区，减少影响的数据；另外对于置于不同文件组的分区，并行查询的性能也要高于对整个表的查询性能。

事实上，在SQL Server 2005中就已经包含了分区功能，甚至在2005之前，还存在一个叫做“Partitioned Views”的功能，能通过将同样结构的表Union在一个View中，实现类似现在分区表的效果。而在SQL Server 2008中，分区功能得到了显著加强，使得我们不仅能够对表和索引做分区，

使得我们不仅能够对表和索引做分区，而且允许对分区上锁，而不是之前的全表上锁。

和Compression一样，在SQL Server 2008中也提供了分区的向导界面。在企业管理器中，需要分区的表上右键选择Storage-》Create Partition:

这里会列出该表所有的字段，包括字段类型、长度、精度及小数位数的信息，可以选择其中的任意一一列作为分区列（Patitioning Column），不仅仅是数字或者日期类型，即使是字符串类型的列，也可以按照字母顺序进行分区。而以下类型的列不可用于分区：text、ntext、image、xml、timestamp、varchar(max)、nvarchar(max)、varbinary(max)、别名、hierarchyid、空间索引或 CLR 用户定义的数据类型。此外，如果使用计算列作为分区列，则必须将该列设为持久化列（Persisit）。

在列表下方，提供了两个选项：

1. 分配到可用分区表：
   这要求在同一数据库下有另一张已分好区的表，同时该表的分区列和当前选中的列的类型完全一致。
   这样的好处是当两张表在查询中有关联时，并且其关联列就是分区列时，使用同样的分区策略会更有效率。
2. 将非唯一索引和唯一索引的存储空间调整为与索引分区列一致：
   这样会将表中的所有索引也一同分区，实现“对齐”。这是一个重要而麻烦的选项，具体需求请参阅MSDN（已分区索引的特殊指导原则）。
   这样的好处是表和索引的分区一致，一方面查询时利用索引更为高效，而且在下文提到的移入移出分区也会更为高效。

注意：这里建议使用聚集索引列作为分区列。一方面索引结构本身就应与查询相关，那么分区列与索引一致会保证查询的最大效率；另一方面，保证索引对齐而且是聚集索引对齐是保证分区的移入移出操作顺畅的前提，否则可能会出现无法移入移出的情况，而分区的移入移出又是管理大数据的重要策略——滑动窗口（SlideWindow）策略的基础操作。另外，如果要进行索引对齐，需要所有索引和表的压缩模式一致。

分区函数与分区方案

选好分区列后，如果没有应用“分配到可用分区表”选项，接下来则会进入选择/创建分区函数以及分区方案的界面。其中分区函数会指定分区边界，而分区方案则规划了每个分区所存储的文件组。

向导操作界面如下：

其中Left boundary说明每个分区的边界值被包含在边界值左侧的分区中，也就是每个分区内的数据约束是<=指定的边界值，相应的，Right boundary则说明每个分区的边界值被包含在边界值右侧的分区中，每个分区内的数据约束是<指定的边界值。

在下方的列表中，列出了当前分区方案下现有的分区。其中文件组（Filegroup）指定了每个分区存放的位置，如果将分区放置于位于不同磁盘中的不同文件组中，由于不同磁盘的读写互不干扰，这将提高分区表并行处理的效率。一般情况下，将所有分区放置在同一个文件组是比较稳妥的做法。关于文件组的展开阅读可以参阅：SQL Server Filegroups。

注意，在这里最后一个分区是没有指定边界的，用于保存所有>(Left Boundary)或>=(Right boundary)最后一个分区边界的数据。

如果选择时间类型的字段作为分区列，可以通过Set按钮实现按条件分组：

这样可以很方便得通过设置起止时间将表按照指定时间段自动分区，但之后依然需要手动指定每个分区的文件组。

制定好分区方案之后可以通过Estimate sotrage预估每个分区的行数、空间占用情况，不过除非需要以占用空间或行数来规划你的分区策略，一般不建议在这里进行预估，因为如果对空表来说，预估的结果当然都是0，而如果表中已经包含大量数据，预估则会花费比较长的时间。

创建分区

通过以上设置，分区已经基本完毕，在向导的最后，可以选择是创建脚本还是立即执行分区操作。

我们可以查看在不同情况下创建分区的脚本的情况：

1.在表没有索引的情况下：

BEGIN TRANSACTION
CREATE PARTITION FUNCTION [TestFunction](datetime) AS RANGE LEFT FOR VALUES (N'2010-01-01T00:00:00', N'2010-02-01T00:00:00',
N'2010-03-01T00:00:00', N'2010-04-01T00:00:00', N'2010-05-01T00:00:00', N'2010-06-01T00:00:00')

CREATE PARTITION SCHEME [TestScheme] AS PARTITION [TestFunction] TO ([PRIMARY], [PRIMARY], [PRIMARY],
[PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])

CREATE CLUSTERED INDEX [ClusteredIndex_on_TestScheme_634025264502439124] ON [dbo].[Account]
(
    [birthday]
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [TestScheme]([birthday])

DROP INDEX [ClusteredIndex_on_TestScheme_634025264502439124] ON [dbo].[Account] WITH ( ONLINE = OFF )
COMMIT TRANSACTION

这里先创建Partition Function以及Partition Scheme，之后在分区列上创建聚集索引并按照分区方案分区，最后删除了这一索引。</>

2.在表有索引的情况下：

如果原先没有聚集索引：

CREATE CLUSTERED INDEX [ClusteredIndex_on_TestScheme_634025229911990663] ON [dbo].[Account]
(
    [birthday]
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [TestScheme]([birthday])

DROP INDEX [ClusteredIndex_on_TestScheme_634025229911990663] ON [dbo].[Account] WITH ( ONLINE = OFF )

这和没有索引的情况一样，如果表原先存在聚集索引，则脚本变为：

CREATE CLUSTERED INDEX [IX_id] ON [dbo].[Account]
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON,
ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [TestScheme]([birthday])

可以看到原有的聚集索引（IX_id）在分区方案上被重建了。

如果选择了“对齐索引”选项，则会对所有索引都应用分区：

CREATE CLUSTERED INDEX [IX_id] ON [dbo].[Account]
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON,
ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [TestScheme]([birthday])

CREATE NONCLUSTERED INDEX [UIX_birthday] ON [dbo].[Account]
(
    [birthday] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON,
ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [TestScheme]([birthday])
CREATE NONCLUSTERED INDEX [UIX_name] ON [dbo].[Account]
(
    [name] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON,
ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)

这里不仅对聚集索引IX_id进行了分区，也对非聚集索引UIX_name和UIX_birthday进行了分区。

注意事项

1. 对一张表分好区后不可以进行再次分区，同时也没有直接取消表分区的方法。
2. 如果要查看已分区表的分区状态以及每个分区中的行数和占用空间，可以通过Storage-》Management Compression查看。同时可以在这里为每个分区指定压缩方式。
3. 如果分区表索引没有对齐，则不可以对该表进行切入切出（Switch in/out）操作，同样也不能执行滑动窗口操作。
4. 分区实际上是在每个分区表都添加了约束，相应的插入操作的性能也会受到影响。
5. 即使进行了分区，如果查询的条件字段和分区列并没有关联，性能也未必会得到提升。

附：对分区并行查询的说明

由于我在实际操作中主要考虑并行查询方面的效率，所以文章里只是略略带过，但评论中有人提到，所以摘录整理一些资料在下面：

1. 并行查询肯定需要多核支持，单核下并行是不可能的。
2. 在2005中，如果有两个以上的Partition，一个线程对应一个Partition，所以如果有10个线程，却只有3个分区的话，就会有7个线程被浪费。
3. 在2008中，这一问题被改进，所有的线程都被投入到所有的Partition中。具体可以参看
4. http://sqlblog.com/blogs/erin_welker/archive/2008/02/10/partitioning-enhancements-in-sql-server-2008.aspx

附作者拖鞋不脱和回复者的互动

并行查询？
如果我没记错的话，就算是分区，并且不同文件组放在不同的硬盘上面，也是顺序读取，分区并不能提高效率，只不过是提高了io而已
所谓并行查询只是我们一厢情愿的一种想法而已

回复 引用 查看

#2楼[楼主]2010-02-23 16:07 | 拖鞋不脱

@尘尘
“此外，如果具有多个 CPU 的系统中存在一个大型表，则对该表进行分区可以通过并行操作获得更好的性能。通过对各个并行子集执行多项操作，可以改善在极大型数据集（例如数百万行）中执行大规模操作的性能。”
原文见：
http://msdn.microsoft.com/zh-cn/library/ms345146%28SQL.90%29.aspx