mysql分区分表区别

MySql分区、分表和分库

数据库的数据量达到一定程度之后，为避免带来系统性能上的瓶颈。需要进行数据的处理，采用的手段是分区、分片、分库、分表。

一些问题的解释：

1.为什么要分表和分区？日常开发中我们经常会遇到大表的情况，所谓的大表是指存储了百万级乃至千万级条记录的表。这样的表过于庞大，导致数据库在查询和插入的时候耗时太长，性能低下，如果涉及联合查询的情况，性能会更加糟糕。分表和表分区的目的就是减少数据库的负担，提高数据库的效率，通常点来讲就是提高表的增删改查效率。2.什么是分表？分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，我们可以称为子表，每个表都对应三个文件，MYD数据文件，.MYI索引文件，.frm表结构文件。这些子表可以分布在同一块磁盘上，也可以在不同的机器上。app读写的时候根据事先定义好的规则得到对应的子表名，然后去操作它。3.什么是分区？分区和分表相似，都是按照规则分解表。不同在于分表将大表分解为若干个独立的实体表，而分区是将数据分段划分在多个位置存放，可以是同一块磁盘也可以在不同的机器。分区后，表面上还是一张表，但数据散列到多个位置了。app读写的时候操作的还是大表名字，db自动去组织分区的数据。4.mysql分表和分区有什么联系呢？（1）都能提高mysql的性高，在高并发状态下都有一个良好的表现。（2）分表和分区不矛盾，可以相互配合的，对于那些大访问量，并且表数据比较多的表，我们可以采取分表和分区结合的方式，访问量不大，但是表数据很多的表，我们可以采取分区的方式等。（3）分表技术是比较麻烦的，需要手动去创建子表，app服务端读写时候需要计算子表名。采用merge好一些，但也要创建子表和配置子表间的union关系。（4）表分区相对于分表，操作方便，不需要创建子表。分区

MySQL的物理数据，存储在表空间文件（.ibdata1和.ibd）中，这里讲的分区的意思是指将同一表中不同行的记录分配到不同的物理文件中，几个分区就有几个.idb文件。

MySQL在5.1时添加了对水平分区的支持。

分区是将一个表或索引分解成多个更小，更可管理的部分。

每个区都是独立的，可以独立处理，也可以作为一个更大对象的一部分进行处理。这个是MySQL支持的功能，业务代码无需改动。

可以通过使用SHOW VARIABLES命令来确定MySQL是否支持分区。

MySQL分区类型RANGE分区：基于一个给定区间边界，得到若干个连续区间范围，按照分区键的落点，把数据分配到不同的分区；LIST分区：类似RANGE分区，区别在于LIST分区是基于枚举出的值列表分区，RANGE是基于给定连续区间范围分区；HASH分区：基于用户自定义的表达式的返回值，对其根据分区数来取模，从而进行记录在分区间的分配的模式。这个用户自定义的表达式，就是MySQL希望用户填入的哈希函数。KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且使用MySQL 服务器提供的自身的哈希函数。RANGE分区

把连续区间按范围划分，是实战最常用的一种分区类型，行数据基于属于一个给定的连续区间的列值被放入分区。

但是记住，当插入的数据不在一个分区中定义的值的时候，会抛异常。RANGE分区主要用于日期列的分区，比如交易表啊，销售表啊等。可以根据年月来存放数据。如果你分区走的唯一索引中date类型的数据，那么注意了，优化器只能对YEAR(),TO_DAYS(),TO_SECONDS(),UNIX_TIMESTAMP()这类函数进行优化选择。实战中可以用int类型的字段来存时间戳做分区列，那么只用存yyyyMM就好了，也不用关心函数了。CREATE TABLE`Order` (`id`INT NOT NULL AUTO_INCREMENT,`partition_key`INT NOT NULL,`amt`DECIMAL(5) NULL) PARTITION BY RANGE(partition_key)PARTITIONS 5(PARTITION part0 VALUES LESS THAN(201901),PARTITION part1 VALUES LESS THAN(201902),PARTITION part2 VALUES LESS THAN(201903),PARTITION part3 VALUES LESS THAN(201904),PARTITION part4 VALUES LESS THAN(201905),PARTITION part4 VALUES LESS THAN MAXVALUE;ｉｎｓｅｒｔ INTO `Order` (`id`, `partition_key`, `amt`) VALUES (‘1‘, ‘201901‘, ‘1000‘);ｉｎｓｅｒｔ INTO `Order` (`id`, `partition_key`, `amt`) VALUES (‘2‘, ‘201902‘, ‘800‘);ｉｎｓｅｒｔ INTO `Order` (`id`, `partition_key`, `amt`) VALUES (‘3‘, ‘201903‘, ‘1200‘);

RANGE分区通过使用PARTITION BY RANGE(expr)实现 ， 其中“expr” 可以是某个列值， 或一个基于某个列值并返回一个整数值的表达式，如YEAR(date)。

不过值得注意的是，expr的返回值，不可以为NULL。

VALUES LESS THAN的排列必须从小到大顺序列出，这样MySQL才能识别一个一个的区间段。

涉及聚合函数SUM()、COUNT()的查询时，如果不指定分区，那么会在每个分区上并行处理。

LIST分区

MySQL中的LIST分区在很多方面类似于RANGE分区。

和RANGE分区一样，LIST分区的每个分区必须明确定义。

它们的主要区别在于，LIST分区是基于枚举出的值列表分区，RANGE是基于给定连续区间范围分区；

LIST分区通过使用PARTITION BY LIST(expr)来实现 。

例如：

create table user(a int(11),b int(11))partition by list(b)(partition p0 values in (1,3,5,7,9),partition p1 values in (2,4,6,8,0));

如果试图插入字段值（或分区表达式的返回值）不在分区值列表中的任何一行时，那么“ｉｎｓｅｒｔ”查询将失败并报错。

要重点注意的是，LIST分区没有类似如“VALUES LESS THAN MAXVALUE”这样的包含其他值在内的定义。所以将要匹配的任何值都必须在值列表中能够找到。

HASH分区

HASH分区主要用来确保数据在预先确定数目的分区中平均分布。

在RANGE和LIST分区中，我们必须明确指定一个给定的区间或列值集合，来指定哪些记录进入哪些分区；

而在HASH分区中，MySQL自动完成分配记录到区间的工作，你所要做的只是确定一个用来做哈希的字段或者表达式，以及指定被分区的表将要被分割成的分区数量。

PARTITION BY HASH

例如：

CREATE TABLE employees (id INT NOT NULL,fname VARCHAR(30),lname VARCHAR(30),hired DATE NOT NULL DEFAULT ‘1970-01-01‘,separated DATE NOT NULL DEFAULT ‘9999-12-31‘,job_code INT,store_id INT)PARTITION BY HASH(store_id)PARTITIONS 4；

如果没有包括一个PARTITIONS子句，那么分区的数量将默认为1。

最有效率的哈希函数是只对单个表列进行计算，并且它的结果值随字段值进行一致地增大或减小，因为这考虑了在分区范围上的“修剪”。

也就是说，表达式值和它所基于的列的值变化越接近，MySQL就可以越有效地使用该表达式来进行HASH分区。

当使用了“PARTITION BY HASH”时，MySQL将基于用户提供的函数结果的模数来确定使用哪个编号的分区。换句话，对于一个表达式“expr”，将要保存记录的分区编号为N ，其中“N = MOD(expr, num)”。

KEY分区

按照KEY进行分区类似于按照HASH分区，除了HASH分区使用的用户定义的表达式，而KEY分区的哈希函数是由MySQL 服务器提供。

MySQLCluster使用函数MD5()来实现KEY分区；对于使用其他存储引擎的表，服务器使用其自己内部的 哈希函数，这些函数是基于与PASSWORD()一样的运算法则。

“CREATE TABLE ... PARTITION BY KEY”的语法规则类似于创建一个通过HASH分区的表的规则。它们唯一的区别在于使用的关键字是KEY而不是HASH，并且KEY分区只采用一个或多个列名的一个列表。

create table user(a int(11),b datetime)partition by key(b)partitions 4;子分区

子分区是分区表中每个分区的再次分割。

例如：

CREATE TABLE ts (id INT, purchased DATE)PARTITION BY RANGE(YEAR(purchased))SUBPARTITION BY HASH(TO_DAYS(purchased))(PARTITION p0 VALUES LESS THAN (1990)(SUBPARTITION s0,SUBPARTITION s1),PARTITION p1 VALUES LESS THAN (2000)(SUBPARTITION s2,SUBPARTITION s3),PARTITION p2 VALUES LESS THAN MAXVALUE(SUBPARTITION s4,SUBPARTITION s5));

注意的语法项：

每个分区必须有相同数量的子分区。如果在一个分区表上的某个分区上使用SUBPARTITION来明确定义子分区，那么就必须定义其他所有分区的子分区。

子分区可以用于特别大的表，在多个磁盘间分配数据和索引。

然后就可以根据具体的情况来持久化：

CREATE TABLE ts (id INT, purchased DATE)PARTITION BY RANGE(YEAR(purchased))SUBPARTITION BY HASH(TO_DAYS(purchased))(PARTITION p0 VALUES LESS THAN (1990)(SUBPARTITION s0aDATA DIRECTORY = ‘/disk0‘INDEX DIRECTORY = ‘/disk1‘,SUBPARTITION s0bDATA DIRECTORY = ‘/disk2‘ INDEX DIRECTORY = ‘/disk3‘),PARTITION p1 VALUES LESS THAN (2000)(SUBPARTITION s1aDATA DIRECTORY = ‘/disk4/data‘ INDEX DIRECTORY = ‘/disk4/idx‘,SUBPARTITION s1bDATA DIRECTORY = ‘/disk5/data‘ INDEX DIRECTORY = ‘/disk5/idx‘),PARTITION p2 VALUES LESS THAN MAXVALUE(SUBPARTITION s2a,SUBPARTITION s2b));DATA DIRECTORY表示数据的物理文件的存放目录INDEX DIRECTORY表示索引的物理文件的存放目录分区的管理

MySQL提供了许多修改分区表的方式。添加、删除、重新定义、合并或拆分已经存在的分区是可能的。

所有这些操作都可以通过使用ALTER TABLE命令的分区扩展来实现。

新增分区

为已创建的未分区表创建分区：

RANGE：ALTER TABLE tb PARTITION BY RANGE (expr) ( range_partitions_exprs(n>0) );LIST：ALTER TABLE tb PARTITION BY LIST (expr) ( list_partitions_exprs(n>0) );HASH：ALTER TABLE tb PARTITION BY HASH(expr) PARTITIONS 2;KEY：ALTER TABLE tb PARTITION BY KEY(expr) PARTITIONS 2;

为分区表添加n个分区：

RANGE：ALTER TABLE tb ADD PARTITION ( range_partitions_exprs(n>0) );LIST：ALTER TABLE tb ADD PARTITION ( list_partitions_exprs(n>0) );HASH & KEY：ALTER TABLE tb ADD PARTITION PARTITIONS n;调整分区

reorganize

数据不丢失的前提下，将m个分区合并为n个分区（m>n），即减量重新组织分区

RANGE：ALTER TABLE tb REORGANIZE PARTITION s0,s1,... INTO ( range_partitions_exprs(n) )LIST：ALTER TABLE tb REORGANIZE PARTITION s0,s1,... INTO ( list_partitions_exprs(n) )HASH & KEY：ALTER TABLE clients COALESCE PARTITION n; (n小于原有分区数)

数据不丢失的前提下，将分区表的m个分区拆分为n个分区（m