MySQL分区 之维护命令

分区维护命令

分区表例子

以下维护命令基于此表结构

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
CREATE TABLE `my_table` (     
`crttm` int(11) NOT NULL,
`srvid` int(11) NOT NULL,
`evtid` int(11) NOT NULL,
`aid` int(11) NOT NULL,
`rid` int(11) NOT NULL,
`itmid` int(11) NOT NULL,
`itmnum` int(11) NOT NULL,
`gdtype` int(11) NOT NULL,
`gdnum` int(11) NOT NULL,
`islmt` int(11) NOT NULL,
KEY `crttm` (`crttm`),
KEY `itemid` (`itmid`),
KEY `srvid` (`srvid`),
KEY `gdtype` (`gdtype`)
) ENGINE=myisam DEFAULT CHARSET=utf8
PARTITION BY RANGE (crttm)
(
PARTITION p201303 VALUES LESS THAN (unix_timestamp('2013-04-01')),
PARTITION p201304 VALUES LESS THAN (unix_timestamp('2013-05-01')),
PARTITION p201305 VALUES LESS THAN (unix_timestamp('2013-06-01')),
PARTITION p201306 VALUES LESS THAN (unix_timestamp('2013-07-01')),
PARTITION p201307 VALUES LESS THAN (unix_timestamp('2013-08-01')),
PARTITION p201308 VALUES LESS THAN (unix_timestamp('2013-09-01')),
PARTITION p201309 VALUES LESS THAN (unix_timestamp('2013-10-01')),
PARTITION p201310 VALUES LESS THAN (unix_timestamp('2013-11-01')),
PARTITION p201311 VALUES LESS THAN (unix_timestamp('2013-12-01')),
PARTITION p201312 VALUES LESS THAN (unix_timestamp('2014-01-01')),
PARTITION p201401 VALUES LESS THAN (unix_timestamp('2014-02-01'))
);

添加分区

SQL示例

1
alter table my_table add partition (partition p0 values less than(1991));  //只能添加大于分区键的分区

删除分区

1
alter table my_table drop partition p0; //可以删除任意分区

删除分区数据

1
2
alter table xxxxxx  truncate partition p1,p2
alter table xxxxxx truncate partition all;

分区重定义

1
2
alter table xxxxx reorganize partition p1,p3,p4 into (partition pm1 values less than(2006),  
partition pm2 values less than(2011));

Rebuild重建分区

1
alter  table xxxxxx rebuild partition pm1/all; //相当于drop所有记录,然后再reinsert;可以解决磁盘碎片

优化表

1
alter  table tt2 optimize partition pm1; //在大量delete表数据后,可以回收空间和碎片整理。但在5.5.30后支持。在5.5.30之前可以通过recreate+analyze来替代,如果用rebuild+analyze速度慢

Analzye表

1
alter  table xxxxxx analyze partition pm1/all;

Check表

1
alter  table xxxxxx check partition pm1/all;