数据切分——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分区管理：

       案例：有一个聊天记录表，用户几千左右，已经对表按照用户进行一定粒度的水平分割，现仍然有部分表存储的记录比较多，于是按照下列方式有对表进行了分区，分区的好处是，可以动态改变分区，删除分区后，数据也一同被删除，如聊天记录只保存两年，那么你就可以按照时间进行分区，定期删除两年前的分区，动态创建新的的分区就能做到很好的数据维护。

   

       分区表创建的语句如下：

        

[sql] view plain copy
 print?在CODE上查看代码片派生到我的代码片
  1. DROP TABLE IF EXISTS `msgss`;  
  2. CREATE TABLE `msgss` (  
  3.   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '表主键',  
  4.   `sender` int(10) unsigned NOT NULL COMMENT '发送者ID',  
  5.   `reciver` int(10) unsigned NOT NULL COMMENT '接收者ID',  
  6.   `msg_type` tinyint(3) unsigned NOT NULL COMMENT '消息类型',  
  7.   `msg` varchar(225) NOT NULL COMMENT '消息内容',  
  8.   `atime` int(10) unsigned NOT NULL COMMENT '发送时间',  
  9.   `sub_id` tinyint(3) unsigned NOT NULL COMMENT '部门ID',  
  10.   PRIMARY KEY (`id`,`atime`,`sub_id`)  
  11. ) ENGINE=InnoDB DEFAULT CHARSET=utf8  
  12. /*********分区信息**************/  
  13. PARTITION BY RANGE (atime) SUBPARTITION BY HASH (sub_id)   
  14. (  
  15.         PARTITION t0 VALUES LESS THAN(1451577600)  
  16.         (  
  17.             SUBPARTITION s0,  
  18.             SUBPARTITION s1,  
  19.             SUBPARTITION s2,  
  20.             SUBPARTITION s3,  
  21.             SUBPARTITION s4,  
  22.             SUBPARTITION s5  
  23.         ),  
  24.         PARTITION t1 VALUES LESS THAN(1483200000)  
  25.         (  
  26.             SUBPARTITION s6,  
  27.             SUBPARTITION s7,  
  28.             SUBPARTITION s8,  
  29.             SUBPARTITION s9,  
  30.             SUBPARTITION s10,  
  31.             SUBPARTITION s11  
  32.         ),  
  33.         PARTITION t2 VALUES LESS THAN MAXVALUE  
  34.         (  
  35.             SUBPARTITION s12,  
  36.             SUBPARTITION s13,  
  37.             SUBPARTITION s14,  
  38.             SUBPARTITION s15,  
  39.             SUBPARTITION s16,  
  40.             SUBPARTITION s17  
  41.         )  
  42. );  


        上述语句创建了三个按照RANGE划分的主分区，每个主分区下面有六个按照HASH划分的子分区。


        插入测试数据：

       

[sql] view plain copy
 print?在CODE上查看代码片派生到我的代码片
  1. INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH',UNIX_TIMESTAMP(NOW()),1);  
  2. INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 2',UNIX_TIMESTAMP(NOW()),2);  
  3. INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 3',UNIX_TIMESTAMP(NOW()),3);  
  4. INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 10',UNIX_TIMESTAMP(NOW()),10);  
  5. INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 7',UNIX_TIMESTAMP(NOW()),7);  
  6. INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 5',UNIX_TIMESTAMP(NOW()),5);  
  7.   
  8. INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH',1451577607,1);  
  9. INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 2',1451577609,2);  
  10. INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 3',1451577623,3);  
  11. INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 10',1451577654,10);  
  12. INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 7',1451577687,7);  
  13. INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 5',1451577699,5);  
  14.   
  15. INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH',1514736056,1);  
  16. INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 2',1514736066,2);  
  17. INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 3',1514736076,3);  
  18. INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 10',1514736086,10);  
  19. INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 7',1514736089,7);  
  20. 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;

        可以检测到分区信息如下：

          


         检测分区数据分布：

         

[sql] view plain copy
 print?在CODE上查看代码片派生到我的代码片
  1. EXPLAIN PARTITIONS SELECT * FROM msgss WHERE `atime`<1451577600;  
  2.   
  3. EXPLAIN PARTITIONS SELECT * FROM msgss WHERE `atime`>1451577600 AND `atime`<1483200000;  
  4.   
  5. EXPLAIN PARTITIONS SELECT * FROM msgss WHERE `atime`>1483200000 AND `atime`<1514736000;  
  6.   
  7. EXPLAIN PARTITIONS SELECT * FROM msgss WHERE `atime`>1514736000;  

                    结果：第一条语句只扫描了t0的所有子分区，第二条语句只扫描了t1的所有子分区，第三四条分别只扫描了t2的所有子分区，证明表的分区和数据分布成功。


         需求：目前已经是2017年，需要将2015年所有的聊天记录删除，但是保留2016年的聊天记录，并且2017年的数据也能正常按照分区进行存储。


        实现以上需求，需要两步，第一步删除t0分区，第二步按照新规则重建分区。

        删除分区语句：

        ALTER TABLE `msgss` DROP PARTITION t0;

        重建分区语句：

       

[sql] view plain copy
 print?在CODE上查看代码片派生到我的代码片
  1. ALTER TABLE `msgss` PARTITION BY RANGE (atime) SUBPARTITION BY HASH (sub_id)   
  2. (  
  3.         PARTITION t0 VALUES LESS THAN(1483200000)  
  4.         (  
  5.             SUBPARTITION s0,  
  6.             SUBPARTITION s1,  
  7.             SUBPARTITION s2,  
  8.             SUBPARTITION s3,  
  9.             SUBPARTITION s4,  
  10.             SUBPARTITION s5  
  11.         ),  
  12.         PARTITION t1 VALUES LESS THAN(1514736000)  
  13.         (  
  14.             SUBPARTITION s6,  
  15.             SUBPARTITION s7,  
  16.             SUBPARTITION s8,  
  17.             SUBPARTITION s9,  
  18.             SUBPARTITION s10,  
  19.             SUBPARTITION s11  
  20.         ),  
  21.         PARTITION t2 VALUES LESS THAN MAXVALUE  
  22.         (  
  23.             SUBPARTITION s12,  
  24.             SUBPARTITION s13,  
  25.             SUBPARTITION s14,  
  26.             SUBPARTITION s15,  
  27.             SUBPARTITION s16,  
  28.             SUBPARTITION s17  
  29.         )  
  30. );  

                    查询发现，15年的数据全部被删除，剩余的数据被重新分区并分布。

         未完。。。。

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/duyinqiang/p/5696361.html