OMM创建分区表

sql server:

1：首先我们创建test1数据库和shop表

2：创建文件组和文件

 3:创建分区

    ①：右键Shop表，弹出菜单中选择 “存储” => "创建分区"

 

  ②：创建“分区函数”名 和 “分区方案”名。

 

③：创建分区映射，也就是将”分区函数“和“文件组”进行关联。

 

④：  最后我们可以看一下界面给我生成的分区函数以及分区方案，蛮有意思的。

 1 USE [Test1]
 2 GO
 3 BEGIN TRANSACTION
 4 CREATE PARTITION FUNCTION [MyRangeCreatTime](datetime) AS RANGE LEFT FOR VALUES (N'2012-12-31T00:00:00', N'2013-12-31T00:00:00', N'2014-12-31T00:00:00')
 5 
 6 
 7 CREATE PARTITION SCHEME [MySchemeCreateTime] AS PARTITION [MyRangeCreatTime] TO ([Before2013], [T2013], [T2014], [After2014])
 8 
 9 
10 ALTER TABLE [dbo].[Shop] DROP CONSTRAINT [PK__Shop__3214EC277F60ED59]
11 
12 
13 ALTER TABLE [dbo].[Shop] ADD PRIMARY KEY NONCLUSTERED 
14 (
15     [ID] ASC
16 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
17 
18 
19 CREATE CLUSTERED INDEX [ClusteredIndex_on_MySchemeCreateTime_635288828144372217] ON [dbo].[Shop] 
20 (
21     [CreateTime]
22 )WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [MySchemeCreateTime]([CreateTime])
23 
24 
25 DROP INDEX [ClusteredIndex_on_MySchemeCreateTime_635288828144372217] ON [dbo].[Shop] WITH ( ONLINE = OFF )
26 
27 
28 
29 
30 COMMIT TRANSACTION

从图中可以看到生成好的分区函数名”[MyRangeCreatTime]“ 和分区架构名“[MySchemeCreateTime]”，最后我们执行下该sql就ok了。

 

⑤ 插入测试数据并进行简单的测试

    这里测试下“2013-1-1”是在哪个分区下。

 

 

总结一下可能用到的所有sql语句：

1，新建默认的 filegroup   
alter database Test add filegroup Before2013
2, 新建默认的 file   
alter database Test add file
(Name=N'Before2013',filename='D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Before20131.ndf',size=5mb,maxsize=100Mb,filegrowth=5mb) 
to filegroup Before2013
3，新建分区函数
create partition function RangeTime (datetime) as range left for values ('2012-12-31','2013-12-31','2014-12-31')
4，新建分区方案
 create partition scheme RangeSchema_CreateTime  as partition RangeTime to (before2013,T2013,T2014,after2014)
5，新建表
create table equipment_n
  (
[dataId] [int]  NOT NULL,
[operaDate] datetime NULL,
[eqId] [int] NULL,
[l1] [varchar](30) NULL,
[l2] [varchar](30) NULL,
[lc] [varchar](30) NULL,
  ) on RangeSchema_CreateTime(operaDate)
 
 6， 自动添加数据
  declare @i int
 set @i = 4
 
 while @i < 1000
 begin insert into equipment_n ([operaDate]
           ,[eqId]
           ,[l1]
           ,[l2]
           ,[lc]) values(dateadd(dd,datediff(dd,'2012-01-01','2015-01-30')*RAND(),'2012-01-01'),1,@i+1,@i+2,@i+3)
 set @i = @i +1
 end
 
 7，   查询分区表中数据分布情况
 select $partition.RangeTime(operaDate) as number, COUNT(*) as count
 from equipment_n group by $partition.RangeTime(operaDate)
 
 8， 设置自增ID
 alter table equipment_n drop column dataId alter table equipment_n add dataId int  identity(1,1)
 ALTER TABLE dbo.equipment_n ADD CONSTRAINT
PK_equipment_n  PRIMARY KEY NONCLUSTERED 
(
dataId 
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


9， 定制计划
DECLARE @maxValue INT,
    @secondMaxValue INT,
    @differ    INT,
    @fileGroupName VARCHAR(200),
    @fileNamePath    VARCHAR(200),
    @fileName   VARCHAR(200),
    @sql        NVARCHAR(1000)


if(MONTH(GETDATE()) != '12')--计划每月执行，如果是12月则可以更新分局
begin
--生成新的文件组
SET @fileGroupName='IN_'+ convert(varchar(4),(YEAR(GETDATE())+1))
PRINT @fileGroupName
SET @sql='ALTER DATABASE [iom_db] ADD FILEGROUP '+@fileGroupName
PRINT @sql
EXEC(@sql)

--新建文件
SET @fileNamePath='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA'
+ @fileGroupName +'.NDF'
SET @fileName=N'File'+convert(varchar(4),(YEAR(GETDATE())+1)) 


SET @sql='ALTER DATABASE [iom_db] ADD FILE (NAME='''+@fileName+''',FILENAME=N'''+@fileNamePath+''') TO FILEGROUP'+'    '+@fileGroupName
PRINT @sql
PRINT 1 
EXEC(@sql)
PRINT 2 

--修改分区方案，用一个新的文件组用于存放下一新增的数据
SET @sql='ALTER PARTITION SCHEME [RangeSchema_CreateTime] NEXT USED'+'    '+@fileGroupName
EXEC(@sql)
PRINT 3 

ALTER PARTITION FUNCTION RangeTime()  --分区函数
SPLIT RANGE  (convert(datetime, convert(varchar(4),(YEAR(GETDATE())+1))+'-12-31 00:00:00.000'))
end

 

oracle:

首先明确分区表和表分区的区别：表分区是一种思想，分区表示一种技术实现。当表的大小过G的时候可以考虑进行表分区，提高查询效率，均衡IO。oracle分区表是oracle数据库提供的一种表分区的实现形式。表进行分区后，逻辑上仍然是一张表，原来的查询SQL同样生效，同时可以采用使用分区查询来优化SQL查询效率，不至于每次都扫描整个表

一、分区表基本操作
1、按时间分区表创建：
create table t_test (
   pk_id                number(30)                      not null,
  add_date_time        DATE,
   constraintPK_T_TEST primary key (pk_id)
)
PARTITION BY RANGE (add_date_time)
(
  PARTITIONt_test_2013_less VALUES LESS THAN (TO_DATE('2013-01-01 00:00:00','yyyy-mm-ddhh24:mi:ss')) TABLESPACE TS_MISPS,
  PARTITIONt_test_2013 VALUES LESS THAN (TO_DATE('2014-01-01 00:00:00','yyyy-mm-ddhh24:mi:ss')) TABLESPACE TS_MISPS,
  PARTITION t_test_2014VALUES LESS THAN (TO_DATE('2015-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss'))TABLESPACE TS_MISPS
)
其中add_date_time为分区字段，每一年一个分区。

插入100W数据

declare
  i    int := 1;
  yearVARCHAR2(20);
begin
  loop
    year := CASEmod(i, 3)
             WHEN 0 THEN
              '2012-01-14 12:00:00'
             WHEN 1 THEN
              '2013-01-14 12:00:00'
             ELSE
              '2014-01-14 12:00:00'
            END;
           insert into t_test values(i, to_date(year, 'yyyy-mm-dd hh24:mi:ss'));
    exit when i= 1000000;
    i := i + 1;
  end loop;
end;
查看分区表的分区的详细信息

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('2015-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACETS_MISPS ;

2、有maxvalue分区添加新分区：

有了maxvalue，就不能直接add partition，而是需要max分区split。例如我们将创建的分区的语句修改下：

create table t_test (
   pk_id                number(30)                      not null,
  add_date_time        DATE,
   constraintPK_T_TEST primary key (pk_id)
)
PARTITION BY RANGE (add_date_time)
(
  PARTITIONt_test_2013_less VALUES LESS THAN (TO_DATE('2013-01-01 00:00:00','yyyy-mm-ddhh24:mi:ss')) TABLESPACE TS_MISPS,
  PARTITIONt_test_2013 VALUES LESS THAN (TO_DATE('2014-01-01 00:00:00','yyyy-mm-ddhh24:mi:ss')) TABLESPACE TS_MISPS,
  PARTITIONt_test_2014 VALUES LESS THAN (TO_DATE('2015-01-01 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('2016-01-01 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。

Alter  table t_test  merge partitions t_test_2013  ,t_Test_2014 into partition t_Test_2013_to_2014

二、对分区表进行查询
2.1查询
不使用分区查询：默认查询所有分区数据

select * from t_test

使用分区查询：只查询该分区数据

select * from t_testpartition(t_test_2014) where add_date_time >=TO_DATE('2014-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss');
2.1插入
insert into t_test values(i, to_date(year,'yyyy-mm-dd hh24:mi:ss'));
2.1删除
使用分区删除

更新的时候指定了分区，而根据查询的记录不在该分区中时，将不会删除数据

delete t_test partition(t_test_2013) where id=1;
不使用分区删除

delete t_test  whereid=1;
2.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;
这种适合静态操作，不保证数据一致性。如果在生产环境切换，利用利用在线重定义功能

 

mysql:

CREATE TABLE p1 
    -> (    a INT,   
    ->  b INT,    
    ->  c INT )  
    ->  PARTITION BY RANGE COLUMNS (a) 
    ->   (    PARTITION p01 VALUES LESS THAN (10),  
    ->     PARTITION p02 VALUES LESS THAN (20),   
    ->      PARTITION p03 VALUES LESS THAN (30), 
    ->    PARTITION p04 VALUES LESS THAN (MAXVALUE)  ); 
--------------------- 

        Mysql的安装方法可以参考：

        http://blog.csdn.net/jhq0113/article/details/43812895

        

        Mysql分区表的介绍可以参考：

        http://blog.csdn.net/jhq0113/article/details/44592865

       

       1.检查你的Mysql是否支持分区

        mysql> SHOW VARIABLES LIKE '%partition%';

       若结果如下，表示你的Mysql支持表分区:

        +-----------------------+-------+

       | Variable_name         | Value |
       +-----------------------+-------+
       | have_partition_engine | YES   |
       +-----------------------+-------+
       1 row in set (0.00 sec)
       
               RANGE分区表创建方式：
DROP TABLE IF EXISTS `my_orders`;
CREATE TABLE `my_orders` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '表主键',
  `pid` int(10) unsigned NOT NULL COMMENT '产品ID',
  `price` decimal(15,2) NOT NULL COMMENT '单价',
  `num` int(11) NOT NULL COMMENT '购买数量',
  `uid` int(10) unsigned NOT NULL COMMENT '客户ID',
  `atime` datetime NOT NULL COMMENT '下单时间',
  `utime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '修改时间',
  `isdel` tinyint(4) NOT NULL DEFAULT '0' COMMENT '软删除标识',
  PRIMARY KEY (`id`,`atime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
 
/*********分区信息**************/
PARTITION BY RANGE (YEAR(atime))
(
   PARTITION p0 VALUES LESS THAN (2016),
   PARTITION p1 VALUES LESS THAN (2017),
   PARTITION p2 VALUES LESS THAN MAXVALUE
);
        以上是一个简单的订单表，分区字段是atime,根据RANGE分区，这样当你向该表中插入数据的时候，Mysql会根据YEAR(atime)的值进行分区存储。

        检查分区是否创建成功，执行查询语句：

         EXPLAIN PARTITIONS SELECT * FROM `my_orders`

         若成功，结果如下：

        

         

        性能分析：

        1）.创建同样表结构，但没有进行分区的表     

DROP TABLE IF EXISTS `my_order`;
CREATE TABLE `my_order` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '表主键',
  `pid` int(10) unsigned NOT NULL COMMENT '产品ID',
  `price` decimal(15,2) NOT NULL COMMENT '单价',
  `num` int(11) NOT NULL COMMENT '购买数量',
  `uid` int(10) unsigned NOT NULL COMMENT '客户ID',
  `atime` datetime NOT NULL COMMENT '下单时间',
  `utime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '修改时间',
  `isdel` tinyint(4) NOT NULL DEFAULT '0' COMMENT '软删除标识',
  PRIMARY KEY (`id`,`atime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
        2）.向两张表中插入相同的数据

      

/**************************向分区表插入数据****************************/
INSERT INTO my_orders(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,CURRENT_TIMESTAMP());
INSERT INTO my_orders(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2016-05-01 00:00:00');
INSERT INTO my_orders(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2017-05-01 00:00:00');
INSERT INTO my_orders(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2018-05-01 00:00:00');
INSERT INTO my_orders(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2015-05-01 00:00:00');
INSERT INTO my_orders(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2016-05-01 00:00:00');
INSERT INTO my_orders(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2017-05-01 00:00:00');
INSERT INTO my_orders(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2018-05-01 00:00:00');
 
/**************************向未分区表插入数据****************************/
INSERT INTO my_order(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,CURRENT_TIMESTAMP());
INSERT INTO my_order(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2016-05-01 00:00:00');
INSERT INTO my_order(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2017-05-01 00:00:00');
INSERT INTO my_order(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2018-05-01 00:00:00');
INSERT INTO my_order(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2015-05-01 00:00:00');
INSERT INTO my_order(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2016-05-01 00:00:00');
INSERT INTO my_order(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2017-05-01 00:00:00');
INSERT INTO my_order(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2018-05-01 00:00:00');

        3）.主从复制，大约20万条左右（主从复制的数据和真实环境有差距，但是能体现出表分区查询的性能优劣）
        

/**********************************主从复制大量数据******************************/
INSERT INTO `my_orders`(`pid`,`price`,`num`,`uid`,`atime`) SELECT `pid`,`price`,`num`,`uid`,`atime` FROM `my_orders`;
INSERT INTO `my_order`(`pid`,`price`,`num`,`uid`,`atime`) SELECT `pid`,`price`,`num`,`uid`,`atime` FROM `my_order`;
     
       4）.查询测试 

/***************************查询性能分析**************************************/
SELECT * FROM `my_orders` WHERE `uid`=89757 AND `atime`< CURRENT_TIMESTAMP();
/****用时0.084s****/
 
SELECT * FROM `my_order` WHERE `uid`=89757 AND `atime`< CURRENT_TIMESTAMP();
/****用时0.284s****/

      通过以上查询可以明显看出进行表分区的查询性能更好，查询所花费的时间更短。
      分析查询过程：

      EXPLAIN PARTITIONS SELECT * FROM `my_orders` WHERE `uid`=89757 AND `atime`< CURRENT_TIMESTAMP();

            

      EXPLAIN PARTITIONS SELECT * FROM `my_order` WHERE `uid`=89757 AND `atime`< CURRENT_TIMESTAMP();

            

       通过以上结果可以看出，my_orders表查询直接经过p0分区，只扫描了49386行，而my_order表没有进行分区，扫描了196983行，这也是性能得到提升的关键所在。

       当然，表的分区并不是分的越多越好，当表的分区太多时找分区又是一个性能的瓶颈了，建议在200个分区以内。

      LIST分区表创建方式：

      

/*****************创建分区表*********************/
CREATE TABLE `products` (
`id`  bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '表主键' ,
`name`  varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '产品名称' ,
`metrial`  tinyint UNSIGNED NOT NULL COMMENT '材质' ,
`weight`  double UNSIGNED NOT NULL DEFAULT 0 COMMENT '重量' ,
`vol`  double UNSIGNED NOT NULL DEFAULT 0 COMMENT '容积' ,
`c_id`  tinyint UNSIGNED NOT NULL COMMENT '供货公司ID' ,
PRIMARY KEY (`id`,`c_id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8
 
/*********分区信息**************/
PARTITION BY LIST(c_id)
(
    PARTITION pA VALUES IN (1,3,11,13),
    PARTITION pB VALUES IN (2,4,12,14),
    PARTITION pC VALUES IN (5,7,15,17),
    PARTITION pD VALUES IN (6,8,16,18),
    PARTITION pE VALUES IN (9,10,19,20)
);

 

       可以看出，LIST分区和RANGE分区很类似，这里就不做性能分析了，和RANGE很类似。
       HASH分区表的创建方式：

 

/*****************分区表*****************/
CREATE TABLE `msgs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '表主键',
  `sender` int(10) unsigned NOT NULL COMMENT '发送者ID',
  `reciver` int(10) unsigned NOT NULL COMMENT '接收者ID',
  `msg_type` tinyint(3) unsigned NOT NULL COMMENT '消息类型',
  `msg` varchar(225) NOT NULL COMMENT '消息内容',
  `atime` int(10) unsigned NOT NULL COMMENT '发送时间',
  `sub_id` tinyint(3) unsigned NOT NULL COMMENT '部门ID',
  PRIMARY KEY (`id`,`sub_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*********分区信息**************/
PARTITION BY HASH(sub_id)
PARTITIONS 10;

          以上语句代表，msgs表按照sub_id进行HASH分区，一共分了十个区。

       Key分区和HASH分区很类似，不再介绍，若想了解可以参考Mysql官方文档进行详细了解。

       子分区的创建方式:

      

CREATE TABLE `msgss` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '表主键',
  `sender` int(10) unsigned NOT NULL COMMENT '发送者ID',
  `reciver` int(10) unsigned NOT NULL COMMENT '接收者ID',
  `msg_type` tinyint(3) unsigned NOT NULL COMMENT '消息类型',
  `msg` varchar(225) NOT NULL COMMENT '消息内容',
  `atime` int(10) unsigned NOT NULL COMMENT '发送时间',
  `sub_id` tinyint(3) unsigned NOT NULL COMMENT '部门ID',
  PRIMARY KEY (`id`,`atime`,`sub_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*********分区信息**************/
PARTITION BY RANGE (atime) SUBPARTITION BY HASH (sub_id) 
(
        PARTITION t0 VALUES LESS THAN(1451577600)
        (
            SUBPARTITION s0,
            SUBPARTITION s1,
            SUBPARTITION s2,
            SUBPARTITION s3,
            SUBPARTITION s4,
            SUBPARTITION s5
        ),
        PARTITION t1 VALUES LESS THAN(1483200000)
        (
            SUBPARTITION s6,
            SUBPARTITION s7,
            SUBPARTITION s8,
            SUBPARTITION s9,
            SUBPARTITION s10,
            SUBPARTITION s11
        ),
        PARTITION t2 VALUES LESS THAN MAXVALUE
        (
            SUBPARTITION s12,
            SUBPARTITION s13,
            SUBPARTITION s14,
            SUBPARTITION s15,
            SUBPARTITION s16,
            SUBPARTITION s17
        )
);

         检查子分区是否创建成功：
        EXPLAIN PARTITIONS SELECT * FROM msgss;