数据库分区（上）

SQL 分区表demo

ps：分区表只能在创建的时候指定为分区表

一，简介

表分区主要用于：
提供性能：
这个是大多人数分区的目的，把一个表分部到不同的硬盘或其他存储介质中，会大大提升查询速度。
提高稳定性：
当一个分区出了问题，不会影响其他分区，仅仅是当前坏的分区不可用。
便于管理：
把一个大表分成若干个小表，则备份和恢复的时候不再需要备份整个表，可以单独备份分区。
存档：
将一些不太常用的数据，单独存放。如：将1年前的数据记录分到一个专门的存档服务器存放。

二，具体流程

1 定义分区函数 ：判断一行数据属于哪个分区

CREATE PARTITION FUNCTION FNATTEND(DATE)
AS RANGE RIGHT
FOR VALUES('2017-01-01','2018-01-01','2019-01-01')

partition :分区
fnattend :函数名（自由定义） date：标记函数数据类型
range righe/left: 决定临界值归属于左边还是右边

SELECT * FROM SYS.partition_functions

查看分区表是否创建成功

2 定义分区架构： 分配每个区属于哪个文件组

CREATE PARTITION SCHEME SchemaForParirion 
AS PARTITION FNATTEND TO
(fileGroup1,fileGroup2,fileGroup3,[PRIMARY])

scheme: 架构
schemaforparirion: 架构名
fanttend；上一步编写的分区函数名
fileGroupX,primary:文件组

select * FROM SYS.partition_schemes

查看已创建的分区架构

3 定义分区表

CREATE TABLE DEMO
(id int,orderId int,salesDate DATE)
ON SchemaForParirion(salesDate)

SchemaForParirion(salesDate)：上一步创建的分区架构，括号中为指定的分区标记列

select convert(varchar(50), ps.name) as partition_scheme,
p.partition_number, 
convert(varchar(10), ds2.name) as filegroup, 
convert(varchar(19), isnull(v.value, ''), 120) as range_boundary, 
str(p.rows, 9) as rows
from sys.indexes i 
join sys.partition_schemes ps on i.data_space_id = ps.data_space_id 
join sys.destination_data_spaces dds
on ps.data_space_id = dds.partition_scheme_id 
join sys.data_spaces ds2 on dds.data_space_id = ds2.data_space_id 
join sys.partitions p on dds.destination_id = p.partition_number
and p.object_id = i.object_id and p.index_id = i.index_id 
join sys.partition_functions pf on ps.function_id = pf.function_id 
LEFT JOIN sys.Partition_Range_values v on pf.function_id = v.function_id
and v.boundary_id = p.partition_number - pf.boundary_value_on_right 
WHERE i.object_id = object_id('DEMO')    --此处是表名
and i.index_id in (0, 1) 
order by p.partition_number

查询分区是否成功

4 分区表分割：

原有分区上，创建新的分区，将原有的分区需要分隔内容插入新的分区，删除老的分区的内容（仅删除划入新分区部分）；

此操作非常消耗IO，且分割过程会锁住需要重新分割的分区，造成该分区暂时不可用，且记录日志是转移数据的4倍

4.1确认新建分区放到哪个文件组

ALTER PARTITION SCHEME SchemaForParirion NEXT USED 'fileGroup1'

4.2建立新的分割点

ALTER PARTITION FUNCTION FNATTEND() SPLIT RANGE('2020-01-01')

5 分区表的合并

分区分割的逆操作，合并分割点必须存在
------合并后，数据存放的文件组取决于最开始定义分区函数的时候是left还是right，如果是left，则左边的分区合并到右边

ALTER PARTITION FUNCTION FNATTEND() MERGE RANGE('2017-01-01')

6 分区查询sql语句用例

SELECT * 
FROM DEMO d WHERE d.orderId = 2 
and $partition.fnattend(salesDate)=1 

其他语句类推即可

ps：
若需创建唯一聚集索引，则唯一索引键必须 适合分区方案，且必须包含分区列，即文中的salesDate

参考博客：https://www.cnblogs.com/kissdodog/p/3156758.html