分区表的实现

一、查看是否支持分区

show plugins;

二、新建分区表

CREATE TABLE `customer_login_log` (
    `customer_id` int(10) unsigned NOT NULL,
    `login_time` datetime NOT NULL,
    `login_ip` int(10) unsigned NOT NULL,
    `login_type` tinyint(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE(YEAR(login_time))(
    PARTITION P0 VALUES LESS THAN(2015),
    PARTITION P1 VALUES LESS THAN(2016),
    PARTITION P2 VALUES LESS THAN(2017)
);

表文件

三、插入数据

INSERT INTO `test`.`customer_login_log` (`customer_id`, `login_time`, `login_ip`, `login_type`) VALUES ('1', '2014-01-27 14:48:38', '2130706433', '1');
INSERT INTO `test`.`customer_login_log` (`customer_id`, `login_time`, `login_ip`, `login_type`) VALUES ('1', '2014-01-27 14:48:38', '2130706433', '1');
INSERT INTO `test`.`customer_login_log` (`customer_id`, `login_time`, `login_ip`, `login_type`) VALUES ('1', '2015-01-27 14:48:38', '2130706433', '1');
INSERT INTO `test`.`customer_login_log` (`customer_id`, `login_time`, `login_ip`, `login_type`) VALUES ('1', '2015-01-27 14:48:38', '2130706433', '1');
INSERT INTO `test`.`customer_login_log` (`customer_id`, `login_time`, `login_ip`, `login_type`) VALUES ('1', '2016-01-27 14:48:38', '2130706433', '1');
INSERT INTO `test`.`customer_login_log` (`customer_id`, `login_time`, `login_ip`, `login_type`) VALUES ('1', '2016-01-27 14:48:38', '2130706433', '1');

查看分区信息

SELECT table_name,partition_name,partition_description,table_rows
FROM information_schema.`PARTITIONS`
WHERE table_name='customer_login_log';

四、新增分区

ALTER TABLE customer_login_log ADD PARTITION(PARTITION P3 VALUES LESS THAN(2018));

五、删除分区

ALTER TABLE customer_login_log DROP PARTITION p0;

六、新建归档表

CREATE TABLE `arch_customer_login_log` (
  `customer_id` int(10) unsigned NOT NULL,
  `login_time` datetime NOT NULL,
  `login_ip` int(10) unsigned NOT NULL,
  `login_type` tinyint(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

七、归档

p1分区数据迁移（mysql5.7以上）

ALTER TABLE customer_login_log exchange PARTITION P1 WITH TABLE arch_customer_login_log;

分区信息

归档表

八、归档引擎修改

archive，占用空间小，不支持更新、删除

ALTER TABLE arch_customer_login_log ENGINE=ARCHIVE;

表文件