數據切分——Mysql分區表的管理與維護

數據切分——Mysql分區表的管理與維護

Mysql支持4種表的分區，即RANGE與LIST、HASH與KEY，其中RANGE和LIST類似，按一種區間進行分區，HASH與KEY類似，是按照某種算法對字段進行分區。

RANGE與LIST分區管理：

案例：有一個聊天記錄表，用戶幾千左右，已經對表按照用戶進行一定粒度的水平分割，現仍然有部分表存儲的記錄比較多，於是按照下列方式有對表進行了分區，分區的好處是，可以動態改變分區，刪除分區後，數據也一同被刪除，如聊天記錄只保存兩年，那麼你就可以按照時間進行分區，定期刪除兩年前的分區，動態創建新的的分區就能做到很好的數據維護。

 

分區表創建的語句如下：

 

DROP TABLE IF EXISTS `msgss`;
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
		)
);

 

上述語句創建了三個按照RANGE劃分的主分區，每個主分區下面有六個按照HASH劃分的子分區。

插入測試數據：

 

INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH',UNIX_TIMESTAMP(NOW()),1);
INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 2',UNIX_TIMESTAMP(NOW()),2);
INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 3',UNIX_TIMESTAMP(NOW()),3);
INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 10',UNIX_TIMESTAMP(NOW()),10);
INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 7',UNIX_TIMESTAMP(NOW()),7);
INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 5',UNIX_TIMESTAMP(NOW()),5);

INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH',1451577607,1);
INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 2',1451577609,2);
INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 3',1451577623,3);
INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 10',1451577654,10);
INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 7',1451577687,7);
INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 5',1451577699,5);

INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH',1514736056,1);
INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 2',1514736066,2);
INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 3',1514736076,3);
INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 10',1514736086,10);
INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 7',1514736089,7);
INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 5',1514736098,5);

進行分區分析：

 

EXPLAIN PARTITIONS SELECT * FROM msgss;

可以檢測到分區信息如下：

檢測分區數據分布：

 

EXPLAIN PARTITIONS SELECT * FROM msgss WHERE `atime`<1451577600;

EXPLAIN PARTITIONS SELECT * FROM msgss WHERE `atime`>1451577600 AND `atime`<1483200000;

EXPLAIN PARTITIONS SELECT * FROM msgss WHERE `atime`>1483200000 AND `atime`<1514736000;

EXPLAIN PARTITIONS SELECT * FROM msgss WHERE `atime`>1514736000;

結果：第一條語句只掃描了t0的所有子分區，第二條語句只掃描了t1的所有子分區，第三四條分別只掃描了t2的所有子分區，證明表的分區和數據分布成功。

 

需求：目前已經是2017年，需要將2015年所有的聊天記錄刪除，但是保留2016年的聊天記錄，並且2017年的數據也能正常按照分區進行存儲。

實現以上需求，需要兩步，第一步刪除t0分區，第二步按照新規則重建分區。

刪除分區語句：

ALTER TABLE `msgss` DROP PARTITION t0;

重建分區語句：

 

ALTER TABLE `msgss` PARTITION BY RANGE (atime) SUBPARTITION BY HASH (sub_id) 
(
		PARTITION t0 VALUES LESS THAN(1483200000)
		(
			SUBPARTITION s0,
			SUBPARTITION s1,
			SUBPARTITION s2,
			SUBPARTITION s3,
			SUBPARTITION s4,
			SUBPARTITION s5
		),
		PARTITION t1 VALUES LESS THAN(1514736000)
		(
			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
		)
);

查詢發現，15年的數據全部被刪除，剩余的數據被重新分區並分布。