怎么根据日期中的天来greenplum 创建分区表表
点击联系发帖人
时间：2016-07-25 09:19
oracle按时间创建分区表
首先明确分区表和表分区的区别：表分区是一种思想，分区表示一种技术实现。当表的大小过G的时候可以考虑进行表分区，提高查询效率，均衡IO。分区表是数据库提供的一种表分区的实现形式。表进行分区后，逻辑上仍然是一张表，原来的查询SQL同样生效，同时可以采用使用分区查询来优化SQL查询效率，不至于每次都扫描整个表
一、分区表基本操作
1、按时间分区表创建：&
create table t_test (
number(30)
add_date_time
constraintPK_T_TEST primary key (pk_id)
PARTITION BY RANGE (add_date_time)
PARTITIONt_test_2013_less VALUES LESS THAN (TO_DATE(' 00:00:00','yyyy-mm-ddhh24:mi:ss')) TABLESPACE TS_MISPS,
PARTITIONt_test_2013 VALUES LESS THAN (TO_DATE(' 00:00:00','yyyy-mm-ddhh24:mi:ss')) TABLESPACE TS_MISPS,
PARTITION t_test_2014VALUES LESS THAN (TO_DATE(' 00:00:00','yyyy-mm-dd hh24:mi:ss'))TABLESPACE TS_MISPS
其中add_date_time为分区字段，每一年一个分区。
插入100W数据
yearVARCHAR2(20);
year := CASEmod(i, 3)
WHEN 0 THEN
' 12:00:00'
WHEN 1 THEN
' 12:00:00'
' 12:00:00'
insert into t_test values(i, to_date(year, 'yyyy-mm-dd hh24:mi:ss'));
exit when i= 1000000;
i := i + 1;
查看分区表的分区的详细信息
Select table_name,partition_name,high_value fromdba_tab_partitions where table_name='T_TEST';
2、分区表修改
2.1增加一个分区
分两种情况：1.没有maxvalue分区。2.有maxvalue分区。我们创建的分区就是没有maxValue的分区
1.没有maxvalue分区添加新分区：
alter table t_test add partition t_test_2015 VALUESLESS THAN (TO_DATE(' 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACETS_MISPS ;
2、有maxvalue分区添加新分区：
有了maxvalue，就不能直接add partition，而是需要max分区split。例如我们将创建的分区的语句修改下：
create table t_test (
number(30)
add_date_time
constraintPK_T_TEST primary key (pk_id)
PARTITION BY RANGE (add_date_time)
PARTITIONt_test_2013_less VALUES LESS THAN (TO_DATE(' 00:00:00','yyyy-mm-ddhh24:mi:ss')) TABLESPACE TS_MISPS,
PARTITIONt_test_2013 VALUES LESS THAN (TO_DATE(' 00:00:00','yyyy-mm-ddhh24:mi:ss')) TABLESPACE TS_MISPS,
PARTITIONt_test_2014 VALUES LESS THAN (TO_DATE(' 00:00:00','yyyy-mm-ddhh24:mi:ss')) TABLESPACE TS_MISPS,
PARTITION t_test_maxVALUES LESS THAN (MAXVALUE)
增加一个2016年的分区语句为：
alter table t_test split partition t_test_max at(TO_DATE(' 00:00:00','yyyy-mm-dd hh24:mi:ss')) into (partitiont_test_2015,partition t_test_max);
2.2删除一个分区
alter table t_test drop partition t_test_2014&
注：droppartition时，该分区内存储的数据也将同时删除，你的本意是希望删除掉指定的分区但保留数据，你应该使用merge partition，执行该语句会导致glocal索引的失效需要重建全局索引
2.3合并分区
相邻的分区可以merge为一个分区，新分区的下边界为原来边界值较低的分区，上边界为原来边界值较高的分区，原先的局部索引相应也会合并，全局索引会失效，需要rebuild。
table t_test
merge partitions t_test_2013
,t_Test_2014 into partition t_Test_2013_to_2014
二、对分区表进行查询
不使用分区查询：默认查询所有分区数据
select * from t_test&
使用分区查询：只查询该分区数据
select * from t_testpartition(t_test_2014) where add_date_time &=TO_DATE(' 00:00:00','yyyy-mm-dd hh24:mi:ss');
insert into t_test values(i, to_date(year,'yyyy-mm-dd hh24:mi:ss'));
使用分区删除
更新的时候指定了分区，而根据查询的记录不在该分区中时，将不会删除数据
delete t_test partition(t_test_2013) where id=1;
不使用分区删除
delete t_test
whereid=1;
使用分区更新
更新的时候指定了分区，而根据查询的记录不在该分区中时，将不会更新数据
delete t_test where id=1;
update t_test partition(t_test)
set id=1 where id=2;
不使用分区
delete t_test where id=1;
update t_test
set id=1 where id=2;
三、普通表和分区表互转
普通表&&分区表
1、新建一个字段一样的中间的分区表（T_NEW）
2、将T数据导入到T_NEW中
INSERT INTO T SELECT field1,filed2, &from T
将老表重命名
RENAME T TO T_OLD;
将新表重命名
RENAME T_NEW TO T;
这种适合静态操作，不保证数据一致性。如果在生产环境切换，利用利用在线重定义功能
顶一下(0) 踩一下(0)
热门标签： 项目问题：有一张日志表，插入和查询为主，每天记录数据为200多万，大小为2G-4G之间。一开始开发人员使用delete语句手动删除，保留7天数据，经常造成阻塞和性能瓶颈。但是如果不删除数据随着表越来越大，查询效率很低，由于应用有超时设置，经常出现timeout。
优化思路：采用分区表来实现日志表的自动随时间窗口滚动，即每天新增明天分区，并将7天前数据归档至日志表。以8月份为例子，当日日期为8号，流程如下图：
具体步骤：
1.建立32个文件组，32个文件，对应于每月31天，每月对31个文件进行复用
-- 创建文件组
USE [master]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY00]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY01]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY02]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY03]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY04]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY05]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY06]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY07]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY08]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY09]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY10]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY11]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY12]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY13]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY14]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY15]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY16]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY17]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY18]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY19]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY20]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY21]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY22]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY23]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY24]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY25]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY26]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY27]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY28]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY29]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY30]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY31]
-- 创建和文件组相对应的文件，由于只有3个盘
USE [master]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY01‘, FILENAME = N‘E:/partfile/FDAY01.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY01]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY02‘, FILENAME = N‘E:/partfile/FDAY02.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY02]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY03‘, FILENAME = N‘E:/partfile/FDAY03.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY03]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY04‘, FILENAME = N‘E:/partfile/FDAY04.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY04]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY05‘, FILENAME = N‘E:/partfile/FDAY05.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY05]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY06‘, FILENAME = N‘E:/partfile/FDAY06.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY06]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY07‘, FILENAME = N‘E:/partfile/FDAY07.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY07]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY08‘, FILENAME = N‘E:/partfile/FDAY08.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY08]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY09‘, FILENAME = N‘E:/partfile/FDAY09.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY09]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY10‘, FILENAME = N‘E:/partfile/FDAY10.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY10]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY11‘, FILENAME = N‘E:/partfile/FDAY11.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY11]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY12‘, FILENAME = N‘E:/partfile/FDAY12.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY12]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY13‘, FILENAME = N‘E:/partfile/FDAY13.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY13]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY14‘, FILENAME = N‘E:/partfile/FDAY14.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY14]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY15‘, FILENAME = N‘E:/partfile/FDAY15.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY15]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY16‘, FILENAME = N‘E:/partfile/FDAY16.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY16]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY17‘, FILENAME = N‘E:/partfile/FDAY17.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY17]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY18‘, FILENAME = N‘E:/partfile/FDAY18.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY18]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY19‘, FILENAME = N‘E:/partfile/FDAY19.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY19]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY20‘, FILENAME = N‘E:/partfile/FDAY20.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY20]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY21‘, FILENAME = N‘E:/partfile/FDAY21.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY21]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY22‘, FILENAME = N‘E:/partfile/FDAY22.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY22]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY23‘, FILENAME = N‘E:/partfile/FDAY23.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY23]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY24‘, FILENAME = N‘E:/partfile/FDAY24.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY24]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY25‘, FILENAME = N‘E:/partfile/FDAY25.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY25]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY26‘, FILENAME = N‘E:/partfile/FDAY26.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY26]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY27‘, FILENAME = N‘E:/partfile/FDAY27.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY27]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY28‘, FILENAME = N‘E:/partfile/FDAY28.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY28]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY29‘, FILENAME = N‘E:/partfile/FDAY29.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY29]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY30‘, FILENAME = N‘E:/partfile/FDAY30.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY30]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY31‘, FILENAME = N‘E:/partfile/FDAY31.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY31]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY00‘, FILENAME = N‘E:/partfile/FDAY00.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY00]
注意：为什么是32个文件组，32个文件。这和分区函数和分区方案有关系，比如分区函数以DATETIME为分区类型，建立150802两个边界，那么就会产生如下3个分区。那相应的分区方案中就要有3个文件组和其对应。
& & & 在建立分区函数时，边界的归属。请看上图，以为例，如果是right，边界归属于右边，所分的两个区间:数据&150801&=数据&；如果是left，边界归属左边，所分区间为:数据&=150801&数据&=;总结一句话，关键字是那边，那么边界值就归属于所分区域的那边。
对于是否有必要将每个文件建立到不同的磁盘上，我这里没有建立，因为应用中的查询一般不会跨分区，都查近几个小时的，所以没有必要将文件建立到不同的磁盘文件中。
如果对于分区表的基础概念还不清楚，请看。
2.建立相应的分区函数和分区方案
USE TClientL
CREATE PARTITION FUNCTION part_day_rang_func(DATETIME)
AS RANGE right FOR VALUES
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘);
CREATE PARTITION SCHEME part_day_rang_scheme
AS PARTITION part_day_rang_func
注意：分区方案指定的是文件组不是文件，FGDAY00文件组对应的分区是‘分区数据&‘，FGDAY31对应的分区是‘分区数据&=‘。
3.建立主日志分区表和历史日志分区表，以表内的时间字段为分区键，索引采用与表分区对齐方式，分区自动化管理脚本如下：
CREATE TABLE [dbo].[ClientLog](
[SynID] [nchar](38) NOT NULL,
[ParkingId] [int] NOT NULL,
[ParkingBoxId] [int] NOT NULL,
[Message] [varchar](max) NULL,
[OccurTime] [datetime] NOT NULL,
[UpdateTime] [datetime] NOT NULL,
[ErrorLevel] [int] NOT NULL,
[State] [int] NULL,
[IsSend] [int] NULL,
CONSTRAINT [PK_ClientLog] PRIMARY KEY NONCLUSTERED
[SynID] ASC,
[OccurTime] ASC
)WITH (PAD_INDEX
= OFF, STATISTICS_NORECOMPUTE
= OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS
= ON) ON [part_day_rang_scheme]([OccurTime])
) ON [part_day_rang_scheme]([OccurTime])
CREATE NONCLUSTERED INDEX [idx_clientlog_otime] ON [dbo].[ClientLog]
[ParkingId] ASC
)WITH (PAD_INDEX
= OFF, STATISTICS_NORECOMPUTE
= OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS
= ON) ON [part_day_rang_scheme]([OccurTime])
注意：主键中必须包含分区键；分区索引必须使用和表一致的分区方案，即索引必须与表对齐，才能进行分区切换；
4.主日志分区表保留7天，每天增加后数第7天的分区，前数第7天的数据进行归档，与日志历史表进行分区交换，流程图参考优化思路的图。
-- =============================================
-- Author:
-- Create date:
-- Description:
&根据日志的滑动窗口业务，进行自动化分区管理&
-- =============================================
-- 1.修改分区方案和分区函数
-- 2.进行分区交换，将归档数据放入历史表
alter PROCEDURE [dbo].[sp_PartitionManage] @td DATETIME
DECLARE @flag CHAR(1)
IF @td IS NULL
--，则默认当天
SET @td = GETDATE()
-- 1.修改分区方案和分区函数,当天新增后数第七天的日期
DECLARE @td_next7 DATETIME
DECLARE @day_next7 VARCHAR(2)
DECLARE @sql NVARCHAR(MAX) --动态sql字符串
SET @td_next7 = DATEADD(DAY, 7, @td) --7天后日期
SET @day_next7 = CASE WHEN LEN(DATENAME(DAY, @td_next7)) = 1
THEN ‘0‘ + DATENAME(DAY, @td_next7)
ELSE DATENAME(DAY, @td_next7)
END; --7天后是当月第几天SELECT
@flag = COUNT(1)
sys.partition_functions a ,
sys.partition_range_values b
a.name = ‘part_day_rang_func‘
AND a.function_id = b.function_id
AND CONVERT(DATETIME, b.value) = CONVERT(VARCHAR(10), @td_next7, 120)
+ ‘ 00:00:00.000‘;
PRINT @flag;
IF ( @flag != ‘1‘ )
SET @sql = ‘alter partition scheme part_day_rang_scheme next used FGDAY‘
+ @day_next7 + ‘;
alter partition function part_day_rang_func() split range(‘‘‘
+ CONVERT(VARCHAR(10), @td_next7, 120) + ‘‘‘)‘
EXEC sp_executesql @sql;
-- 2.进行分区交换，将归档数据放入历史表
DECLARE @td_before7 DATETIME
DECLARE @day_before7 VARCHAR(2)
SET @td_before7 = DATEADD(DAY, -7, @td) --7天前日期
SET @day_before7 = CASE WHEN LEN(DATENAME(DAY, @td_before7)) = 1
THEN ‘0‘ + DATENAME(DAY, @td_before7)
ELSE DATENAME(DAY, @td_before7)
END; --7天前是当月第几天DECLARE @partition_num INT
@partition_num = boundary_id + 1
sys.partition_functions a ,
sys.partition_range_values b
a.name = ‘part_day_rang_func‘
AND a.function_id = b.function_id
AND CONVERT(DATETIME, b.value) = CONVERT(VARCHAR(10), @td_before7, 120)
+ ‘ 00:00:00.000‘;
PRINT @partition_num;
ALTER TABLE [dbo].[ClientLog_new] SWITCH PARTITION @partition_num TO [dbo].[ClientLog_newhis] PARTITION @partition_num
ALTER TABLE [dbo].[ClientLog] SWITCH PARTITION @partition_num TO [dbo].[ClientLoghis] PARTITION @partition_num
配置job：
注意：我这里会增加后7天的分区，如果只建立明天的分区，我怕有一天报错的话，就会报找不到分区插入的错误。此外对该作业进行监控，每天发生错误的话，就手工重跑。
对于通知的配置，需要先配置邮箱服务器。
采用分区表和分区表的交换是因为如果采用普通表的话限制有很多：普通表和分区必须在同一个文件组内；普通表和分区表表结构（字段、索引等）必须一致；普通表必须在分区键所在字段上加和要交换分区限制条件一样的约束。采用分区表的好处是可以屏蔽这些限制，直接用分区表对应的分区直接交换就行。而且对于历史表想清空数据的话，可以新增一个空的临时表，直接进行分区交换，相当方便。
5、对历史表设置保留天数，进行数据清理
历史表的数据清理，准备运行观察一下决定保留的天数，确定后可以配置到自动化管理脚本中。思路就是之前说的建立一个临时的空分区表，每天与历史表要删除的分区进行交换。
之所以要建立临时空表，是因为交换分区的目标表必须是空表。
收集自互联网，仅供学习交流使用！mysql 能不能自动按日期分区（3个月如果手写要写90个partition）
现在有一张表需要按日期进行分区，因为每天大概千万级的数据，索引想按天进行分区。目前数据库中有3个月的数据，就是90天左右吧。看了下MySQL分区的SQL：
CREATE TABLE `pvstat_new` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`goods_id` int(11) NOT NULL DEFAULT '0',
`shop_id` int(11) NOT NULL DEFAULT '0',
`seller_nick` char(30) NOT NULL DEFAULT '0',
`show_count` int(11) NOT NULL DEFAULT '0' ,
`click_count` int(11) NOT NULL DEFAULT '0',
`date` date NOT NULL DEFAULT '' ,
`tagword` char(20) DEFAULT '0' ,
`search_type` int(11) DEFAULT '0',
PRIMARY KEY (`ID`),
KEY `goods_id_index` (`goods_id`),
KEY `shop_id_index` (`shop_id`),
KEY `date_index` (`date`)
) ENGINE=MyISAM AUTO_INCREMENT= DEFAULT CHARSET=utf8
partition by range (to_days(`date`))
(PARTITION p1 VALUES LESS THAN (to_days('')) ,
PARTITION p2 VALUES LESS THAN (to_days('')) ,
PARTITION p3 VALUES LESS THAN (to_days('')));
这是按月份分区的，如果改成按日期分区就要加90个Pattition，太麻烦了。有没有方法自动按日期分区，可以偷懒的呀？
参考这个 。。
--- 共有 1 条评论 ---
虽然和我当时想的不一样，不过存储过程的方案还是很优的
写个脚本循环生成partition语句吧...2406人阅读
假如已创建的表不能再创建分区，只有重新建分区表，然后将数据导入表中，再将表名改为原表名：
1.我的原表名为MONITOR_DATA,现在建临时分区表：
CREATE TABLE GPS_MONITOR_DATA_TMP
& DATA_ID&&&&&&&&& CHAR(36 BYTE)&&&&&&&&&&&&&&& NOT NULL,
& EQUIP_ID&&&&&&&& CHAR(36 BYTE)&&&&&&&&&&&&&&& NOT NULL,
& GPS_DATE&&&&&&&& DATE&&&&&&&&&&&&&&&&&&&&&&&& NOT NULL,
& CREATE_DATE&&&&& DATE&&&&&&&&&&&&&&&&&&&&&&&& NOT NULL,
& SPEED&&&&&&&&&&& NUMBER(3)&&&&&&&&&&&&&&&&&&& NOT NULL,
& KILOMETER&&&&&&& NUMBER(6,2)&&&&&&&&&&&&&&&&& NOT NULL,
& LNG&&&&&&&&&&&&& NUMBER(7,4)&&&&&&&&&&&&&&&&& NOT NULL,
& LAT&&&&&&&&&&&&& NUMBER(7,4)&&&&&&&&&&&&&&&&& NOT NULL
PARTITION BY RANGE (GPS_DATE)
&INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
(PARTITION P1 VALUES LESS THAN (TO_DATE('', 'YYYY-MM-DD')));
按GPS_DATE按月建分区表，因为我的数据是从有的数据，所以开始日期要写
2.创建好表后，将原表数据导入到该临时表
INSERT INTO GPS_MONITOR_DATA_TMP
SELECT * FROM GPS_MONITOR_DATA
3.删除GPS_MONITOR_DATA，将GPS_MONITOR_DATA_TMP改名为GPS_MONITOR_DATA
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：27591次
排名：千里之外
原创：14篇
评论：14条
(1)(2)(4)(1)(1)(2)(1)(1)(2)(2)(1)(1)(1)(1)(1)&&国之画&&&& &&&&&&
&& &&&&&&&&&&&&&&
鲁ICP备号-4
打开技术之扣，分享程序人生！}
首先明确分区表和表分区的区别：表分区是一种思想，分区表示一种技术实现。当表的大小过G的时候可以考虑进行表分区，提高查询效率，均衡IO。分区表是数据库提供的一种表分区的实现形式。表进行分区后，逻辑上仍然是一张表，原来的查询SQL同样生效，同时可以采用使用分区查询来优化SQL查询效率，不至于每次都扫描整个表
一、分区表基本操作
1、按时间分区表创建：&
create table t_test (
number(30)
add_date_time
constraintPK_T_TEST primary key (pk_id)
PARTITION BY RANGE (add_date_time)
PARTITIONt_test_2013_less VALUES LESS THAN (TO_DATE(' 00:00:00','yyyy-mm-ddhh24:mi:ss')) TABLESPACE TS_MISPS,
PARTITIONt_test_2013 VALUES LESS THAN (TO_DATE(' 00:00:00','yyyy-mm-ddhh24:mi:ss')) TABLESPACE TS_MISPS,
PARTITION t_test_2014VALUES LESS THAN (TO_DATE(' 00:00:00','yyyy-mm-dd hh24:mi:ss'))TABLESPACE TS_MISPS
其中add_date_time为分区字段，每一年一个分区。
插入100W数据
yearVARCHAR2(20);
year := CASEmod(i, 3)
WHEN 0 THEN
' 12:00:00'
WHEN 1 THEN
' 12:00:00'
' 12:00:00'
insert into t_test values(i, to_date(year, 'yyyy-mm-dd hh24:mi:ss'));
exit when i= 1000000;
i := i + 1;
查看分区表的分区的详细信息
Select table_name,partition_name,high_value fromdba_tab_partitions where table_name='T_TEST';
2、分区表修改
2.1增加一个分区
分两种情况：1.没有maxvalue分区。2.有maxvalue分区。我们创建的分区就是没有maxValue的分区
1.没有maxvalue分区添加新分区：
alter table t_test add partition t_test_2015 VALUESLESS THAN (TO_DATE(' 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACETS_MISPS ;
2、有maxvalue分区添加新分区：
有了maxvalue，就不能直接add partition，而是需要max分区split。例如我们将创建的分区的语句修改下：
create table t_test (
number(30)
add_date_time
constraintPK_T_TEST primary key (pk_id)
PARTITION BY RANGE (add_date_time)
PARTITIONt_test_2013_less VALUES LESS THAN (TO_DATE(' 00:00:00','yyyy-mm-ddhh24:mi:ss')) TABLESPACE TS_MISPS,
PARTITIONt_test_2013 VALUES LESS THAN (TO_DATE(' 00:00:00','yyyy-mm-ddhh24:mi:ss')) TABLESPACE TS_MISPS,
PARTITIONt_test_2014 VALUES LESS THAN (TO_DATE(' 00:00:00','yyyy-mm-ddhh24:mi:ss')) TABLESPACE TS_MISPS,
PARTITION t_test_maxVALUES LESS THAN (MAXVALUE)
增加一个2016年的分区语句为：
alter table t_test split partition t_test_max at(TO_DATE(' 00:00:00','yyyy-mm-dd hh24:mi:ss')) into (partitiont_test_2015,partition t_test_max);
2.2删除一个分区
alter table t_test drop partition t_test_2014&
注：droppartition时，该分区内存储的数据也将同时删除，你的本意是希望删除掉指定的分区但保留数据，你应该使用merge partition，执行该语句会导致glocal索引的失效需要重建全局索引
2.3合并分区
相邻的分区可以merge为一个分区，新分区的下边界为原来边界值较低的分区，上边界为原来边界值较高的分区，原先的局部索引相应也会合并，全局索引会失效，需要rebuild。
table t_test
merge partitions t_test_2013
,t_Test_2014 into partition t_Test_2013_to_2014
二、对分区表进行查询
不使用分区查询：默认查询所有分区数据
select * from t_test&
使用分区查询：只查询该分区数据
select * from t_testpartition(t_test_2014) where add_date_time &=TO_DATE(' 00:00:00','yyyy-mm-dd hh24:mi:ss');
insert into t_test values(i, to_date(year,'yyyy-mm-dd hh24:mi:ss'));
使用分区删除
更新的时候指定了分区，而根据查询的记录不在该分区中时，将不会删除数据
delete t_test partition(t_test_2013) where id=1;
不使用分区删除
delete t_test
whereid=1;
使用分区更新
更新的时候指定了分区，而根据查询的记录不在该分区中时，将不会更新数据
delete t_test where id=1;
update t_test partition(t_test)
set id=1 where id=2;
不使用分区
delete t_test where id=1;
update t_test
set id=1 where id=2;
三、普通表和分区表互转
普通表&&分区表
1、新建一个字段一样的中间的分区表（T_NEW）
2、将T数据导入到T_NEW中
INSERT INTO T SELECT field1,filed2, &from T
将老表重命名
RENAME T TO T_OLD;
将新表重命名
RENAME T_NEW TO T;
这种适合静态操作，不保证数据一致性。如果在生产环境切换，利用利用在线重定义功能
顶一下(0) 踩一下(0)
热门标签： 项目问题：有一张日志表，插入和查询为主，每天记录数据为200多万，大小为2G-4G之间。一开始开发人员使用delete语句手动删除，保留7天数据，经常造成阻塞和性能瓶颈。但是如果不删除数据随着表越来越大，查询效率很低，由于应用有超时设置，经常出现timeout。
优化思路：采用分区表来实现日志表的自动随时间窗口滚动，即每天新增明天分区，并将7天前数据归档至日志表。以8月份为例子，当日日期为8号，流程如下图：
具体步骤：
1.建立32个文件组，32个文件，对应于每月31天，每月对31个文件进行复用
-- 创建文件组
USE [master]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY00]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY01]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY02]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY03]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY04]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY05]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY06]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY07]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY08]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY09]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY10]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY11]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY12]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY13]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY14]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY15]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY16]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY17]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY18]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY19]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY20]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY21]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY22]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY23]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY24]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY25]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY26]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY27]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY28]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY29]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY30]
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY31]
-- 创建和文件组相对应的文件，由于只有3个盘
USE [master]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY01‘, FILENAME = N‘E:/partfile/FDAY01.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY01]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY02‘, FILENAME = N‘E:/partfile/FDAY02.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY02]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY03‘, FILENAME = N‘E:/partfile/FDAY03.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY03]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY04‘, FILENAME = N‘E:/partfile/FDAY04.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY04]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY05‘, FILENAME = N‘E:/partfile/FDAY05.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY05]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY06‘, FILENAME = N‘E:/partfile/FDAY06.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY06]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY07‘, FILENAME = N‘E:/partfile/FDAY07.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY07]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY08‘, FILENAME = N‘E:/partfile/FDAY08.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY08]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY09‘, FILENAME = N‘E:/partfile/FDAY09.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY09]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY10‘, FILENAME = N‘E:/partfile/FDAY10.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY10]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY11‘, FILENAME = N‘E:/partfile/FDAY11.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY11]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY12‘, FILENAME = N‘E:/partfile/FDAY12.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY12]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY13‘, FILENAME = N‘E:/partfile/FDAY13.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY13]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY14‘, FILENAME = N‘E:/partfile/FDAY14.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY14]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY15‘, FILENAME = N‘E:/partfile/FDAY15.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY15]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY16‘, FILENAME = N‘E:/partfile/FDAY16.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY16]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY17‘, FILENAME = N‘E:/partfile/FDAY17.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY17]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY18‘, FILENAME = N‘E:/partfile/FDAY18.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY18]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY19‘, FILENAME = N‘E:/partfile/FDAY19.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY19]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY20‘, FILENAME = N‘E:/partfile/FDAY20.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY20]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY21‘, FILENAME = N‘E:/partfile/FDAY21.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY21]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY22‘, FILENAME = N‘E:/partfile/FDAY22.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY22]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY23‘, FILENAME = N‘E:/partfile/FDAY23.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY23]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY24‘, FILENAME = N‘E:/partfile/FDAY24.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY24]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY25‘, FILENAME = N‘E:/partfile/FDAY25.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY25]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY26‘, FILENAME = N‘E:/partfile/FDAY26.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY26]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY27‘, FILENAME = N‘E:/partfile/FDAY27.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY27]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY28‘, FILENAME = N‘E:/partfile/FDAY28.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY28]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY29‘, FILENAME = N‘E:/partfile/FDAY29.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY29]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY30‘, FILENAME = N‘E:/partfile/FDAY30.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY30]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY31‘, FILENAME = N‘E:/partfile/FDAY31.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY31]
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY00‘, FILENAME = N‘E:/partfile/FDAY00.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY00]
注意：为什么是32个文件组，32个文件。这和分区函数和分区方案有关系，比如分区函数以DATETIME为分区类型，建立150802两个边界，那么就会产生如下3个分区。那相应的分区方案中就要有3个文件组和其对应。
& & & 在建立分区函数时，边界的归属。请看上图，以为例，如果是right，边界归属于右边，所分的两个区间:数据&150801&=数据&；如果是left，边界归属左边，所分区间为:数据&=150801&数据&=;总结一句话，关键字是那边，那么边界值就归属于所分区域的那边。
对于是否有必要将每个文件建立到不同的磁盘上，我这里没有建立，因为应用中的查询一般不会跨分区，都查近几个小时的，所以没有必要将文件建立到不同的磁盘文件中。
如果对于分区表的基础概念还不清楚，请看。
2.建立相应的分区函数和分区方案
USE TClientL
CREATE PARTITION FUNCTION part_day_rang_func(DATETIME)
AS RANGE right FOR VALUES
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘,
‘ 00:00:00‘);
CREATE PARTITION SCHEME part_day_rang_scheme
AS PARTITION part_day_rang_func
注意：分区方案指定的是文件组不是文件，FGDAY00文件组对应的分区是‘分区数据&‘，FGDAY31对应的分区是‘分区数据&=‘。
3.建立主日志分区表和历史日志分区表，以表内的时间字段为分区键，索引采用与表分区对齐方式，分区自动化管理脚本如下：
CREATE TABLE [dbo].[ClientLog](
[SynID] [nchar](38) NOT NULL,
[ParkingId] [int] NOT NULL,
[ParkingBoxId] [int] NOT NULL,
[Message] [varchar](max) NULL,
[OccurTime] [datetime] NOT NULL,
[UpdateTime] [datetime] NOT NULL,
[ErrorLevel] [int] NOT NULL,
[State] [int] NULL,
[IsSend] [int] NULL,
CONSTRAINT [PK_ClientLog] PRIMARY KEY NONCLUSTERED
[SynID] ASC,
[OccurTime] ASC
)WITH (PAD_INDEX
= OFF, STATISTICS_NORECOMPUTE
= OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS
= ON) ON [part_day_rang_scheme]([OccurTime])
) ON [part_day_rang_scheme]([OccurTime])
CREATE NONCLUSTERED INDEX [idx_clientlog_otime] ON [dbo].[ClientLog]
[ParkingId] ASC
)WITH (PAD_INDEX
= OFF, STATISTICS_NORECOMPUTE
= OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS
= ON) ON [part_day_rang_scheme]([OccurTime])
注意：主键中必须包含分区键；分区索引必须使用和表一致的分区方案，即索引必须与表对齐，才能进行分区切换；
4.主日志分区表保留7天，每天增加后数第7天的分区，前数第7天的数据进行归档，与日志历史表进行分区交换，流程图参考优化思路的图。
-- =============================================
-- Author:
-- Create date:
-- Description:
&根据日志的滑动窗口业务，进行自动化分区管理&
-- =============================================
-- 1.修改分区方案和分区函数
-- 2.进行分区交换，将归档数据放入历史表
alter PROCEDURE [dbo].[sp_PartitionManage] @td DATETIME
DECLARE @flag CHAR(1)
IF @td IS NULL
--，则默认当天
SET @td = GETDATE()
-- 1.修改分区方案和分区函数,当天新增后数第七天的日期
DECLARE @td_next7 DATETIME
DECLARE @day_next7 VARCHAR(2)
DECLARE @sql NVARCHAR(MAX) --动态sql字符串
SET @td_next7 = DATEADD(DAY, 7, @td) --7天后日期
SET @day_next7 = CASE WHEN LEN(DATENAME(DAY, @td_next7)) = 1
THEN ‘0‘ + DATENAME(DAY, @td_next7)
ELSE DATENAME(DAY, @td_next7)
END; --7天后是当月第几天SELECT
@flag = COUNT(1)
sys.partition_functions a ,
sys.partition_range_values b
a.name = ‘part_day_rang_func‘
AND a.function_id = b.function_id
AND CONVERT(DATETIME, b.value) = CONVERT(VARCHAR(10), @td_next7, 120)
+ ‘ 00:00:00.000‘;
PRINT @flag;
IF ( @flag != ‘1‘ )
SET @sql = ‘alter partition scheme part_day_rang_scheme next used FGDAY‘
+ @day_next7 + ‘;
alter partition function part_day_rang_func() split range(‘‘‘
+ CONVERT(VARCHAR(10), @td_next7, 120) + ‘‘‘)‘
EXEC sp_executesql @sql;
-- 2.进行分区交换，将归档数据放入历史表
DECLARE @td_before7 DATETIME
DECLARE @day_before7 VARCHAR(2)
SET @td_before7 = DATEADD(DAY, -7, @td) --7天前日期
SET @day_before7 = CASE WHEN LEN(DATENAME(DAY, @td_before7)) = 1
THEN ‘0‘ + DATENAME(DAY, @td_before7)
ELSE DATENAME(DAY, @td_before7)
END; --7天前是当月第几天DECLARE @partition_num INT
@partition_num = boundary_id + 1
sys.partition_functions a ,
sys.partition_range_values b
a.name = ‘part_day_rang_func‘
AND a.function_id = b.function_id
AND CONVERT(DATETIME, b.value) = CONVERT(VARCHAR(10), @td_before7, 120)
+ ‘ 00:00:00.000‘;
PRINT @partition_num;
ALTER TABLE [dbo].[ClientLog_new] SWITCH PARTITION @partition_num TO [dbo].[ClientLog_newhis] PARTITION @partition_num
ALTER TABLE [dbo].[ClientLog] SWITCH PARTITION @partition_num TO [dbo].[ClientLoghis] PARTITION @partition_num
配置job：
注意：我这里会增加后7天的分区，如果只建立明天的分区，我怕有一天报错的话，就会报找不到分区插入的错误。此外对该作业进行监控，每天发生错误的话，就手工重跑。
对于通知的配置，需要先配置邮箱服务器。
采用分区表和分区表的交换是因为如果采用普通表的话限制有很多：普通表和分区必须在同一个文件组内；普通表和分区表表结构（字段、索引等）必须一致；普通表必须在分区键所在字段上加和要交换分区限制条件一样的约束。采用分区表的好处是可以屏蔽这些限制，直接用分区表对应的分区直接交换就行。而且对于历史表想清空数据的话，可以新增一个空的临时表，直接进行分区交换，相当方便。
5、对历史表设置保留天数，进行数据清理
历史表的数据清理，准备运行观察一下决定保留的天数，确定后可以配置到自动化管理脚本中。思路就是之前说的建立一个临时的空分区表，每天与历史表要删除的分区进行交换。
之所以要建立临时空表，是因为交换分区的目标表必须是空表。
收集自互联网，仅供学习交流使用！mysql 能不能自动按日期分区（3个月如果手写要写90个partition）
现在有一张表需要按日期进行分区，因为每天大概千万级的数据，索引想按天进行分区。目前数据库中有3个月的数据，就是90天左右吧。看了下MySQL分区的SQL：
CREATE TABLE `pvstat_new` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`goods_id` int(11) NOT NULL DEFAULT '0',
`shop_id` int(11) NOT NULL DEFAULT '0',
`seller_nick` char(30) NOT NULL DEFAULT '0',
`show_count` int(11) NOT NULL DEFAULT '0' ,
`click_count` int(11) NOT NULL DEFAULT '0',
`date` date NOT NULL DEFAULT '' ,
`tagword` char(20) DEFAULT '0' ,
`search_type` int(11) DEFAULT '0',
PRIMARY KEY (`ID`),
KEY `goods_id_index` (`goods_id`),
KEY `shop_id_index` (`shop_id`),
KEY `date_index` (`date`)
) ENGINE=MyISAM AUTO_INCREMENT= DEFAULT CHARSET=utf8
partition by range (to_days(`date`))
(PARTITION p1 VALUES LESS THAN (to_days('')) ,
PARTITION p2 VALUES LESS THAN (to_days('')) ,
PARTITION p3 VALUES LESS THAN (to_days('')));
这是按月份分区的，如果改成按日期分区就要加90个Pattition，太麻烦了。有没有方法自动按日期分区，可以偷懒的呀？
参考这个 。。
--- 共有 1 条评论 ---
虽然和我当时想的不一样，不过存储过程的方案还是很优的
写个脚本循环生成partition语句吧...2406人阅读
假如已创建的表不能再创建分区，只有重新建分区表，然后将数据导入表中，再将表名改为原表名：
1.我的原表名为MONITOR_DATA,现在建临时分区表：
CREATE TABLE GPS_MONITOR_DATA_TMP
& DATA_ID&&&&&&&&& CHAR(36 BYTE)&&&&&&&&&&&&&&& NOT NULL,
& EQUIP_ID&&&&&&&& CHAR(36 BYTE)&&&&&&&&&&&&&&& NOT NULL,
& GPS_DATE&&&&&&&& DATE&&&&&&&&&&&&&&&&&&&&&&&& NOT NULL,
& CREATE_DATE&&&&& DATE&&&&&&&&&&&&&&&&&&&&&&&& NOT NULL,
& SPEED&&&&&&&&&&& NUMBER(3)&&&&&&&&&&&&&&&&&&& NOT NULL,
& KILOMETER&&&&&&& NUMBER(6,2)&&&&&&&&&&&&&&&&& NOT NULL,
& LNG&&&&&&&&&&&&& NUMBER(7,4)&&&&&&&&&&&&&&&&& NOT NULL,
& LAT&&&&&&&&&&&&& NUMBER(7,4)&&&&&&&&&&&&&&&&& NOT NULL
PARTITION BY RANGE (GPS_DATE)
&INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
(PARTITION P1 VALUES LESS THAN (TO_DATE('', 'YYYY-MM-DD')));
按GPS_DATE按月建分区表，因为我的数据是从有的数据，所以开始日期要写
2.创建好表后，将原表数据导入到该临时表
INSERT INTO GPS_MONITOR_DATA_TMP
SELECT * FROM GPS_MONITOR_DATA
3.删除GPS_MONITOR_DATA，将GPS_MONITOR_DATA_TMP改名为GPS_MONITOR_DATA
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：27591次
排名：千里之外
原创：14篇
评论：14条
(1)(2)(4)(1)(1)(2)(1)(1)(2)(2)(1)(1)(1)(1)(1)&&国之画&&&& &&&&&&
&& &&&&&&&&&&&&&&
鲁ICP备号-4
打开技术之扣，分享程序人生！}
我要回帖
更多关于 mysql 分区表创建 的文章
更多推荐
- ·qq历史拉黑多久个性签名消失别人可以看到吗
- ·望远银川顺丰快递电话多少？
- ·有什么好书推荐家长阅读的书籍给家长们？
- ·求Cosplay原神人物图图片
- ·声音沙哑 时好时坏是不是怎么判断自己声带是否受损的问题？
- ·电影《绝地逃亡首映礼完整版》720p完整版百度云下载
- ·去年只是在一个群里说了一个女的鼻梁低，她一直在群里装白富美，然后没忍住就张剑拆穿何凯文她了
- ·ZUK Z2 Pro多少钱？一加7和7pro哪个更值得入手购买吗
- ·苹果电脑刚进水没事进水
- ·QQ发微信红包来了声音怎么设置时输入密码的声音在哪里可以关闭?
- ·因为我现在有在微商代理什么产品好卖产品，有没有些更简单快捷的方法可以加到人又可以省心的软件？求大神指教！！！
- ·宏印户外宏印写真机官网打印墨水成本高不高
- ·嗨钱网上钱站审核要多久最快要多久？
- ·挑食公主分子造合什么qq昵称《女》四字词
- ·苹果se怎么点哪里哪里就会报影音先锋资源网 seboo
- ·微信自动抢红包软件(免费)排雷有没有控制尾数软件
- ·求霍比特人2百度云 英音3高清百度云英音中字，谢谢
- ·新中大财务软件官网件
- ·求雾矢翊星际之宠妻指南的（星际）宠妻指南全本百度云链接 谢谢各位
- ·网上刚起步干什么能一个月赚十万工作一个月1万元
- ·怎么根据日期中的天来greenplum 创建分区表表
- ·QQ的厘米秀胶囊里面胶囊是多少分钟一个？
- ·没有通过审核的健身房连锁加盟店加盟店合法么
- ·全国一等jiang奥运小报
- ·怎样看到姚记扑克背面认牌牌是什么
- ·八塞罗那巴西奥运会主题曲曲？
- ·大型游泳池防水胶膜scjmdz要用防水纸尿裤的标语
- ·平昌酒乡宾馆晚上天河游泳馆附近宾馆多少元一张
- ·4周岁的男孩,立定跳远教案125cm算远么
- ·杜兰特库里汤普森加盟勇士后，库里和汤普森谁的数据会更受影响
- ·江东人才公寓一个篮球馆旁边有个公寓出租的是什么地方
- ·天气太热,爸爸要到小溪里去游泳,想带宝宝跟着爸爸一起去旅行，可是宝宝才9个月可以带去吗？
- ·大青山登山步道附近国家登山健身步道收门票吗
- ·非折叠永久自行车车能运吗
- ·乒乓球打球技巧左手打球很有水平可是反拉不行。我姨国家一级二级的人。他教的东西都是不好，
- ·波尔多上海飞巴塞罗那那只有easyjet吗
