数据切分——Mysql分区表的管理与维护

时间：2023-03-09 09:03:14
数据切分——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分区管理：

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

分区表创建的语句如下：

  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划分的子分区。

插入测试数据：

  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. INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH',1451577607,1);
  8. INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 2',1451577609,2);
  9. INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 3',1451577623,3);
  10. INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 10',1451577654,10);
  11. INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 7',1451577687,7);
  12. INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 5',1451577699,5);
  13. INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH',1514736056,1);
  14. INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 2',1514736066,2);
  15. INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 3',1514736076,3);
  16. INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 10',1514736086,10);
  17. INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 7',1514736089,7);
  18. 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;

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

数据切分——Mysql分区表的管理与维护

检测分区数据分布：

  1. EXPLAIN PARTITIONS SELECT * FROM msgss WHERE `atime`<1451577600;
  2. EXPLAIN PARTITIONS SELECT * FROM msgss WHERE `atime`>1451577600 AND `atime`<1483200000;
  3. EXPLAIN PARTITIONS SELECT * FROM msgss WHERE `atime`>1483200000 AND `atime`<1514736000;
  4. EXPLAIN PARTITIONS SELECT * FROM msgss WHERE `atime`>1514736000;

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

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

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

删除分区语句：

ALTER TABLE `msgss` DROP PARTITION t0;

重建分区语句：

  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年的数据全部被删除，剩余的数据被重新分区并分布。

未完。。。。

相关文章