分表实例_Mysql 学习笔记：分库分表 (sharding)

最新推荐文章于 2024-05-05 07:58:03 发布

weixin_29187895

最新推荐文章于 2024-05-05 07:58:03 发布

阅读量125

点赞数

文章标签：分表实例

本文链接：https://blog.csdn.net/weixin_29187895/article/details/112584222

版权

本文介绍了Mysql数据库的分库分表策略，包括逻辑拆分的分区与分表，以及物理拆分的读写分离、垂直拆分和水平拆分。逻辑拆分通过分区和分表提高查询效率；物理拆分通过主从复制实现读写分离，垂直拆分按业务拆分数据库，水平拆分利用哈希算法分布数据。文章还讨论了拆分带来的问题及解决方案，如跨库事务和跨表Join等。

摘要由CSDN通过智能技术生成

作者：马迪奥

出自：InfoQ 写作平台

原文：xie.infoq.cn/article/200e90b980e8fec1ad6850878

当数据库性能出现瓶颈时就需要通过扩展来提升性能，对于扩展性来说要么加强机器本身的性能，要么把任务分发到不同的机器上。对于数据库来说通过强悍的机器解决成本是很大的，如Oracle。通过多个廉价的机器实现水平扩展是现代的主流解决方案，如Mysql。

数据库水平扩展的核心是把数据拆分成不同的单元并放在不同的独立的实例上，这样就做到了负载均衡。拆分分为逻辑和物理拆分，逻辑拆分是对物理上不可分割的实例进行逻辑上的分割，物理拆分是拆分成多个独立的实例：

逻辑拆分
分区（Partition）
分表
物理拆分
读写分离
垂直拆分（分库）
水平拆分（分表）

1、逻辑拆分

（1）分区

我理解的逻辑分区：举个例子，操作系统中的分区，是将硬盘根据大小进行逻辑分区，就是我们看到的C、D、E、F盘，逻辑分区还是在同一个操作系统中。数据库产品的Partition分区也是一样的道理，将数据进行逻辑分区，对数据划分界限。

MySql 支持Range，List，Hash，Key。最常用的是Range。

Range：范围

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 NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN (21)
);

LIST：列表

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 LIST(store_id) (
    PARTITION pNorth VALUES IN (3,5,6,9,17),
    PARTITION pEast VALUES IN (1,2,10,11,19,20),
    PARTITION pWest VALUES IN (4,12,13,14,18),
    PARTITION pCentral VALUES IN (7,8,15,16)
);

Key：键

CREATE TABLE k1 (
    id INT NOT NULL,
    name VARCHAR(20),
    UNIQUE KEY (id)
)
PARTITION BY KEY()
PARTITIONS 2;

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( YEAR(hired) )
PARTITIONS 4;

例子：

数据：新闻表，2010开始记录，假设10年到15年每年的数据为200W，总数1000W；

条件：查询15年7月所有的新闻数据；

未分区：需要把表遍历，1000W条数据，查询性能就不用说了；

分区：按照年份分区，当要查询15年数据，只会遍历15年的数据200W条，加快了查询；

（2）分表

当单表数据行数超过一定量级时，读/写会变慢，查询需要检索更多数据，DML操作需要更多时间创建/更新索引；我们可以通过把这些数据分散到多个表中来提高效率，这样只涉及到部分数据而不是所有，最常用的分表算法是哈希算法。哈希函数使用除留余数法，即取余的方式。

建立所需要的N个表，表名：user_0 ... user_N-1，通过对ID取余运算直接路由到所在的表

user_0： 5%5
user_1： 1%5 / 6%5
user_2： 2%5
user_3： 3%5
user_4： 4%5

小结：逻辑分区是数据库提供的功能，不用对应用和业务做任何改变就能实现。哈希分表实现简单，只需要修改少量代码就能实现。对单表进行分表后，能够大大提高我们读写的效率。

2、物理拆分

（1）读写分离（主从复制）

读写分离的核心是把读/写操作路由的不同实例上，实例之间要的数据要保障一致（通过复制实现），路由可以自己识别 Insert/Update/Delete/Selete 做路由，也可以使用代理(mysql proxy)或中间件。

一般站点的读操作比写操作更加密集，查询量暴增的时候单台服务器无法处理这么多读操作，我们需要增加额外的服务器来支撑，使用主从方式，主做写操作，从做读操作，通过主从复制达到数据一致性，这样读操作压力会被分散。mysql使用单线程把主机数据复制到从机上实现数据一致性，所以需要对主从进行配置。

在上面的主从架构中，如果从库有很多个可能会出现复制延迟过大现象，原因是因为mysql复制需要在slave和master建立长连接，并且master需要开启binlog dump线程进行数据推送，过多的slave会导致复制延迟过大。可以增加复制源和开启半同步复制解决。

增加复制源：

开启半同步复制：主库提交事务时，将事件写入它的二进制日志，而从库在准备就绪时请求它们。主库无需等待从库的ACK回复，直接提交事务并返回客户端。异步复制不确保所有事件都能到达从库，无法保证数据完整性

（2）垂直拆分（分库）

读写分离不能解决写操作频繁带来的性能瓶颈，比如主库写操作占80%，这时需要把写操作拆分到独立的实例上，垂直拆分是按照业务相关度把数据拆分到不同的DB上，这样写操作自然就被拆分开来。

拆分了之后还可以继续做读写分离进一步提升性能，但垂直拆分也带来了问题，原本在一个事务中的数据操作，在拆分之后就无法在同一个事务中完成，这使得我们业务应用需要额外的成本去解决，如通过引入分布式事务或最终一致来解决。

（3）水平拆分（分表）

对数据库做了垂直切分和读写分离可以解决大部分站点的问题，但是在体量巨大的应用中主数据库写操作压力依然会达到极限，这时需要对表进行水平拆分并分布在不同机器上面。

水平拆分最简单的方式就是用哈希算法，一个表只能根据一个字段sharding。下面列举了一些常用的拆分方法：

1.简单hash算法

建立所需要的N个表，表名：user_0 ... user_N-1，通过对ID取余运算直接路由到所在的表：

user_0： 5%5
user_1： 1%5 / 6%5
user_2： 2%5
user_3： 3%5
user_4： 4%5

优点：

查询分片位置的时间复杂度为O(1)，简单有效。

缺点：

动态扩容有局限：当容量不足需要增加分片数量来扩容，哈希值会发生改变，涉及全量数据迁移。
热点数据集中：活跃用户分到了同一个片上，这个实例压力非常大可能会过载。

2.一致性hash算法

在扩容时简单hash算法需要全量数据迁移成本和风险很高，一致性hash算法对该算法进行了优化，通过对固定值2^32-1进行取余保证hash结果不变，再通过范围把环拆分成N份，增加节点时只影响新节点到逆时针第一个节点之间的数据。

整体扩容：如果分片数量不足需要扩容，因为要保证数据分布均匀，所以受影响的节点会占总量的一半。

局部扩容：一致性hash通过在局部增加节点实现灵活扩容，而不必每次都翻倍扩容，可以对热点数据表进行再拆分，只影响新节点到逆时针第一个节点之间的数据，但是需要额外再维护映射表保证其他节点还映射到旧表。

优点：

可以灵活选择局部还是整体扩容，局部扩容可以对某个热点数据的节点再拆分而不影响其他节点。

缺点：

在节点过多的情况下查询效率较低，表映射实现复杂。

3.动态映射

热点数据集中可能是由于某个ID产生的数据过多造成的，通过配置指定到具体的分片上可以过热问题。

优点：可以做局部扩容解决热点数据问题。

缺点：实现比较复杂，每次都需要查询获取对应分片性能比简答hash差，会影响查询效率。

（4）拆分带来的问题

物理拆分带来好处的同时也带来的一些问题：

跨库事务
通过分布式事务或最终一致解决
跨库Join
把Join操作拆分成多次查询并在应用中做聚合
使用搜索引擎做数据聚合和查询
使用CQRS做数据聚合
跨表分页和排序：
由中间件去所有分片聚合数据，再做分页和排序

接下来讲一下CQRS是怎么做的。

CQRS是对应用做读写职责分离，每次写操作都会以类似日志的形式记录在Event Store中，并不是直接修改字段值到期望值，再由Event Bus把事件同步到读服务，读服务对读库数据进行修改，所有查询都会走读服务。在该架构模式中读服务可以把想要的业务数据聚合到读库中，其实就是通过冗余数据的方式避免应用去多库中查询和聚合数据，以空间换时间。