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

た入场券 2022-07-27 10:46 216阅读 0赞

关于Mysql分区表的介绍可以参考:

[ http://blog.csdn.net/jhq0113/article/details/44592865][http_blog.csdn.net_jhq0113_article_details_44592865]

关于Mysql分区表的创建可以参考：

[ http://blog.csdn.net/jhq0113/article/details/44593511][http_blog.csdn.net_jhq0113_article_details_44593511]

前面已经提过，Mysql支持4种表的分区，即RANGE与LIST、HASH与KEY，其中RANGE和LIST类似，按一种区间进行分区，HASH与KEY类似，是按照某种算法对字段进行分区。

RANGE与LIST分区管理：

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

分区表创建的语句如下：

**\[sql\]**  [view plain][]  [copy][view plain]

[print][view plain] [?][view plain] [![在CODE上查看代码片][CODE]][CODE_CODE] [![派生到我的代码片][ico_fork.svg]][ico_fork.svg 1]

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][view plain]

[print][view plain] [?][view plain] [![在CODE上查看代码片][CODE]][CODE_CODE] [![派生到我的代码片][ico_fork.svg]][ico_fork.svg 1]

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;

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

![Center][]

检测分区数据分布：

**\[sql\]**  [view plain][]  [copy][view plain]

[print][view plain] [?][view plain] [![在CODE上查看代码片][CODE]][CODE_CODE] [![派生到我的代码片][ico_fork.svg]][ico_fork.svg 1]

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][view plain]

[print][view plain] [?][view plain] [![在CODE上查看代码片][CODE]][CODE_CODE] [![派生到我的代码片][ico_fork.svg]][ico_fork.svg 1]

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

未完。。。。

[http_blog.csdn.net_jhq0113_article_details_44592865]: http://blog.csdn.net/jhq0113/article/details/44592865
[http_blog.csdn.net_jhq0113_article_details_44593511]: http://blog.csdn.net/jhq0113/article/details/44593511
[view plain]: http://blog.csdn.net/jhq0113/article/details/44617781#
[CODE]: https://code.csdn.net/assets/CODE_ico.png
[CODE_CODE]: https://code.csdn.net/snippets/627407
[ico_fork.svg]: https://code.csdn.net/assets/ico_fork.svg
[ico_fork.svg 1]: https://code.csdn.net/snippets/627407/fork
[Center]: /images/20220724/e3821e29fc8d478aa11f392390617894.png