Mysql 分区操作简介

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;