Mysql分区表常见操作

-- 创建分区表

create table tb_bmp_sys_monitor_result_his

(

  RESULT_HIS_ID   INT(9) not null,

  OWNER_ID        INT(9),

  OWNER_TYPE      VARCHAR(32),

  COLLECT_ID      INT(9),

  COLLECT_TYPE    VARCHAR(64),

  TASK_ID         INT(9),

  TASK_START_TIME VARCHAR(32),

  CREATE_DATE     DATETIME,

  VALUE0          VARCHAR(128),

  VALUE1          VARCHAR(128),

  VALUE2          VARCHAR(128),

  VALUE3          VARCHAR(128),

  VALUE4          VARCHAR(128),

  VALUE5          VARCHAR(128),

  VALUE6          VARCHAR(128),

  VALUE7          VARCHAR(128),

  VALUE8          VARCHAR(128),

  VALUE9          VARCHAR(128),

  VALUE10         VARCHAR(128),

  VALUE11         VARCHAR(128),

  VALUE12         VARCHAR(128),

  VALUE13         VARCHAR(128),

  VALUE14         VARCHAR(128),

  VALUE15         VARCHAR(128),

  index IDX_COMPLEX_RESULT_HIS  (OWNER_ID, COLLECT_TYPE, TASK_START_TIME, VALUE0),

  index IDX_COMPLEX_RESULT_HIS_2 (VALUE0, TASK_START_TIME),

  index IDX_COMPLEX_RESULT_HIS_4 (OWNER_ID, COLLECT_TYPE, TASK_START_TIME, VALUE3),

  index IDX_COMPLEX_RESULT_HIS_5 (OWNER_ID, TASK_START_TIME, COLLECT_TYPE)

) ENGINE=InnoDB 

partition by range columns(CREATE_DATE)

(

  partition PART_SYS_MONITOR_201508 values less than ('2015-09-01 00:00:00'),

  -- 此处不需要对日期字段进行格式转换

  partition PART_SYS_MONITOR_201509 values less than ('2015-10-01 00:00:00'),

  partition PART_SYS_MONITOR_201510 values less than ('2015-11-01 00:00:00'),

  partition PART_SYS_MONITOR_201511 values less than ('2015-12-01 00:00:00'),

  partition PART_SYS_MONITOR_201512 values less than ('2016-01-01 00:00:00'),

  partition PART_SYS_MONITOR_201601 values less than ('2016-02-01 00:00:00'),

  partition PART_SYS_MONITOR_201602 values less than ('2016-03-01 00:00:00'),

  partition PART_SYS_MONITOR_201603 values less than ('2016-04-01 00:00:00'),

  partition PART_SYS_MONITOR_201604 values less than ('2016-05-01 00:00:00'),

  partition PART_SYS_MONITOR_201605 values less than ('2016-06-01 00:00:00'),

  partition PART_SYS_MONITOR_201606 values less than ('2016-07-01 00:00:00')

);

-- 增加分区:

alter table tb_bmp_sys_monitor_result_his add partition (partition PART_SYS_MONITOR_201512 values less than ('2016-01-01 00:00:00'));

-- 删除分区:

alter table tb_bmp_sys_monitor_result_his drop partition PART_SYS_MONITOR_201511,PART_SYS_MONITOR_201512;

-- 删除表的所有分区:

alter table tb_bmp_sys_monitor_result_his remove partitioning;

-- 重新定义range分区表:

alter table tb_bmp_sys_monitor_result_his partition by range columns(CREATE_DATE)

(

  partition PART_SYS_MONITOR_201508 values less than ('2015-09-01 00:00:00'),

  partition PART_SYS_MONITOR_201609 values less than ('2015-10-01 00:00:00'),

  partition PART_SYS_MONITOR_201610 values less than ('2015-11-01 00:00:00')

); 

-- 合并分区:

alter table tb_bmp_sys_monitor_result_his

reorganize partition PART_SYS_MONITOR_201508,PART_SYS_MONITOR_201609 into

(partition PART_SYS_MONITOR_201609 values less than ('2015-10-01 00:00:00'));

-- 分解分区:

alter table tb_bmp_sys_monitor_result_his

reorganize partition PART_SYS_MONITOR_201609 into

(

partition PART_SYS_MONITOR_201508 values less than ('2015-09-01 00:00:00'),

partition PART_SYS_MONITOR_201609 values less than ('2015-10-01 00:00:00')

); 

-- 重建分区：

这和先删除保存在分区中的所有记录，然后重新插入它们，具有同样的效果。它可用于整理分区碎片。

alter table tb_bmp_sys_monitor_result_his rebuild partition PART_SYS_MONITOR_201508,PART_SYS_MONITOR_201609;

alter table tb_bmp_sys_monitor_result_his rebuild partition PART_SYS_MONITOR_201610;

-- 分析分区：

alter table tb_bmp_sys_monitor_result_his analyze partition PART_SYS_MONITOR_201508,PART_SYS_MONITOR_201609;

-- 可以查看创建分区表的create语句 

show create table tb_bmp_sys_monitor_result_his;

-- 可以查看表具有哪几个分区、分区的方法、分区中数据的记录数等信息

select 

  partition_name part,  

  partition_expression expr,  

  partition_description descr,  

  table_rows  

from information_schema.partitions  where 

  table_schema = schema()  

  and table_name='tb_bmp_sys_monitor_result_his';