mysql数据库什么时候才需要分库分表

客官°小女子只卖身不卖艺 2024-04-06 09:53 55阅读 0赞

1.引入

微服务架构想必大家都是有所耳闻

简单来说，微服务架构就是把传统的一个单体应用以一套"小服务"的方式进行开发，这些"小服务"可以运行在不同机器上，它们在自己的进程中运行，"小服务"之间可以通过像是 HTTP API 这样的轻量级的机制进行通信,这些"小服务"紧紧围绕项目的业务需求开发，同时，它们是以业务边界进行划分成独立的微服务。这些微服务看似独立又像是一个整体，构成了一个业务集群。

2.为何分库

微服务架构从业务逻辑实现的角度上看系统的性能得到了优化，可是对数据库的负担就加重了。假设一个分布式电子商务系统，那么这个系统会包含会员信息、订单信息、商品信息、商品库存信息等等内容，数据存放在数据库中，要访问数据，就要与数据库建立连接，而数据库的连接是有限的，况且在这样的业务环境下，会出现较多的高并发场景，如果都同时向这个商城数据库访问数据，数据库显然是受不起这样的折腾。如图：

![76e68a1da2e74d769a19f811aedb91d3.png][]  
上文提到，微服务是以业务边界进行划分的，那么这些服务就可以使用不同的编程语言书写，以及不同数据存储技术，前提是保持最低限度的集中式管理。也就是说，各个微服务处理的数据可以达到自治。

因此，为了处理高并发，设计数据库就可以采取分库的方式进行，使得各个微服务拥有自己独立的数据库，就好比订单微服务自治订单信息、支付流水信息、退款信息等等，当订单微服务需要会员微服务的会员数据时，可以通过服务的通讯机制，比如feign，以此达到分担传统模式压力的效果，如图：

![d2cb04b37c264a59ac070b7255e3b533.png][]

同时，对于每一个划分好的库也可以再进行分库部署，划分出的库拥有相同的表，不同的只有存放的数据集。它可以有效的缓解单机单库的性能瓶颈和压力随着需求的细化，项目的业务量是庞大的，这也导致项目的数据量是庞大的，数据库分库部署可以有效减轻磁盘负担。如图：

![5eb5e97f5b5e4a389b8f57a5a95e7eae.png][]  
3.为何分表

微服务开发中，我们经常会遇到大表的情况，所谓大表是指存储了百万级乃至千万级条记录的表，这样的表数据过于庞大，导致数据库在查询和插入的时候耗时太长，就算使用索引，在大量的数据面前，查询的效率也会有所降低，更何况是使用不到索引的情况，下边列举一些使用不到索引的情况：

\# 使用LIKE通配符置于字符串前面

mysql> SELECT \* FROM test WHERE name LIKE '%小王';  
\# 函数运算  
mysql> SELECT \* FROM test WHERE UPPER(name) = 'ZS';

分表是对表进行分区，最主要的目的就是减轻数据库的负担，提高数据库的效率。表分区是根据一定的规则，把数据库的一张表分解为多个更小的表，使用分区的表从逻辑上看还是一个表，但物理存储分为了多份，表分区后的每个部分，都可以独立的进行数据处理，分区具有以下好处：

存储空间更大了

查询速度更快，只需要扫描需要的分区表，再将结果进行合并。不会因为全表扫描，而浪费不必要的资源  
对于删除数据来说，处理更方便了，只需要删除对应分区的数据即可

跨越磁盘存储，充分利用磁盘读取，提高吞吐量  
分区是将数据分段划分在多个位置存放，可以是同一块磁盘也可以在不同的机器。

表分区有很多的策略，根据不同的策略可以适应多种业务场景，例如可以通过表内属性值的范围进行分表，如下图，将商城支付流水表以流水时间进行划分：

![217b223875ae43d99acf39a9028f035a.png][]  
表在分区后，表面上还是一张表，但数据散列到多个位置了。应用程序读写的时候操作的还是大表的表名，数据库系统自动去组织分区的数据。 使用分区需要注意：

MySQL 8.0版本前支持创建表分区的存储引擎有InnoDB、Memory、MyISAM、MERGE，MySQL 8.0之后就只支持InnoDB存储引擎了

分区表必须一致，即同一张表分区后，各个分区表必须使用一致的存储引擎

3.1.表分区

表分区可在创建数据库表的时候进行指定，格式如下：

CREATE TABLE TABLE\_NAME(  
………  
)  
PARTITION BY RANGE|LIST|HASH(TABLE\_COLUMN)(  
PARTITION P0……  
)  
上文提到表分区有不同策略，也可以称为不同类型：

RANGE分区  
LIST分区  
COLUMNS分区  
HASH分区  
KEY分区  
子分区

3.1.1.RANGE分区

RANGE分区是基于一个给定连续区间范围，区间之间的不能互相重叠，数据会根据范围，分配到不同的分区,RANGE的分区键必须是单列的int类型，每个分区范围必须按顺序（后一个分区范围值比前一个值大）。 假设指定一表为RANGE分区，分4个区，最后一个区为了防止数据定义问题，将其设置为数值最大值“MAXVALUE”：

mysql> CREATE TABLE testrange(  
\-> id INT PRIMARY KEY AUTO\_INCREMENT,  
\-> name VARCHAR(10))  
\-> PARTITION BY RANGE(id)(  
\-> PARTITION p0 VALUES LESS THAN(50),  
\-> PARTITION p1 VALUES LESS THAN(100),  
\-> PARTITION p2 VALUES LESS THAN(150),  
\-> PARTITION p3 VALUES LESS THAN(MAXVALUE));

3.1.2.LIST分区

LIST分区的分区键的类型也只能是int类型，LIST分区是基于枚举值列表进行分区，枚举的范围同样不能有重复的值，如果插入数据不在枚举范围之内，则会报错。

\# 指定为LIST分区，分2个区  
mysql> CREATE TABLE testlist(  
\-> id INT PRIMARY KEY AUTO\_INCREMENT,  
\-> name VARCHAR(10))  
\-> PARTITION BY LIST(id)(  
\-> PARTITION p0 VALUES IN (1,2,3),  
\-> PARTITION p1 VALUES IN (4,6,9));  
  
mysql> INSERT INTO testlist VALUES(null,'张三');  
mysql> INSERT INTO testlist VALUES(2,'李四');

\# 插入列限制数值不存在的数，则会提示这张表没有改值的分区

mysql> INSERT INTO testlist VALUES(5,'李三');  
ERROR 1526 (HY000): Table has no partition for value 5

3.1.3.COLUMNS分区

COLUMNS分区区别于RANGE分区和LIST分区的最大特点是支持多列的分区，COLUMNS分区有两种形式，RANGE COLUMNS和LIST COLUMNS分区。两种分区形式都支持整数类型，日期类型，字符类型，区别在于，如果COLUMNS分区的分区键有多个，当数据库要进行数据插入时，会先考虑第一个键是否满足，如果满足条件就会进行数据写入，如果不满足条件就要对第二个键的条件进行判断，以此类推。

mysql> CREATE TABLE testrancol(  
\-> sid INT,cid INT,PRIMARY KEY(sid,cid))  
\-> PARTITION BY RANGE COLUMNS(sid,cid)(  
\-> PARTITION p0 VALUES LESS THAN(1,10),  
\-> PARTITION p1 VALUES LESS THAN(10,20));

3.1.4.HASH分区

HASH分区主要用于将一整个数据，分散为若干个相等数量的分区，HASH分区有两种类型：

(1)常规HASH分区，使用的是取模运算。假设分区数为4，则有0，1，2，3四个值，对应分区为四个。因为使用的取模运算，所以分区键必须是整数类型的列或返回整数类型的表达式：

mysql> CREATE TABLE testhash1(  
\-> id INT PRIMARY KEY,num VARCHAR(10))  
\-> PARTITION BY HASH(id) PARTITIONS 4;

\# 如果此时插入数据id为83，则模4取余得3，将数据放在第三个分区中

(2)线性HASH分区,其语法书写不同于常规HASH分区，需要加上LINEAR关键字。在数据分配的时候，使用的是2的幂运算进行分配数据的配分有两步运算：

mysql> CREATE TABLE testhash2(  
\-> id INT PRIMARY KEY,num VARCHAR(10))  
\-> PARTITION BY LINEAR HASH(id) PARTITIONS 4;

\# 1、第一步，算出V的值  
\# 计算方式为：V=POWER(2,CEILING(LOG(2,分区数)))  
\# 假设分区数为4，log()的值为2  
\# Ceiling()取最小整数，依旧是2  
\# power返回2的2次方，最后V=4

\# 2、第二步，对分区键和V-1进行位与运算  
\# 假设分区键值为8和10：  
\# 那么插入8与10和4-1的3进行按位与运算得出插入分区分别是0与2分区

3.1.5.KEY分区

KEY分区有与HASH分区类似，不同的地方有以下几点：

KEY分区不允许使用自定义表达式作为分区键  
KEY分区如果不指定分区键，则会默认使用表中主键。如果没有主键，则使用非空唯一键。如果都没有，那就必须手动指定分区键  
可以使用非数值类型的列作为分区键 KEY分区也分为常规KEY分区和线性KEY分区,其运算规则与HASH分区一致，不多做赘述。

mysql> CREATE TABLE testhash1(  
\-> id INT PRIMARY KEY,num VARCHAR(10))  
\-> PARTITION BY \[LINEAR\] KEY(id) PARTITIONS 4;

3.1.6.子分区

子分区是对分区表的每个区分，进行二次的分区，使用RANGE和LIST对表进行分区，则可以使用HASH或KEY进行子分区，假设表有2个分区，这2个分区又被进一步的分为2个子分区，总共有4个分区，写法有两种：

隐式创建子分区，子分区的名字是自动创建且重名，但不会冲突，输入小于1900的年份，会按HASH分区的规则(模2运算)，分别存放在两个P0中：

mysql> CREATE TABLE testfh(  
\-> id INT,pur DATE)  
\-> PARTITION BY RANGE(YEAR(pur))  
\-> SUBPARTITION BY HASH(TO\_DAYS(pur))  
\-> SUBPARTITIONS 2 (  
\-> PARTITION p0 VALUES LESS THAN(1900),  
\-> PARTITION p1 VALUES LESS THAN MAXVALUE);

显示创建子分区，要求每个分区的子分区数量必须一致，且分区的创建必须一致，即全部子分区都使用隐式创建或显式创建，不可混用，同时子分区的名字必须唯一

mysql> CREATE TABLE testfh(  
\-> id INT,pur DATE)  
\-> PARTITION BY RANGE(YEAR(pur))  
\-> SUBPARTITION BY HASH(TO\_DAYS(pur))(  
\-> PARTITION p0 VALUES LESS THAN(1900)(  
\-> SUBPARTITION s0, SUBPARTITION s1),  
\-> PARTITION p1 VALUES LESS THAN MAXVALUE(  
\-> SUBPARTITION s2, SUBPARTITION s3));

3.2.表分区注意点

3.2.1.RANGE表分区注意点

使用RANGE策略进行表分区的好处在于分区后数据的扩容性好，不需要进行数据迁移，如果插入的数据超过原先建立分区的范围可以根据实际情况考虑在原有基础上增加表分区或者水平部署一张相同的表进行存放数据，增加表分区可参考以下格式：

\# RANGE分区中添加分区，是在尾部进行添加，所以如果RANGE已经有包含最大值的分区，那么新添加的分区就会报错

ALTER TABLE 表名 ADD PARTITION (PARTITION 分区名 VALES LESS THAN (范围));

需要注意的是表在RANGE分区的时候指定的分区键需要考虑实际情况，如果使用不当会造成个别分区数据过多的情况。

假设一个电子商务系统需要存放订单的相关信息，用户进行商品购买则产生订单，订单往往包含多个不同商品，可以设置订单项表用于存放订单的每个商品id、商品名、价格、数量等数据，如果以商品id作为分区键则会产生"数据热点"问题，有些商品销量好，那么分区的数据就多，一些商品销量不好那么数据就会很少。

![2930c45245eb4972af145516ea20befc.png][]  
3.2.2.HASH表分区注意点

使用HASH策略的好处就在于解决数据热点问题，但是，HASH表分区的分区数量是固定的，如果数据过多达到了瓶颈，就要将分区数量进行修改了，因此原先已经存放的数据又要进行取模运算重新存放，需要进行数据迁移，语法如下：

\# 增加2个分区  
ALTER TABLE 表名 PARTITION 2;  
\# 减少2个分区  
ALTER TABLE 表名 COALESCE PARTITION 2;  
3.2.3.MySQL表分区遇到NULL值

当MySQL表分区遇到NULL值，MySQL不会禁止分区键有NULL值，但不同的分区类型会将NULL值当成不同的数据对待，如：

在RANGE分区中，NULL值被当成最小的数。

在LIST分区中，NULL被当成字符串，如果NULL不在LIST的枚举范围中，还有出现报错  
在HASH和KEY中，NULL值被当成0 所以，在使用分区时，要注意处理NULL值输入，以免出现MySQL的误判，将分区键设为NOT NULL或默认值都可以好的对应这种情况。

4.总结

本文介绍了为什么微服务架构大多采用分库分表的方式进行设计数据库，当然，分布式系统在设计过程中进行分库分表还需要注意一些问题，比如，在我们创建数据库表的时候是否可以先考虑表内数据的特性，事先将一些不经常需要更改的内容抽离出来，形成一张新的表，从某种程度上说，这种方式也是一种"分表"的操作。

同时，在进行分库在涉及事务安全性的时候也需要注意，比如商城中用户提交了订单，那么系统就需要对所购买商品的库存进行锁定，如果出现用户未支付订单超时等问题，就需要将已经锁定的库存进行数据回滚了，可是订单和库存在不同的数据，要如何保证事务的原子性呢？

如果都在本地部署，可以使用AOP对事务进行代理，在不同机器部署的情况下也可以通过设置undo\_log表并通过阿里的Seata进行代理。

但是在高并发情况下，这些方式容易造成"雪崩"，这个时候还可以考虑消息队列，通过延迟队列来完成库存的解锁。

[76e68a1da2e74d769a19f811aedb91d3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/ec5b1c0b2036422f9a41f6c09ecd961e.png
[d2cb04b37c264a59ac070b7255e3b533.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/209b94d99e574fe9addfdf5f482fcf5e.png
[5eb5e97f5b5e4a389b8f57a5a95e7eae.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/3667930cb5f246a188939b56672e3e1c.png
[217b223875ae43d99acf39a9028f035a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/9a8ceff755764e88bdbaf18561fd9644.png
[2930c45245eb4972af145516ea20befc.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/f0b1b7af30684e319bf24ee68b82f3f3.png