mysql中的分表分区

数据库在数据量很大的时候分表储存和分区储存在查询的时候能很好的提高数据库性能。

为什么要分区？

看一下表结构

user, CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `sex` varchar(2) DEFAULT NULL,
  `age` int(11) DEFAULT '0',
    primary key(id)
) ENGINE=InnoDB AUTO_INCREMENT=10762 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

用存储过程向表中添加20万条数据：

delimiter $
create procedure adduser()
begin 
	declare i int default 0;
    while i<200000
    do
    insert into user(name,sex,age) values(substring(MD5(RAND()),1,20),'男',rand()*100);
    set i = i+1;
    end while;
end;$
delimiter ;
call adduser;

等待时间太长就关了，大概添加了十二万条数据吧。够用了，现在开始不分区来查询。

右下角时间0.375s，用的时间还是比较长的；

现在我们给user这个表增加分区(分区字段必须为主键或表中没有主键)：

user, CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `sex` varchar(10) DEFAULT NULL,
  `age` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (`age`)
(PARTITION child VALUES LESS THAN (12) ENGINE = InnoDB,
 PARTITION young VALUES LESS THAN (18) ENGINE = InnoDB,
 PARTITION adult VALUES LESS THAN (40) ENGINE = InnoDB,
 PARTITION middleage VALUES LESS THAN (60) ENGINE = InnoDB,
 PARTITION `old` VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

查看一下我们创建的分区

可以看到已经按照我们添加的分区分好了，现在我们向表中添加数据。

添加20万条数据大概跑了10分钟多。。。。。。

来看分区的查询吧：

刚才在12万条的数据用了0.375s,在20万条的数据查询用了0.156，性能大概提高了3倍。如此看来在数据量比较大的时候分区还是很有必要的。

分区方式

1.range分区

      上述分区即为range分区，即区分不同的范围来对表进行分区，每个分区都有自己的独立数据，索引文件的目录结构。

还可以将这些 数据所在的磁盘分开完全独立，以提高IO的吞吐量。

2.list分区

       例子：

PARTITION BY LIST (‘分区字段’) (  
     PARTITION p0 VALUES IN (0,4,8,12) , 
     PARTITION p1 VALUES IN (1,5,9,13) ,  
     PARTITION p2 VALUES IN (2,6,10,14),  
     PARTITION p3 VALUES IN (3,7,11,15)   
);
/*
 in 后边写情况，相当于一个list集合，判断字段的值在不在list中。 */

3.hash分区

例子：

PARTITION BY HASH (id) PARTITIONS 4 (  
     PARTITION p0,  
     PARTITION p1,  
     PARTITION p2,  
     PARTITION p3
);

通过指定分区数量，通过hash值分区。只支持以数字型分区。

4.key分区

例子：

PARTITION BY KEY (id) PARTITIONS 4 (  
     PARTITION p0,  
     PARTITION p1,  
     PARTITION p2,  
     PARTITION p3
);

对hash的一种延申，key支持除text和blob之外的所有类型的分区，当创建分区的时候没有指定分区字段时会首先以主键作为分区，如果不存在主键会用非空唯一键作为分区字段。

 

分区管理

1.删除分区

alter table user drop partition p0;

2.增加分区

alter table user add partition p0 然后就是约束信息。

3.重建分区

alter table user reorganize partition 然后就是分区的条件。