mysql分区（转）

mysql分区的分区,简单来说是将一个表根据指定的条件,水平切分. 

将一张物理表,切为多个物理表,但在数据库表现上还是一张表.

这样的好处是, 当查询条件可以判定到某张分区表的时候,会只查询这张分区表,而不用整表扫描.

值得注意的是,有外键关联的表不能进行分区. 直到现在这个情况也没有改变. 当前mysql最新版本为5.5.

 

 

一. 查看数据库是否支持分区

1	SHOW VARIABLES LIKE '%partition%';

+-----------------------+-------+

| Variable_name         | Value |

+-----------------------+-------+

| have_partition_engine | YES   |

+-----------------------+-------+

1 row in set (0.00 sec)

 

 

二. 创建分区

分区目前有4个类型 RANGE, LIST, HASH, KEY,  这篇博客要讲的是RANGE类型. 手册里如下描述.

1	RANGE : 基于属于一个给定连续区间的列值，把多行分配给分区。

注意:RANGE的值只能从小到大,而且,标识列必须包含在主键里.

 

创建range分区有2种方式,1种是创建表时就创建分区,另1种是创建表后再创建分区.

 

1.创建表时就创建分区

1 2 3 4 5 6 7 8 9 10 11 12

CREATE TABLE members (     id INT,      fname VARCHAR(25),     entry_time DATETIME,     PRIMARY KEY (`id`,`entry_time`) ) PARTITION BY RANGE(entry_time) (     PARTITION p0 VALUES LESS THAN ('2013-06-30 23:59:59') ENGINE = InnoDB,     PARTITION p1 VALUES LESS THAN ('2013-12-31 23:59:59') ENGINE = InnoDB,     PARTITION p2 VALUES LESS THAN ('2014-06-30 23:59:59') ENGINE = InnoDB,     PARTITION p3 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB );

 

2.创建表后再创建分区

1 2 3 4 5 6

ALTER TABLE members PARTITION BY RANGE(entry_time) (     PARTITION p0 VALUES LESS THAN ('2013-06-30 23:59:59') ENGINE = InnoDB,     PARTITION p1 VALUES LESS THAN ('2013-12-31 23:59:59') ENGINE = InnoDB,     PARTITION p2 VALUES LESS THAN ('2014-06-30 23:59:59') ENGINE = InnoDB,     PARTITION p3 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB );

 

分区后, Mysql 将会 members 分成 4个表, 将 '2013-06-30 23:59:59' 以前注册的都放在p0里, 将 '2013-12-31 23:59:59'以前注册的都放在p1里,以此类推,

2014年7月份和以后的都放在p3里.

 

三. 测试分区

使用 explain partitions 测试查询语句是否精确到单个分区.

1 2 3 4 5 6 7 8

explain partitions select count(*) from members where entry_time = '2013-03-01 23:59:59';                           +----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+--------------------------+ | id | select_type | table  | partitions | type  | possible_keys | key       | key_len | ref  | rows | Extra                    | +----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+--------------------------+ |  1 | SIMPLE      | school | p0         | index | PRIMARY       | id      | 8       | NULL |    2 | Using where; Using index | +----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+--------------------------+ 1 row in set (0.00 sec)

 

四. 获取分区信息

1	mysql> SHOW CREATE TABLE members\G

显示如下

1 2 3 4 5 6 7 8 9 10 11

CREATE TABLE members (     id INT,     fname VARCHAR(25),     entry_time DATETIME ) PARTITION BY RANGE(entry_time) (     PARTITION p0 VALUES LESS THAN ('2013-06-30 23:59:59') ENGINE = InnoDB,     PARTITION p1 VALUES LESS THAN ('2013-12-31 23:59:59') ENGINE = InnoDB,     PARTITION p2 VALUES LESS THAN ('2014-06-30 23:59:59') ENGINE = InnoDB,     PARTITION p3 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB );

 

 

五. 修改分区(拆分,合并)

拆分

上边 member 的分区,只分到了2014年6月份.现在我们将p3分区重拆分为多个分区.

1 2 3 4 5 6 7

ALTER TABLE members REORGANIZE PARTITION p3 INTO (     PARTITION p4 VALUES LESS THAN ('2014-12-31 23:59:59') ENGINE = InnoDB,     PARTITION p5 VALUES LESS THAN ('2015-06-30 23:59:59') ENGINE = InnoDB,     PARTITION p6 VALUES LESS THAN ('2015-12-31 23:59:59') ENGINE = InnoDB,     PARTITION p7 VALUES LESS THAN ('2016-06-30 23:59:59') ENGINE = InnoDB,     PARTITION p8 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB );

合并

再次声明:RANGE的值只能从小到大,而且,标识列必须包含在主键里.

现在将p0,p1,p2合并到m1里.

1 2 3

ALTER TABLE members REORGANIZE PARTITION p0,p1,p2 INTO (     PARTITION m1 VALUES LESS THAN ('2014-06-31 23:59:59') ENGINE = InnoDB );

 

六. 重新分区

语法与创建分区一样, 直接 alter by 就可以了.Mysql会将旧分区逻辑删除掉.

比如更改分区 标志列.

1 2 3 4 5 6

ALTER TABLE members PARTITION BY RANGE(id) (     PARTITION p0 VALUES LESS THAN (10000) ENGINE = InnoDB,     PARTITION p1 VALUES LESS THAN (20000) ENGINE = InnoDB,     PARTITION p2 VALUES LESS THAN (30000) ENGINE = InnoDB,     PARTITION p3 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB );

还可以更改为其他分区类型 如 LIST,HASH等. 举一个HASH的例子, 将id平均分布到5分区表.

1	ALTER TABLE members PARTITION BY HASH (id) PARTITIONs 5;

 

七. 删除分区以及分区数据

如非必要,尽量不要用,使用 拆分合并更好一点.

1	ALTER TABLE tr DROP PARTITION p1;