Mysql 分区操作简介

来源：互联网发布：江苏旅游局官网通知编辑：程序博客网时间：2024/05/07 07:51

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;