MSSQL數據分區
1. 什么是分區
在sqlserver中，一般情況下所有的數據都是存儲到一個文件上的（默認為.mdf文件），這樣在數據非常多的時候效率肯定比較低。 而如果采用分區，數據就會按照我們指定的分區規則，存儲到不同的文件，這樣一來，一個非常的大文件就被分成了多個小文件，這樣一來查詢效率也會大大提升。
如果我們不做任何分區，也就是說，所有的數據都是存儲在主數據文件(.mdf)中的。 如果進行了分區，那么我們就可以指定次要數據文件(.ndf)的數量，來分攤主數據文件的壓力。除此之外，還有一個日志數據文件，也就是(.ldf)文件。
分區分為兩種，一種是水平分區，另一種是垂直分區。
水平分區：對表的行進行分區。每個物理區域保存一定量的行數據，它們組合起來就是完整的表數據。進行水平分區，一定要指定某個屬性列進行數據分割。比如：一年的訂單表可以按照時間分四個區（這里就是按照時間進行數據分割的。）
垂直分區：對表的列進行分區。通過對表的垂直劃分來減少目標表的寬度，使某些特定的列被劃分到特定的分區，每個分區都包含了其中的列所對應的行。
2. 準備測試數據
在正式開始之前，我們需要先創建一些數據。創建了一個數據庫(mydb)，和一張訂單表(order)，并且往訂單表中插入了1千萬條測試數據。
create database mydb;
GO
use mydb
GO
create table order_detail
(
order_id bigint not null primary key nonclustered identity(1,1),
customer_id bigint not null,
goods_price decimal(10,2) not null,
create_time datetime not null,
);
GO
create clustered index create_time_clustered_index on order_detail(create_time)
GO
execute sp_addextendedproperty 'MS_Description', '訂單編號', 'user', 'dbo', 'table', 'order_detail', 'column', 'order_id';
execute sp_addextendedproperty 'MS_Description', '用戶id', 'user', 'dbo', 'table', 'order_detail', 'column', 'customer_id';
execute sp_addextendedproperty 'MS_Description', '商品數量', 'user', 'dbo', 'table', 'order_detail', 'column', 'goods_price';
execute sp_addextendedproperty 'MS_Description', '創建時間', 'user', 'dbo', 'table', 'order_detail', 'column', 'create_time';
GO
--插入1千萬條數據，大概需要15分鐘
declare @price_min Int=1 --測試數據最低價格
declare @price_max Int=1000000 --測試數據最高價格
declare @decimal Int=2 --價格保留小數點
declare @i int
set @i = 1
while @i < 10000000
begin
insert into order_detail(customer_id,goods_price,create_time)
values(
ABS(CHECKSUM(NEWID()))
,@price_min+round((@price_max-@price_min)*rand(),@decimal) --價格
,GETDATE()-(0.01*@i)/15); --插入時間，時間長度大概是19年
set @i = @i + 1;
end
3. 如何進行水平分區
在SQL Server中進行水平分區的過程不是一個簡單的SQL命令就可以搞定的，它涉及到數據文件，文件組，分區函數，和分區方案。 下面筆者將會把這個過程劃分成一個個的小步驟，并且為每個步驟都做了較為詳細的解釋。
3.1 創建文件組
這個步驟的作用就是指定數據分區后要存儲的文件。這里有兩個概念，一個是數據文件，另一個是文件組，一個文件組可以管理多個數據文件，我們在創建分區方案的時候就需要指定這些文件組。在創建分區完成后，分區表中的數據會按照我們指定的規則分散地存儲到各個數據文件中。
既可以在創建數據庫的時候創建文件組，也可以在數據庫創建完成后再創建文件組。
在創建完成數據庫后，可以看到文件組可以被分為PRIMARY和你自定義的文件組兩種。主數據文件(mydb)是屬于PRIMARY文件組，并且不能被改變。myfile1,2,3,4,5是屬于次要數據文件，次要數據文件的歸組就比較隨意，既可以是PRIMARY，也可以是你自定義的文件組。還有一個日志數據文件(mydb_log)不屬于任何文件組。
創建完成后，打開你的數據存儲目錄，可以看到SQL Server為你創建了如下文件。
關于文件和文件組的更多信息，可以移步到 Microsoft Database Files and Filegroups。
上面展示了在創建數據庫時候創建數據文件和文件組。其實也可以數據庫創建完成后，再創建數據文件和文件組。
創建文件組
右鍵數據庫 -> 屬性（Properties） -> 文件組（Filegroups）
創建數據文件
右鍵數據庫 -> 屬性（Properties） -> 文件組（Files）
通過T-SQL創建數據文件和文件組
你也可以通過T-SQL腳本來創建數據文件和文件組
--創建數據庫文件組
--alter database 數據庫名稱 add filegroup 文件組名稱
alter database mydb add filegroup myfilegroup1
alter database mydb add filegroup myfilegroup2
alter database mydb add filegroup myfilegroup3
alter database mydb add filegroup myfilegroup4
alter database mydb add filegroup myfilegroup5
--創建數據文件
--alter database 數據庫名稱 add file
--(name=N'文件名稱',filename=N'文件路徑',size=文件初始,filegrowth=文件自動增量)
--to filegroup 文件組名稱
alter database mydb add file
(name=N'myfile1',filename=N'D:\softwares\sqlserver\root\MSSQL14.MSSQLSERVER\MSSQL\DATA\myfile1.ndf',size=10Mb,filegrowth=5mb)
to filegroup myfilegroup1
alter database mydb add file
(name=N'myfile2',filename=N'D:\softwares\sqlserver\root\MSSQL14.MSSQLSERVER\MSSQL\DATA\myfile2.ndf',size=10Mb,filegrowth=5mb)
to filegroup myfilegroup2
alter database mydb add file
(name=N'myfile3',filename=N'D:\softwares\sqlserver\root\MSSQL14.MSSQLSERVER\MSSQL\DATA\myfile3.ndf',size=10Mb,filegrowth=5mb)
to filegroup myfilegroup3
alter database mydb add file
(name=N'myfile4',filename=N'D:\softwares\sqlserver\root\MSSQL14.MSSQLSERVER\MSSQL\DATA\myfile4.ndf',size=10Mb,filegrowth=5mb)
to filegroup myfilegroup4
alter database mydb add file
(name=N'myfile5',filename=N'D:\softwares\sqlserver\root\MSSQL14.MSSQLSERVER\MSSQL\DATA\myfile5.ndf',size=10Mb,filegrowth=5mb)
to filegroup myfilegroup5
3.2 創建分區函數
上面已經創建了文件組，接下來就是創建分區函數，分區函數是數據庫中的一個獨立對象，它將表的行映射到一組分區，所以分區函數解決的是HOW的問題，即表如何分區的問題。
create partition function 分區函數名(<分區列類型>) as range [left/right] for values (每個分區的邊界值,....)
上面的left代表左邊界，right代表右邊界。當 數據庫引擎 按升序從左到右排序時，邊界值是屬于左側還是右側（默認為左側）。換句話說，就是一個為小于等于，另一個為小于。
create partition function myPartitionFun(datetime) as range right for values ('2010-01-01 00:00:00','2017-01-01 00:00:00','2019-01-01 00:00:00','2020-01-01 00:00:00')
筆者數據庫中所有數據的時間范圍是2002到2020年。上面的時間間隔是呈現錐形分布的，之所以這么設計，這是因為訂單表對當年發生的訂單的操作是最頻繁的，其次是1年前的訂單（頻繁），再就是2-3年的訂單（操作比較頻繁），再則就是3-10年的訂單（操作偶爾發生），最后是10年前的訂單（幾乎不再操作訂單），因此這個時間間隔是越來越大的。當然如果有對所有數據都有較為頻繁的操作，可以分18個區（2002年到2020年），給每年都分區。
3.3 創建分區方案
分區方案定義了一個特定的分區函數將使用的物理存儲結構（就是文件組），或者說是分區方案將分區函數生成的分區映射到我們定義的一組文件組。因此創建一個分區方案，需要分區函數和文件組名稱。
create partition scheme <分區方案名稱> as partition <分區函數名稱> [all]to (文件組名稱,....)
我們已經知道分區函數解決的就是HOW的問題， 而這個分區方案就是WHERE的問題，它把分區函數生成的分區映射映射到指定的一組文件組中。
create partition scheme myPartitionSchema as partition myPartitionFun to (myfilegroup1,myfilegroup2,myfilegroup3,myfilegroup4,myfilegroup5);
分區函數生成的分區數不能大于分區方案中指定的文件組數量。如果生成的分區數小于文件組的數量，那么多出的文件組，會被標記為下次使用的文件組。 myPartitionFun 指定了4臨界值因此會生成5個分區， myPartitionSchema 恰好指定了5個文件組一一對應5個分區。
創建好分區方案后，可以在數據庫 -> 存ⅲ⊿torage） -> 分區方案（Partition Schemas）中查看
3.4 創建分區表
我們已經創建了分區方案了，接下來就是把分區方案應用到數據表上，這就是創建分區表。
create table <表名> (
<列定義>
)on<分區方案名>(分區列名)
例如：
create table MyOrder
(
id bigint not null identity(1,1),
order_num nvarchar(32) not null,
order_status int not null,
createtime datetime not null,
updatetime datetime not null,
order_desc nvarchar(500) null
)
on myPartitionSchema(id);
上面是創建了一個新表，并且指定了分區方案。由于在2.準備測試數據中已經創建了數據表，因此這里我們再不需要新建表，只需要將原來的表轉化為分區表就可以了。
將普通表轉化為分區表
分區表需要按照某一個字段把數據通過分區方案分到不同的文件中，而這個作為分區條件的字段必需要有聚集索引才可以。之前創建的表 order_detail 的聚集索引是在 create_time 上的，這里我們并不需要任何的修改。值得一提的是，分區方案實際上是和聚集索引關聯的，而且如果你想要創建一個帶分區方案的聚集索引（也就是給表分區），那么只有先刪除之前的聚集索引，然后再創建一個帶分區的聚集索引。這種帶分區的聚集索引，也叫做分區索引。
--刪除以前的聚集索引
DROP INDEX [create_time_clustered_index] ON [dbo].[order_detail] WITH ( ONLINE = OFF )
GO
--創建分區索引
CREATE CLUSTERED INDEX [create_time_clustered_index] ON [dbo].[order_detail]
(
create_time
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [myPartitionSchema]([create_time])
也可以通過向導把普通表轉化為分區表
右鍵聚集索引 -> 屬性(Properties) -> 存儲(Storage)
向導創建分區索引的過程也是顯示吧舊聚集索引刪除，然后再創建一個新的聚集索引并且指定分區方案。
3.5 使用分區向導創建分區表
上面介紹的幾乎都是通過代碼實現的，Microsoft SQL Server Management Studio 提供了更方便的圖形化方式。
右鍵要分區的表 -> 存儲（Storage）-> 創建分區（Create Partition）-> 下一步
選擇要分區的列
選擇或創建分區函數
選擇或創建分區方案
指定臨界值，以及左邊界或右邊界。
3.6 秀一秀肌肉
到目前為止，分區表已經創建完成了，接下來就是秀一秀它的性能了。我準備了一張表 order_detail_non_partition ，數據和索引都和 order_detail 表一樣，只是 order_detail_non_partition 只是沒有分區。
--分區索引
select * from order_detail where create_time >= '2012-05-01 00:00:00' and create_time < '2019-06-01 00:00:00';
--無分區，聚集索引
select * from order_detail_non_partition where create_time >= '2012-05-01 00:00:00' and create_time < '2019-06-01 00:00:00';
筆者的打開了SQL SERVER Management Studio中的實時數據查詢功能。
上面查詢了2010年7月份到2020年9月份的所有數據，雖然查詢所花費的時間都差不多，但是分區表在性能方面還是明顯優于非分區表（I/O消耗，CPU消耗, 子樹的大小，運算符的開銷…）。
4. 關于表分區的常用管理
4.1 拆分分區
在分區函數中新增一個邊界值。
--分區拆分
alter partition function myPartitionFun()
split range(N'2005-01-01T00:00:00.000')
如果分區函數已經關聯了分區方案，那么分區數 不能大于分區方案中的文件組數。如果你的分區數和分區方案中的文件組數不符合要求，你可以先擴展分區方案中的文件組數，再擴展分區函數的臨界值。
擴展分區方案中的文件組數
--新建立一個數據文件
alter database mydb add filegroup myfilegroup6
GO
--新建一個文件組
alter database mydb add file
(name=N'myfile6',filename=N'D:\softwares\sqlserver\root\MSSQL14.MSSQLSERVER\MSSQL\DATA\myfile6.ndf',size=10Mb,filegrowth=5mb)
to filegroup myfilegroup6
GO
--添加文件組到分區方案
ALTER PARTITION SCHEME myPartitionSchema
NEXT USED myfilegroup6;
GO
4.2 合并分區
合并分區和拆分分區恰好相反，就是把兩個分區合并為一個分區，可以通過刪除分區函數中的臨界值來完成。
--合并分區
alter partition function myPartitionFun()
merge range(N'2005-01-01T00:00:00.000')
4.3 查看指定數據所在的分區
當進行了表分區后，數據就會分散存儲到不同的分區中。可以通過如下的命令，來查詢數據到底存到那個分區中的：
--查詢分區依據列為2020-08-28 14:34:02.890的數據在哪個分區上
select $partition.myPartitionFun(N'2020-08-28 14:34:02.890') --返回值是5，表示此值存在第5個分區
也可查詢所有非空分區中存在的數據行數
--查看分區表中，每個非空分區存在的行數
select $partition.myPartitionFun(create_time) as partitionNum,count(*) as recordCount
from order_detail
group by $partition.myPartitionFun(create_time)
或是查詢某個分區中的所有數據
---查看指定分區中的數據記錄
select * from order_detail where $partition.myPartitionFun(create_time)=5
該文章在 2024/7/22 10:59:44 編輯過
400 186 1886
