mysql分区seect_实战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(&＃39;2013-04-01&＃39;)),

PARTITION p201304 VALUES LESS THAN (unix_timestamp(&＃39;2013-05-01&＃39;)),

PARTITION p201305 VALUES LESS THAN (unix_timestamp(&＃39;2013-06-01&＃39;)),

PARTITION p201306 VALUES LESS THAN (unix_timestamp(&＃39;2013-07-01&＃39;)),

PARTITION p201307 VALUES LESS THAN (unix_timestamp(&＃39;2013-08-01&＃39;)),

PARTITION p201308 VALUES LESS THAN (unix_timestamp(&＃39;2013-09-01&＃39;)),

PARTITION p201309 VALUES LESS THAN (unix_timestamp(&＃39;2013-10-01&＃39;)),

PARTITION p201310 VALUES LESS THAN (unix_timestamp(&＃39;2013-11-01&＃39;)),

PARTITION p201311 VALUES LESS THAN (unix_timestamp(&＃39;2013-12-01&＃39;)),

PARTITION p201312 VALUES LESS THAN (unix_timestamp(&＃39;2014-01-01&＃39;)),

PARTITION p201401 VALUES LESS THAN (unix_timestamp(&＃39;2014-02-01&＃39;))

);

注意：

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&＃39;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&＃39;s partitioning function

2. 范围分区添加分区只能在最大值后面追加分区

3. 所有分区的engine必须一样

4. 范围分区分区字段：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 <&＃39;2006-01-01&＃39; or (separated >= &＃39;2006-01-01&＃39; and separated

重定义分区(包括重命名分区，伴随移动数据；合并分区)

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 &＃39;employees2&＃39;\G; //查看表时候是分区表 如“Create_options: partitioned”

select * from information_schema.KEY_COLUMN_USAGE where table_name=&＃39;employees2&＃39;; //查看索引

SELECT * FROM information_schema.partitions WHERE table_name=&＃39;employees2&＃39; //查看分区表

explain partitions select * from employees2 where separated <&＃39;1990-01-01&＃39; or separated > &＃39;2016-01-01&＃39;; //查看分区是否被select使用