資料切分——Mysql分區表的管理與維護，切分mysql

資料切分——Mysql分區表的管理與維護，切分mysql

        關於Mysql分區表的介紹可以參考:

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

       關於Mysql分區表的建立可以參考：

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

       前面已經提過，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年的資料全部被刪除，剩餘的資料被重新分區並分布。

         未完。。。。