实战mysql分区(PARTITION)
前些天拿到一个表，将近有4000w数据，没有任何索引，主键。（建这表的绝对是个人才）
这是一个日志表，记录了游戏中物品的产出与消耗，原先有一个后台对这个表进行统计。。。。。（这要用超级计算机才能统计得出来吧），只能帮前人填坑了。。。。
数据太大，决定用分区来重构。
如果你发现是empty，说明你的mysql版本不够，分区至少要5.1
下面针对业务查询，决定用时间来做range分区(还有list,hash等类型)，一个月一个区.
按照RANGE分区的表是通过如下一种方式进行分区的，每个分区包含那些分区表达式的值位于一个给定的连续区间内的行。这些区间要连续且不能相互重叠，使用VALUES LESS THAN操作符来进行定义。
新建一个表：
CREATE TABLE xxxxxxxx ( 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'))
);
注意：
- 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
- 范围分区添加分区只能在最大值后面追加分区
- 所有分区的engine必须一样
- 范围分区分区字段：integer、数值表达式、日期列，日期函数表达式(如year(),to_days(),to_seconds(),unix_timestamp())
将旧的表数据导入到新表后，看到新表的数据都分布到不同的区了！
维护命令：
添加分区
alter table xxxxxxx add partition (partition p0 values less than(1991)); //只能添加大于分区键的分区
删除分区
alter table xxxxxxx drop partition p0; //可以删除任意分区
删除分区数据
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');
重定义分区（包括重命名分区，伴随移动数据；合并分区)
alter table xxxxx reorganize partition p1,p3,p4 into (partition pm1 values less than(2006),
partition pm2 values less than(2011));
rebuild重建分区
alter table xxxxxx rebuild partition pm1/all; //相当于drop所有记录，然后再reinsert；可以解决磁盘碎片
优化表
alter table tt2 optimize partition pm1; //在大量delete表数据后，可以回收空间和碎片整理。但在5.5.30后支持。在5.5.30之前可以通过recreate+analyze来替代，如果用rebuild+analyze速度慢
analzye表
alter table xxxxxx analyze partition pm1/all;
check表
alter table xxxxxx check partition pm1/all;
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使用
zhuzy