【进阶篇】MySQL分库分表详解

这篇具有很好参考价值的文章主要介绍了【进阶篇】MySQL分库分表详解。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

0. 前言

假设有一个电商网站，随着用户量和订单量的增加，单一数据库难以承载如此庞大的数据量，查询速度也逐渐降低，这时就需要进行分库分表。

为什么分库

我们知道数据库连接是有限的。在高并发的场景下，大量请求访问数据库，MySQL单机是扛不住的！当前非常火的微服务架构出现，就是为了应对高并发。它把订单、用户、商品等不同模块，拆分成多个应用，并且把单个数据库也拆分成多个不同功能模块的数据库（订单库、用户库、商品库），以分担读写压力。

当单个数据库无法处理高负载和大规模数据时，可以通过分库将数据分布在多个数据库中，以实现水平扩展和负载均衡。每个数据库实例负责处理部分数据和请求，提高整体系统的性能和吞吐量。
某些情况下，需要对数据进行隔离，例如多租户系统，每个租户的数据需要分开存储，以保证数据的安全性和隐私性。通过分库，可以将不同租户的数据存储在不同的数据库中，避免数据混淆和冲突。
当应用涉及多个地理位置或数据中心时，可以将数据分布在不同的数据库中，使数据就近存储，提高数据访问速度和降低网络延迟。
对于大型系统，数据的管理和维护可能会变得复杂和困难。通过分库，可以将数据按照业务功能或模块进行划分，简化数据管理和维护的操作。

为什么分表

关于MySQL 2000万条性能瓶颈的传言

作为工作3-5年以上的开发同学，应该都听说过前辈或者网上帖子说MySQL单表性能瓶颈的说法，即当单表数据量超过2000万行时，性能会明显下降。其实这个传言一直存在，并且一直传承者，我觉得也不是什么坏事，至少在性能优化方面有个底。经过我看到的野史是。关于MySQL单表性能瓶颈的说法，即当单表数据量超过2000万行时，性能会明显下降。野史说，这个说法最早据说起源于百度，后来被百度的工程师带到了其他公司，逐渐在业界流传开来。

我很早之前，也曾好奇做过一个测试验证。其实在8核、16G、机械硬盘、单表32个字段情况下。数据库表数据达到1000多万条，没有经过索引优化的情况下。时候性能大概在查询一次的时间5-8秒不等。但是经过索引优化后，可以降低到3秒内。其实这也算是有性能瓶颈，达不到2000万条。

阿里巴巴给出的最佳实践

阿里巴巴的《Java开发手册》提出了单表行数超过500万行或者单表容量超过2GB时才推荐进行分库分表。然而，这个数值并不是固定的，它与MySQL的配置和机器的硬件有关。当单表数据库达到某个上限时，内存无法存储其索引，导致之后的SQL查询产生磁盘IO，从而性能下降。增加硬件配置可能会提升性能。

阿里巴巴的《Java开发手册》补充到，如果预计三年后的数据量不会达到这个级别，就不要在创建表时就进行分库分表。根据自身机器情况综合评估，可以将500万行作为一个统一的标准。根据实际测试，在InnoDB引擎下，MySQL单表在800万数据量下的查询性能可能会很差。使用MyISAM引擎查询速度可能更快，但是对于数据完整性和事务支持不如InnoDB。因此，根据实际需求选择合适的优化方案。

1. 分库分表

1.1. 垂直分库分表

电商网站有用户模块和订单模块，可以将用户表和订单表拆分到不同的数据库中。例如，原本在同一个数据库中的用户表（包含用户基础信息和用户扩展信息）和订单表，可以拆分为两个数据库，一个数据库存储用户的基础信息，另一个数据库存储用户的扩展信息和订单信息。

最常见的博客文章都是以电商系统作为案例，因为他是最具备代表性，电商网站的用户模块和订单模块是可以拆分到不同的数据库中的。这种拆分方式可以提高系统的可扩展性和性能。

1.1.1. 垂直分库

假设有一个电商网站，它有一个数据库，其中包含两个表：用户表（User）和订单表（Order）。用户表包含用户的基础信息（如用户名、密码等）和用户的扩展信息（如用户的购物偏好、历史订单等）。订单表包含订单的详细信息（如订单号、购买的商品、数量、价格等）。

【进阶篇】MySQL分库分表详解,MySQL从入门到精通,MySQL,数据库,大数据
当将用户、订单和商品拆分到不同的数据库中时

用户库 (User Database)

用户信息表 (User Information Table)
认证信息表 (Authentication Information Table)
权限信息表 (Authorization Information Table)
用户统计信息表 (User Statistics Information Table)

订单库 (Order Database)

订单信息表 (Order Information Table)
订单状态表 (Order Status Table)
支付信息表 (Payment Information Table)

商品库 (Product Database)

商品信息表 (Product Information Table)
库存信息表 (Inventory Information Table)
价格信息表 (Price Information Table)

每个库都包含了与其相关的表和数据。将用户、订单和商品分别存储在不同的数据库中，可以实现以下优势：

由于用户、订单和商品在不同的库中，每个库可以根据其特定需求进行优化。例如，用户库可以针对用户查询和认证进行优化，订单库可以针对订单处理和状态跟踪进行优化，商品库可以针对商品检索和库存管理进行优化。这样可以提高整个系统的性能。

每个库都可以独立进行水平扩展。当用户、订单或商品的数量增加时，可以通过增加数据库实例或分片来扩展相应的库，以满足高并发和大规模数据的需求。

将用户、订单和商品分开存储在不同的库中，可以实现数据的隔离和安全性。例如，访问订单库的权限可以与访问用户库或商品库的权限分开控制，从而提高系统的安全性。

1.1.2. 垂直分表

按照目前电商行业的用户画像。用户画像就是对这些数据的分析而得到的用户基本属性、购买能力、行为特征、社交网络、心理特征和兴趣爱好等方面的标签模型。如果我们基于此来设计数据库表。那就太复杂了，我们做个简单的来实现一下示例，让大家基本了解就OK。具体拆分设计是很复杂的一个过程。
【进阶篇】MySQL分库分表详解,MySQL从入门到精通,MySQL,数据库,大数据

ok，那么我们来设计一个简单的电商系统的用户表。一般电商系统的简单用户表如下。我这边做个示例，包含了用户的基本信息以及一些与用户相关的属性。

class User {
  - user_id: int       // 用户ID
  - username: string   // 用户名
  - password: string   // 密码
  - email: string      // 电子邮件
  - phone_number: string   // 手机号码
  - full_name: string  // 姓名
  - gender: string     // 性别
  - date_of_birth: date    // 出生日期
  - registration_date: date    // 注册日期
  - last_login_date: date      // 最后登录日期
  - address: string    // 地址
  - postal_code: string   // 邮编
  - country: string    // 国家
  - state: string      // 省份/州
  - city: string       // 城市
  - avatar: string     // 头像
  - bio: string        // 个人简介
  - is_active: boolean     // 是否激活
  - is_admin: boolean     // 是否管理员
  - balance: decimal      // 余额
  - email_verification_status: boolean    // 邮箱验证状态
  - phone_verification_status: boolean    // 手机验证状态
  - referrer_id: int     // 推荐人ID
  - registration_ip: string    // 注册IP地址
  - last_login_ip: string     // 最后登录IP地址
  - order_count: int    // 订单数量
  - wishlist: string    // 收藏夹
  - user_level: string     // 用户级别/角色
}

ok，让我们思考一下如何进行垂直拆分。

我们先来说垂直拆分的原则
垂直拆分是将一个大型的表按照功能或主题进行拆分成多个较小的表的过程。
在用户表的情况下，可能会考虑以下几个方面进行拆分：

身份验证和授权表 (Authentication and Authorization Table)：
这个表包含与用户身份验证和授权相关的信息，比如用户名、密码等。这个表可以用于用户登录和权限验证的过程。拆分出这个表可以提高安全性和性能，因为敏感信息可以单独管理和存储。
个人资料表 (Profile Table)
这个表包含用户的个人资料信息，比如电子邮件、注册日期等。这个表可以用于显示用户个人资料或进行用户统计分析。拆分出这个表可以减少主用户表的数据量，提高查询性能。
活动日志表 (Activity Log Table)
这个表用于记录用户的活动日志，比如最后登录日期、操作记录等。这个表可以用于跟踪用户的活动和生成日志报告。拆分出这个表可以避免在主用户表上频繁写入操作，减少对主表的负载。

按照上面的拆分原则我们拆分后的表结构

垂直拆分后的身份验证表、个人资料表和用户统计表。每个表都是一个单独的类，包含了拆分后的字段。
【进阶篇】MySQL分库分表详解,MySQL从入门到精通,MySQL,数据库,大数据

身份验证表 (Authentication Table)

@startuml

class t_user_authentication {
  - user_id: int       // 用户ID
  - username: string   // 用户名
  - password: string   // 密码
  - is_active: boolean     // 是否激活
  - is_admin: boolean     // 是否管理员
  - email_verification_status: boolean    // 邮箱验证状态
  - phone_verification_status: boolean    // 手机验证状态
}

@enduml

个人资料表 (Profile Table)

@startuml

class  t_user_Profile {
  - user_id: int       // 用户ID
  - email: string      // 电子邮件
  - phone_number: string   // 手机号码
  - full_name: string  // 姓名
  - gender: string     // 性别
  - date_of_birth: date    // 出生日期
  - address: string    // 地址
  - postal_code: string   // 邮编
  - country: string    // 国家
  - state: string      // 省份/州
  - city: string       // 城市
  - avatar: string     // 头像
  - bio: string        // 个人简介
  - registration_date: date    // 注册日期
  - last_login_date: date      // 最后登录日期
  - registration_ip: string    // 注册IP地址
  - last_login_ip: string     // 最后登录IP地址
}

@enduml

用户统计表 (User Statistics Table)


class  t_user_Statistics {
  - user_id: int       // 用户ID
  - order_count: int    // 订单数量
  - wishlist: string    // 收藏夹
  - user_level: string     // 用户级别/角色
  - balance: decimal      // 余额
  - referrer_id: int     // 推荐人ID
}

2. 水平分库分表

2.1. 水平分库

在电商系统中，标准的数据库可能无法处理各种高负载和大数据量的情况，因此有必要进行数据库分库或分表。这也可以帮助减少访问量和不必要的负载。

假设我们有一个电商平台，它有上千万的用户和订单。如果我们把全部的用户数据和订单数据都放在一个数据库中，那么随着时间和系统的扩大，这个数据库将可能面临许多问题，例如查询缓慢，数据存储不足等。因此，我们可能需要对数据库进行水平分库。

以用户ID作为分库依据，可以把用户数据分成多个数据库。比如，可以让用户ID 1-50000 的用户数据存储在数据库 1，让用户 ID 50001-100000 的用户数据存储在数据库2，以此类推。这样，查询和其他操作就可以在不同的数据库上并行进行，从而提高系统效率和可扩展性。

然后，对于订单数据，也可以通过类似的方式进行分库。例如，可以通过订单ID或订单日期进行分库。比如，订单ID 1-10000 的订单数据存储在数据库 1，订单ID 10001-20000的订单数据存储在数据库 2，等等。

这样进行水平分库后，电商平台就可以大大减少数据库的负载，并提升系统性能和数据查询效率。

【进阶篇】MySQL分库分表详解,MySQL从入门到精通,MySQL,数据库,大数据

2.2. 水平拆分订单表

如果你希望将订单表拆分为两个表，并使用取模的方式进行分片，可以按照以下示例进行操作：

假设我们有一个名为"orders"的订单表，包含以下字段：

order_id：订单ID
customer_id：客户ID
order_date：订单日期
total_amount：订单总额
shipping_address：配送地址
…

我们可以使用客户ID进行取模运算，将客户ID除以2，然后根据余数的不同将订单数据分配到两个订单表中。

orders_1：存储客户ID取模结果为0的订单数据。
orders_2：存储客户ID取模结果为1的订单数据。

当系统接收到一个新的订单时，将该订单的客户ID进行取模运算。如果取模结果为0，将订单插入到orders_1表中；如果取模结果为1，将订单插入到orders_2表中。

在查询订单时，系统需要根据客户ID的取模结果确定需要在哪个订单表中执行查询操作。例如，如果要查询某个客户ID为123的订单，将该客户ID进行取模运算，如果取模结果为0，则在orders_1表中执行查询；如果取模结果为1，则在orders_2表中执行查询。
【进阶篇】MySQL分库分表详解,MySQL从入门到精通,MySQL,数据库,大数据

通过将订单表按照客户ID的取模结果分配到不同的表中，可以实现简单的水平拆分，并将订单数据均匀地分布到两个表中，以提高查询性能和可扩展性。

需要注意的是，使用取模进行分片可能导致数据不均匀分布的情况，因为某些客户ID可能会倾向于产生更多的订单，导致某个表的数据量较大。在实际应用中，需要根据实际情况进行调整和优化，以确保数据分布均匀，并满足系统性能和扩展性的需求。

这是一个简单的订单表水平拆分的示例，具体的实施方式可能因系统设计和需求而有所不同。

2. 理解过程及实现方案

记住一句话，不是所有系统一上来就搞分库分表，包括淘宝，京东。尤其这种超前设计，对小公司来说就是累赘，甚至隐患。不仅人才成本，资源成本，运维成本。甚至学习成本都不容小觑。所以基本上都是不断衍生到分库分表，才算是中小公司正确的技术路线和最优实践。

我们以一个场景：例如有一个电子商务网站，随着业务的发展，用户数量、商品数量和交易数量都在快速增长。原来单一的数据库已经无法满足需求，查询速度慢，系统负载高，甚至出现宕机情况。这样的情况下，为了提高系统的性能和稳定性，就需要进行数据库的分库分表。

2.1. 问题讨论

分库分表的目的是为了解决单个数据库无法承受大量数据和高并发的问题。但是分库分表也会带来一些问题，例如数据一致性问题、分布式事务问题、跨库跨表查询问题、数据迁移问题等。

2.2. 衍生出分库分表策略

垂直分库和分表是常见的数据库拆分策略，用于解决单个数据库的性能瓶颈和扩展性问题。以下是垂直分库和分表的常用策略：

2.2.1. 垂直分库（Vertical Sharding）

功能分库：按照功能模块将数据分散到不同的数据库中。例如，将用户信息存储在一个数据库中，将订单信息存储在另一个数据库中，以此类推。每个数据库专注于处理特定功能的数据，从而提高性能和可扩展性。
垂直分库可以根据业务需求和数据关联性进行划分，将关联性不强的数据存储在不同的库中，减少单个数据库的负载。

2.2.2. 垂直分表（Vertical Partitioning）

列分表：按照列的关联性将表中的字段分散到不同的表中。将经常使用的字段和较少使用的字段分开存储，以减少查询时的数据量和提高查询性能。
实体分表：按照数据实体的关联性将表中的行分散到不同的表中。将具有强关联性的数据行分散到不同的表中，以减少数据冗余和提高查询性能。

2.2.3. 垂直分库和分表的策略我们如何选型

垂直分库和分表的选择取决于系统的需求和数据特征。

垂直分库适合处理大型系统中的功能模块，每个模块有不同的数据访问模式和负载。例如，【用户模块、订单模块、产品模块等】。
垂直分表适合处理表中的大量列或行，其中某些列或行的访问频率较高，而其他部分的访问频率较低。例如，【我们拆分的用户表】。

2.3 水平分库分表

水平分库和分表是数据库拆分的另一种常见策略，用于应对大规模数据和高并发负载的情况。

2.3.1. 水平分库（Horizontal Sharding）

按照数据行进行分库：将数据行按照某种规则（如哈希函数或范围）划分到不同的数据库中。每个数据库只负责存储和处理一部分数据行。例如，可以根据用户ID的哈希值将用户数据分散到不同的数据库中。
水平分库可以实现数据的并行处理和扩展性，每个数据库都可以独立地处理自己的数据，从而提高系统的吞吐量和性能。

2.3.2. 水平分表（Horizontal Partitioning）

按照数据行进行分表：将数据表按照某种规则（如哈希函数或范围）划分为多个表。每个表只包含部分数据行，例如根据订单ID的范围将订单数据划分为多个表。
水平分表可以减少单个表的数据量，提高查询性能和数据访问的并行度。同时，它也提供了更好的数据管理和维护灵活性。

在水平分库和分表的策略中，需要考虑以下几个方面：

数据划分规则：确定划分数据的规则，如基于哈希值、范围、时间等。该规则应考虑数据的均衡性，避免数据倾斜和热点问题。

数据迁移和一致性：在分库和分表后，需要考虑数据的迁移和一致性问题。迁移数据时，可以使用批量导入、ETL工具或分布式数据同步技术来保证数据的一致性。

跨库和跨表查询：当需要进行跨库和跨表查询时，需要设计合适的查询路由和分布式查询机制。这包括在应用层实现查询路由逻辑，或使用分布式查询工具和中间件来处理跨库和跨表查询。

事务处理：在水平分库和分表的环境中，事务处理可能涉及到多个数据库或表。需要考虑分布式事务管理技术，如两阶段提交（2PC）、补偿事务（Saga）等，以确保事务的一致性和可靠性。

2.3.3. 常见的水平分表策略

基于范围的分表策略: 在这种策略中，数据根据其在特定范围内的值进行分割。例如，根据日期进行分割，例如，2019年的数据、2020年的数据和2021年的数据各自存储在不同的表中。
基于列表的分表策略: 这是用于离散值的一种策略，它将特定值的行放入同一张表。例如，根据顾客的地区进行分割，美国的顾客、英国的顾客和中国的顾客各自存储在不同的表中。
基于哈希的分表策略: 数据根据哈希函数的值进行分割。该函数接受行数据（通常是主键值）作为输入，并返回一个哈希值，用于确定将行放入哪张表。目的是为了随机化数据，使其均匀分布在多张表中。

当在实践中实现水平分表时，应该考虑以下几个因素：

在总体应用未知查询的情况下，哈希分表可能是最好的选择，因为它可以保证在多张表中都有相等的数据。

如果查询主要基于特定的列（例如“日期”或者“地区”），那么范围分表或者列表分表可能是更好的选择。

水平分表可能会引入增加的复杂性，特别是处理事务和维护引用完整性时，因此需要谨慎操作。

通过分析工作负载并评估各种策略对查询性能的影响，可以决定哪种策略是最合适的。

借助成熟组件使用

常见框架：

MyCat：一个开源的分库分表中间件，支持自定义分片规则，可以实现读写分离、事务和SQL的完全透明。
Sharding-JDBC：一款轻量级的Java框架，提供了强大的分库分表、读写分离、分布式事务和分布式序列等功能。
Shardingsphere：包含了Sharding-JDBC、Sharding-Proxy和Sharding-Sidecar这三款独立的产品，可以满足不同场景下的数据分片需求。
分库分表的复杂问题：

分库分表阶段完成后面临的问题

当技术演进到我们已经解决了分库分表基本问题，解决了并发问题，解决性能问题，接下来我们基本上面临如下几个问题。关于这些问题又需要我们继续去学习和研究尝试更多方案。此处我们不做展开。

1. 异地多活问题

在数据库地理分布、灾备等问题上，如果是多库多表的情况，数据同步和备份的复杂度将会增加。

2. 数据迁移问题

.在分库分表后，如何对旧数据进行迁移以及如何在迁移过程中保证业务的正常运行是个大问题。

3. 分布式事务问题

在传统的单体数据库中，事务是保证数据一致性的重要手段。但在分库分表的环境下，原有的事务机制不能再使用，需要引入新的分布式事务解决方案。

4. join查询的问题

在分库分表后，原本在一个库或一个表中可以做的join查询就变得困难，需要通过应用层去做关联和组合。

分库分表的策略实现示例

函数法（哈希、取模等）：根据某个字段（比如用户ID）的函数结果进行分库分表。
范围法（枚举、区间）：比如根据日期范围，订单ID范围等进行分库分表。
一致性哈希：在新增或减少数据库节点的时候，能够最小化数据的迁移。

我们用Java写一些伪代码方便大家理解。
假设我们有多种根据某个值（如用户ID或订单ID）来确定应将数据存储在哪个数据库的方式。我们分别用函数法，范围法和一致性哈希法三种方法模拟一下。

函数法：我们可以将用户ID除以数据库数量的余数作为数据库的索引。

public String getDatabase(int userId) {
    int dbCount = 2; // 假设我们有2个数据库
    int dbIndex = userId % dbCount;
    return "db" + dbIndex;
}

范围法：我们可以使用订单ID作为范围进行划分，比如订单ID小于10000的存储在一个数据库，大于等于10000的存储在另一个数据库。

public String getDatabase(int orderId) {
    if (orderId < 10000) {
        return "db0";
    } else {
        return "db1";
    }
}

一致性哈希：这种方法需要使用到特殊的数据结构，例如TreeMap。首先，我们将每个数据库（称为节点）添加到一个TreeMap中，然后计算键（如用户ID）的哈希值，并在TreeMap中找到该哈希值对应的数据库。如果没有找到，则返回哈希值最接近的数据库。

public class ConsistentHashing {
    private TreeMap<Integer, String> nodes = new TreeMap<>();

    public void addNode(String nodeName) {
        int hash = nodeName.hashCode();
        nodes.put(hash, nodeName);
    }

    public void removeNode(String nodeName) {
        int hash = nodeName.hashCode();
        nodes.remove(hash);
    }

    public String getDatabase(String key) {
        if (nodes.isEmpty()) {
            return null;
        }

        int hash = key.hashCode();
        if (!nodes.containsKey(hash)) {
            SortedMap<Integer, String> tailMap = nodes.tailMap(hash);
            hash = tailMap.isEmpty() ? nodes.firstKey() : tailMap.firstKey();
        }
        return nodes.get(hash);
    }
}

其实真实场景中我们大多都使用分库分表组件或者中间件，最著名的和大家最常用的sharding-jdbc就是解决这类场景的一个优秀的，轻量级的分库分表组件，虽然有很多bug或者不足，但是足以解决我们90%的问题了。后面有一个章节我会写一个详细教程关于sharding-jdbc使用详解。除了它还有一些国产的中间件也不错。如下

TDDL：阿里巴巴开源的分库分表中间件，支持复杂的分库分表策略。
Oceanus：网易云数据库的分库分表解决方案，支持自动分库分表、读写分离、分布式事务等功能。

2. 参考文档

“MySQL分库分表方案” - InfoQ。介绍了 MySQL 的分库分表方案，并对实施这种方案的步骤进行了详细的讨论。链接：https://www.infoq.cn/article/solution-of-mysql-sub-database-and-sub-table
“MySQL 分库分表实践” - 阿里云社区的一篇文章也可以参考学习。介绍了 MySQL 分库分表的实践和经验。链接：https://developer.aliyun.com/article/776687
“分库分表架构设计” 详细介绍了分库分表架构的设计和实施。链接：https://www.jianshu.com/p/d7f3d3808f25
ShardingSphere 是 Apache 的一个开源项目包包含了我上面说的sharding-jdbc，提供了分库分表的解决方案。链接：https://shardingsphere.apache.org/document/current/cn/overview/文章来源地址https://www.toymoban.com/news/detail-691998.html

到了这里，关于【进阶篇】MySQL分库分表详解的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！