实战mysql分区
时间：2021-07-01 10:21:17
帮助过：8人阅读
`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‘))
);
注意：
1. primary key和unique key必须包含在分区key的一部分，否则在创建primary key和unique index时会报”ERROR 1503 (HY000)“
mysql> create unique index idx_employees1_job_code on employees1(job_code); ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table‘s partitioning function 或 mysql> ALTER TABLE `skate`.`employees1` ADD PRIMARY KEY (`id`) ; ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table‘s partitioning function
2. 范围分区添加分区只能在最大值后面追加分区 3. 所有分区的engine必须一样 4. 范围分区分区字段：integer、数值表达式、日期列，日期函数表达式(如year(),to_days(),to_seconds(),unix_timestamp())
将旧的表数据导入到新表后，看到新表的数据都分布到不同的区了！
维护命令：
添加分区
Sql代码
- alter table xxxxxxx add partition (partition p0 values less than(1991)); //只能添加大于分区键的分区
删除分区
Sql代码
- alter table xxxxxxx drop partition p0; //可以删除任意分区
删除分区数据
Sql代码
- alter table xxxxxx truncate partition p1,p2;
- alter table xxxxxx truncate partition all;
- 或
- delete from xxxxxx where separated < ‘2006-01-01‘ or (separated >= ‘2006-01-01‘ and separated<‘2011-01-01‘);
重定义分区（包括重命名分区，伴随移动数据；合并分区)
Sql代码
- alter table xxxxx reorganize partition p1,p3,p4 into (partition pm1 values less than(2006),
- partition pm2 values less than(2011));
rebuild重建分区
Sql代码
- alter table xxxxxx rebuild partition pm1/all; //相当于drop所有记录，然后再reinsert；可以解决磁盘碎片
优化表
Sql代码
- alter table tt2 optimize partition pm1; //在大量delete表数据后，可以回收空间和碎片整理。但在5.5.30后支持。在5.5.30之前可以通过recreate+analyze来替代，如果用rebuild+analyze速度慢
analzye表
Sql代码
- alter table xxxxxx analyze partition pm1/all;
check表
Sql代码
- alter table xxxxxx check partition pm1/all;
Sql代码
- show create table employees2; //查看分区表的定义
- show table status like ‘employees2‘\G; //查看表时候是分区表 如“Create_options: partitioned”
- select * from information_schema.KEY_COLUMN_USAGE where table_name=‘employees2‘; //查看索引
- SELECT * FROM information_schema.partitions WHERE table_name=‘employees2‘ //查看分区表
- explain partitions select * from employees2 where separated < ‘1990-01-01‘ or separated > ‘2016-01-01‘; //查看分区是否被select使用
实战mysql分区
标签：大量 fun 速度 mysq xpl 碎片 like column font
