MySQL分库分表的实践示例

2025-08-22 09:42 数据库作者：没事学AI

一、分库分表的触发条件

在mysql数据库的使用过程中，当数据量增长到一定规模时，单库单表的架构会面临性能瓶颈，此时就需要考虑分库分表。以下是常见的触发场景：

1.1 数据量阈值

单表数据量达到1000万-2000万行时，查询性能会明显下降。这是因为MySQL的B+树索引在数据量过大时，树的高度增加，会导致磁盘IO次数增多，查询效率降低。
例如，一个电商平台的订单表，随着业务的增长，每月新增订单量达到数百万，经过一年多的积累，数据量突破1500万，此时简单的查询如“查询用户近三个月的订单”响应时间从原来的几百毫秒增加到几秒，严重影响用户体验。

1.2 并发压力

当数据库的并发连接数过高，超过单库的处理能力时，会出现连接超时、锁等待等问题。

比如一个社交应用的消息表，在高峰期每秒有数千次的读写操作，单库无法承受这样的并发压力，导致消息发送延迟、读取失败等情况。

二、分库分表的核心技术模块

2.1 水平分表

水平分表是将一个表中的数据按照某种规则（如范围、哈希）拆分成多个结构相同的子表，每个子表只包含一部分数据。

2.1.1 技术原理

范围分片：按照数据的某个字段（如时间、ID范围）进行分片。例如，订单表按照月份分片，每个月的数据存放在一个子表中。
哈希分片：对数据的某个字段进行哈希计算，根据哈希结果将数据分配到不同的子表中。比如，根据用户ID进行哈希，将不同用户的订单分配到不同的子表。

2.1.2 案例与代码实现

案例：一个电商平台的订单表orders，包含字段order_id（订单ID）、user_id（用户ID）、order_time（下单时间）等，数据量达到2000万，需要进行水平分表。采用按order_id范围分片，每500万订单ID为一个区间，分为4个子表orders_1、orders_2、orders_3、orders_4。

代码实现：

-- 创建分表
CREATE TABLE orders_1 (
  order_id BIGINT NOT NULL PRIMARY KEY,
  user_id BIGINT NOT NULL,
  order_time DATETIME NOT NULL,
  -- 其他字段
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WHERE order_id BETWEEN 1 AND 5000000;
CREATE TABLE orders_2 (
  order_id BIGINT NOT NULL PRIMARY KEY,
  user_id BIGINT NOT NULL,
  order_time DATETIME NOT NULL,
  -- 其他字段
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WHERE orderpython_id BETWEEN 5000001 AND 10000000;
CREATE TABLE orders_3 (
  order_id BIGINT NOT NULL PRIMARY KEY,
  user_id BIGINT NOT NULL,
  order_time DATETIME NOT NULL,
  -- 其他字段
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WHERE order_id BETWEEN 10000001 AND 15000000;
CREATE TABLE orders_4 (
  order_id BIGINT NOT NULL PRIMARY KEY,
  user_id BIGINT NOT NULL,
  order_pythontime DATETIME NOT NULL,
  -- 其他字段
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WHERE order_id BETWEEN 15000001 AND 20000000;
-- 创建视图，方便查询
CREATE VIEW orders AS
SELECT * FROM orders_1
UNION ALL
SELECT * FROM orders_2
UNION ALL
SELECT * FROM orders_3
UNION ALL
SELECT * FROM orders_4;

2.2 垂直分表

垂直分表是将一个表中字段较多的表，按照字段的热点程度、访问频率等，拆分成多个包含部分字段的子表。

2.2.1 技术原理

将经常被查询的热点字段放在一个子表中，将不常被查询的冷字段放在另一个子表中。这样可以减少每次查询时读取的数据量，提高查询效率。
例如，用户表中，用户的基本信息（如用户名、手机号）经常被查询，而用户的详细信息（如家庭住址、个人简介）不常被查询，可以将其拆分成两个编程客栈子表。

2.2.2 案例与代码实现

案例：用户表user包含字段user_id、username、phone、address、introduction等，其中username、phone经常被查询，address、introduction不常被查询，进行垂直分表。

代码实现：

-- 创建用户基本信息表（热点字段）
CREATE TABLE user_base (
  user_id BIGINT NOT NULL PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  phone VARCHAR(20)编程客栈 NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 创建用户详细信息表（冷字段）
CREATE TABLE user_detail (
  user_id BIGINT NOT NULL PRIMARY KEY,
  address VARCHAR(200),
  introduction TEXT,
  FOREIGN KEY (user_id) REFERENCES user_base(user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2.3 分库

分库是将多个表按照一定的规则拆分到不同的数据库中，以降低单库的压力。

2.3.1 技术原理

可以按照业务模块进行分库，将不同业务模块的表放在不同的数据库中。例如，电商平台可以将用户相关的表放在用户库，订单相关的表放在订单库。
也可以结合分表进行分库，将分表后的子表分布到不同的数据库中。

2.3.2 案例与代码实现

案例：一个大型电商平台，包含用户模块、商品模块、订单模块，将这三个模块的表分别放在user_db、product_db、order_db三个数据库中。

代码实现：

在不同的数据库实例中分别创建对应的表，这里以用户库和订单库为例：

-- 在user_db数据库中创建用户相关表
USE user_db;
CREATE TABLE user_base (
  user_id BIGINT NOT NULL PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  phone VARCHAR(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 在order_db数据库中创建订单相关表
USE order_db;
CREATE TABLE orders_1 (
  order_id BIGINT NOT NULL PRIMARY KEY,
  user_id BIGINT NOT NULL,
  order_time DATETIME NOT NULL
  -- 其他字段
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

三、分库分表带来的新问题

3.1 分布式事务

分库分表后，一个业务操作可能涉及多个数据库或多个表，此时保证事务的一致性变得复杂。

3.1.1 问题说明

在单库单表中，MySQL的ACID特性可以保证事务的一致性。但在分布式环境下，多个数据库之间无法直接使用本地事务，可能出现部分操作成功、部分操作失败的情况。

3.1.2 案例

用户下单操作，需要在订单库中创建订单记录，同时在库存库中减少商品库存。如果订单创建成功，但库存减少失败，就会出现数据不一致。

3.2 跨库查询

分库分表后，查询可能需要涉及多个数据库或多个表，增加了查询的复杂度。

3.2.1 问题说明

例如，查询某个用户在多个月份的订单，由于订单表按月份分表且可能分布在不同的库中，需要同时查询多个库和表，然后合并结果。

3.2.2 案例

查询用户user_id=100在2023年1月和2月的订单，需要分别查询order_db1中的orders_202301表和order_DB2中的orders_202302表，然后将结果合并。

3.3 数据迁移与扩容

随着业务的发展，可能需要对分库分表的方案进行调整，如增加分表数量、调整分片规则等，这会涉及到数据的迁移和扩容。

3.3.1 问题说明

数据迁移过程中需要保证数据的一致性和完整性，同时要尽量减少对业务的影响。扩容时需要考虑新的分片规则如何与原有规则兼容。

3.3.2 案例

原来订单表按照order_id范围分表，每500万一个表，现在由于业务增长，需要将每个分表的范围调整为250万，需要将原有的orders_1表（1-500万）拆分成orders_1（1-250万）和orders_5（251-500万），并迁移数据。

四、分库分表的解决方案

4.1 中间件方案

使用专门的分库分表中间件，如Sharding-JDBC、MyCat等，这些中间件可以帮助开发者透明地实现分库分表，减少手动处理的复杂度。

4.1.1 Sharding-JDBC

原理：Sharding-JDBC作为JDBC的增强版，通过对JDBC接口的封装，实现了分库分表的功能。它可以解析SQL语句，根据分片规则路由到对应的数据库和表，并将结果合并返回。
案例：使用Sharding-JDBC实现订单表的水平分表，按照order_id取模分片。

代码实现（Spring Boot整合Sharding-JDBC）：

spring:
  shardingsphere:
    datasource:
      names: db0,db1
      db0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/order_db0
        username: root
        password: root
      db1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/order_db1
        username: root
        password: root
    rules:
      sharding:
        tables:
          orders:
            actual-data-nodes: db${0..1}.orders_${0..1}
            database-strategy:
              standard:
                sharding-column: order_id
                sharding-algorithm-name: order_db_inline
            table-strategy:
              standard:
                sharding-column: order_id
                sharding-algorithm-name: order_table_inline
        sharding-algorithms:
          order_db_inline:
            type: INLINE
            props:
              algorithm-expression: db${order_id % 2}
          order_table_inline:
            type: INLINE
            props:
              algorithm-expression: orders_${order_id % 2}
    props:
      sql-show: true

4.2 分布式事务解决方案

4.2.1 两阶段提交（2PC）

原理：分为准备阶段和提交阶段。准备阶段，协调者向所有参与者发送准备请求，参与者执行事务操作但不提交，并反馈是否可以提交；提交阶段，如果所有参与者都反馈可以提交，协调者发送提交请求，否则发送回滚请求。
缺点：性能较差，协调者故障可能导致参与者处于阻塞状态。

4.2.2 最终一致性方案（如TCC、SAGA）

TCC（Try-Confirm-Cancel）：将一个事务拆分为Try、Confirm、Cancel三个操作。Try阶段尝试执行事务，预留资源；Confirm阶段确认执行事务；Cancel阶段取消事务，释放资源。
SAGA：将一个长事务拆分为多个短事务，每个短事务都有对应的补偿事务，当某个短事务失败时，执行前面所有成功的短事务的补偿事务，以保证数据的最终一致性。

TCC案例代码（伪代码）：

// 订单服务
public interface OrderTCCService {
    // Try阶段：创建订单，预留库存
    boolean tryCreateOrder(OrderDTO orderDTO);
    // Confirm阶段：确认创建订单
    boolean confirmCreateOrder(OrderDTO orderDTO);
    // Cancel阶段：取消创建订单，释放库存
    booleanjs cancelCreateOrder(OrderDTO orderDTO);
}
// 库存服务
public interface InventoryTCCService {
    // Try阶段：扣减库存预留
    boolean tryDeductInventory(InventoryDTO inventoryDTO);
    // Confirm阶段：确认扣减库存
    boolean confirmDeductInventory(InventoryDTO inventoryDTO);
    // Cancel阶段：取消扣减库存，恢复库存
    boolean cancelDeductInventory(InventoryDTO inventoryDTO);
}

五、分库分表的最佳实践

5.1 合理选择分片策略

根据业务特点选择合适的分片策略，如订单表可以按照时间范围分片，方便查询历史数据；用户表可以按照用户ID哈希分片，使数据分布均匀。
避免过度分片，分片数量过多会增加管理复杂度和跨库查询的开销。

5.2 做好数据备份与恢复

分库分表后的数据分布在多个库和表中，需要制定完善的数据备份策略，定期备份数据，并确保备份数据可以正常恢复。

5.3 监控与调优

对分库分表后的数据库进行实时监控，包括各库表的性能指标（如查询响应时间、吞吐量、连接数等）、数据增长情况等。
根据监控结果进行调优，如调整分片规则、优化SQL语句、增加硬件资源等。

5.4 考虑未来扩展性

在设计分库分表方案时，要考虑未来业务的增长，预留一定的扩展空间，使方案能够方便地进行扩容和调整。例如，采用可扩展的分片规则，当数据量增长到一定程度时，可以方便地增加新的分库分表。

到此这篇关于MySQL分库分表的实践与挑战的文章就介绍到这了,更多相关mysql分库分表内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！

继续阅读：mysql分库分表实践

目录

一、分库分表的触发条件

1.1 数据量阈值

1.2 并发压力

二、分库分表的核心技术模块

2.1 水平分表

2.1.1 技术原理

2.1.2 案例与代码实现

2.2 垂直分表

2.2.1 技术原理

2.2.2 案例与代码实现

2.3 分库

2.3.1 技术原理

2.3.2 案例与代码实现

三、分库分表带来的新问题

3.1 分布式事务

3.1.1 问题说明

3.1.2 案例

3.2 跨库查询

3.2.1 问题说明

3.2.2 案例

3.3 数据迁移与扩容

3.3.1 问题说明

3.3.2 案例

四、分库分表的解决方案

4.1 中间件方案

4.1.1 Sharding-JDBC

4.2 分布式事务解决方案

4.2.1 两阶段提交（2PC）

4.2.2 最终一致性方案（如TCC、SAGA）

五、分库分表的最佳实践

5.1 合理选择分片策略

5.2 做好数据备份与恢复

5.3 监控与调优

5.4 考虑未来扩展性

更多精彩内容

精彩评论

最新数据库

Redis的哨兵模式工作流程及原理详解

SQL Server查询所有表数据量的代码实例

MySQL分库分表的实践示例

MySQL中On duplicate key update的实现示例

Python与MySQL实现数据库实时同步的详细步骤

数据库排行榜

Hadoop Key Management Server (KMS)配置及测试

spark报错ERROR ObjectStore: Version information found in metastore differs 2.1.0 from expected schema version 1.2.0. Schema verififcation is disabled hive.metastore.schema.verification so setting version.

Navicat连接Oracle数据库的详细步骤与注意事项

解决Navicat远程连接MySQL出现 10060 unknow error的方法

redis-cluster集群调优之cluster-require-full-coverage参数