生活随笔
收集整理的這篇文章主要介紹了
数据切分——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?
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劃分的子分區。
??????? 插入測試數據：
???????
[sql]?view plain
?copy ?print?
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;
??????? 可以檢測到分區信息如下：
??????????
???????? 檢測分區數據分布：
?????????
[sql]?view plain
?copy ?print?
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;
??????? 重建分區語句：
???????
[sql]?view plain
?copy ?print?
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年的數據全部被刪除，剩余的數據被重新分區并分布。
???????? 未完。。。。
轉載于:https://www.cnblogs.com/duyinqiang/p/5696360.html
總結
以上是生活随笔為你收集整理的数据切分——Mysql分区表的管理与维护的全部內容，希望文章能夠幫你解決所遇到的問題。
如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。
