mysql分区

1.分区和分表的区别

分表：从表面意思上看呢，就是把一张表分成N多个小表

分区：分区呢就是把一张表的数据分成N多个区块，这些区块可以在同一个磁盘上，也可以在不同的磁盘上

2.创建分区表

DROP TABLE IF EXISTS `mg_zz_feature`;
CREATE TABLE  mg_zz_feature  (
feature VARCHAR(50),
fileCode VARCHAR(32),
flag enum('0','1'),
readFlag enum('0','1')
)  ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY KEY(feature) PARTITIONS 5;

3.创建储存过程添加数据

drop PROCEDURE if exists batch_insert_test;

delimiter $$
create PROCEDURE batch_insert_test(records integer)
BEGIN
DECLARE i int;
set i =0;
while i<records DO
			insert into mg_zz_feature values(FLOOR(RAND()*10000000),FLOOR(RAND()*100000),'1','0');
set i = i+1;
end while;
END
$$

call batch_insert_test(5000);

4.查看分区情况

#查看每一个分区的表名，区名，分区方式，分区的字段，拥有的记录数量
select TABLE_NAME,PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,TABLE_ROWS from information_schema.`PARTITIONS` where TABLE_NAME='mg_zz_feature';
#查看分区表的记录总数
select sum(TABLE_ROWS) from information_schema.`PARTITIONS` where TABLE_NAME='mg_zz_feature';
#查看版本
select VERSION();

5.增加和删除分区

# 增加3个分区
alter table mg_zz_feature ADD PARTITION PARTITIONS 3;
#删除2个分区
alter table mg_zz_feature COALESCE PARTITION 2;

6.对分区表增删改查

SELECT * from mg_zz_feature；
insert into mg_zz_feature values("562356","568956",'1','0');