MySQL 横向表分区之RANGE分区小结

时间:2024-10-06 20:03:56

MySQL 横向表分区之RANGE分区小结

by:授客 QQ:1033553122

目录

简介 1

RANGE分区 1

创建分区表 1

查看表分区 2

新增表分区 2

新增数据 3

分区表查询 3

删除数据 4

删除分区 4

简介

mysql表分区--RANGE分区,属于横向分区。举例说,假如有100条数据,分成十份,前10条数据放到第一个分区,第二个10条数据放到第二个分区,依此类推。横向分区,并不会改变表的结构。

 

RANGE分区

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

创建分区表

例子:按user_id分区,创建分区表

CREATE TABLE `auth_user_group` (

`id` INT(11) NOT NULL AUTO_INCREMENT,

`user_id` INT(11) NOT NULL,

`group_id` INT(11) NOT NULL,

PRIMARY KEY (id, user_id)

)

PARTITION BY RANGE(user_id) (

PARTITION p1 VALUES LESS THAN (6),

PARTITION p2 VALUES LESS THAN (11),

PARTITION pmax VALUES LESS THAN MAXVALUE

);

注意:

1、分区字段必须是主键、联合主键的一部分,否则会报如下错误:

A PRIMARY KEY must include all columns in the table's partitioning function

2、分区字段值的设置必须是连续的,否则会报错

注解:

PARTITION 分区名 VALUES THAN (字段值)

如上表定义,意为,user_id值小于6的记录,存放在p1表分区,值大于等于6,小于11的记录,存放在p2表分区,小于最大值,大于等于11的记录,都存放在pmax分区。

查看表分区

SHOW CREATE TABLE table_name;

例子:

SHOW CREATE TABLE auth_user_group;

结果如下:

CREATE TABLE `auth_user_group` (

`id` INT(11) NOT NULL AUTO_INCREMENT,

`user_id` INT(11) NOT NULL,

`group_id` INT(11) NOT NULL,

PRIMARY KEY (`id`,`user_id`)

) ENGINE=INNODB DEFAULT CHARSET=utf8

新增表分区

注意:如果创建分区表时,使用了MAXVALUE,形如:

PARTITION pmax VALUES LESS MAXVALUE,那么新增分区时会报错,类似如下:

MAXVALUE can only be used in last partition definition

原因:因为新增分区时,是在原有分区列表末尾继续添加,而THAN MAXVALUE正好位于末尾,同时其也包含了要添加的分区范围,所提系统会任务冲突,解决方法,定义分区表时,不设置MAXVALUE,如下:

CREATE TABLE `auth_user_group2` (

`id` INT(11) NOT NULL AUTO_INCREMENT,

`user_id` INT(11) NOT NULL,

`group_id` INT(11) NOT NULL,

PRIMARY KEY (id, user_id)

)

PARTITION BY RANGE(user_id) (

PARTITION p1 VALUES LESS THAN (6),

PARTITION p2 VALUES LESS THAN (11)

);

ALTER TABLE auth_user_group2 ADD PARTITION

(PARTITION p3 VALUES LESS THAN (21),

PARTITION p4 VALUES LESS THAN (31)

)

新增数据

和普通插入一样,但是要注意的是,分区字段值不能超过给定分区的最大值(例中为30),

例:

INSERT INTO auth_user_group2(user_id, group_id) VALUES(39, 30);

报错:

Table has no partition for value 39

修改改成这样就可以:

INSERT INTO auth_user_group2(user_id, group_id) VALUES(10, 30);

分区表查询

增对分区表,我们在查询时可以指定分区,加快查询速度。

SELECT * FROM auth_user_group2 PARTITION(p2)

MySQL 横向表分区之RANGE分区小结

如果涉及多个分区,则用逗号隔开。

SELECT * FROM auth_user_group2 PARTITION(p2, p3)

删除数据

增对分区表,我们在删除时可以指定分区,避免检索带来的时间消耗。

DELETE FROM auth_user_group2 PARTITION (p2) WHERE id = 1

MySQL 横向表分区之RANGE分区小结MySQL 横向表分区之RANGE分区小结

和查询一样,如果涉及到多个分区,则逗号分隔

DELETE FROM auth_user_group2 PARTITION (p2, p3) WHERE id = 1

删除分区

ALTER TABLE auth_user_group2 DROP PARTITION p3

注意:删除分区也会删除记录