mysql 设置分区吗_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
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);
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 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);