SqlServer 性能优化Partition（创建分区）
和压缩（Compression）相比，数据库分区（Partition）的操作更为复杂繁琐。而且与Compression一次操作，终身保持不同，分区是一项需要长期维护周期变更的操作。 分区的意义在于将大数据从物理上切割为几个相互独立的小部分，从而在查询时只取出其中一个或几
和压缩（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）。
在列表下方，提供了两个选项：
-
分配到可用分区表：
这要求在同一数据库下有另一张已分好区的表，同时该表的分区列和当前选中的列的类型完全一致。
这样的好处是当两张表在查询中有关联时，并且其关联列就是分区列时，使用同样的分区策略会更有效率。 -
将非唯一索引和唯一索引的存储空间调整为与索引分区列一致：
这样会将表中的所有索引也一同分区，实现“对齐”。这是一个重要而麻烦的选项，具体需求请参阅MSDN（已分区索引的特殊指导原则）。
这样的好处是表和索引的分区一致，一方面查询时利用索引更为高效，而且在下文提到的移入移出分区也会更为高效。
注意：这里建议使用聚集索引列作为分区列。一方面索引结构本身就应与查询相关，那么分区列与索引一致会保证查询的最大效率；另一方面，保证索引对齐而且是聚集索引对齐是保证分区的移入移出操作顺畅的前提，否则可能会出现无法移入移出的情况，而分区的移入移出又是管理大数据的重要策略——滑动窗口（SlideWindow）策略的基础操作。
分区函数与分区方案
选好分区列后，如果没有应用“分配到可用分区表”选项，接下来则会进入选择\创建分区函数以及分区方案的界面。其中分区函数会指定分区边界，而分区方案则规划了每个分区所存储的文件组。
向导操作界面如下：
其中Left boundary说明每个分区的边界值被包含在边界值左侧的分区中，也就是每个分区内的数据约束是，相应的，Right boundary则说明每个分区的边界值被包含在边界值右侧的分区中，每个分区内的数据约束是。
在下方的列表中，列出了当前分区方案下现有的分区。其中文件组（Filegroup）指定了每个分区存放的位置，如果将分区放置于位于不同磁盘中的不同文件组中，由于不同磁盘的读写互不干扰，这将提高分区表并行处理的效率。一般情况下，将所有分区放置在同一个文件组是比较稳妥的做法。关于文件组的展开阅读可以参阅：SQL Server Filegroups。
注意，在这里最后一个分区是没有指定边界的，用于保存所有>(Left Boundary)或>=(Right boundary)最后一个分区边界的数据。
如果选择时间类型的字段作为分区列，可以通过Set按钮实现按条件分组：
这样可以很方便得通过设置起止时间将表按照指定时间段自动分区，但之后依然需要手动指定每个分区的文件组。
制定好分区方案之后可以通过Estimate sotrage预估每个分区的行数、空间占用情况，不过除非需要以占用空间或行数来规划你的分区策略，一般不建议在这里进行预估，因为如果对空表来说，预估的结果当然都是0，而如果表中已经包含大量数据，预估则会花费比较长的时间。
创建分区
通过以上设置，分区已经基本完毕，在向导的最后，可以选择是创建脚本还是立即执行分区操作。
我们可以查看在不同情况下创建分区的脚本的情况：
1.在表没有索引的情况下：
<span>BEGIN TRANSACTION
CREATE PARTITION FUNCTION </span>[TestFunction]<span>(</span><span>datetime</span><span>) </span><span>AS RANGE </span><span>LEFT </span><span>FOR VALUES </span><span>(</span><span>N'2010-01-01T00:00:00'</span><span>, </span><span>N'2010-02-01T00:00:00'</span><span>, <br></span><span>N'2010-03-01T00:00:00'</span><span>, </span><span>N'2010-04-01T00:00:00'</span><span>, </span><span>N'2010-05-01T00:00:00'</span><span>, </span><span>N'2010-06-01T00:00:00'</span><span>)
</span><span>CREATE PARTITION </span>SCHEME [TestScheme] <span>AS PARTITION </span>[TestFunction] <span>TO </span><span>(</span>[PRIMARY]<span>, </span>[PRIMARY]<span>, </span>[PRIMARY]<span>, <br></span>[PRIMARY]<span>, </span>[PRIMARY]<span>, </span>[PRIMARY]<span>, </span>[PRIMARY]<span>)
</span><span>CREATE CLUSTERED INDEX </span>[ClusteredIndex_on_TestScheme_634025264502439124] <span>ON </span>[dbo]<span>.</span>[Account]
<span>(
</span>[birthday]
<span>)</span><span>WITH </span><span>(</span><span>SORT_IN_TEMPDB </span><span>= </span><span>OFF</span><span>, </span><span>IGNORE_DUP_KEY </span><span>= </span><span>OFF</span><span>, </span><span>DROP_EXISTING </span><span>= </span><span>OFF</span><span>, </span><span>ONLINE </span><span>= </span><span>OFF</span><span>) </span><span>ON </span>[TestScheme]<span>(</span>[birthday]<span>)
</span><span>DROP INDEX </span>[ClusteredIndex_on_TestScheme_634025264502439124] <span>ON </span>[dbo]<span>.</span>[Account] <span>WITH </span><span>( </span><span>ONLINE </span><span>= </span><span>OFF </span><span>)
</span><span>COMMIT TRANSACTION</span>这里先创建Partition Function以及Partition Scheme，之后在分区列上创建聚集索引并按照分区方案分区，最后删除了这一索引。
2.在表有索引的情况下：
如果原先没有聚集索引：
<span>CREATE CLUSTERED INDEX </span>[ClusteredIndex_on_TestScheme_634025229911990663] <span>ON </span>[dbo]<span>.</span>[Account]
<span>(
</span>[birthday]
<span>)</span><span>WITH </span><span>(</span><span>SORT_IN_TEMPDB </span><span>= </span><span>OFF</span><span>, </span><span>IGNORE_DUP_KEY </span><span>= </span><span>OFF</span><span>, </span><span>DROP_EXISTING </span><span>= </span><span>OFF</span><span>, </span><span>ONLINE </span><span>= </span><span>OFF</span><span>) </span><span>ON </span>[TestScheme]<span>(</span>[birthday]<span>)
</span><span>DROP INDEX </span>[ClusteredIndex_on_TestScheme_634025229911990663] <span>ON </span>[dbo]<span>.</span>[Account] <span>WITH </span><span>( </span><span>ONLINE </span><span>= </span><span>OFF </span><span>)
</span>这和没有索引的情况一样，如果表原先存在聚集索引，则脚本变为：
<span>CREATE CLUSTERED INDEX </span>[IX_id] <span>ON </span>[dbo]<span>.</span>[Account]
<span>(
</span>[id] <span>ASC
</span><span>)</span><span>WITH </span><span>(</span><span>PAD_INDEX </span><span>= </span><span>OFF</span><span>, </span><span>STATISTICS_NORECOMPUTE </span><span>= </span><span>OFF</span><span>, </span><span>SORT_IN_TEMPDB </span><span>= </span><span>OFF</span><span>, </span><span>IGNORE_DUP_KEY </span><span>= </span><span>OFF</span><span>, </span><span>DROP_EXISTING </span><span>= </span><span>ON</span><span>, <br></span><span>ONLINE </span><span>= </span><span>OFF</span><span>, </span><span>ALLOW_ROW_LOCKS </span><span>= </span><span>ON</span><span>, </span><span>ALLOW_PAGE_LOCKS </span><span>= </span><span>ON</span><span>) </span><span>ON </span>[TestScheme]<span>(</span>[birthday]<span>)</span>可以看到原有的聚集索引（IX_id）在分区方案上被重建了。
如果选择了“对齐索引”选项，则会对所有索引都应用分区：
<span>CREATE CLUSTERED INDEX </span>[IX_id] <span>ON </span>[dbo]<span>.</span>[Account]
<span>(
</span>[id] <span>ASC
</span><span>)</span><span>WITH </span><span>(</span><span>PAD_INDEX </span><span>= </span><span>OFF</span><span>, </span><span>STATISTICS_NORECOMPUTE </span><span>= </span><span>OFF</span><span>, </span><span>SORT_IN_TEMPDB </span><span>= </span><span>OFF</span><span>, </span><span>IGNORE_DUP_KEY </span><span>= </span><span>OFF</span><span>, </span><span>DROP_EXISTING </span><span>= </span><span>ON</span><span>, <br></span><span>ONLINE </span><span>= </span><span>OFF</span><span>, </span><span>ALLOW_ROW_LOCKS </span><span>= </span><span>ON</span><span>, </span><span>ALLOW_PAGE_LOCKS </span><span>= </span><span>ON</span><span>) </span><span>ON </span>[TestScheme]<span>(</span>[birthday]<span>)
</span><span>CREATE NONCLUSTERED INDEX </span>[UIX_birthday] <span>ON </span>[dbo]<span>.</span>[Account]
<span>(
</span>[birthday] <span>ASC
</span><span>)</span><span>WITH </span><span>(</span><span>PAD_INDEX </span><span>= </span><span>OFF</span><span>, </span><span>STATISTICS_NORECOMPUTE </span><span>= </span><span>OFF</span><span>, </span><span>SORT_IN_TEMPDB </span><span>= </span><span>OFF</span><span>, </span><span>IGNORE_DUP_KEY </span><span>= </span><span>OFF</span><span>, </span><span>DROP_EXISTING </span><span>= </span><span>ON</span><span>, <br></span><span>ONLINE </span><span>= </span><span>OFF</span><span>, </span><span>ALLOW_ROW_LOCKS </span><span>= </span><span>ON</span><span>, </span><span>ALLOW_PAGE_LOCKS </span><span>= </span><span>ON</span><span>) </span><span>ON </span>[TestScheme]<span>(</span>[birthday]<span>)
</span><span>CREATE NONCLUSTERED INDEX </span>[UIX_name] <span>ON </span>[dbo]<span>.</span>[Account]
<span>(
</span>[name] <span>ASC
</span><span>)</span><span>WITH </span><span>(</span><span>PAD_INDEX </span><span>= </span><span>OFF</span><span>, </span><span>STATISTICS_NORECOMPUTE </span><span>= </span><span>OFF</span><span>, </span><span>SORT_IN_TEMPDB </span><span>= </span><span>OFF</span><span>, </span><span>IGNORE_DUP_KEY </span><span>= </span><span>OFF</span><span>, </span><span>DROP_EXISTING </span><span>= </span><span>ON</span><span>, <br></span><span>ONLINE </span><span>= </span><span>OFF</span><span>, </span><span>ALLOW_ROW_LOCKS </span><span>= </span><span>ON</span><span>, </span><span>ALLOW_PAGE_LOCKS </span><span>= </span><span>ON</span><span>)</span>
这里不仅对聚集索引IX_id进行了分区，也对非聚集索引UIX_name和UIX_birthday进行了分区。
注意事项
- 对一张表分好区后不可以进行再次分区，同时也没有直接取消表分区的方法。
- 如果要查看已分区表的分区状态以及每个分区中的行数和占用空间，可以通过Storage-》Management Compression查看。同时可以在这里为每个分区指定压缩方式。
- 如果分区表索引没有对齐，则不可以对该表进行切入切出（Switch in/out）操作，同样也不能执行滑动窗口操作。
- 分区实际上是在每个分区表都添加了约束，相应的插入操作的性能也会受到影响。
- 即使进行了分区，如果查询的条件字段和分区列并没有关联，性能也未必会得到提升。
熱AI工具
Undresser.AI Undress
人工智慧驅動的應用程序，用於創建逼真的裸體照片
AI Clothes Remover
用於從照片中去除衣服的線上人工智慧工具。
Undress AI Tool
免費脫衣圖片
Clothoff.io
AI脫衣器
Video Face Swap
使用我們完全免費的人工智慧換臉工具，輕鬆在任何影片中換臉！
熱門文章
熱工具
記事本++7.3.1
好用且免費的程式碼編輯器
SublimeText3漢化版
中文版，非常好用
禪工作室 13.0.1
強大的PHP整合開發環境
Dreamweaver CS6
視覺化網頁開發工具
SublimeText3 Mac版
神級程式碼編輯軟體(SublimeText3)
PHP 陣列鍵值翻轉：不同方法的效能比較分析
May 03, 2024 pm 09:03 PM
PHP數組鍵值翻轉方法效能比較顯示：array_flip()函數在大型數組（超過100萬個元素）下比for迴圈效能更優，耗時更短。手動翻轉鍵值的for迴圈方法耗時相對較長。
不同Java框架的效能對比
Jun 05, 2024 pm 07:14 PM
不同Java框架的效能比較：RESTAPI請求處理：Vert.x最佳，請求速率達SpringBoot2倍，Dropwizard3倍。資料庫查詢：SpringBoot的HibernateORM優於Vert.x及Dropwizard的ORM。快取操作：Vert.x的Hazelcast客戶端優於SpringBoot及Dropwizard的快取機制。合適框架：根據應用需求選擇，Vert.x適用於高效能Web服務，SpringBoot適用於資料密集型應用，Dropwizard適用於微服務架構。
C++ 程式最佳化：時間複雜度降低技巧
Jun 01, 2024 am 11:19 AM
時間複雜度衡量演算法執行時間與輸入規模的關係。降低C++程式時間複雜度的技巧包括：選擇合適的容器（如vector、list）以最佳化資料儲存和管理。利用高效演算法（如快速排序）以減少計算時間。消除多重運算以減少重複計算。利用條件分支以避免不必要的計算。透過使用更快的演算法（如二分搜尋）來優化線性搜尋。
C++中如何優化多執行緒程式的效能？
Jun 05, 2024 pm 02:04 PM
優化C++多執行緒效能的有效技術包括：限制執行緒數量，避免爭用資源。使用輕量級互斥鎖，減少爭用。優化鎖的範圍，最小化等待時間。採用無鎖定資料結構，提高並發性。避免忙等，透過事件通知執行緒資源可用性。
PHP 數組轉物件對效能的影響是什麼？
Apr 30, 2024 am 08:39 AM
在PHP中，陣列到物件的轉換會對效能產生影響，主要受陣列大小、複雜度、物件類別等因素影響。為了優化效能，可以考慮使用自訂迭代器、避免不必要的轉換、批次轉換數組等技巧。
Golang 中隨機數產生器的效能如何？
Jun 01, 2024 pm 09:15 PM
在Go中產生隨機數的最佳方法取決於應用程式所需的安全性等級。低安全性：使用math/rand套件產生偽隨機數字，適合大多數應用程式。高安全性：使用crypto/rand套件產生加密安全的隨機字節，適用於需要更強隨機性的應用程式。
解決 PHP 函數效率低的方法有哪些？
May 02, 2024 pm 01:48 PM
PHP函數效率最佳化的五大方法：避免不必要的變數複製。使用引用以避免變數複製。避免重複函數呼叫。內聯簡單的函數。使用數組優化循環。
Java框架的效能比較
Jun 04, 2024 pm 03:56 PM
根據基準測試，對於小型、高效能應用程序，Quarkus（快速啟動、低記憶體）或Micronaut（TechEmpower優異）是理想選擇。 SpringBoot適用於大型、全端應用程序，但啟動時間和記憶體佔用稍慢。
