MySQL分库分表的实践示例

2025-08-21 22:50
文章标签 mysql 实践 示例 database 分库 分表

本文主要是介绍MySQL分库分表的实践示例，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

《MySQL分库分表的实践示例》MySQL分库分表适用于数据量大或并发压力高的场景,核心技术包括水平/垂直分片和分库,需应对分布式事务、跨库查询等挑战,通过中间件和解决方案实现,最佳实践为合理策略、备...

一、分库分表的触发条件

在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 技术原理

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

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)China编程 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分库分表内容请搜索编程China编程(www.chinasem.cn)以前的文章或继续浏览下面的相关文章希望大家以后多多支持China编程(www.chinasem.cn)！

这篇关于MySQL分库分表的实践示例的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/1155854。

相关文章

MySQL数据库双机热备的配置方法详解

MySQL数据库双机热备的配置方法详解

《MySQL数据库双机热备的配置方法详解》在企业级应用中,数据库的高可用性和数据的安全性是至关重要的,MySQL作为最流行的开源关系型数据库管理系统之一,提供了多种方式来实现高可用性,其中双机热备（M... 目录1. 环境准备1.1 安装mysql1.2 配置MySQL1.2.1 主服务器配置1.2.2 从
阅读更多...
JDK21对虚拟线程的几种用法实践指南

JDK21对虚拟线程的几种用法实践指南

《JDK21对虚拟线程的几种用法实践指南》虚拟线程是Java中的一种轻量级线程,由JVM管理,特别适合于I/O密集型任务,：本文主要介绍JDK21对虚拟线程的几种用法,文中通过代码介绍的非常详细,... 目录一、参考官方文档二、什么是虚拟线程三、几种用法1、Thread.ofVirtual().start(
阅读更多...
详解SpringBoot+Ehcache使用示例

详解SpringBoot+Ehcache使用示例

《详解SpringBoot+Ehcache使用示例》本文介绍了SpringBoot中配置Ehcache、自定义get/set方式,并实际使用缓存的过程,文中通过示例代码介绍的非常详细,对大家的学习或者... 目录摘要概念内存与磁盘持久化存储：配置灵活性：编码示例引入依赖：配置ehcache.XML文件：配置
阅读更多...
从基础到高级详解Go语言中错误处理的实践指南

从基础到高级详解Go语言中错误处理的实践指南

《从基础到高级详解Go语言中错误处理的实践指南》Go语言采用了一种独特而明确的错误处理哲学,与其他主流编程语言形成鲜明对比,本文将为大家详细介绍Go语言中错误处理详细方法,希望对大家有所帮助... 目录1 Go 错误处理哲学与核心机制1.1 错误接口设计1.2 错误与异常的区别2 错误创建与检查2.1 基础
阅读更多...
深入理解Mysql OnlineDDL的算法

深入理解Mysql OnlineDDL的算法

《深入理解MysqlOnlineDDL的算法》本文主要介绍了讲解MysqlOnlineDDL的算法,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小... 目录一、Online DDL 是什么？二、Online DDL 的三种主要算法2.1COPY（复制法）
阅读更多...
mysql8.0.43使用InnoDB Cluster配置主从复制

mysql8.0.43使用InnoDB Cluster配置主从复制

《mysql8.0.43使用InnoDBCluster配置主从复制》本文主要介绍了mysql8.0.43使用InnoDBCluster配置主从复制,文中通过示例代码介绍的非常详细,对大家的学习或者... 目录1、配置Hosts解析(所有服务器都要执行)2、安装mysql shell(所有服务器都要执行)3、
阅读更多...
k8s中实现mysql主备过程详解

k8s中实现mysql主备过程详解

《k8s中实现mysql主备过程详解》文章讲解了在K8s中使用StatefulSet部署MySQL主备架构,包含NFS安装、storageClass配置、MySQL部署及同步检查步骤,确保主备数据一致... 目录一、k8s中实现mysql主备1.1 环境信息1.2 部署nfs-provisioner1.2.
阅读更多...
Java高效实现PowerPoint转PDF的示例详解

Java高效实现PowerPoint转PDF的示例详解

《Java高效实现PowerPoint转PDF的示例详解》在日常开发或办公场景中,经常需要将PowerPoint演示文稿（PPT/PPTX）转换为PDF,本文将介绍从基础转换到高级设置的多种用法,大家... 目录为什么要将 PowerPoint 转换为 PDF安装 Spire.Presentation fo
阅读更多...
MySQL中VARCHAR和TEXT的区别小结

MySQL中VARCHAR和TEXT的区别小结

《MySQL中VARCHAR和TEXT的区别小结》MySQL中VARCHAR和TEXT用于存储字符串,VARCHAR可变长度存储在行内,适合短文本；TEXT存储在溢出页,适合大文本,下面就来具体的了解... 目录一、VARCHAR 和 TEXT 基本介绍1. VARCHAR2. TEXT二、VARCHAR
阅读更多...
springboot依靠security实现digest认证的实践

springboot依靠security实现digest认证的实践

《springboot依靠security实现digest认证的实践》HTTP摘要认证通过加密参数（如nonce、response）验证身份,避免明文传输,但存在密码存储风险,相比基本认证更安全,却因... 目录概述参数Demopom.XML依赖Digest1Application.JavaMyPasswo
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2