mysql的分区：使用range分区

一.概念

1.range分区：根据分区键值的范围对数据行存储到表的不同分区中；多个分区的范围要连续，但是不能重叠，默认情况下使用values less than属性，即每个分区不包含设置的那个值，上限取不到。

2.使用场景：分区键是日期或者时间

3..分区注意事项

1.具有主键或唯一索引的表，主键或唯一索引必须是分区键的一部分。

2.对分区表进行查询最好在where从句中包含分区键

3.结合业务场景选择分区键，避免分区查询。

二.案例：

1.sql语句：
 

CREATE TABLE `bonc_test`.`user_login_tb`  (
  `user_id` int(10) UNSIGNED  NOT NULL  AUTO_INCREMENT,
  `user_name` varchar(50) NOT NULL,
  `ip` varchar(255) NOT NULL,
  `login_time` datetime(0) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8
partition by RANGE(YEAR(login_time))(
partition p0 values  less than(2014),
partition p1 values  less than(2015),
partition p2 values  less than(2017)
);

当有主键的表时会出现，

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

分区的字段必须是要包含在主键当中。这时候分区的字段要么是主键，要么把分区字段加入到主键中，从而形成复合主键。

2.修改sql：

CREATE TABLE `bonc_test`.`user_login_tb`  (
  `user_id` int(10) UNSIGNED  NOT NULL  AUTO_INCREMENT,
  `user_name` varchar(50) NOT NULL,
  `ip` varchar(255) NOT NULL,
  `login_time` datetime(0) NOT NULL,
  PRIMARY KEY (`user_id`,login_time)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8
partition by RANGE(YEAR(login_time))(
partition p0 values  less than(2014),
partition p1 values  less than(2015),
partition p2 values  less than(2017)
);

3.添加数据：

insert into  user_login_tb(user_id,user_name,ip,login_time) values(111,'bb','192.168.23.4','2012-08-02')
insert into  user_login_tb(user_id,user_name,ip,login_time) values(112,'bb','192.168.23.4','2013-08-02')
insert into  user_login_tb(user_id,user_name,ip,login_time) values(113,'bb','192.168.23.4','2014-08-02')
insert into  user_login_tb(user_id,user_name,ip,login_time) values(null,'bb','192.168.23.4','2014-08-02')
insert into  user_login_tb(user_id,user_name,ip,login_time) values(null,'bb','192.168.23.4','2015-08-02')
insert into  user_login_tb(user_id,user_name,ip,login_time) values(null,'bb','192.168.23.4','2016-07-02')

4.查看分区信息：

SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,
TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD
FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='user_login_tb';

5.新增一条大于分区的数据：可以看到大于分区的数据，无法添加进去。

insert into  user_login_tb(user_id,user_name,ip,login_time) values(null,'bb','192.168.23.4','2018-07-02')

6.新增分区

我们可以使用alter table tablename add partition 方式再最后面添加分区

#1.新添加分区
alter table user_login_tb add partition(partition p3 values less than(2019));

#2.查看分区：

SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,
TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD
FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='user_login_tb';

3.新添加数据

insert into  user_login_tb(user_id,user_name,ip,login_time) values(null,'bb','192.168.23.4','2018-07-02')
4.再次查看

7.删除分区

#删除分区
alter table user_login_tb drop partition p1;

8.分区迁移

alter table user_login_tb exchange partition p2 with table arch_login_log

如果user_login_tb 表中p2分区为空，则交换到arch_login_log中的数据进行更新，覆盖掉原来的数据，要慎用。