<返回更多

MySql分区、分表和分库

2021-09-27  博客园  ML李嘉图
加入收藏

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

一些问题的解释：

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
        )
    );

分区的管理

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

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

新增分区

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

为分区表添加n个分区：

调整分区

reorganize

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

数据不丢失的前提下，将分区表的m个分区拆分为n个分区（m<n），即增量重新组织分区

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

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

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

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

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

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

删除分区

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

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

查看分区

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

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

分表

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

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

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

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

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

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

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

分表和分区的区别

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

分表的类型

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

水平分表

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

MySql分区、分表和分库

 

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

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

范围切分

HASH取模

地理/国籍/类型等

时间

垂直分表

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

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

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

MySql分区、分表和分库

 

分库

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

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

分库作用

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

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

分库的类型

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

水平分库

垂直分库

MySql分区、分表和分库

 

问题

事务问题。

跨库跨表的join问题。

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

 


本文作者： ML李嘉图

本文链接：
https://www.cnblogs.com/zwtblog/p/15332735.html

声明：本站部分内容来自互联网,如有版权侵犯或其他问题请与我们联系，我们将立即删除或处理。
▍相关推荐
更多资讯 >>>
Copyright @ 简易百科 V 2.0 京ICP备14042104号