mysql学习笔记(11)之分库分表（全）

最新推荐文章于 2024-06-06 11:24:14 发布

小鱼AS

最新推荐文章于 2024-06-06 11:24:14 发布

阅读量363

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/weixin_39775274/article/details/114292350

版权

mysql 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

分库分表

表分区与分表介绍
分库分表
- - 水平切分
  - 垂直切分

表分区与分表介绍

什么是分表？

分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，我们可以称为子表，每个表都对应三个文件，MYD数据文件，.MYI索引文件，.frm表结构文件。这些子表可以分布在同一块磁盘上，也可以在不同的机器上。app读写的时候根据事先定义好的规则得到对应的子表名，然后去操作它。

什么是分区？

分区和分表相似，都是按照规则分解表。不同在于分表将大表分解为若干个独立的实体表，而分区是将数据分段划分在多个位置存放，可以是同一块磁盘也可以在不同的机器。分区后，表面上还是一张表，但数据散列到多个位置了。app读写的时候操作的还是大表名字，数据库自动去组织分区的数据。

在讲分表之前先介绍一下MySQL的分区，因为从某一些方面它也可以说是“分表”， mysql的表分区主要是通过一些特殊的语句，创建独立的空间，事实上创建分区表的每个分区都是有索引的独立表，分区看上去像一个单独的表数据库分区是一种物理数据库设计技术分区的主要目的是为了让某些特定的查询操作减少响应时间，同时对于应用来讲分区完全是透明的。MySQL的分区主要有两种形式：水平分区（Horizontal Partitioning）和垂直分区（Vertical Partitioning）。

使用场景：

表非常大以至于无法全部都放在内存中，或者只在表的最后部分有热点数据，其他均是历史数据。
分区表的数据更容易维护。例如，想批量删除大量数据可以使用清除整个分区的方式。
另外，还可以对一个独立分区进行优化、检查、修复等操作。
分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备。
可以使用分区表来避免某些特殊的瓶颈，例如ImnoDB的单个索引的互斥访间、ext3文件系统的inode锁竞争等。
如果需要，还可以备份和恢复独立的分区，这在非常大的数据集的场景下效果非常好。

分区表本身也有一定的限制，下面是比较重要的几点

一个表最多只能有1024个分区。
在MySQL5.1中，分区表达式必须是整数，或者是返回整数的表达式。在MySQL5.5中，某些场景中可- - 以直接使用列来进行分区。
如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来。
分区表中无法使用外键约束。

分区的优点

与单个磁盘或文件系统分区相比，可以存储更多的数据。

对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便地实现。通常和分区有关的其他优点包括下面列出的这些。MySQL分区中的这些功能目前还没有实现，但是在我们的优先级列表中，具有高的优先级；我们希望在5.1的生产版本中，能包括这些功能。

一些查询可以得到极大的优化，这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改，所以在第一次配置分区方案时还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。涉及到例如SUM()和COUNT()这样聚合函数的查询，可以很容易地进行并行处理。这种查询的一个简单例子如 “SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id；”。通过“并行”，这意味着该查询可以在每个分区上同时进行，最终结果只需通过总计所有分区得到的结果。通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。

表分区的类型

MySQL对于表分区有四种方式分别是：range，list，hash，key的方式分区

RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。
LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

表分区演示

range类型

range分区使用values less than 操作符来进行定义，把连续且不相互重叠的字段分配给分区，命令如下。

create table emp(
  `no` varchar(20) not null,
  `name` varchar(20),
  deptno int,
  birthdate date,
  salary int
)
partition by range(salary) (
  partition p1 values less than(1000),
  partition p2 values less than(5000),
  partition p3 values less than(10000)
);

insert into emp values('001', 'shineyork', 10, '2019-10-10', 5000);
insert into emp values('002', 'keke', 20, '2019-10-10', 1500);
insert into emp values('003', 'a', 10, '2019-10-10', 10500);
insert into emp values('004', 'n', 20, '2019-10-10', 1000);
insert into emp values('004', 'c', 20, '2019-10-10', 6000);

如上的方式就是把数据根据salary的value进行划分，区分到不同的表区中；而这其中partition by range的语法类似于“switch…case”的语法，如果salary小余5000就会在p1中。。。

而如上就是分区之后的数据表的结构，其中emp#P#p2.ibd是分区之后的数据，而emp.frm就是表的结构

mysql> insert into emp values(‘003’, ‘a’, 10, ‘2019-10-10’, 10500);
ERROR 1526 (HY000): Table has no partition for value 10500

而上面的insert中因为10500不在这个范围类所以会产生问题,解决这个问题的办法就是在其后加入“partition p4 values less than maxvalue” 语法

create table emp(
  `no` varchar(20) not null,
  `name` varchar(20),
  deptno int,
  birthdate date,
  salary int
)
partition by range(salary) (
  partition p1 values less than(1000),
  partition p2 values less than(5000),
  partition p3 values less than(10000),
  partition p4 values less than maxvalue
)

查询分区之后某一个区中的数据

select * from emp partition (p3);

在range中也可以使用MySQL的系统函数，比如根据年龄进行区分

create table emp(
  `no` varchar(20) not null,
  `name` varchar(20),
  deptno int,
  birthdate date,
  salary int
)
partition by range(year(birthdate)) (
  partition p1 values less than(1990),
  partition p2 values less than(2000),
  partition p3 values less than(2010),
  partition p4 values less than maxvalue
)

list类型

list分区类似于range分区，区别在于list中的每个分区的定义和选择基于某列的值从属于一个集合，而range分区是属于一个连续区间值得集合。

create table emp(
  `no` varchar(20) not null,
  `name` varchar(20),
  deptno int,
  birthdate date,
  salary int
)
partition by list(deptno) (
  partition p1 values in (10,20,30),
  partition p2 values in (1,2,3),
  partition p4 values in (4,40)
);

insert into emp values('001', 'shineyork', 1, '2019-10-10', 5000);
insert into emp values('002', 'keke', 20, '2019-10-10', 1500);
insert into emp values('003', 'a', 10, '2019-10-10', 10500);
insert into emp values('004', 'n', 40, '2019-10-10', 1000);
insert into emp values('005', 'c', 6, '2019-10-10', 6000);

mysql> insert into emp values('005', 'c', 6, '2019-10-10', 6000);
ERROR 1526 (HY000): Table has no partition for value 6

hash类型

HASH分区是基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式。 HASH分区主要用来确保数据在预先确定数目的分区中平均分布。在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中；而在HASH分区中，MySOL自动完成这些工作，用户所要做的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。

create table emp(
  `no` varchar(20) not null,
  `name` varchar(20),
  deptno int,
  birthdate date,
  salary int
)
partition by hash(year(birthdate)) partitions 4;

key类型

类似于hash分区，区别在于key分区只支持计算依赖或多列，且MySQL服务器其自身的哈希函数，这些函数是基于password()一样的运算规则

create table emp(
  `no` varchar(20) not null,
  `name` varchar(20),
  deptno int,
  birthdate date,
  salary int
)
partition by key(year(birthdate)) partitions 4;

分区的原理

分区表是由多个相关的底层表实现，这些底层表也是由句柄对象表示，所以我们也可以直接访问各个分区，存储引擎管理分区的各个底层表和管理普通表一样（所有的底层表都必须使用相同的存储引擎），分区表的索引只是在各个底层表上各自加上一个相同的索引，从存储引擎的角度来看，底层表和一个普通表没有任何不同，存储引擎也无须知道这是一个普通表还是一个分区表的一部分。

在分区表上的操作按照下面的操作逻辑进行：

select查询

当查询一个分区表的时候，分区层先打开并锁住所有的底层表，优化器判断是否可以过滤部分分区，然后再调用对应的存储引擎接口访问各个分区的数据

insert操作

当写入一条记录时，分区层打开并锁住所有的底层表，然后确定哪个分区接受这条记录，再将记录写入对应的底层表

delete操作

当删除一条记录时，分区层先打开并锁住所有的底层表，然后确定数据对应的分区，最后对相应底层表进行删除操作

update操作

当更新一条数据时，分区层先打开并锁住所有的底层表，mysql先确定需要更新的记录在哪个分区，然后取出数据并更新，再判断更新后的数据应该放在哪个分区，然后对底层表进行写入操作，并对原数据所在的底层表进行删除操作有些操作是支持过滤的。例如，当删除一条记录时，MySQL需要先找到这条记录，如果WHERE条件恰好和分区表达式匹配，就可以将所有不包含这条记录的分区都过滤掉。这对UPDATE语句同样有效。如果是INSERT操作，则本身就是只命中一个分区，其他分区都会被过滤掉。MySQL先确定这条记录属于哪个分区，再将记录写入对应的底层分区表，无须对任何其他分区进行操作。虽然每个操作都会“先打开并锁住所有的底层表”，但这并不是说分区表在处理过程中是锁住全表的。如果存储引擎能够自己实现行级锁，例如InnoDB，则会在分区层释放对应表锁。这个加锁和解锁过程与普通InnoDB上的查询类似。后面我们会通过一些例子来看看，当访问一个分区表的时候，打开和锁住所有底层表的代价及其带来的后果。

分库分表

当一个表太大不利于维护时，可考虑将大表拆分成小表，当然，这些表是属于同一个数据库的，这种技术称为分表；当一个数据库的处理能力不够支撑业务，增加CPU的作用也十分有限时，就可能需要讲部分移到表的数据库，以增加系统处理能力，这种技术成为分库；通过精心的数据模型设计，将大的业务表拆分成小表，再将一系列小表分到不同的服务器，使得每台服务器都能独立处理部分业务，这种技术称为水平拆分，俗称分库分表。分表的数量可以和物理的机器数不一致，分表数量称为逻辑份数，分库的数量称为物理份数,当逻辑份数大于物理份数时，就可以迅速获得水平扩展能力。

何为切分？

简单来说，就是指通过某种特定的条件，将我们存放在同一个数据库中的数据分散存放到多个数据库（主机）上面，以达到分散单台设备负载的效果。数据的切分（Sharding）根据其切分规则的类型，可以分为两种切分模式。一种是按照不同的表（或者Schema）来切分到不同的数据库（主机）之上，这种切可以称之为数据的垂直（纵向）切分；另外一种则是根据表中的数据的逻辑关系，将同一个表中的数据按照某种条件拆分到多台数据库（主机）上面，这种切分称之为数据的水平（横向）切分。

垂直切分的最大特点就是规则简单，实施也更为方便，尤其适合各业务之间的耦合度非常低，相互影响很小，业务逻辑非常清晰的系统。在这种系统中，可以很容易做到将不同业务模块所使用的表分拆到不同的数据库中。根据不同的表来进行拆分，对应用程序的影响也更小，拆分规则也会比较简单清晰。

水平切分于垂直切分相比，相对来说稍微复杂一些。因为要将同一个表中的不同数据拆分到不同的数据库中，对于应用程序来说，拆分规则本身就较根据表名来拆分更为复杂，后期的数据维护也会更为复杂一些。

水平切分

相对于垂直拆分，水平拆分不是将表做分类，而是按照某个字段的某种规则来分散到多个库之中，每个表中包含一部分数据。简单来说，我们可以将数据的水平切分理解为是按照数据行的切分，就是将表中的某些行切分到一个数据库，而另外的某些行又切分到其他的数据库中，如图
在这里插入图片描述

拆分数据就需要定义分片规则。关系型数据库是行列的二维模型，拆分的第一原则是找到拆分维度。比如：从会员的角度来分析，商户订单交易类系统中查询会员某天某月某个订单，那么就需要按照会员结合日期来拆分，不同的数据按照会员 ID 做分组，这样所有的数据查询 join 都会在单库内解决；如果从商户的角度来讲，要查询某个商家某天所有的订单数，就需要按照商户 ID 做拆分；但是如果系统既想按会员拆分，又想按商家数据，则会有一定的困难。如何找到合适的分片规则需要综合考虑衡量。

几种典型的分片规则包括：

按照用户 ID 求模，将数据分散到不同的数据库，具有相同数据用户的数据都被分散到一个库中；
按照日期，将不同月甚至日的数据分散到不同的库中；
按照某个特定的字段求摸，或者根据特定范围段分散到不同的库中。

既然数据做了拆分有优点也就优缺点。
优点：

拆分规则抽象好，join 操作基本可以数据库做；
不存在单库大数据，高并发的性能瓶颈；
应用端改造较少；提高了系统的稳定性跟负载能力。

缺点：

拆分规则难以抽象；
分片事务一致性难以解决；
数据多次扩展难度跟维护量极大；
跨库 join 性能较差。

垂直切分

一个数据库由很多表的构成，每个表对应着不同的业务，垂直切分是指按照业务将表进行分类，分布到不同的数据库上面，这样也就将数据或者说压力分担到不同的库上面，如下图
在这里插入图片描述

系统被切分成了，用户，订单交易，支付几个模块。一个架构设计较好的应用系统，其总体功能肯定是由很多个功能模块所组成的，而每一个功能模块所需要的数据对应到数据库中就是一个或者多个表。而在架构设计中，各个功能模块相互之间的交互点越统一越少，系统的耦合度就越低，系统各个模块的维护性以及扩展性也就越好。这样的系统，实现数据的垂直切分也就越容易。但是往往系统之有些表难以做到完全的独立，存在这扩库 join 的情况，对于这类的表，就需要去做平衡，是数据库让步业务，共用一个数据源，还是分成多个库，业务之间通过接口来做调用。在系统初期，数据量比较少，或者资源有限的情况下，会选择共用数据源，但是当数据发展到了一定的规模，负载很大的情况，就需要必须去做分割。

一般来讲业务存在着复杂 join 的场景是难以切分的，往往业务独立的易于切分。如何切分，切分到何种程度是考验技术架构的一个难题。

优点：