数据库连接池耗尽排查分库分表实战读写分离性能优化避免MySQL高并发场景下单表超500万行瓶颈

想象一下，你的应用像早高峰的地铁，乘客（用户请求）源源不断地涌进来，而车厢（数据库）只有那么几节，门（连接池）只能同时上那么多人。如果这时候没人规划，车厢挤爆，门卡死，整个地铁停运——这就是生产环境中数据库崩溃的写照。

我是老张，干了十年后端，见过太多因为数据库没规划好而半夜被电话叫醒的痛苦。今天咱们不聊虚的，就聊聊怎么让数据库在高并发下稳如泰山。我会把分库分表、读写分离、连接池管理这些看似高大上的东西，掰开揉碎了讲给你听，顺便穿插一些我踩过的坑和救火经历。

一、连接池：别让数据库“饿死”或“撑死”

连接池是应用和数据库之间的“中转站”。没有它，每次请求都新建连接，那效率低得让人怀疑人生。有了它，连接复用，速度提升明显。但连接池配置不当，就是灾难的根源。

1.1 连接池耗尽的常见症状

应用响应缓慢，甚至超时：请求卡在获取连接这一步。
报错：Too many connections 或 Connection pool exhausted：数据库连接数已达上限，或应用侧连接池耗尽。
数据库 CPU 使用率飙升：大量连接导致上下文切换频繁，数据库忙于管理连接而非执行查询。
线程池阻塞：等待连接的线程堆积，后续请求无法处理。

1.2 排查步骤：从症状到根源

第一步：确认瓶颈在应用侧还是数据库侧

应用侧监控：

查看连接池状态指标（以 HikariCP 为例）：

Active connections：当前正在使用的连接数。
Idle connections：空闲连接数。
Total connections：池中总连接数。
Pending requests：等待连接的请求数（关键！）。

如果 Pending requests > 0，说明连接池已耗尽，请求在排队。

数据库侧监控：

执行 SHOW STATUS LIKE 'Threads_connected'; 查看当前连接数。
执行 SHOW STATUS LIKE 'Threads_running'; 查看正在执行查询的线程数。
如果 Threads_connected 接近 max_connections，说明数据库连接数已达上限。

对比分析：

如果应用侧 Total connections < 配置的最大连接数，但 Pending requests > 0，说明连接池配置过小，或查询执行时间长，连接占用久。
如果数据库侧 Threads_connected 接近 max_connections，而应用侧连接池远未饱和，说明数据库侧限制了连接数，或存在大量空闲连接未释放。

第二步：分析连接占用时间

连接被长时间占用，是导致池耗尽的主要原因之一。

查询慢查询：

SHOW VARIABLES LIKE 'long_query_time';
SET long_query_time = 1; -- 设置慢查询阈值为 1 秒
SHOW VARIABLES LIKE 'slow_query_log';
-- 开启慢查询日志后，分析日志，找出执行时间长的 SQL

查看当前正在执行的查询：

SHOW PROCESSLIST;

关注 Time 列，找出执行时间长的查询。

分析锁等待：

-- MySQL 5.7+
SELECT * FROM information_schema.INNODB_LOCK_WAITS;
-- MySQL 8.0+
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;

锁等待会导致查询阻塞，占用连接时间长。

第三步：检查连接池配置

以 HikariCP 为例，关键配置参数：

maximumPoolSize：最大连接数。默认值是 10，对于高并发场景通常太小。
minimumIdle：最小空闲连接数。
maxLifetime：连接最大生命周期（默认 30 分钟）。设置过短会导致频繁创建连接，过长可能导致连接失效。
idleTimeout：空闲连接超时时间（默认 10 分钟）。设置过短会导致频繁关闭空闲连接，过长会占用资源。
connectionTimeout：获取连接的超时时间（默认 30 秒）。设置过短会导致请求快速失败，过长会导致线程阻塞。

调整建议：

maximumPoolSize：根据数据库 max_connections 和应用并发量调整。一般建议 maximumPoolSize * 应用实例数 < max_connections 的 80%。
maxLifetime：建议设置为 25-30 分钟，避免连接失效。
idleTimeout：建议设置为 10 分钟左右，节省资源。
connectionTimeout：根据业务容忍度设置，一般 5-10 秒。

第四步：检查代码中的连接使用

确保连接及时释放：

// 错误示例：连接未释放
Connection conn = dataSource.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT ...");
// 如果这里抛出异常，连接不会释放
// 正确做法：使用 try-with-resources
try (Connection conn = dataSource.getConnection();
     Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery("SELECT ...")) {
    // 处理结果
}

避免在事务中执行耗时操作：事务持有连接，耗时操作会长时间占用连接。

// 错误示例：事务中包含耗时操作
@Transactional
public void processOrder(Order order) {
    // 数据库操作
    saveOrder(order);
    // 耗时操作：调用外部服务、计算等
    callExternalService(order); // 这会占用连接！
}

避免大事务：大事务持有连接时间长，容易耗尽连接池。尽量拆分小事务。

1.3 实战案例：连接池耗尽的救火

有一次，我们的订单服务在高并发促销期间，响应时间从 100ms 飙升到 1s 以上，最终大量超时。监控显示应用侧连接池 Pending requests 持续 > 0，数据库侧 Threads_connected 接近上限。

排查过程：

查看慢查询日志，发现有多条查询执行时间超过 5 秒，主要是 SELECT * FROM orders WHERE create_time BETWEEN ? AND ?，没有索引。
查看 SHOW PROCESSLIST，发现大量连接处于 Sending data 状态，执行上述查询。
检查连接池配置，maximumPoolSize 为 20，对于 4 个应用实例，总连接数 80，而数据库 max_connections 为 100，确实接近上限。
但问题根源是慢查询导致连接占用时间长，而非连接数不足。

解决方案：

为 create_time 字段添加索引。
将 SELECT * 改为只查询需要的字段。
适当增加连接池大小，maximumPoolSize 调整为 30。
优化查询，避免大分页，改用游标或基于 ID 的翻页。

调整后，响应时间恢复到 100ms 左右，连接池 Pending requests 降为 0。

二、分库分表：单表超过 500 万行的瓶颈与突破

单表数据量超过 500 万行，查询性能会显著下降。索引效率降低，全表扫描变慢，甚至影响更新操作。分库分表是解决这一问题的有效手段。

2.1 为什么单表有 500 万行的瓶颈？

索引效率下降：B+ 树索引深度增加，IO 次数增多。
全表扫描变慢：数据量增大，扫描耗时增加。
更新性能下降：索引维护成本增加，锁竞争加剧。
备份恢复耗时：大表备份恢复时间长，影响运维。

2.2 分库分表策略

水平分表

将单表数据分散到多个表中，通常按某个字段取模或范围划分。

常见分表键：

用户 ID、订单 ID、时间等。

分表算法：

取模分表：table_id = user_id % N，均匀分布，但跨表查询困难。
范围分表：按时间范围划分，如每月一张表，查询效率高，但数据可能倾斜。

示例：订单表 orders 按用户 ID 取模分 10 张表：

orders_0, orders_1, ..., orders_9

查询用户 ID 为 10001 的订单：

int tableIndex = 10001 % 10;
String tableName = "orders_" + tableIndex;
SELECT * FROM orders_0 WHERE user_id = 10001;

垂直分表

将单表的列分散到多个表中，通常将热点列和非热点列分开。

示例：订单表 orders 分为 orders 和 orders_detail：

orders：订单 ID、用户 ID、金额、状态、创建时间等常用字段。
orders_detail：商品详情、备注等不常用字段。

分库分表中间件

直接使用中间件简化开发，如 ShardingSphere、MyCat 等。

ShardingSphere 示例：

# ShardingSphere 配置示例
dataSources:
  ds_0:
    url: jdbc:mysql://localhost:3306/db0
    username: root
    password: 123456
  ds_1:
    url: jdbc:mysql://localhost:3306/db1
    username: root
    password: 123456

shardingRule:
  tables:
    orders:
      actualDataNodes: ds_$->{0..1}.orders_$->{0..9}
      tableStrategy:
        standard:
          shardingColumn: user_id
          shardingAlgorithmName: ordersTableSharding
      databaseStrategy:
        standard:
          shardingColumn: user_id
          shardingAlgorithmName: ordersDatabaseSharding
  shardingAlgorithms:
    ordersTableSharding:
      type: MOD
      props:
        sharding-count: 10
    ordersDatabaseSharding:
      type: MOD
      props:
        sharding-count: 2

2.3 分库分表的挑战

跨表查询困难：JOIN 操作复杂，通常需要应用层处理。
分布式事务：保证数据一致性困难，可使用 TCC、Saga 等方案。
分页查询复杂：需要合并多个表的结果。
数据迁移成本高：历史数据迁移需考虑一致性和停机时间。

2.4 实战案例：订单系统分库分表

我们公司的订单系统，随着业务发展，订单表单表超过 1000 万行，查询性能严重下降。

分表策略：

按用户 ID 取模分 16 张表，分散到 2 个数据库中。
使用 ShardingSphere 作为中间件，透明化分表逻辑。

实施步骤：

设计阶段：确定分表键、分表算法、中间件选型。
开发阶段：改造代码，使用 ShardingSphere 提供的 API 进行分表操作。
数据迁移：编写数据迁移脚本，将历史数据分批迁移到新表，确保一致性。
测试阶段：进行功能测试、性能测试、压力测试。
上线阶段：灰度上线，监控性能和稳定性。

效果：

查询性能提升 5-10 倍，响应时间从 500ms 降至 50ms 左右。
系统稳定性提升，支撑了日均 100 万订单的业务量。

踩坑经历：

跨表查询：最初尝试使用 ShardingSphere 的跨表 JOIN，但性能不佳，后来改为应用层合并查询。
数据迁移：历史数据迁移时，未考虑大事务，导致迁移时间过长，最终改为分批迁移，每批 10 万条。
分页查询：跨表分页需合并多个表的结果，使用游标方式优化。

三、读写分离：提升查询性能的有效手段

读写分离是将读操作和写操作分散到不同的数据库实例上，读操作走从库，写操作走主库，从而减轻主库压力，提升查询性能。

3.1 读写分离的原理

主库：处理写操作（INSERT、UPDATE、DELETE）和读操作。
从库：通过主从复制，同步主库数据，处理读操作（SELECT）。

复制机制：

MySQL 主从复制基于 binlog，主库将数据变更写入 binlog，从库读取并执行 binlog，实现数据同步。

3.2 读写分离的优势

提升查询性能：读操作分散到多个从库，减轻主库压力。
提高系统可用性：从库可作为主库的备份，主库故障时可切换。
扩展性强：可横向扩展从库数量，提升读性能。

3.3 读写分离的挑战

数据延迟：主从复制存在延迟，从库数据可能不是最新。
跨库查询困难：写操作后查询最新数据，需确保查询主库。
主从切换复杂：主库故障时，需手动或自动切换从库为主库。

3.4 解决数据延迟问题

强一致场景：写操作后立即查询，强制路由主库。
最终一致场景：接受短暂延迟，查询从库。
缓存方案：使用 Redis 等缓存，缓存最新数据，减轻主库压力。

示例：

// 写操作后立即查询，强制路由主库
@Transactional
public Order createOrder(Order order) {
    orderMapper.insert(order);
    // 强制查询主库
    return orderMapper.selectByPrimaryKey(order.getId());
}

// 普通查询，路由从库
public List<Order> listOrders(long userId) {
    return orderMapper.selectByUserId(userId);
}

3.5 读写分离中间件

可使用中间件简化读写分离配置，如 ShardingSphere、MyCat 等。

ShardingSphere 配置示例：

dataSources:
  ds_master:
    url: jdbc:mysql://localhost:3306/master_db
    username: root
    password: 123456
  ds_slave_0:
    url: jdbc:mysql://localhost:3306/slave_db0
    username: root
    password: 123456
  ds_slave_1:
    url: jdbc:mysql://localhost:3306/slave_db1
    username: root
    password: 123456

masterslaveRule:
  name: ms
  masterDataSourceName: ds_master
  slaveDataSourceNames: [ds_slave_0, ds_slave_1]

3.6 实战案例：电商系统读写分离

我们的电商系统，查询流量远大于写流量，尤其是商品详情、订单列表等接口。

实施方案：

1 个主库，3 个从库，使用 ShardingSphere 实现读写分离。
写操作和需要最新数据的查询路由主库，普通查询路由从库。
监控主从延迟，延迟超过 1 秒时，告警并强制路由主库。

效果：

查询性能提升 3-5 倍，主库压力显著降低。
系统稳定性提升，支撑了日均 500 万 UV 的业务量。