MySql分区、分表和分库

2022-10-19 17:42:16

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分区类型

  1. RANGE分区：基于一个给定区间边界，得到若干个连续区间范围，按照分区键的落点，把数据分配到不同的分区；
  2. LIST分区：类似RANGE分区，区别在于LIST分区是基于枚举出的值列表分区，RANGE是基于给定连续区间范围分区；
  3. HASH分区：基于用户自定义的表达式的返回值，对其根据分区数来取模，从而进行记录在分区间的分配的模式。这个用户自定义的表达式，就是MySQL希望用户填入的哈希函数。
  4. 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;
    
INSERT INTO `Order` (`id`, `partition_key`, `amt`) VALUES ('1', '201901', '1000');
INSERT INTO `Order` (`id`, `partition_key`, `amt`) VALUES ('2', '201902', '800');
INSERT 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)
);

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

要重点注意的是，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
        )
    );

注意的语法项：

  1. 每个分区必须有相同数量的子分区。
  2. 如果在一个分区表上的某个分区上使用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 s0a
                DATA DIRECTORY = '/disk0'
                INDEX DIRECTORY = '/disk1',
            SUBPARTITION s0b
                DATA DIRECTORY = '/disk2' 
                INDEX DIRECTORY = '/disk3'
        ),
        PARTITION p1 VALUES LESS THAN (2000)
        (
            SUBPARTITION s1a
                DATA DIRECTORY = '/disk4/data' 
                INDEX DIRECTORY = '/disk4/idx',
            SUBPARTITION s1b
                DATA 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<n），即增量重新组织分区

  • RANGE：ALTER TABLE tb REORGANIZE PARTITION p0,p1,... INTO ( range_partitions_exprs(n) )
  • LIST：ALTER TABLE tb REORGANIZE PARTITION p0,p1,... INTO ( list_partitions_exprs(n) )

不能使用REORGANIZE PARTITION来改变表的分区类型；也就是说。

重建分区，即先删除分区中的所有记录，然后重新插入。可用于整理分区碎片。

  • ALTER TABLE tb REBUILD PARTITION p0, p1;

优化分区，整理分区碎片 optimize

  • ALTER TABLE tb OPTIMIZE PARTITION p0, p1;

如从分区中删除了大量的行，或者对一个带有可变长度字段（VARCHAR、BLOB、TEXT类型）的行作了许多修改，可以使用优化分区来收回没有使用的空间，并整理分区数据文件的碎片。

修复分区，修补被破坏的分区。

  • ALTER TABLE tb REPAIR PARTITION p0,p1;

检查分区，这个命令可以告诉你分区中的数据或索引是否已经被破坏，如果被破坏，请使用修复分区来修补

  • ALTER TABLE tb CHECK PARTITION p1;

删除分区

删除一个分区，以及分区内的所有数据：

  • ALTER TABLE tb DROP PARTITION p2;

删除一个分区，但保留分区内的所有数据(MySQL 5.5引入)： truncate

  • ALTER TABLE tb TRUNCATE PARTITION p2;

查看分区

查看某个schema下某个表的分区信息

  • SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = 'xxx' AND TABLE_NAME LIKE 'xxxx';

分析某个分区,主要看行数和名称以及状态

  • ALTER TABLE tb ANALYZE PARTITION p3;

分表

分表顾名思义，就是把一张超大的数据表，拆分为多个较小的表，得到有效的缓解。

超大表会带来如下的影响：

  1. 单表数据量太大，会被频繁读写，加锁操作密集，导致性能降低。
  2. 单表数据量太大，对应的索引也会很大，查询效率降低，增删操作的性能也会降低。

分表和分区看起来十分类似，确实，分区已经能够在磁盘层面将一张表拆分成多个文件了，理论上前面提到的大表的问题都能得到有效解决。因为分区就是分表的数据库实现版本。

在MySQL 5.1分区功能出现以前，要想解决超大表问题，只能采用分表操作，因为这类问题十分常见，MySQL才自带了一个分区功能，以达到相同的效果。

所以你可以直接说分区就是分表的替代，分表是分区出现以前的做法。不过这不代表我们就没有必要学习分表了，相反，水平分表的功能或许可以用更加便捷的分区来替代，但是垂直分表的功能，分区却无法替代。

分表只能通过程序代码来实现，目前市面上有许多分表的框架。（ Apache ShardingSphere ）

分表和分区的区别

  1. 分区只是一张表中的数据和索引的存储位置发生改变，分表则是将一张表分成多张表，是真实的有多套表的配套文件
  2. 分区没法突破数据库层面，不论怎么分区，这些分区都要在一个数据库下。而分表可以将子表分配在同一个库中，也可以分配在不同库中，突破数据库性能的限制。
  3. 分区只能替代水平分表的功能，无法取代垂直分表的功能。

分表的类型

分表分为水平分表和垂直分表。

水平分表

水平分表和分区很像，或者说分区就是水平分表的数据库实现版本，它们分的都是行记录。

MySql分区、分表和分库

但是需要注意，如果这些表还是在同一个库中，所以库级别的数据库操作还是有IO瓶颈。分表可以将单张表的数据切分到多个服务器上去，每个服务器具有相应的库与子表，这是分区所不能有的优势。

水平分表的切分规则一般有如下几种：

范围切分

  • 可以根据某个字段的范围做划分，比如订单号字段，从0到10000一个表，10001到20000一个表。

HASH取模

  • 可以根据某个字段的HASH取模做划分，比如将一个用户表分成10个子表，可以取用户id，然后hash后取10的模，从而分配到不同的数据库上。不过这种划分一旦确定后，就无法改变子表数量了。

地理/国籍/类型等

  • 比如按照华东，华南，华北这样来区分业务表，或者安卓用户，IOS用户等来区分用户表。

时间

  • 按照时间切分，比如将6个月前，甚至一年前的数据切出去放到另外的一张表，因为随着时间流逝，这些表的数据被查询的概率变小，所以没必要和“热数据”放在一起，这个也是“冷热数据分离”。

垂直分表

水平分表分的是行记录，而垂直分表，分的是列字段，它就像用一把刀，垂直的将一个表切成多张表一样。

垂直分表是基于列字段进行的。一般是表中的字段较多，或者有数据较大长度较长（比如text，blob，varchar(1000)以上的字段）的字段时，我们将不常用的，或者数据量大的字段拆分到“扩展表”上。这样避免查询时，数据量太大造成的“跨页”问题。

垂直分表的切分规则很好理解，一般是“不常用”或者“字段数据量大”这两点来做切割

MySql分区、分表和分库

分库

分库同样是为了应对超大数据带来的巨大的IO需求，如果不拆库，那么单库所能支持的吞吐能力和磁盘空间，就会成为制衡业务发展的瓶颈。

分库的主要目的是为突破单节点数据库服务器的I/O能力限制，解决数据库水平扩展性问题。

分库作用

分区和分表可以把单表分到不同的硬盘上，但不能分配到不同服务器上。一台机器的性能是有限制的，用分库可以解决单台服务器性能不够，或者成本过高问题。

将一个库分成多个库，并在多个服务器上部署，就可以突破单服务器的性能瓶颈，这是分库必要性的最主要原因。

分库的类型

分库同样分为水平分库和垂直分库。

水平分库

  • 水平分库和水平分表相似，并且关系紧密，水平分库就是将单个库中的表作水平分表，然后将子表分别置于不同的子库当中，独立部署。
  • 因为库中内容的主要载体是表，所以水平分库和水平分表基本上如影随形。
  • 例如用户表，我们可以使用注册时间的范围来分表，将2020年注册的用户表usrtb2020部署在usrdata20中，2021年注册的用户表usrtb2021部署在usrdata21中。

垂直分库

  • 同样的，垂直分库和垂直分表也十分类似，不过垂直分表拆分的是字段，而垂直分库，拆分的是表。
  • 垂直分库是将一个库下的表作不同维度的分类，然后将其分配给不同子库的策略。
  • 例如，我们可以将用户相关的表都放置在usrdata这个库中，将订单相关的表都放置在odrdata中，以此类推。
  • 垂直分库的分类维度有很多，可以按照业务模块划分（用户/订单...），按照技术模块分（日志类库/图片类库...），或者空间，时间等等。
MySql分区、分表和分库

问题

事务问题。

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

跨库跨表的join问题。

  • 问题描述：在执行了分库分表之后，难以避免会将原本逻辑关联性很强的数据划分到不同的表、不同的库上，这时，表的关联操作将受到限制，我们无法join位于不同分库的表，也无法join分表粒度不同的表，结果原本一次查询能够完成的业务，可能需要多次查询才能完成。
  • 解决方法：tddl、MyCAT等都支持跨分片join。但是我们应该尽力避免跨库join，如果一定要整合数据，那么请在代码中多次查询完成。

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

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


下一篇：Oracle 表分区详解（partition table）