分区(5.7)

1.准备

# 随机生成字符串函数定义
delimiter $$

create function rand_string(n int) returns varchar(255) 
begin 
 declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
 declare return_str varchar(255) default '';
 declare i int default 0;
 while i < n do 
   set return_str = concat(return_str, substring(chars_str, floor(rand() * 52) + 1, 1));
   set i = i + 1;
 end while;
 return return_str;
end $$

delimiter ;

# 查看数据库版本
select version();

# 查看分区是否激活
show plugins;
partition ---->> ACTIVE 

# 查询某分区数据(p0分区名称)
select * from table partition(p0)

# 删除分区(数据也被删除)
alter  table user drop partition p0;

几种分区模式(一张表最多1024个分区)
    Range：基于属于一个给定连续区间的列值 把多行分配给分区 
    List：类似于按RANGE分区 区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择 
    Hash：基于用户定义的表达式的返回值来进行选择的分区 该表达式使用将要插入到表中的这些行的列值进行计算 这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式
    Key：KEY分区：类似于按HASH分区 区别在于KEY分区只支持计算一列或多列 且MySQL服务器提供其自身的哈希函数 必须有一列或多列包含整数值

2.range分区

# 创建表user(p0 p1分区)
create table `user`(
    `id` int not null auto_increment,
    `username` varchar(30) not null,
    primary key(id)
)engine=innodb default charset=utf8
partition by range(id)(
    partition p0 values less than(10),
    partition p1 values less than(20)
);

# 添加p2分区
alter table user ADD partition (
    partition p2 values less than maxvalue 
);

# 插入30条数据
delimiter $$
create procedure insert_user(in start int(10), in max_num int(10))
begin
    declare i int default 0; 
    set autocommit = 0;  
    repeat
        set i = i + 1;
        insert into user values ((start+i) ,rand_string(10));
        until i = max_num
    end repeat;
   commit;
end $$
delimiter ;

call insert_user(0, 30);

# explain可以看出走分区啦
explain select * from user where id=15;

# 重建分区
alter table user reorganize partition p0,p1 INTO (partition p0 values less than (20));

3.List分区

# 创建表user1(p0 p1 p2分区)
create table `user1`(
    `id` int not null auto_increment,
    `username` varchar(30) not null,
    `user_type` int not null,
    primary key(id, user_type)
)engine=innodb default charset=utf8
partition by list(user_type)(
    partition p0 values in (1, 2),
    partition p1 values in (3, 4)
);

# 插入数据
delimiter $$
create procedure insert_user1(in start int(10), in max_num int(10), in user_type int(10))
begin
    declare i int default 0; 
    set autocommit = 0;  
    repeat
        set i = i + 1;
        insert into user1 values ((start+i) ,rand_string(10), user_type);
        until i = max_num
    end repeat;
   commit;
end $$
delimiter ;

call insert_user1(0, 5, 1);
call insert_user1(5, 9, 2);
call insert_user1(9, 11, 3);
call insert_user1(11, 20, 4);

# explain可以看出走分区啦
explain select * from user1 where user_type=1;

# 重建分区
alter table user1 reorganize partition p0,p1 INTO (partition p0 values in (1, 2, 3, 4));

4.Key(HashKey是MySQL系统产生)

# 创建表user2(p0 p1 p2分区)
create table user2 (  
    `id` int not null auto_increment,
    `username` varchar(30) not null,
    primary key(id, username)
) engine=innodb default charset=utf8 
partition by key (username) partitions 3 (  
    partition p0,  
    partition p1,  
    partition p2
);

# 插入数据
delimiter $$
create procedure insert_user2(in start int(10), in max_num int(10))
begin
    declare i int default 0; 
    set autocommit = 0;  
    repeat
        set i = i + 1;
        insert into user2 values ((start+i) ,rand_string(10));
        until i = max_num
    end repeat;
   commit;
end $$
delimiter ;

call insert_user2(0, 30);

# 可以查看各分区数据
select * from user2 PARTITION(p0);

# 无分区type=all 有分区type=index
explain select * from user2 where username='MDJInsZUxJ';

5.Hash(必须整数)

# 创建表user3(p0 p1 p2分区)
create table user3 (  
    `id` int not null auto_increment,
    `username` varchar(30) not null,
    primary key(id)
) engine=innodb default charset=utf8 
partition by hash (id) partitions 3 (  
    partition p0,  
    partition p1,  
    partition p2
);

# 插入数据
delimiter $$
create procedure insert_user3(in start int(10), in max_num int(10))
begin
    declare i int default 0; 
    set autocommit = 0;  
    repeat
        set i = i + 1;
        insert into user3 values ((start+i) ,rand_string(10));
        until i = max_num
    end repeat;
   commit;
end $$
delimiter ;

call insert_user3(0, 30);

# 可以查看各分区数据
select count(*) from user3 PARTITION(p0);

explain select * from user3 where id=15;

6. 子分区(暂时不说了)