SQL大表转为分区表实例

SQL大表转为分区表实例
--  进行演示操作前, 先备份, 以便可以在演示完成后, 恢复到原始状态
USE  master
--  备份
BACKUP   DATABASE  AdventureWorks
    
TO   DISK   =   ' AdventureWorks.bak '
    
WITH  FORMAT

-- -- 恢复
--
RESTORE DATABASE AdventureWorks
--
    FROM DISK = 'AdventureWorks.bak'
--
    WITH REPLACE
GO

-- =========================================
--
 转换为分区表
--
=========================================
USE  AdventureWorks
GO

--  1. 创建分区函数
--
    a. 适用于存储历史存档记录的分区表的分区函数
DECLARE   @dt   datetime
SET   @dt   =   ' 20020101 '
CREATE  PARTITION  FUNCTION  PF_HistoryArchive( datetime )
AS  RANGE  RIGHT
FOR   VALUES (
    
@dt ,
    
DATEADD ( Year 1 @dt ))

--     b. 适用于存储历史记录的分区表的分区函数
--
DECLARE @dt datetime
SET   @dt   =   ' 20030901 '
CREATE  PARTITION  FUNCTION  PF_History( datetime )
AS  RANGE  RIGHT
FOR   VALUES (
    
@dt ,
    
DATEADD ( Month 1 @dt ),  DATEADD ( Month 2 @dt ),  DATEADD ( Month 3 @dt ),
    
DATEADD ( Month 4 @dt ),  DATEADD ( Month 5 @dt ),  DATEADD ( Month 6 @dt ),
    
DATEADD ( Month 7 @dt ),  DATEADD ( Month 8 @dt ),  DATEADD ( Month 9 @dt ),
    
DATEADD ( Month 10 @dt ),  DATEADD ( Month 11 @dt ),  DATEADD ( Month 12 @dt ))
GO

--  2. 创建分区架构
--
    a. 适用于存储历史存档记录的分区表的分区架构
CREATE  PARTITION SCHEME PS_HistoryArchive
AS  PARTITION PF_HistoryArchive
TO ( [ PRIMARY ] [ PRIMARY ] [ PRIMARY ] )

--     b. 适用于存储历史记录的分区表的分区架构
CREATE  PARTITION SCHEME PS_History
AS  PARTITION PF_History
TO ( [ PRIMARY ] [ PRIMARY ] ,
    
[ PRIMARY ] [ PRIMARY ] [ PRIMARY ] ,
    
[ PRIMARY ] [ PRIMARY ] [ PRIMARY ] ,
    
[ PRIMARY ] [ PRIMARY ] [ PRIMARY ] ,
    
[ PRIMARY ] [ PRIMARY ] [ PRIMARY ] )
GO

--  3. 删除索引
--
    a. 删除存储历史存档记录的表中的索引
DROP   INDEX  Production.TransactionHistoryArchive.IX_TransactionHistoryArchive_ProductID
DROP   INDEX  Production.TransactionHistoryArchive.IX_TransactionHistoryArchive_ReferenceOrderID_ReferenceOrderLineID

--     b. 删除存储历史记录的表中的索引
DROP   INDEX  Production.TransactionHistory.IX_TransactionHistory_ProductID
DROP   INDEX  Production.TransactionHistory.IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID
GO

--  4. 转换为分区表
--
    a. 将存储历史存档记录的表转换为分区表
ALTER   TABLE  Production.TransactionHistoryArchive
    
DROP   CONSTRAINT  PK_TransactionHistoryArchive_TransactionID
    
WITH (
        MOVE 
TO  PS_HistoryArchive(TransactionDate))

--     b.将存储历史记录的表转换为分区表
ALTER   TABLE  Production.TransactionHistory
    
DROP   CONSTRAINT  PK_TransactionHistory_TransactionID
    
WITH (
        MOVE 
TO  PS_History(TransactionDate))
GO

--  5. 恢复主键
--
    a. 恢复存储历史存档记录的分区表的主键
ALTER   TABLE  Production.TransactionHistoryArchive
    
ADD   CONSTRAINT  PK_TransactionHistoryArchive_TransactionID
        
PRIMARY   KEY   CLUSTERED (
            TransactionID,
            TransactionDate)

--     b. 恢复存储历史记录的分区表的主键
ALTER   TABLE  Production.TransactionHistory
    
ADD   CONSTRAINT  PK_TransactionHistory_TransactionID
        
PRIMARY   KEY   CLUSTERED (
            TransactionID,
            TransactionDate)
GO

--  6. 恢复索引
--
    a. 恢复存储历史存档记录的分区表的索引
CREATE   INDEX  IX_TransactionHistoryArchive_ProductID
    
ON  Production.TransactionHistoryArchive(
        ProductID)

CREATE   INDEX  IX_TransactionHistoryArchive_ReferenceOrderID_ReferenceOrderLineID
    
ON  Production.TransactionHistoryArchive(
        ReferenceOrderID,
        ReferenceOrderLineID)

--     b. 恢复存储历史记录的分区表的索引
CREATE   INDEX  IX_TransactionHistory_ProductID
    
ON  Production.TransactionHistory(
        ProductID)

CREATE   INDEX  IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID
    
ON  Production.TransactionHistory(
        ReferenceOrderID,
        ReferenceOrderLineID)
GO

--  7. 查看分区表的相关信息
SELECT
    SchemaName 
=  S.name,
    TableName 
=  TB.name,
    PartitionScheme 
=  PS.name,
    PartitionFunction 
=  PF.name,
    PartitionFunctionRangeType 
=   CASE
            
WHEN  boundary_value_on_right  =   0   THEN   ' LEFT '
            
ELSE   ' RIGHT '   END ,
    PartitionFunctionFanout 
=  PF.fanout,
    SchemaID 
=  S.schema_id,
    ObjectID 
=  TB. object_id ,
    PartitionSchemeID 
=  PS.data_space_id,
    PartitionFunctionID 
=  PS.function_id
FROM  sys.schemas S
    
INNER   JOIN  sys.tables TB
        
ON  S.schema_id  =  TB.schema_id
    
INNER   JOIN  sys.indexes IDX
        
on  TB. object_id   =  IDX. object_id
            
AND  IDX.index_id  <   2
    
INNER   JOIN  sys.partition_schemes PS
        
ON  PS.data_space_id  =  IDX.data_space_id
    
INNER   JOIN  sys.partition_functions PF
        
ON  PS.function_id  =  PF.function_id
GO

-- =========================================
--
 移动分区表数据
--
=========================================
--
 1. 为存储历史存档记录的分区表增加分区, 并接受从历史记录分区表移动过来的数据
--
    a. 修改分区架构, 增加用以接受新分区的文件组
ALTER  PARTITION SCHEME PS_HistoryArchive
NEXT  USED  [ PRIMARY ]

--     b. 修改分区函数, 增加分区用以接受从历史记录分区表移动过来的数据
DECLARE   @dt   datetime
SET   @dt   =   ' 20030901 '
ALTER  PARTITION  FUNCTION  PF_HistoryArchive()
SPLIT RANGE(
@dt )

--     c. 将历史记录表中的过期数据移动到历史存档记录表中
ALTER   TABLE  Production.TransactionHistory
    SWITCH PARTITION 
2
        
TO  Production.TransactionHistoryArchive PARTITION $PARTITION.PF_HistoryArchive( @dt )

--     d. 将接受到的数据与原来的分区合并
ALTER  PARTITION  FUNCTION  PF_HistoryArchive()
MERGE RANGE(
@dt )
GO

--  2. 将存储历史记录的分区表中不包含数据的分区删除, 并增加新的分区以接受新数据
--
    a. 合并不包含数据的分区
DECLARE   @dt   datetime
SET   @dt   =   ' 20030901 '
ALTER  PARTITION  FUNCTION  PF_History()
MERGE RANGE(
@dt )

--     b.  修改分区架构, 增加用以接受新分区的文件组
ALTER  PARTITION SCHEME PS_History
NEXT  USED  [ PRIMARY ]

--     c. 修改分区函数, 增加分区用以接受新数据
SET   @dt   =   ' 20041001 '
ALTER  PARTITION  FUNCTION  PF_History()
SPLIT RANGE(
@dt )
GO

-- =========================================
--
 清除历史存档记录中的过期数据
--
=========================================
--
 1. 创建用于保存过期的历史存档数据的表
CREATE   TABLE  Production.TransactionHistoryArchive_2001_temp(
    TransactionID 
int   NOT   NULL ,
    ProductID 
int   NOT   NULL ,
    ReferenceOrderID 
int   NOT   NULL ,
    ReferenceOrderLineID 
int   NOT   NULL
        
DEFAULT  (( 0 )),
    TransactionDate 
datetime   NOT   NULL
        
DEFAULT  ( GETDATE ()),
    TransactionType 
nchar ( 1 NOT   NULL ,
    Quantity 
int   NOT   NULL ,
    ActualCost 
money   NOT   NULL ,
    ModifiedDate 
datetime   NOT   NULL
        
DEFAULT  ( GETDATE ()),
    
CONSTRAINT  PK_TransactionHistoryArchive_2001_temp_TransactionID
        
PRIMARY   KEY   CLUSTERED (
            TransactionID,
            TransactionDate)
)

--  2. 将数据从历史存档记录分区表移动到第1步创建的表中
ALTER   TABLE  Production.TransactionHistoryArchive
    SWITCH PARTITION 
1
        
TO  Production.TransactionHistoryArchive_2001_temp

--  3. 删除不再包含数据的分区
DECLARE   @dt   datetime
SET   @dt   =   ' 20020101 '
ALTER  PARTITION  FUNCTION  PF_HistoryArchive()
MERGE RANGE(
@dt )

--  4. 修改分区架构, 增加用以接受新分区的文件组
ALTER  PARTITION SCHEME PS_HistoryArchive
NEXT  USED  [ PRIMARY ]

--  5. 修改分区函数, 增加分区用以接受新数据
SET   @dt   =   ' 20040101 '
ALTER  PARTITION  FUNCTION  PF_HistoryArchive()
SPLIT RANGE(
@dt )


查询分区信息:

;
WITH
TBINFO 
AS (
    
SELECT
        SchemaName 
=  S.name,
        TableName 
=  TB.name,
        PartitionScheme 
=  PS.name,
        PartitionFunction 
=  PF.name,
        PartitionFunctionRangeType 
=   CASE
                
WHEN  boundary_value_on_right  =   0   THEN   ' LEFT '
                
ELSE   ' RIGHT '   END ,
        PartitionFunctionFanout 
=  PF.fanout,
        SchemaID 
=  S.schema_id,
        ObjectID 
=  TB. object_id ,
        PartitionSchemeID 
=  PS.data_space_id,
        PartitionFunctionID 
=  PS.function_id
    
FROM  sys.schemas S
        
INNER   JOIN  sys.tables TB
            
ON  S.schema_id  =  TB.schema_id
        
INNER   JOIN  sys.indexes IDX
            
on  TB. object_id   =  IDX. object_id
                
AND  IDX.index_id  <   2
        
INNER   JOIN  sys.partition_schemes PS
            
ON  PS.data_space_id  =  IDX.data_space_id
        
INNER   JOIN  sys.partition_functions PF
            
ON  PS.function_id  =  PF.function_id
),
PF1 
AS (
    
SELECT  PFP.function_id, PFR.boundary_id, PFR.value, Type  =   CONVERT (sysname,
            
CASE  T.name
                
WHEN   ' numeric '   THEN   ' decimal '
                
WHEN   ' real '   THEN   ' float '
                
ELSE  T.name  END
            
+   CASE
                
WHEN  T.name  IN ( ' decimal ' ' numeric ' )
                    
THEN   QUOTENAME ( RTRIM (PFP. precision )
                        
+   CASE   WHEN  PFP.scale  >   0   THEN   ' , '   +   RTRIM (PFP.scale)  ELSE   ''   END ' () ' )
                
WHEN  T.name  IN ( ' float ' ' real ' )
                    
THEN   QUOTENAME (PFP. precision ' () ' )
                
WHEN  T.name  LIKE   ' n%char '
                    
THEN   QUOTENAME (PFP.max_length  /   2 ' () ' )
                
WHEN  T.name  LIKE   ' %char '   OR  T.name  LIKE   ' %binary '
                    
THEN   QUOTENAME (PFP.max_length,  ' () ' )
                
ELSE   ''   END )
    
FROM  sys.partition_parameters PFP
        
LEFT   JOIN  sys.partition_range_values PFR
            
ON  PFR.function_id  =  PFP.function_id
                
AND  PFR.parameter_id  =  PFP.parameter_id
        
INNER   JOIN  sys.types T
            
ON  PFP.system_type_id  =  T.system_type_id
),
PF2 
AS (
    
SELECT   *   FROM  PF1
    
UNION   ALL
    
SELECT
        function_id, boundary_id 
=  boundary_id  -   1 , value, type
    
FROM  PF1
    
WHERE  boundary_id  =   1
),
PF 
AS (
    
SELECT
        B.function_id, boundary_id 
=   ISNULL (B.boundary_id  +   1 1 ),
        value 
=   STUFF (
            
CASE
                
WHEN  A.boundary_id  IS   NULL   THEN   ''
                
ELSE   '  AND [partition_column_name]  '   +  PF.LessThan  +   '   '   +   CONVERT ( varchar ( max ), A.value)  END
            
+   CASE
                
WHEN  A.boundary_id  =   1   THEN   ''
                
ELSE   '  AND [partition_column_name]  '   +  PF.MoreThan  +   '   '   +   CONVERT ( varchar ( max ), B.value)  END ,
            
1 5 '' ),
        B.Type
    
FROM  PF1 A       
        
RIGHT   JOIN  PF2 B
            
ON  A.function_id  =  B.function_id
                
AND  (A.boundary_id  -   1   =  B.boundary_id
                    
OR (A.boundary_id  IS   NULL   AND  B.boundary_id  IS   NULL ))
        
INNER   JOIN (
            
SELECT
                function_id,
                LessThan 
=   CASE
                        
WHEN  boundary_value_on_right  =   0   THEN   ' <= '
                        
ELSE   ' < '   END ,
                MoreThan 
=   CASE
                        
WHEN  boundary_value_on_right  =   0   THEN   ' > '
                        
ELSE   ' >= '   END
            
FROM  sys.partition_functions
        )PF
            
ON  B.function_id  =  PF.function_id
),
PS 
AS (
    
SELECT
        DDS.partition_scheme_id, DDS.destination_id,
        FileGroupName 
=  FG.name, IsReadOnly  =  FG.is_read_only
    
FROM  sys.destination_data_spaces DDS
        
INNER   JOIN  sys.filegroups FG
            
ON  DDS.data_space_id  =  FG.data_space_id
),
PINFO 
AS (
    
SELECT
        RowID 
=  ROW_NUMBER()  OVER ( ORDER   BY  SchemaID, ObjectID, PS.destination_id),
        TB.SchemaName, TB.TableName,
        TB.PartitionScheme, PS.destination_id, PS.FileGroupName, PS.IsReadOnly,
        TB.PartitionFunction, TB.PartitionFunctionRangeType, TB.PartitionFunctionFanout,
        PF.boundary_id, PF.Type, PF.value
    
FROM  TBINFO TB
        
INNER   JOIN  PS
            
ON  TB.PartitionSchemeID  =  PS.partition_scheme_id
        
LEFT   JOIN  PF
            
ON  TB.PartitionFunctionID  =  PF.function_id
                
AND  PS.destination_id  =  PF.boundary_id
)
SELECT
    RowID,
    SchemaName 
=   CASE  destination_id
            
WHEN   1   THEN  SchemaName
            
ELSE  N ''   END ,
    TableName 
=   CASE  destination_id
            
WHEN   1   THEN  TableName
            
ELSE  N ''   END ,
    PartitionScheme 
=   CASE  destination_id
            
WHEN   1   THEN  PartitionScheme
            
ELSE  N ''   END ,
    destination_id, FileGroupName, IsReadOnly,
    PartitionFunction 
=   CASE  destination_id
            
WHEN   1   THEN  PartitionFunction
            
ELSE  N ''   END ,
    PartitionFunctionRangeType 
=   CASE  destination_id
            
WHEN   1   THEN  PartitionFunctionRangeType
            
ELSE  N ''   END ,
    PartitionFunctionFanout 
=   CASE  destination_id
            
WHEN   1   THEN   CONVERT ( varchar ( 20 ), PartitionFunctionFanout)
            
ELSE  N ''   END ,
    boundary_id 
=   ISNULL ( CONVERT ( varchar ( 20 ), boundary_id),  '' ),
    Type 
=   ISNULL (Type, N '' ),
    value 
=   CASE  PartitionFunctionFanout
            
WHEN   1   THEN   ' <ALL Data> '
            
ELSE   ISNULL (value, N ' <NEXT USED> ' END
FROM  PINFO
ORDER   BY  RowID




-- ==================================
--
drop database dbPartitionTest
   -- 测试数据库
   create   database  dbPartitionTest
  
go
  
use
  dbPartitionTest
  
go
  
-- 增加分组
   alter   database  dbPartitionTest  ADD  FILEGROUP P200801
  
alter   database  dbPartitionTest  ADD  FILEGROUP P200802
  
alter   database  dbPartitionTest  ADD  FILEGROUP P200803
  
go
  
-- 分区函数
   CREATE  PARTITION  FUNCTION  part_Year( datetime )
  
AS  RANGE  LEFT   FOR   VALUES
  (
  ’
20080131   23 : 59 : 59.997 ’,’ 20080229   23 : 59 : 59.997 ’,’ 20080331   23 : 59 : 59.997
  )
  
go
  
-- 增加文件组
   ALTER   DATABASE  dbPartitionTest  ADD   FILE  (NAME  =  N’P200801’,FILENAME  =  N’c:tb_P200801.ndf’,SIZE  =  1MB,MAXSIZE  =  500MB,FILEGROWTH  =  1MB) TO  FILEGROUP P200801
  
ALTER   DATABASE  dbPartitionTest  ADD   FILE  (NAME  =  N’P200802’,FILENAME  =  N’c:tb_P200802.ndf’,SIZE  =  1MB,MAXSIZE  =  500MB,FILEGROWTH  =  1MB) TO  FILEGROUP P200802
  
ALTER   DATABASE  dbPartitionTest  ADD   FILE  (NAME  =  N’P200803’,FILENAME  =  N’c:tb_P200803.ndf’,SIZE  =  1MB,MAXSIZE  =  500MB,FILEGROWTH  =  1MB) TO  FILEGROUP P200803
  
go
  
-- 分区架构
   CREATE  PARTITION SCHEME part_YearScheme  AS  PARTITION part_Year   TO  (P200801,P200802,P200803, [ PRIMARY ] )
  
go
  
CREATE   TABLE   [ dbo ] .t_part
  (name 
varchar ( 100 default   newid (),date  datetime   NOT   NULL )
  
ON  part_YearScheme (date)
  
go
  
-- 添加测试数据,每天1条
   declare   @date   datetime
  
set   @date = 2007 - 12 - 31
  
while   @date <= 2008 - 04 - 0
  
1   begin
  
insert   into  t_part(date) values ( @date )
  
set   @date = @date + 1
  
end
  
go
  
-- 查询数据分布在哪些分区
   select  $partition.part_Year(date)  as  分区编号, *   from  t_part  order   by  date
  
-- 查询数据库文件
   go
  sp_helpfile
<think>我们有一个需求:将MySQL按照创建时间按月划分为12个分区。 根据引用[1][2][3][4],我们可以知道: - 分区类型:这里我们使用RANGE分区,并且使用日期函数(如TO_DAYS, YEAR, MONTH等)来定义分区范围。 - 分区键:我们选择创建时间字段(假设字段名为create_time)。 - 分区数量:12个分区,每个分区对应一个月。 但是,注意:按月份分区通常我们会使用RANGE COLUMNS分区,并且使用日期字段。但是这里要求12个分区,也就是说,我们可能希望将数据按照一年中的12个月进行分区,而不考虑年份?或者我们是要按每个自然月(包括年份)来分区? 实际上,如果我们按每个自然月分区,那么每个分区包含一个月的所有数据(不同年份的同一个月会在不同分区)。但这样分区会随着时间推移而增加,而题目要求12个分区,这似乎意味着我们只保留最近12个月的数据,并自动滚动分区?或者我们固定分为12个分区,每个分区代一个月份(不考虑年份)?后者是不常见的,因为不同年份的同一个月数据会混在一起,而且这样会导致分区键值不唯一(比如2023年1月和2024年1月都会进入同一个1月分区?),这显然不合理。 因此,更合理的解释是:我们需要将数据按照每个自然月分区(即每个分区包含一个月的所有数据),并且我们可能希望只保留最近12个月的数据,然后定期删除旧分区并添加新分区(即滚动分区)。 但是题目要求是“划分为12个分区”,并没有说滚动。所以我们可以理解为:将过去12个月(从当前时间算起的前12个月)以及未来的数据分成12个分区?或者固定12个分区,每个分区代一个月份(比如1月、2月...12月),但这样会导致不同年份的同一个月数据进入同一个分区,这样在查询时如果指定年份和月份,实际上会扫描多个分区(因为同一个分区里有多个年份),而且分区键值范围重叠(比如2023年1月和2024年1月都在1月分区)?这显然是不符合分区规则的。 所以,我们通常的做法是:按时间范围(比如每个月的第一天到下一个月的第一天)来划分分区,每个分区对应一个自然月(包括年份)。这样,每个分区都是独立的,不会重叠。然后我们可以设置12个分区,但是随着时间的推移,我们需要添加新的分区(例如下个月的分区)并删除旧的分区(例如超过12个月前的分区),以保持总是有12个分区(即最近12个月)。 因此,我们需要: 1. 创建时定义12个分区,每个分区对应最近12个月(从当前时间往前推12个月,包括当前月)。 2. 设置一个定时任务(事件)来自动添加下个月的分区,并删除最旧的分区(即滚动分区)。 但是,题目没有明确说明是否滚动。根据问题描述“将MySQL按月划分为12个分区”,我们可以理解为固定12个分区,每个分区代一个月份(1-12),不考虑年份?但这样是不符合分区要求的,因为分区键的值(日期)是连续的,而1-12月份是循环的,所以不能这样分。 因此,我们只能按照时间范围来分区,并且需要滚动维护12个分区(即保留最近12个月的数据,每个月滚动更新分区)。 下面,我们按照滚动分区的方式(即保留最近12个月的数据)来设计。 步骤: 1. 创建分区表:使用RANGE分区,按月份划分,初始创建12个分区,覆盖从当前月开始的往前12个月(包括当前月)的每个自然月。 2. 创建存储过程,用于每月自动添加下一个月的分区,并删除最早的分区(即保持12个分区)。 3. 创建事件,定期(比如每月一次)调用存储过程。 但是,注意:在MySQL中,分区的管理要求分区表达式必须是递增的,并且我们使用日期字段(如create_time)作为分区键。 假设我们的结构如下(以引用[2]中的为例,但根据需求调整): 名:your_table 有一个日期字段:create_time(DATE类型) 创建分区表语句(初始分区): 我们首先计算当前日期(假设为2024年6月),然后创建12个分区,分别对应2023年7月到2024年6月(即12个月)。 注意:分区范围使用TO_DAYS函数将日期转换为天数。 分区定义示例(以每个月作为一个分区): 分区p202307:VALUES LESS THAN (TO_DAYS('2023-08-01')) 分区p202308:VALUES LESS THAN (TO_DAYS('2023-09-01')) ... 分区p202406:VALUES LESS THAN (TO_DAYS('2024-07-01')) 但是,我们不可能手动计算12个月,所以我们可以先创建,然后通过存储过程来初始化分区?或者我们可以在创建时使用动态SQL?实际上,创建时我们需要指定具体的分区。 因此,我们可以先手动创建初始分区(12个),然后通过存储过程来维护。 下面,我们按照引用[2]中的示例来创建。 创建语句(初始分区): 注意:主键必须包含分区键(在MySQL中,如果有主键,则分区键必须是主键的一部分,或者是主键的前缀)。因此,我们需要将create_time包含在主键中,或者使用联合主键。 假设原有主键id,我们可以创建联合主键(id, create_time)?或者将create_time作为唯一索引的一部分?但是这样可能会改变结构。 另一种做法:不使用主键,但这样可能不符合业务需求。或者,我们可以使用分区键作为主键的一部分,但这样可能会导致主键变得很。 根据引用[4]中的例子,他们使用了联合主键(id, dete),其中dete是日期字段。所以我们也需要将create_time加入主键。 但是,如果原已经有主键(比如自增id),那么我们需要修改主键为(id, create_time)?注意:这样主键就不是唯一的了,因为同一个id可能出现在不同的create_time上?所以我们需要确保业务上id在create_time上是唯一的?或者我们使用一个新的唯一键(比如(id, create_time))作为主键。 因此,创建语句如下(假设名为your_table): ```sql CREATE TABLE your_table ( id INT NOT NULL AUTO_INCREMENT, create_time DATE NOT NULL, -- 其他字段 PRIMARY KEY (id, create_time) -- 联合主键,包含分区键 ) PARTITION BY RANGE (TO_DAYS(create_time)) ( PARTITION p202307 VALUES LESS THAN (TO_DAYS('2023-08-01')), PARTITION p202308 VALUES LESS THAN (TO_DAYS('2023-09-01')), -- ... 其他10个分区 PARTITION p202406 VALUES LESS THAN (TO_DAYS('2024-07-01')) ); ``` 但是,我们如何自动创建这12个分区?我们可以先计算当前日期,然后计算过去12个月的每个月的分区范围。 然而,在创建时,我们只能手动指定。所以我们可以先创建,然后通过ALTER TABLE来添加分区(但初始创建时至少要有一个分区,或者我们可以先创建一个的分区,然后再拆分?)。 另一种做法:先创建,然后通过存储过程来添加12个初始分区。 但是,如果已经存在,我们需要修改结构来添加分区?这需要用到引用[4]中的方法。 根据问题,我们假设是新建。 但是,如果已经存在,我们需要先修改结构(添加联合主键)并添加分区?这可能会很耗时,因为需要重建。 因此,我们分两种情况: 情况1:新建 情况2:已有 由于问题没有明确,我们以新建为例。 但是,初始分区如何设置?我们需要覆盖最近12个月(包括当前月)。例如,当前是2024年6月,那么我们需要从2023年7月到2024年6月(共12个月)的分区。 我们可以这样写创建的语句(以当前日期为基准,动态生成SQL语句,但需要外部程序生成,或者使用存储过程生成并执行,但在创建时不能使用存储过程)。 因此,我们可以在应用层生成创建SQL语句。 下面,我们给出一个示例的创建语句(以当前日期为基准,生成过去12个月的分区): 假设当前日期为'2024-06-15',那么: 第一个分区:2023年7月 -> 分区名p202307,范围小于'2023-08-01' 最后一个分区:2024年6月 -> 分区名p202406,范围小于'2024-07-01' 然后,我们需要一个存储过程来每月自动维护分区(添加下个月的分区,删除最早的分区)。 存储过程`create_partition_for_next_month`(参考引用[2]): 功能: 1. 添加下个月的分区(例如,当前是2024年6月,那么下个月是2024年7月,添加分区p202407,范围小于'2024-08-01') 2. 删除最早的分区(例如,当前有12个分区,从2023年7月到2024年6月,那么添加2024年7月后,删除2023年7月的分区) 但是,注意:我们只能添加比当前最分区范围更的分区(RANGE分区要求分区范围递增),所以添加下个月分区是可行的。删除最旧的分区也是可行的。 存储过程代码(参考引用[2]): 注意:这里我们假设名为`your_table`,分区名的命名规则为'p' + 年月(如p202307) 步骤: 1. 获取当前最分区的分区名和范围(即最后一个分区) 2. 计算下一个月的第一天和下下个月的第一天 3. 添加分区(使用下下个月的第一天作为分区的上限) 4. 删除最早的分区(即第一个分区) 但是,在存储过程中,我们需要动态生成SQL语句并执行。 参考引用[2]中的存储过程: ```sql DELIMITER $$ CREATE PROCEDURE create_partition_for_next_month() BEGIN DECLARE next_month_start DATE; DECLARE next_month_end DATE; DECLARE next_partition_name VARCHAR(20); DECLARE oldest_partition_name VARCHAR(20); DECLARE current_max_value DATE; -- 获取当前最分区的范围(即最后一个分区的上限值,它是TO_DAYS值,我们需要将其转换为日期) -- 我们可以通过查询information_schema.partitions来获取最后一个分区的范围描述 SELECT MAX(PARTITION_DESCRIPTION) INTO @max_description FROM information_schema.partitions WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'your_table' AND PARTITION_NAME IS NOT NULL; -- 注意:PARTITION_DESCRIPTION是字符串,如TO_DAYS('2024-07-01'),我们需要提取日期字符串 -- 这里我们假设分区描述是类似"TO_DAYS('2024-07-01')"的字符串,我们可以使用字符串函数提取日期 SET @max_date = SUBSTRING_INDEX(SUBSTRING_INDEX(@max_description, "'", 2), "'", -1); SET current_max_value = STR_TO_DATE(@max_date, '%Y-%m-%d'); -- 计算下个月的第一天(即当前最分区对应的月份的下一个月) SET next_month_start = DATE_ADD(current_max_value, INTERVAL 1 MONTH); SET next_month_end = DATE_ADD(next_month_start, INTERVAL 1 MONTH); SET next_partition_name = CONCAT('p', DATE_FORMAT(next_month_start, '%Y%m')); -- 添加分区 SET @sql_add = CONCAT('ALTER TABLE your_table ADD PARTITION (PARTITION ', next_partition_name, ' VALUES LESS THAN (TO_DAYS(\'', DATE_FORMAT(next_month_end, '%Y-%m-%d'), '\')))'); PREPARE stmt FROM @sql_add; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- 删除最早的分区 -- 获取最早的分区名 SELECT MIN(PARTITION_NAME) INTO oldest_partition_name FROM information_schema.partitions WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'your_table' AND PARTITION_NAME IS NOT NULL; SET @sql_drop = CONCAT('ALTER TABLE your_table DROP PARTITION ', oldest_partition_name); PREPARE stmt FROM @sql_drop; EXECUTE stmt; DEALLOCATE PREPARE stmt; END$$ DELIMITER ; ``` 然后,我们需要创建一个事件,每月执行一次这个存储过程(例如,在每个月最后一天执行)。 创建事件: ```sql CREATE EVENT IF NOT EXISTS event_partition_maintenance ON SCHEDULE EVERY 1 MONTH STARTS TIMESTAMP(DATE_ADD(CURDATE(), INTERVAL 1 MONTH - DAY(CURDATE()) + 1 DAY), '23:59:00') -- 下个月的第一天前一天的23:59:00(即本月最后一天23:59:00) DO CALL create_partition_for_next_month(); ``` 注意:事件调度器需要开启。可以通过`SET GLOBAL event_scheduler = ON;`开启。 但是,以上存储过程有几点需要注意: 1. 在获取最分区的范围描述时,我们假设分区描述是`TO_DAYS('日期')`的字符串,并且我们提取了日期字符串。这依赖于分区描述的具体格式。 2. 删除最早的分区时,我们直接删除分区名最小的那个分区。这依赖于分区名的命名(我们使用'p'+年月,并且按年月排序,所以最小的分区名就是最早的分区)。 另外,我们还需要考虑初始分区数量。在创建时,我们创建了12个分区。然后每个月执行一次存储过程,就会添加一个分区并删除一个分区,所以分区数保持12个。 但是,如果初始分区不是12个,或者我们想要保留更多或更少的分区,我们可以在存储过程中调整。这里我们固定保留12个分区。 对于已有的情况,我们可以参考引用[4]中的方法,使用新迁移数据的方式添加分区。 总结: 1. 创建时使用联合主键(包含create_time)和按TO_DAYS(create_time)分区的12个初始分区(覆盖最近12个月)。 2. 创建存储过程,用于每月添加下个月的分区并删除最早的分区。 3. 创建事件,每月自动调用存储过程。 注意:在实际操作前,请确保MySQL版本支持分区(如MySQL 5.6及以上),并且分区功能已启用。 另外,由于分区管理操作(添加和删除分区)可能会锁,所以建议在业务低峰期执行。 以上方案实现了按月分区,并且保持12个分区(即保留最近12个月的数据)。 但是,如果业务需要保存超过12个月的数据,那么就不应该删除旧分区。这种情况下,我们可能不需要删除分区,而是只添加新分区(这样分区数量会不断增加)。但题目要求12个分区,所以我们采用滚动删除。 因此,我们按照上述步骤实现。 下面,我们生成相关问题。</think>### MySQL按创建时间每月分区(12个分区)实现方案 #### 1. 创建分区表(新建) ```sql CREATE TABLE your_table ( id INT NOT NULL AUTO_INCREMENT, create_time DATETIME NOT NULL, -- 分区键 content VARCHAR(255), PRIMARY KEY (id, create_time) -- 必须包含分区键 ) PARTITION BY RANGE (TO_DAYS(create_time)) ( PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')), PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')), PARTITION p202303 VALUES LESS THAN (TO_DAYS('2023-04-01')), PARTITION p202304 VALUES LESS THAN (TO_DAYS('2023-05-01')), PARTITION p202305 VALUES LESS THAN (TO_DAYS('2023-06-01')), PARTITION p202306 VALUES LESS THAN (TO_DAYS('2023-07-01')), PARTITION p202307 VALUES LESS THAN (TO_DAYS('2023-08-01')), PARTITION p202308 VALUES LESS THAN (TO_DAYS('2023-09-01')), PARTITION p202309 VALUES LESS THAN (TO_DAYS('2023-10-01')), PARTITION p202310 VALUES LESS THAN (TO_DAYS('2023-11-01')), PARTITION p202311 VALUES LESS THAN (TO_DAYS('2023-12-01')), PARTITION p202312 VALUES LESS THAN (TO_DAYS('2024-01-01')) ); ``` #### 2. 已有添加分区(需数据迁移) ```sql -- 步骤1:创建新分区表(结构同原) CREATE TABLE new_table LIKE your_table; -- 步骤2:修改分区表 ALTER TABLE new_table PARTITION BY RANGE (TO_DAYS(create_time)) ( PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')), ... -- 同新建的分区定义 ); -- 步骤3:迁移数据 INSERT INTO new_table SELECT * FROM your_table; -- 步骤4:重命名 RENAME TABLE your_table TO old_table, new_table TO your_table; ``` #### 3. 自动分区管理(滚动维护12个月) ```sql -- 创建存储过程(每月添加新分区+删除旧分区) DELIMITER $$ CREATE PROCEDURE maintain_partitions() BEGIN DECLARE next_month DATE; DECLARE oldest_part VARCHAR(20); -- 添加下个月分区 SET next_month = DATE_FORMAT(NOW() + INTERVAL 1 MONTH, '%Y-%m-01'); SET @add_sql = CONCAT( 'ALTER TABLE your_table ADD PARTITION (', 'PARTITION p', DATE_FORMAT(next_month, '%Y%m'), ' VALUES LESS THAN (TO_DAYS(\'', DATE_FORMAT(next_month + INTERVAL 1 MONTH, '%Y-%m-01'), '\'))' ); PREPARE stmt FROM @add_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- 删除最旧分区(保持12个分区) SELECT PARTITION_NAME INTO oldest_part FROM information_schema.PARTITIONS WHERE TABLE_NAME = 'your_table' ORDER BY PARTITION_ORDINAL_POSITION ASC LIMIT 1; SET @drop_sql = CONCAT('ALTER TABLE your_table DROP PARTITION ', oldest_part); PREPARE stmt FROM @drop_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END$$ DELIMITER ; -- 创建定时事件(每月1号执行) CREATE EVENT partition_maintenance_event ON SCHEDULE EVERY 1 MONTH STARTS TIMESTAMP(DATE_FORMAT(NOW() + INTERVAL 1 MONTH, '%Y-%m-01 02:00:00')) DO CALL maintain_partitions(); -- 启用事件调度器 SET GLOBAL event_scheduler = ON; ``` #### 4. 关键配置说明 1. **分区键选择** - 必须使用`DATETIME`或`DATE`类型字段 - 主键必须包含分区键(联合主键)[^4] 2. **分区函数** - `TO_DAYS()`:将日期转为天数(推荐) - `YEAR()`+`MONTH()`:需创建达式分区(MySQL 8.0+) 3. **分区命名规范** - 建议使用`pYYYYMM`格式(如`p202306`) - 避免使用MySQL保留字 #### 5. 查询优化示例 ```sql -- 查询2023年3月数据(仅扫描单个分区) SELECT * FROM your_table PARTITION (p202303) WHERE create_time BETWEEN '2023-03-01' AND '2023-03-31'; -- 查看分区使用情况 SELECT PARTITION_NAME, TABLE_ROWS FROM information_schema.PARTITIONS WHERE TABLE_NAME = 'your_table'; ``` #### 注意事项 1. 分区字段不能为`NULL` 2. 最分区数:MySQL 8.0为8192个 3. `ALTER TABLE`操作会锁,建议在低峰期执行 4. 使用`EXPLAIN PARTITIONS`验证分区裁剪效果 5. 备份`mysql.event`防止事件配置丢失 [^1]: MySQL分区表通过物理分隔数据提升查询性能,特别适合时间序列数据 [^2]: 自动分区维护需要事件调度器权限,存储过程实现滚动更新 [^3]: 分区键必须包含在主键中,这是MySQL分区表的强制约束 [^4]: 使用`TO_DAYS()`函数可确保分区表达式效率最优 --- ### 相关问题 1. **如何验证MySQL分区表是否生效?** - 使用`EXPLAIN PARTITIONS`查看查询涉及的分区 - 检查`information_schema.PARTITIONS`的分区数据分布 2. **分区表与分库分的本质区别是什么?** - 分区是单实例内的物理数据分隔 - 分库分涉及跨实例/跨服务器的数据分布 3. **按月分区时如何处理闰月数据?** - MySQL的`TO_DAYS()`函数自动处理日期差异 - 分区边界使用`YYYY-MM-01`格式可避免歧义 4. **分区表有哪些性能陷阱需要注意?** - 跨分区查询可能比未分区表更慢 - `MAX()`/`MIN()`聚合函数可能扫描所有分区 - 索引在分区内独立维护,需确保索引有效性 5. **如何备份特定分区的数据?** - 使用`SELECT ... INTO OUTFILE`导出分区数据 - 物理备份可通过复制分区对应.ibd文件实现 - Percona XtraBackup支持单分区备份
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值