一文搞懂 MySQL 高读写负载的系统级优化方案

散一世繁华，颠半世琉璃

976人浏览 · 2025-06-08 21:15:07

散一世繁华，颠半世琉璃 · 2025-06-08 21:15:07 发布

随着业务规模的增长，系统对数据库性能提出了更高要求。MySQL虽然功能强大，但在面对 海量数据和高并发请求 时，也不可避免地遇到性能瓶颈，尤其体现在读写负载的持续上升。

本文将详细介绍应对 MySQL读写负载过重 的系统性解决方案：

什么是读写负载？它们如何影响系统性能？
面对读负载过重，有哪些具体策略？
写入压力大时如何通过分库分表解决，Sharding-JDBC怎么落地？
分表之后的分页查询要怎么处理，避免“性能陷阱”？

1. 数据库读写负载过重的背景与挑战

1.1 读负载过重

读操作是大多数系统的主要负载类型，尤其是：

电商系统的商品浏览；
金融系统的交易查询；
日志系统的检索和分析。

读负载过重常导致：

单表数据量膨胀，查询效率下降；
索引命中率降低；
热点数据频繁被访问，锁竞争加剧；
查询延迟升高，甚至拖垮主库。

1.2 写负载过重

随着用户量的提升和事件频度加快，写入量不断增长，表现为：

大量并发写入导致锁争抢；
自增主键写入热点明显；
单表容量逼近上限（如 MySQL InnoDB 建议单表不超过 2000W~5000W 行）。

2. 应对读负载过重的系统级方案

2.1 垂直拆分与读写分离（实现细节）

读写分离原理：

MySQL 主从架构支持 binlog 复制，从库只承担读操作，主库专注写入。

中间件代理（推荐）

MyCat、Cobar 等中间件自动管理主从路由。
业务无感知，但增加一层代理。

SpringBoot 多数据源配置

spring:
  datasource:
    write:
      url: jdbc:mysql://write-db:3306/app
      username: root
      password: xxx
    read:
      url: jdbc:mysql://read-db:3306/app
      username: root
      password: xxx

@Configuration
public class DataSourceConfig {
    @Primary
    @Bean(name = "writeDataSource")
    public DataSource writeDataSource() { ... }

    @Bean(name = "readDataSource")
    public DataSource readDataSource() { ... }

    @Bean
    public AbstractRoutingDataSource routingDataSource() {
        DynamicRoutingDataSource routing = new DynamicRoutingDataSource();
        routing.setTargetDataSources(Map.of("write", writeDataSource(), "read", readDataSource()));
        routing.setDefaultTargetDataSource(writeDataSource());
        return routing;
    }
}

2.2 MySQL 分区（Partition）

MySQL 原生支持表级分区，提高查询效率。

典型用法：按时间字段 RANGE 分区

CREATE TABLE order_log (
    id BIGINT,
    order_time DATE,
    ...
) PARTITION BY RANGE (YEAR(order_time)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);

特点：

优点：减少全表扫描、提升查询速度；
限制：
- 不支持 JOIN 与子查询；
- 分区数上限（默认1024）；
- 跨分区查询性能下降。

2.3 引入 Elasticsearch（ES）

适用于以下查询类型：

模糊搜索；
全文检索；
复杂聚合（terms、avg、date_histogram 等）。

数据同步方式：

Canal：监听 binlog 实时同步；
Debezium + Kafka：结合 CDC 机制进行异步同步。

# Canal 配置 MySQL 数据源
canal.instance.master.address=127.0.0.1:3306
canal.instance.dbUsername=...

查询示例：

GET /orders/_search
{
  "query": {
    "match": {
      "product_name": "苹果"
    }
  }
}

2.4 引入数据仓库（OLAP）

适合离线分析场景，例如：

日志分析；
报表统计；
BI 查询。

常见技术栈：

ClickHouse：适用于大宽表；
StarRocks：支持实时数据入仓；
Flink + Kafka + Hive：实时/批量同步数据。

2.5 Redis 缓存

热点缓存示例：

String key = "product:" + productId;
String product = redis.get(key);
if (product == null) {
    product = mysql.query(...);
    redis.setex(key, 3600, product);  // 设置过期时间
}

常见优化手段：

缓存预热：系统启动加载热点数据；
布隆过滤器：避免缓存穿透；
异步更新：写入后异步更新缓存，避免双写不一致。

3. Sharding-JDBC 实战

3.1 为什么要分库分表？

随着业务发展，系统中的核心数据表数据量可能达到千万甚至上亿级，单库、单表架构将面临以下瓶颈：

单库性能瓶颈：数据库实例的 CPU、内存、网络 I/O、连接数资源有限，超过物理承载能力会导致响应变慢、甚至连接超时；
单表热点冲突严重：高并发写入时，InnoDB 层存在行锁、间隙锁冲突，热点记录（如订单、交易）写入性能急剧下降；
文件系统限制：单张表的索引文件、数据文件持续膨胀，对底层文件系统 I/O 造成压力，影响查询和备份效率。

最根本的原因在于写入压力集中在一个数据库或一张表上，成为系统瓶颈。

因此，分库分表的核心目的在于：

分摊写入压力，提高系统可用性和并发写性能。

尽管分库分表在某些场景下也能间接带来读性能的提升（如分片后查询数据量减少），但读压力更常通过读写分离、缓存、ES 等手段分担，因此要清晰认识：

分表的根本目的是分摊写负载，而非分摊读负载。

3.2 ShardingSphere-JDBC 介绍

为了优雅地实现分库分表，Apache ShardingSphere 提供了强大的中间件解决方案，其中最适合嵌入 Java 项目的就是ShardingSphere-JDBC：

ShardingSphere-JDBC 是一款轻量级的 Java 客户端分库分表框架，主要特性如下：

3.2.1 无侵入式设计

以 Jar 包形式嵌入到 SpringBoot/Spring 应用中，无需独立部署中间件；
对应用层无感知，SQL 层无需大幅修改。

3.2.2 功能完备

分库分表支持：基于分片键，实现数据水平切分，支持 INLINE、HASH_MOD、自定义算法等；
读写分离支持：自动将读请求路由到从库，提升读性能；
灵活的路由机制：支持标准分片、范围分片、Hint 强制路由、绑定表、广播表；
结果聚合与排序：分片后 SQL 查询自动执行、聚合、排序返回，开发者无需关心底层执行逻辑；
事务兼容性强：支持本地事务，部分版本支持 XA 分布式事务。

3.2.3 组件式架构

ShardingSphere-JDBC 在内部通过以下 4 个模块协同完成一次 SQL 执行：

SQL 解析：解析原始 SQL，提取表名、字段、条件等信息；
路由计算：根据配置的分片策略，计算出目标数据库和分表；
SQL 改写与执行：将 SQL 改写成具体表名并发送至对应数据库执行；
结果归并与处理：将各个数据库返回的结果合并，统一返回给业务层。

3.2.4 使用场景

ShardingSphere-JDBC 适用于：

单体或轻量微服务架构；
对延迟敏感，需本地事务支持；
数据体量中大、写入频繁的核心表（如订单、日志、交易流水）；
快速引入分库分表能力，但不希望引入额外中间件组件。

3.3 SpringBoot 实战

Maven 依赖

<dependency>
  <groupId>org.apache.shardingsphere</groupId>
  <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
  <version>5.4.1</version>
</dependency>

application.yml 配置

spring:
  shardingsphere:
    datasource:
      names: ds0, ds1
      ds0:
        url: jdbc:mysql://localhost:3306/order_db_0
        username: root
        password: root
        driver-class-name: com.mysql.cj.jdbc.Driver
      ds1:
        url: jdbc:mysql://localhost:3306/order_db_1
        username: root
        password: root
        driver-class-name: com.mysql.cj.jdbc.Driver
    rules:
      sharding:
        tables:
          orders:
            actualDataNodes: ds$->{0..1}.orders_$->{0..3}
            tableStrategy:
              standard:
                shardingColumn: user_id
                shardingAlgorithmName: order_hash
        shardingAlgorithms:
          order_hash:
            type: INLINE
            props:
              algorithm-expression: orders_$->{user_id % 4}
    props:
      sql-show: true

上面是 Spring Boot 项目中使用 ShardingSphere-JDBC 实现 分库分表 的典型配置，逐项解析如下：

spring:
  shardingsphere:
    datasource:
      names: ds0, ds1

定义数据源名称，表示接下来会配置两个逻辑数据库：ds0 和 ds1，用于分库。

      ds0:
        url: jdbc:mysql://localhost:3306/order_db_0
        username: root
        password: root
        driver-class-name: com.mysql.cj.jdbc.Driver
      ds1:
        url: jdbc:mysql://localhost:3306/order_db_1
        username: root
        password: root
        driver-class-name: com.mysql.cj.jdbc.Driver

为每个数据源分别配置连接信息，对应的物理数据库是：
- ds0 → order_db_0
- ds1 → order_db_1
即将所有 orders 表数据平均分布在两个库中。

    rules:
      sharding:
        tables:
          orders:
            actualDataNodes: ds$->{0..1}.orders_$->{0..3}

actualDataNodes 指定实际的表节点位置：
- ds0.orders_0 ~ ds0.orders_3
- ds1.orders_0 ~ ds1.orders_3
即总共创建了 2（库）× 4（表）= 8 个物理分表。

            tableStrategy:
              standard:
                shardingColumn: user_id
                shardingAlgorithmName: order_hash

指定分片字段为 user_id。
使用名为 order_hash 的算法来决定数据该落在哪张表中。

        shardingAlgorithms:
          order_hash:
            type: INLINE
            props:
              algorithm-expression: orders_$->{user_id % 4}

配置了一个内联分片算法（INLINE）：
- user_id % 4 取模后决定落在哪个表（orders_0 ~ orders_3）。
- 示例：user_id = 7 → orders_3。
注意，这个算法只控制表路由，分库是由 actualDataNodes 的默认 hash 路由实现的。

    props:
      sql-show: true

启用 SQL 打印功能。
控制台会打印出真实执行的 SQL（包括分库分表后实际查询的 SQL 语句），用于调试和优化。

Mapper 示例

@Mapper
public interface OrderMapper {
    @Insert("INSERT INTO orders (user_id, amount) VALUES (#{userId}, #{amount})")
    void insert(Order order);

    @Select("SELECT * FROM orders WHERE user_id = #{userId}")
    List<Order> findByUserId(Long userId);
}

4. 分表后的分页查询

4.1 问题描述

在未分表时，我们可以直接使用：

SELECT * FROM orders ORDER BY create_time DESC LIMIT 10, 10;

分表后，这类全局分页就变得困难：

数据分散在 orders_0 ~ orders_3；
无法使用原生 SQL 进行统一排序和分页；
性能下降明显。

4.2 解决方案

4.2.1 应用层聚合分页

向所有分表发出 LIMIT 查询；
汇总结果后在内存中排序分页。

缺点：分页越深，查询越慢。

4.2.2 构建分页索引表

维护一张索引表 orders_index，记录各分表中主键、时间戳等信息：

orders_index(id, sub_table, order_id, create_time)

分页时先从索引表分页，再定位真实子表查明细。

4.2.3 借助搜索引擎（如 ES）

全量同步订单数据至 ES，借助其倒排索引实现天然分页。

智能体开发者社区

中国智能体开发者社区，聚焦智能体与大模型开发，提供前沿资讯、实用工具链、开源项目及行业案例。通过技术沙龙、开发者大赛等活动，促进经验交流与协作，助力开发者快速构建创新智能应用。

更多推荐

cover

22道AI Agent 工程师必会的面试题，别啥都不会就出去找工作

智能体开发者社区

cover

基于AI agent的智能图书馆

智能体开发者社区

cover

AI Agent 自动化安全审计实战：基于 LangChain 打造智能化漏洞扫描助手

智能体开发者社区

所有评论(0)

查看更多评论

散一世繁华，颠半世琉璃

已为社区贡献2条内容