SQL Server按月分区实战之动态边界自动生成方案

┌─────────────────────────────────────┐
│  多张设备参数表                      │
│  ├── 表1: xxx参数数据                │
│  ├── 表2: xxx参数数据                │
│  ├── ...                            │
│  └── 表n: xxx参数数据                │
│  共同特征：均有 create_time 时间戳    │
└─────────────────────────────────────┘

分区函数定义:
create partition function pf_monthly(datetime2)
as range right for values('2023-06-01','2023-07-01','2023-08-01')

实际分区映射:
┌──────────┬─────────────────┬─────────────────┬──────────────────┐
│ 分区 1   │ 分区 2          │ 分区 3          │ 分区 4           │
│ (-∞,     │ [2023-06-01,    │ [2023-07-01,    │ [2023-08-01,     │
│ 2023-06) │  2023-07-01)    │  2023-08-01)    │  +∞)             │
└──────────┴─────────────────┴─────────────────┴──────────────────┘

-- 查询6月数据时，where条件自然对应当月：
select * from table 
where create_time >= '2023-06-01' 
  and create_time <  '2023-07-01'

-- range right 下，'2023-06-01' 归入分区2（6月）
-- 分区消除精准命中，不会跨区

原始数据最早时间: 2023-06-15 08:30:00
     ↓ 对齐到月初
分区起始边界:     2023-06-01

好处:
✓ 每个分区完整对应一个自然月
✓ 查询逻辑直观，不需要记住偏移量
✓ 运维时按自然月扩展/合并，不易出错

-- ============================================================
-- 脚本功能：动态创建按月分区函数
-- 适用场景：多张业务表需要统一按月分区
-- 特性：
--   1. 自动识别数据起始时间
--   2. 自动预留未来12个月分区
--   3. 支持重复执行（幂等）
--   4. 空表保护
-- ============================================================

declare
    @mindate   date,               -- 最早数据日期
    @loopdate  date,               -- 循环游标
    @futureend date,               -- 分区终点
    @valstr    nvarchar(max) = n'',-- 边界值拼接
    @sqlfunc   nvarchar(max);      -- 动态sql

-- ─────────────────────────────────────────
-- 步骤1：联合查询所有目标表的最小时间
-- ─────────────────────────────────────────
select @mindate = min(t.mindt)
from (
    select cast(min(create_time) as date) from biz_param_data_table1
    union all
    select cast(min(create_time) as date) from biz_param_data_table2
    union all
    select cast(min(create_time) as date) from biz_param_data_table3
    -- ... 追加更多表
) t;

-- ─────────────────────────────────────────
-- 步骤2：空数据处理 + 月初对齐
-- ─────────────────────────────────────────
if @mindate is null
    set @mindate = datefromparts(year(getdate()), month(getdate()), 1);

set @mindate   = datefromparts(year(@mindate), month(@mindate), 1);
set @futureend = dateadd(month, 12, getdate());
set @loopdate  = @mindate;

-- ─────────────────────────────────────────
-- 步骤3：生成边界值列表
-- ─────────────────────────────────────────
while @loopdate <= @futureend
begin
    set @valstr += n'''' + convert(varchar, @loopdate, 120) + n''' ,';
    set @loopdate = dateadd(month, 1, @loopdate);
end
set @valstr = left(@valstr, len(@valstr) - 1);

-- ─────────────────────────────────────────
-- 步骤4：幂等创建分区函数
-- ─────────────────────────────────────────
if not exists (
    select 1 from sys.partition_functions 
    where name = 'pf_month_device_data'
)
begin
    set @sqlfunc = n'
    create partition function pf_month_device_data(datetime2)
    as range right for values(' + @valstr + n');
    ';
    exec sp_executesql @sqlfunc;
    print '分区函数创建成功。边界数量: ' + cast(len(@valstr)-len(replace(@valstr,',',''))+1 as varchar);
end
else
    print '分区函数已存在，跳过创建。';

-- ============================================================
-- 创建分区方案，指定文件组映射
-- ============================================================
if not exists (
    select 1 from sys.partition_schemes 
    where name = 'ps_month_device_data'
)
begin
    create partition scheme ps_month_device_data
    as partition pf_month_device_data
    all to ([primary]);
    print '分区方案创建成功。';
end

-- ============================================================
-- 为业务表创建分区聚集索引
-- ⚠️ 执行前请确认处于业务低峰期
-- ============================================================
create clustered index ix_tablename_create_time
    on biz_param_data_table1(create_time)
    on ps_month_device_data(create_time);

┌────────────────────────────────────────────────────────────┐
│                     脚本执行流程                            │
└────────────────────────────────────────────────────────────┘

  开始
   │
   ▼
┌─────────────────┐    空     ┌──────────────────┐
│ 查询所有表最早   │─────────→│ 使用当前月1号     │
│ create_time     │  数据    │ 作为起始边界      │
└────────┬────────┘          └────────┬─────────┘
         │ 有数据                     │
         ▼                            ▼
┌─────────────────────────────────────────┐
│ 将最早时间对齐到当月1号                   │
│ datefromparts(year, month, 1)           │
└────────────────────┬────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────┐
│ 计算终止边界 = getdate() + 12个月        │
└────────────────────┬────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────┐
│ while 循环生成边界字符串                 │
│ '2023-06-01','2023-07-01',...          │
└────────────────────┬────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────┐
│ 检查分区函数是否存在                     │
│ 不存在 → 动态执行 create partition      │
│ 已存在 → 跳过                           │
└─────────────────────────────────────────┘
                     │
                     ▼
                  结束

-- ❌ 错误写法：直接拼日期，易出现语言/格式问题
set @valstr += @loopdate + ',';

-- ✅ 正确写法：convert 指定 style 120 (yyyy-mm-dd)
set @valstr += n'''' + convert(varchar, @loopdate, 120) + n''' ,';

-- 循环拼接后的字符串：
-- '2023-06-01' ,'2023-07-01' ,'2023-08-01' ,
--                                            ↑ 多余逗号

-- 去除尾逗号，保留有效边界：
set @valstr = left(@valstr, len(@valstr) - 1);
-- 结果：'2023-06-01' ,'2023-07-01' ,'2023-08-01'

create partition function pf_month_device_data(datetime2)  -- ← 这里

-- 通过系统视图检查对象是否存在
if not exists (
    select 1 from sys.partition_functions 
    where name = 'pf_month_device_data'
)

-- 查看全部分区边界及对应的分区号
select 
    p.boundary_id            as 边界序号,
    p.value                  as 边界值,
    p.boundary_id + 1        as 对应分区号
from sys.partition_functions pf
join sys.partition_range_values p 
    on p.function_id = pf.function_id
where pf.name = 'pf_month_device_data'
order by p.boundary_id;

-- 查看每个分区的数据量及时间范围
select 
    $partition.pf_month_device_data(create_time) as 分区号,
    count(*)                                      as 记录数,
    min(create_time)                              as 最早记录,
    max(create_time)                              as 最晚记录
from biz_param_data_table1
group by $partition.pf_month_device_data(create_time)
order by 分区号;

-- 开启统计信息，验证是否仅扫描目标分区
set statistics io on;

select count(*) 
from biz_param_data_table1
where create_time >= '2024-03-01' 
  and create_time <  '2024-04-01';

set statistics io off;

-- 查看消息窗口的 "逻辑读取" 次数
-- 正确分区消除时，读取页数应远小于全表

-- 建议每月1号定时执行
declare @newmonth date = datefromparts(year(getdate()), month(getdate()), 1);
set @newmonth = dateadd(month, 1, @newmonth);

alter partition scheme ps_month_device_data 
    next used [primary];

alter partition function pf_month_device_data()  
    split range (@newmonth);

print '已新增分区边界: ' + cast(@newmonth as varchar);

-- 将指定月份的数据快速迁出
-- 第1步：创建结构相同的归档表
select top 0 * 
into biz_param_data_table1_archive_202306
from biz_param_data_table1;

-- 第2步：切换分区（秒级完成，仅修改元数据）
alter table biz_param_data_table1
    switch partition 2 to biz_param_data_table1_archive_202306;

-- 第3步：合并空分区
alter partition function pf_month_device_data()  
    merge range ('2023-07-01');

-- ============================================================
-- 月度分区维护作业
-- 执行频率：每月1号 02:00
-- ============================================================
begin try
    begin transaction;
    
    -- 1. 扩展新月份
    declare @newboundary date;
    set @newboundary = dateadd(month, 1, 
        datefromparts(year(getdate()), month(getdate()), 1));
    
    alter partition scheme ps_month_device_data next used [primary];
    alter partition function pf_month_device_data() 
        split range (@newboundary);
    
    -- 2. 日志记录
    insert into maintenance_log (operation, detail, exec_time)
    values ('partition_split', 
            '边界值:' + cast(@newboundary as varchar), 
            getdate());
    
    commit transaction;
    print '分区维护成功完成';
end try
begin catch
    rollback transaction;
    print '分区维护失败: ' + error_message();
end catch

if @mindate is null
    set @mindate = datefromparts(year(getdate()), month(getdate()), 1);

select 
    t.name           as 表名,
    i.name           as 索引名,
    ps.name          as 分区方案
from sys.tables t
join sys.indexes i on t.object_id = i.object_id
join sys.partition_schemes ps on i.data_space_id = ps.data_space_id
where i.type = 1;  -- 1 = clustered