mysql 分区 分表 分库分表

分区

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

mysql数据库中的数据是以文件的形势存在磁盘上的，默认放在/mysql/data下面（可以通过my.ini中的datadir来查看），

一张表主要对应着两个个文件（innodb引擎为例），一个是frm存放表结构的，一个是ibd数据和索引文件。如果一张表的数据量太大的话，那么myd,ibd就会变的很大，查找数据就会变的很慢，这个时候我们可以利用mysql的分区功能，在物理上将这一张表对应的三个文件，分割成许多个小块，这样呢，我们查找一条数据时，就不用全部查找了，只要知道这条数据在哪一块，然后在那一块找就行了。如果表的数据太大，可能一个磁盘放不下，这个时候，我们可以把数据分配到不同的磁盘里面去。

 MyISAM有三个文件frm(存储表定义)，MYD(MYData，存储数据)，MYI(MYIndex，存储索引)

 表分区，是指根据一定规则，将数据库中的一张表分解成多个更小的，容易管理的部分。从逻辑上看，只有一张表，但是底层却是由多个物理分区组成。

分类

RANGE

根据范围分区，范围应该连续但是不重叠，使用PARTITION BY RANGE, VALUES LESS THAN关键字。不使用COLUMNS关键字时RANGE括号内必须为整数字段名或返回确定整数的函数。

ALTER TABLE `examine_task` 
 PARTITION BY RANGE(id) PARTITIONS 3(

 PARTITION part0 VALUES LESS THAN (660609),  

 PARTITION part1 VALUES LESS THAN (1260609),  

 PARTITION part2 VALUES LESS THAN (maxvalue)) ;

 INSERT INTO `zb`.`examine_task`     (`id`, `create_time`, `update_time`, `enable`)  

                                  VALUES   (1360609, '2022-05-14 15:55:00', '2022-06-27 15:42:37', 0);

id=1360609范围在660609和1260609之间所以存入的时候存入的第二个分区part1

LIST

根据具体数值分区，每个分区数值不重叠，使用PARTITION BY LIST、VALUES IN关键字。跟Range分区类似，不使用COLUMNS关键字时List括号内必须为整数字段名或返回确定整数的函数。

ALTER TABLE `logs` 
 PARTITION BY LIST(id) PARTITIONS 4( 
 PARTITION part0 VALUES IN (1,5), 
 PARTITION part1 VALUES IN (2,6), 
 PARTITION part2 VALUES IN (3,7),  
 PARTITION part3 VALUES IN (4,8)) ;

 INSERT INTO `logs`     (`id`, `create_time`, `update_time`, `enable`)  

                                  VALUES   (1, '2022-05-14 15:55:00', '2022-06-27 15:42:37', 0);

id=1 in（1，5）所以存入的时候存入的第一个分区part0

HASH

Hash分区主要用来确保数据在预先确定数目的分区中平均分布，Hash括号内只能是整数列或返回确定整数的函数，实际上就是使用返回的整数对分区数取模。

ALTER TABLE  `logs` 
 PARTITION BY HASH(id) PARTITIONS 3 ;

id=4 4mod3=1 所以在第二个分区p1

LINEAR HASH

分区和hash的算法不同，分区的编号是通过2的幂（powers-of-two）算法得到 。

ALTER TABLE  `logs` 
 PARTITION BY LINEAR HASH(id) PARTITIONS 3 ;

KEY

Key分区与Hash分区很相似，只是Hash函数不同，定义时把Hash关键字替换成Key即可，同样Key分区也有对应与线性Hash的线性Key分区方法。

ALTER TABLE `logs` 
 PARTITION BY KEY(id) PARTITIONS 3 ;

LINEAR KEY

ALTER TABLE `zb`.`logs` 
 PARTITION BY LINEAR KEY(id) PARTITIONS 3 ;

子分区

子分区只能在range和list分区下添加的子分区。子分区只有Hash，Key，linear Hash和linear Key

ALTER TABLE `logs` 
 PARTITION BY RANGE (id)

 SUBPARTITION BY HASH (id)
 SUBPARTITIONS 2(

   PARTITION p0 VALUES LESS THAN (3) ,
   PARTITION p1 VALUES LESS THAN (6) ,
   PARTITION p3 VALUES LESS THAN MAXVALUE

) 

注意事项

分区的列必须包含主键列和唯一索引列

ERROR 1503: A PRIMARY KEY must include all columns in the table's partitioning function
SQL Statement:

ERROR 1503: A UNIQUE INDEX must include all columns in the table's partitioning function
SQL Statement:

分表

将一个表分成n个表

分类

垂直切分

如果一个数据表一部分是经常使用，另一部分使用频率很低，那么可以将高频访问的数据和另一部分拆成两个表，关联起来。

水平切分

如果是因为表中的数据量过于庞大，则可以采用水平切分，按照某种约定好的规则（最经典的是时间范围）将数据切分到不同的数据库中；

注意事项（通过merge存储引擎来实现分表）

所有表（分表和主表）的列，列名，列类型，列长度必须一致

所有表存储引擎必须都是myisam

字符集，主表必须是分表的超集

主表有的索引分表必须有，索引名称可以不一样，主表没有的索引，分表可以有

分表改名，主表失效需要删除重新创建，主表改名，不影响。

分区和分表的区别和各自优缺点

提高性能上

• 分表后，单表的并发能力提高了，因为查询一次花费的时间变短了，如果出现高并发的话，总表可以根据不能的查询，将并发分到不同的小表里面。磁盘I/O性能也提高了，因为一个大的数据分成了好几个小的。
• 分区后，将一个表分成多个区，能提高mysql的性高，单表的数据量达到一定之后，分区将显的很无力这时候就需要分表来解决。

所以开发前期确定一个表的数据数据量可以1个表承受，分区好一点（例如历史数据会定期删除，现有数据量上不会达到数据库存储量瓶颈。）。如果不能那就是分表

实现的难易度上

• 分表的方法有很多，用merge来分表，是最简单的一种方式。这种方式根分区难易度差不多，并且对程序代码来说可以做到透明的。如果是用其他分表方式就比分区麻烦了。
• 分区实现是比较简单的，建立分区表，根建平常的表没什么区别，并且对开代码端来说是透明的。

mysql分表和分区有什么联系呢

• 都能提高mysql的性高，在高并发状态下都有一个良好的表面
• 分表和分区不矛盾，可以相互配合的，对于那些大访问量，并且表数据比较多的表，我们可以采取分表和分区结合的方式（如果merge这种分表方式，不能和分区配合的话，可以用其他的分表试），访问量不大，但是表数据很多的表，我们可以采取分区的方式等。

分库分表存在的问题

事务问题

在执行分库分表之后，由于数据存储到了不同的库上，数据库事务管理出现了困难。如果依赖数据库本身的分布式事务管理功能去执行事务，将付出高昂的性能代价；如果由应用程序去协助控制，形成程序逻辑上的事务，又会造成编程方面的负担。

跨库跨表的join问题

在执行了分库分表之后，难以避免会将原本逻辑关联性很强的数据划分到不同的表、不同的库上，这时，表的关联操作将受到限制，我们无法join位于不同分库的表，也无法join分表粒度不同的表，结果原本一次查询能够完成的业务，可能需要多次查询才能完成。

额外的数据管理负担和数据运算压力

额外的数据管理负担，最显而易见的就是数据的定位问题和数据的增删改查的重复执行问题，这些都可以通过应用程序解决，但必然引起额外的逻辑运算，例如，对于一个记录用户成绩的用户数据表userTable，业务要求查出成绩最好的100位，在进行分表之前，只需一个order by语句就可以搞定，但是在进行分表之后，将需要n个order by语句，分别查出每一个分表的前100名用户数据，然后再对这些数据进行合并计算，才能得出结果。