数据库扛不住高并发MySQL加锁性能优化实战连接池配置索引优化读写分离与分库分表方案

兄弟，你这个问题问得太实在了。我见过太多线上事故的复盘，十有八九都能追溯到MySQL这个”背锅侠”。高并发下数据库被打爆，不是某一个环节的问题，而是从连接管理、SQL写法、索引设计到架构选型的全链路问题。今天咱们就扒开底层，把能优化的地方全部过一遍。

---

先理解MySQL在高并发下到底”卡”在哪

很多开发者一上来就优化SQL，这是误区。MySQL在高并发下的瓶颈，按优先级排通常是这个顺序：

连接数耗尽 > 锁竞争 > 磁盘I/O > 内存不足 > 慢SQL

你可以这样理解：MySQL就像一个餐厅，连接池是餐厅的座位数，锁是后厨炒菜的速度，索引是菜单的排版效率，读写分离和分库分表就是多开分店。只盯着一个环节优化，其他环节不跟上，照样会崩。

---

连接池配置：别让你的数据库被”淹死”

为什么连接池这么重要

每次建立MySQL连接都要经过TCP三次握手、SSL协商、权限验证，这个过程在毫秒级别，但连接数到几千时，光建立连接的开销就能把数据库拖垮。连接池的核心价值是：复用连接，减少建立/销毁的开销。

核心参数配置实战

以最常用的 Druid 连接池为例，这是一个在生产环境中实打实用过、扛过百万级QPS的配置方案：

<!-- Spring Boot Druid连接池配置示例 -->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">

    <!-- 基本连接信息 -->
    <property name="url" value="${jdbc.url}"/>
    <property name="username" value="${jdbc.username}"/>
    <property name="password" value="${jdbc.password}"/>
    <property name="driverClassName" value="${jdbc.driver}"/>

    <!-- ========== 核心线程池配置 ========== -->
    <!-- 初始连接数：启动时创建多少连接 -->
    <property name="initialSize" value="5"/>

    <!-- 最小空闲连接数：保证最低可用连接数 -->
    <property name="minIdle" value="10"/>

    <!-- 最大活跃连接数：这是最重要的参数，决定了同时能处理多少请求 -->
    <!-- 计算公式参考：maxIdle = (CPU核数 * 2) + 磁盘数
         一般电商系统建议设置在200-500之间，具体看你的业务场景 -->
    <property name="maxActive" value="300"/>

    <!-- 最大等待时间：连接池耗尽时，请求最多等多久
         建议3-5秒，太短会直接抛异常，太长会累积积压请求 -->
    <property name="maxWait" value="3000"/>

    <!-- ========== 连接探测与回收配置 ========== -->
    <!-- 间隔多久检测一次空闲连接，单位毫秒 -->
    <property name="timeBetweenEvictionRunsMillis" value="60000"/>

    <!-- 连接在池中最小生存时间，防止被错误回收 -->
    <property name="minEvictableIdleTimeMillis" value="300000"/>

    <!-- 连接最大生存时间，防止连接老化导致的问题 -->
    <property name="maxEvictableIdleTimeMillis" value="900000"/>

    <!-- 检测连接是否有效的SQL -->
    <property name="validationQuery" value="SELECT 1"/>

    <!-- 借出连接时不做验证，提升性能 -->
    <property name="testWhileIdle" value="true"/>

    <!-- 借出连接时验证，高并发下建议false，开销大 -->
    <property name="testOnBorrow" value="false"/>

    <!-- 归还连接时验证，建议false，开销大 -->
    <property name="testOnReturn" value="false"/>

    <!-- ========== 防SQL注入与监控 ========== -->
    <!-- 打开SQL防火墙，防止批量注入攻击 -->
    <property name="wall" ref="wallConfig"/>

    <!-- 开启监控统计（生产环境建议开启，性能损耗<1%） -->
    <property name="filters" value="stat"/>

    <!-- 慢SQL记录阈值，单位毫秒 -->
    <property name="timeBetweenLogStatsMillis" value="60000"/>

    <!-- ========== 连接泄漏检测 ========== -->
    <!-- 开启连接泄漏检测 -->
    <property name="removeAbandoned" value="true"/>

    <!-- 连接泄漏阈值：超过180秒未归还的连接视为泄漏 -->
    <property name="removeAbandonedTimeout" value="180"/>

    <!-- 泄漏时打印日志，方便排查 -->
    <property name="logAbandoned" value="true"/>

</bean>

配置参数的经验值参考

参数	推荐值	说明
maxActive	200-500	根据CPU核数和业务类型调整，写多读少的场景可以适当降低
minIdle	maxActive的30%-50%	保证突发流量时有足够连接池
initialSize	10-50	启动预热，避免冷启动时连接池空转
maxWait	3000-5000ms	根据业务容忍度调整，超时直接走降级逻辑
testOnBorrow	false	高并发下关闭，靠testWhileIdle保证连接有效
removeAbandoned	true	必须开启，防止连接泄漏拖垮数据库

连接池常见的坑

坑一：maxActive设得过大

有些开发者觉得”加大连接数肯定没问题”，恰恰相反。MySQL服务端本身有max_connections限制（默认151），每个连接会占用内存（约256KB起步）。500个连接就能吃掉128MB内存，而且连接太多会导致MySQL上下文切换频繁，CPU时间被白白浪费。

坑二：连接泄漏不检测

如果代码中有try块没有正确关闭连接，连接池中的连接会被默默耗尽。开启removeAbandoned后，超过阈值的连接会被强制回收并打印堆栈，能迅速定位问题代码。

坑三：没有监控告警

连接池必须接入监控，关注这几个核心指标：

• activeCount：当前活跃连接数，超过maxActive的80%就要告警
• poolingCount：池中空闲连接数，接近0说明连接不够用
• notEmptyWaitMillis：等待连接的总时间，持续升高说明连接池成为瓶颈
• connectionHoldErrorCount：连接获取失败次数

// 监控连接池状态的示例代码
@RestController
@RequestMapping("/monitor")
public class DataSourceMonitorController {

    @Autowired
    private DataSource dataSource;

    @GetMapping("/pool")
    public Map<String, Object> getPoolStatus() {
        HikariDataSource hikariDataSource = (HikariDataSource) dataSource;
        HikariPoolMXBean pool = hikariDataSource.getHikariPoolMXBean();

        Map<String, Object> result = new HashMap<>();
        result.put("activeConnections", pool.getActiveConnections());
        result.put("idleConnections", pool.getIdleConnections());
        result.put("totalConnections", pool.getTotalConnections());
        result.put("threadsAwaitingConnection", pool.getThreadsAwaitingConnection());
        result.put("connectionTimeoutCount", pool.getConnectionAcquisitionStats().getTotalAcquisitionCount());

        // 关键告警判断
        if (pool.getActiveConnections() > pool.getTotalConnections() * 0.8) {
            result.put("warning", "连接池使用率超过80%");
        }
        if (pool.getThreadsAwaitingConnection() > 0) {
            result.put("warning", "有线程在等待连接");
        }

        return result;
    }
}

---

索引优化：让数据库”少走弯路”

索引的本质：用空间换时间

索引就是一棵B+树， MySQL通过这棵树把全表扫描（O(n)）变成树形查找（O(log n)）。但索引不是越多越好，每个索引都会占用磁盘空间，并且影响写操作的性能。

覆盖索引：避免回表，性能提升3-10倍

这是一个经常被忽视的优化点。回表是指：查询的列不在索引中，MySQL需要通过主键索引再回表查完整行数据。

-- 假设有一个用户表
CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    username VARCHAR(64) NOT NULL,
    email VARCHAR(128),
    age INT,
    status TINYINT DEFAULT 1,
    created_at DATETIME
);

-- 场景：按用户名查询用户邮箱
-- 错误做法：没有索引，全表扫描
SELECT email FROM users WHERE username = 'zhangsan';

-- 正确做法：建立覆盖索引
ALTER TABLE users ADD INDEX idx_username_email (username, email);

-- 这条查询就命中了覆盖索引，不需要回表
SELECT email FROM users WHERE username = 'zhangsan';

覆盖索引的判定方法：在SQL语句前加EXPLAIN，看Extra列是否包含Using index。

EXPLAIN SELECT email FROM users WHERE username = 'zhangsan';
-- 结果中 Extra 列出现 "Using index" 说明命中覆盖索引

最左前缀原则：联合索引的”生死线”

联合索引 (a, b, c) 的效果等价于 (a) + (a, b) + (a, b, c)，但不包含 (b)、(c)、(b, c)。

-- 索引：idx_ab (a, b)

-- ✅ 命中索引
SELECT * FROM t WHERE a = 1 AND b = 2;
SELECT * FROM t WHERE a = 1;

-- ❌ 不命中索引，b列不会走索引
SELECT * FROM t WHERE b = 2;

-- ⚠️ 部分命中，只有a列走索引
SELECT * FROM t WHERE a > 1 AND b = 2;

实际业务中，我经常看到这种写法：WHERE a = ? AND b > ? AND c = ?，如果索引是 (a, b, c)，那么只有 a 和 b 能用上索引，c 因为 b 是范围查询所以失效了。解决方式有两种：

1. 调整索引列顺序，把等值查询的列放前面
2. 改成两个条件分别走索引，用FORCE INDEX或优化SQL结构

索引下推（ICP）：MySQL 5.6+的优化利器

索引下推（Index Condition Pushdown）是MySQL 5.6引入的优化，在存储引擎层提前过滤数据，减少回表次数。

-- 假设索引为 idx_name_age (name, age)
-- 查询：name like '张%' AND age = 25

-- 没有索引下推时：
-- 1. 遍历索引找到所有name='张'开头的记录
-- 2. 回表查询每一行
-- 3. 在服务器层判断age是否等于25

-- 有索引下推时：
-- 1. 遍历索引找到name='张'开头的记录
-- 2. 在存储引擎层直接判断age是否等于25（不返回服务器层）
-- 3. 只有满足条件的才回表

-- 效果：回表次数大幅减少，性能显著提升
EXPLAIN SELECT * FROM users WHERE name LIKE '张%' AND age = 25;
-- 看Extra列是否有 "Using index condition"

避免索引失效的常见写法

这些写法会让索引形同虚设，务必在生产代码中避免：

-- ❌ 对索引列做运算
SELECT * FROM users WHERE age + 1 = 25;
-- 改为
SELECT * FROM users WHERE age = 24;

-- ❌ 隐式类型转换（字符串字段传了数字）
-- 假设email是VARCHAR类型
SELECT * FROM users WHERE email = 123456;
-- 改为
SELECT * FROM users WHERE email = '123456';

-- ❌ LIKE前面加%
SELECT * FROM users WHERE name LIKE '%张三';
-- 改为：如果业务允许，改成name LIKE '张三%'

-- ❌ OR条件中有一列没索引
SELECT * FROM users WHERE id = 1 OR email = 'test@test.com';
-- 如果email没有索引，整条语句都不会走索引
-- 改为分别查询后UNION，或给email加索引

-- ❌ 不使用NULL约束的字段
-- 列定义允许NULL且没有默认值，索引效率会下降
-- 改为：ALTER TABLE users MODIFY COLUMN email VARCHAR(128) NOT NULL DEFAULT '';

索引设计的实战 checklist

1. 区分度高的列放前面：比如status只有几个值，区分度低，放联合索引末尾
2. 短索引优先：VARCHAR(255)建索引不如VARCHAR(100)，前缀索引可以考虑
3. 尽量用覆盖索引：查询尽量只取索引中存在的列
4. 避免在大文本列上建索引：除非用前缀索引
5. 定期用pt-index-usage工具分析索引使用情况

-- 查看哪些索引从未被使用（MySQL 8.0+）
SELECT * FROM sys.schema_unused_indexes;

-- 查看表的索引统计信息
SHOW INDEX FROM users;

-- 查看索引选择性和区分度
SELECT COUNT(DISTINCT username) / COUNT(*) AS selectivity
FROM users;
-- 区分度越接近1，索引效果越好

---

锁优化：高并发下的”交通规则”

MySQL锁的层次

MySQL的锁不是单一概念，有这几个层次：

锁类型	作用范围	说明
全局锁	整个实例	FLUSH TABLES WITH READ LOCK，备份时用
表级锁	整张表	MyISAM引擎默认，InnoDB也有部分场景
行级锁	单行数据	InnoDB默认，高并发首选
间隙锁	索引区间	防止幻读，MVCC下可减少使用

InnoDB的行锁机制

InnoDB的行锁不是锁”行”，而是锁索引记录。这是一个关键点，很多人不理解。

-- 假设表结构
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT,
    status TINYINT,
    created_at DATETIME,
    INDEX idx_user_id (user_id)
);

-- 场景1：精确主键查询，锁一行
SELECT * FROM orders WHERE id = 1001 FOR UPDATE;
-- 锁的是主键索引中id=1001这一条记录，非常精确

-- 场景2：用非唯一索引查询，锁的范围取决于范围
SELECT * FROM orders WHERE user_id = 200 FOR UPDATE;
-- 如果user_id=200有10条记录，这10条都会被锁住

-- 场景3：范围查询，锁的范围更大
SELECT * FROM orders WHERE id BETWEEN 1000 AND 2000 FOR UPDATE;
-- 不仅锁1000-2000的记录，还可能锁住相邻的间隙（Next-Key Lock）

高并发下的锁竞争优化

问题场景：秒杀系统中，1000个人同时抢1件商品，数据库里stock字段被并发更新，导致大量的行锁等待甚至死锁。

-- 错误的写法：直接扣减库存，锁竞争激烈
UPDATE products SET stock = stock - 1 WHERE id = 1 AND stock > 0;
-- 这行UPDATE会锁住id=1的整条记录，所有并发请求都在等待这把锁

优化方案一：用条件更新替代先查后更

-- 正确的写法：一步到位，减少锁持有时间
UPDATE products SET stock = stock - 1 WHERE id = 1 AND stock > 0;
-- 虽然还是锁了同一行，但减少了事务持续时间
-- 配合优化索引，把这条语句的执行时间压到最小

优化方案二：库存预热到Redis，异步落库

@Service
public class SeckillService {

    @Autowired
    private RedisTemplate<String, String> redisTemplate;

    @Autowired
    private OrderMapper orderMapper;

    /**
     * 秒杀扣减库存
     * 原理：Redis做原子扣减，MySQL只做最终落库
     * Redis的 DECR命令是单线程原子操作，扛住10万QPS没问题
     */
    @Transactional(rollbackFor = Exception.class)
    public SeckillResult seckill(Long productId, Long userId) {

        // 1. Redis原子扣减库存，设置超卖保护
        String stockKey = "seckill:stock:" + productId;
        Long remaining = redisTemplate.opsForValue().decrement(stockKey);

        if (remaining < 0) {
            // 库存不足，提前return，不触碰数据库
            return SeckillResult.outOfStock();
        }

        // 2. 库存充足，写入订单
        Order order = new Order();
        order.setProductId(productId);
        order.setUserId(userId);
        order.setCreateTime(new Date());
        orderMapper.insert(order);

        // 3. 异步更新商品表库存（不阻塞主流程）
        asyncUpdateProductStock(productId, userId);

        return SeckillResult.success(order.getId());
    }

    /**
     * 异步落库，用消息队列削峰填谷
     */
    @Async
    public void asyncUpdateProductStock(Long productId, Long userId) {
        // 通过MQ或者线程池异步更新
        // 这里用延迟队列保证数据最终一致
        messageSender.send("stock.update",
            JsonUtils.toJson(Map.of("productId", productId, "userId", userId)));
    }
}

# Spring Boot 异步配置
spring:
  task:
    execution:
      pool:
        core-size: 10        # 核心线程数
        max-size: 50         # 最大线程数
        queue-capacity: 1000 # 队列容量
      thread-name-prefix: seckill-async-

优化方案三：分批提交，缩短单条事务锁持有时间

/**
 * 批量导入/更新场景：分批提交，避免长时间持锁
 */
@Service
public class BatchUpdateService {

    @Autowired
    private OrderMapper orderMapper;

    /**
     * 分批处理，每批500条提交一次
     * 原理：单条事务持锁时间短，减少锁竞争窗口
     */
    public void batchUpdate(List<Order> orders) {
        int batchSize = 500;
        for (int i = 0; i < orders.size(); i += batchSize) {
            List<Order> batch = orders.subList(i, Math.min(i + batchSize, orders.size()));

            // 每批开启独立事务，提交后锁立即释放
            // 外层不用@Transactional，由方法内手动管理事务
            updateBatch(batch);
        }
    }

    @Transactional(rollbackFor = Exception.class)
    public void updateBatch(List<Order> orders) {
        for (Order order : orders) {
            orderMapper.updateById(order);
        }
    }
}

死锁的排查与预防

-- 开启死锁检测日志
SHOW ENGINE INNODB STATUS;
-- 查看LATEST DETECTED DEADLOCK部分

-- 查看当前死锁配置
SHOW VARIABLES LIKE 'innodb%deadlock%';

# 关键参数调优
# 死锁检测超时时间，默认50秒，建议调小到10秒
innodb_lock_wait_timeout = 10

# 死锁检测后，回滚整个事务还是只回滚一条语句
# 设置为1：只回滚最小子事务，减少锁持有时间
innodb_lock_wait_timeout = 50

死锁预防的最佳实践：

1. 统一加锁顺序：所有事务按相同顺序访问表/行
2. 小事务：尽量在最小范围内加锁，尽快提交
3. 用SELECT ... FOR UPDATE SKIP LOCKED：MySQL 8.0+支持，跳过已被锁定的记录
4. 设置合理的锁等待超时：超时后快速失败，不堆积

-- MySQL 8.0 跳过已锁记录，高并发场景神器
SELECT * FROM products WHERE id IN (1,2,3,4,5) FOR UPDATE SKIP LOCKED;
-- 被其他事务锁住的记录会被跳过，当前事务只处理能加锁的记录

---

读写分离：把读压力分流出去

读写分离的基本原理

数据库的读操作和写操作是两种完全不同的 workload。写操作需要保证一致性，通常会加锁；读操作可以接受最终一致性，完全可以分散到多个从库。读写分离的核心思想就是：写走主库，读走从库。

架构层级选择

应用层读写分离（推荐）
    应用代码中根据SQL类型选择数据源
    优点：灵活可控，不依赖中间件
    缺点：需要代码改动

代理层读写分离（ShardingSphere / MyCAT）
    通过数据库代理自动路由
    优点：对业务代码透明
    缺点：增加一个依赖，故障点增多

MySQL原生主从复制 + 路由层
    MySQL Proxy 或 MaxScale
    优点：原生支持
    缺点：稳定性不如ShardingSphere

基于ShardingSphere的读写分离配置（推荐）

# ShardingSphere读写分离配置（application.yml）
spring:
  shardingsphere:
    datasource:
      names: master,slave0,slave1

      # 主库配置
      master:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://192.168.1.100:3306/mydb?useUnicode=true&characterEncoding=utf8mb4&serverTimezone=Asia/Shanghai
        username: root
        password: ${DB_PASSWORD}
        hikari:
          minimum-idle: 5
          maximum-pool-size: 50
          idle-timeout: 30000
          max-lifetime: 1800000
          connection-timeout: 30000

      # 从库0
      slave0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://192.168.1.101:3306/mydb?useUnicode=true&characterEncoding=utf8mb4&serverTimezone=Asia/Shanghai
        username: root
        password: ${DB_PASSWORD}
        hikari:
          minimum-idle: 5
          maximum-pool-size: 100
          idle-timeout: 30000
          max-lifetime: 1800000
          connection-timeout: 30000

      # 从库1
      slave1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://192.168.1.102:3306/mydb?useUnicode=true&characterEncoding=utf8mb4&serverTimezone=Asia/Shanghai
        username: root
        password: ${DB_PASSWORD}
        hikari:
          minimum-idle: 5
          maximum-pool-size: 100
          idle-timeout: 30000
          max-lifetime: 1800000
          connection-timeout: 30000

    masterslave:
      # 读写分离规则
      load-balance-algorithm-type: round_robin  # 轮询负载均衡
      primary-data-source-name: master
      slave-data-source-names:
        - slave0
        - slave1

    props:
      sql-show: false  # 生产环境关闭SQL显示，提升性能

// 使用ShardingSphere，业务代码无感知
@Service
public class UserService {

    @Autowired
    private UserMapper userMapper;

    /**
     * 写操作：自动路由到主库
     */
    public void createUser(User user) {
        userMapper.insert(user);
    }

    /**
     * 读操作：自动路由到从库（轮询）
     */
    public User getUserById(Long id) {
        return userMapper.selectById(id);
    }

    /**
     * 特殊场景：写后立即读，强制走主库
     * ShardingSphere提供强制路由注解
     */
    public User createUserAndGetBack(Long id) {
        userMapper.insert(user);
        // 强制读主库，解决主从延迟问题
        return SpringShardingAwareness.get().master().read(() -> userMapper.selectById(id));
    }
}

主从延迟的处理

这是读写分离最核心的痛点。MySQL主从复制是异步的，从库可能存在几毫秒到几秒的延迟。如果用户在写操作后立即执行读操作，可能读到旧数据。

/**
 * 主从延迟解决方案：强制路由到主库
 */
@Component
public class MasterRoutingService {

    /**
     * 方案一：使用ThreadLocal标记，强制读主库
     */
    public <T> T executeInMaster(Supplier<T> action) {
        try {
            // 标记本次查询走主库
            MasterSlaveRouteHolder.setMaster();
            return action.get();
        } finally {
            MasterSlaveRouteHolder.clear();
        }
    }

    /**
     * 方案二：写操作后短时间强制走主库
     * 适用于写后立即读的常见场景
     */
    public void writeAndRead(String key, Runnable write, Supplier<?> read) {
        write.run();
        // 写入后500ms内强制读主库，规避主从延迟
        executeInMaster(() -> read.get());
    }
}

/**
 * ThreadLocal路由标记类
 */
public class MasterSlaveRouteHolder {

    private static final ThreadLocal<Boolean> MASTER_FLAG = ThreadLocal.withInitial(() -> false);

    public static void setMaster() {
        MASTER_FLAG.set(true);
    }

    public static boolean isMaster() {
        return MASTER_FLAG.get();
    }

    public static void clear() {
        MASTER_FLAG.remove();
    }
}

/**
 * 自定义路由策略，实现主从分离
 */
@Component
public class CustomMasterSlaveRouter extends MasterSlaveRouteRule {

    @Override
    public String getDataSourceName(DataNode dataNode, String sql) {
        // 写操作或标记了主库的读操作走主库
        if (isWriteOperation(sql) || MasterSlaveRouteHolder.isMaster()) {
            return "master";
        }
        // 其他读操作走从库
        return "slave";
    }

    private boolean isWriteOperation(String sql) {
        String upperSql = sql.toUpperCase().trim();
        return upperSql.startsWith("INSERT")
            || upperSql.startsWith("UPDATE")
            || upperSql.startsWith("DELETE")
            || upperSql.startsWith("REPLACE")
            || upperSql.startsWith("CREATE")
            || upperSql.startsWith("DROP")
            || upperSql.startsWith("ALTER");
    }
}

---

分库分表：突破单机MySQL的性能天花板

为什么要分库分表

单机MySQL的瓶颈通常在：

• CPU：单核算力有限，复杂查询跑满后只能加机器
• 内存：Buffer Pool放不下大表，大量查询回磁盘
• 磁盘I/O：单盘读写能力有限，RAID阵列也有上限
• 连接数：单机连接数有限，并发场景下连接池先爆

分库分表就是把一张大表”切”成多个小表/多个库，分散压力。

分库分表策略选择

垂直拆分（按列）          水平拆分（按行）
┌──────────────┐         ┌──────────┐  ┌──────────┐
│ 用户基本信息   │         │  订单表    │  │  订单表    │
│ - id         │         │ - id     │  │ - id     │
│ - name       │         │ - uid    │  │ - uid    │
│ - phone      │  →      │ - amount │  │ - amount │
└──────────────┘         │ - status │  │ - status │
┌──────────────┐         │ - addr   │  │ - addr   │
│ 用户扩展信息   │         │ - time   │  │ - time   │
│ - ext_field1 │         └──────────┘  └──────────┘
│ - ext_field2 │         DB1           DB2
└──────────────┘

垂直拆分适合大字段分离，比如把TEXT/BLOB类型的字段拆出去，减少主表查询时的内存消耗。

水平拆分适合大流量表，把数据按规则分散到多张表中。

分表键的选择

常见分表键：
├── 用户ID（uid）：适合用户相关数据，数据分布均匀
├── 订单ID（order_id）：适合订单表，天然递增
├── 时间分片（按天/月）：适合日志类数据，冷热分离
└── 复合键：用户ID+时间，兼顾查询和分布

ShardingSphere分库分表示例

# 分库分表配置（application.yml）
spring:
  shardingsphere:
    datasource:
      names: ds0,ds1

      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://192.168.1.100:3306/order_db_0
        username: root
        password: ${DB_PASSWORD}

      ds1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://192.168.1.100:3306/order_db_1
        username: root
        password: ${DB_PASSWORD}

    rules:
      sharding:
        tables:
          t_order:
            # 实际数据表分布
            actual-data-nodes: ds$->{0..1}.t_order_$->{0..3}

            # 分库策略：按user_id取模
            database-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: database-inline

            # 分表策略：按order_id取模
            table-strategy:
              standard:
                sharding-column: order_id
                sharding-algorithm-name: table-inline

        sharding-algorithms:
          database-inline:
            type: INLINE
            props:
              algorithm-expression: ds$->{user_id % 2}

          table-inline:
            type: INLINE
            props:
              algorithm-expression: t_order_$->{order_id % 4}

        # 分布式主键策略
        key-generators:
          snowflake:
            type: SNOWFLAKE
            props:
              worker-id: 123  # 每台机器唯一

    props:
      sql-show: false

/**
 * 分库分表下的查询注意事项
 */
@Service
public class OrderService {

    @Autowired
    private OrderMapper orderMapper;

    /**
     * ✅ 合法的查询：带分片键
     * 路由直接命中具体表，性能最优
     */
    public Order getOrder(Long userId, Long orderId) {
        return orderMapper.selectOne(
            new LambdaQueryWrapper<Order>()
                .eq(Order::getUserId, userId)
                .eq(Order::getOrderId, orderId)
        );
    }

    /**
     * ⚠️ 非法的查询：不带分片键
     * 会广播到所有分片，性能差且可能数据不全
     * 解决方案：用ES或专门的宽表查询
     */
    public List<Order> searchByStatus(Integer status) {
        // 这种查询不应该直接打数据库
        // 应该走搜索引擎，或者建立冗余表
        return orderMapper.selectList(
            new LambdaQueryWrapper<Order>()
                .eq(Order::getStatus, status)
        );
    }

    /**
     * 跨分片查询的解决方案：用ES做聚合查询
     */
    @Autowired
    private ElasticsearchRestTemplate esTemplate;

    public AggregatedOrderResult searchOrders(OrderQueryCondition condition) {
        // 1. 先查ES获取符合条件的订单ID列表
        NativeSearchQuery searchQuery = new NativeSearchQueryBuilder()
            .withQuery(QueryBuilders.boolQuery()
                .must(QueryBuilders.termQuery("status", condition.getStatus()))
                .must(QueryBuilders.rangeQuery("createTime")
                    .gte(condition.getStartTime())
                    .lte(condition.getEndTime()))
            )
            .withPageable(PageRequest.of(0, condition.getPageSize()))
            .build();

        SearchHits<OrderDoc> searchHits = esTemplate.search(searchQuery, OrderDoc.class);

        // 2. 拿到ID后回MySQL精确查询
        List<Long> orderIds = searchHits.get().map(OrderDoc::getOrderId).collect(Collectors.toList());
        if (orderIds.isEmpty()) {
            return AggregatedOrderResult.empty();
        }

        List<Order> orders = orderMapper.selectBatchIds(orderIds);
        return AggregatedOrderResult.of(orders, searchHits.getTotalHits());
    }
}

/**
 * ES同步方案：保证数据库和ES数据一致性
 */
@Component
public class OrderEsSyncListener {

    @Autowired
    private ElasticsearchRestTemplate esTemplate;

    @Autowired
    private OrderMapper orderMapper;

    /**
     * 监听订单表变更，同步到ES
     * 方案：Canal监听MySQL binlog，发送到MQ，消费时更新ES
     */
    @RabbitListener(queues = "order.sync.es")
    public void syncToEs(OrderChangeEvent event) {
        Order order = orderMapper.selectById(event.getOrderId());
        if (order == null) {
            // 删除操作
            esTemplate.delete(OrderDoc.class, String.valueOf(event.getOrderId()));
            return;
        }

        OrderDoc doc = OrderDoc.from(order);
        esTemplate.index(IndexQuery.indexBuilder()
            .withId(String.valueOf(order.getOrderId()))
            .withObject(doc)
            .build());
    }
}

分库分表的常见坑

坑一：跨分片查询

分库分表后，不按分片键查询会变成全库扫描，性能极差。解决方案：

1. 加冗余字段，让查询能带上分片键
2. 用ES/MongoDB做异构索引
3. 用消息队列异步构建宽表

坑二：分布式事务

分库后，同一个业务可能跨多个库。传统事务管不了跨库场景。 解决方案：

1. Seata AT模式：阿里开源的分布式事务框架，对业务代码侵入小
2. 最终一致性：用消息队列+本地事务表，接受短暂的不一致
3. TCC模式：业务层实现Try-Confirm-Cancel，复杂但可控

/**
 * Seata分布式事务示例
 * 需要在方法上加@GlobalTransactional注解
 */
@GlobalTransactional
public void createOrderAndDeductStock(CreateOrderRequest request) {
    // 1. 扣减库存（可能在不同数据库）
    stockService.deductStock(request.getProductId(), request.getQuantity());

    // 2. 创建订单（可能在另一个数据库）
    Order order = new Order();
    order.setUserId(request.getUserId());
    order.setProductId(request.getProductId());
    order.setAmount(request.getAmount());
    order.setStatus(OrderStatus.CREATED);
    orderMapper.insert(order);

    // 3. 扣减用户余额（可能在第三个数据库）
    userService.deductBalance(request.getUserId(), request.getAmount());
}

坑三：历史数据迁移

上线分库分表方案时，老数据怎么迁？

• 双写方案：新数据同时写旧表和新表，逐步切流
• 离线迁移：用ETL工具把历史数据按规则拆分到各分片
• 在线迁移：用ShardingSphere-Proxy的 migration 功能

迁移步骤：
1. 搭建新的分库分表环境
2. 全量迁移历史数据
3. 开启双写：新数据同时写旧表和新表
4. 校验数据一致性
5. 逐步切流到新表
6. 下线旧表

---

整体优化方案总结

高并发MySQL优化不是单点优化，而是一套组合拳：

流量进入
    │
    ▼
┌──────────────┐
│  连接池管理   │ ← 控制连接数，防止数据库被连接打爆
└──────┬───────┘
       │
       ▼
┌──────────────┐
│  SQL + 索引   │ ← 让每一条SQL都走到最优执行路径
└──────┬───────┘
       │
       ▼
┌──────────────┐
│  锁优化       │ ← 减少锁等待，缩短锁持有时间
└──────┬───────┘
       │
       ▼
┌──────────────┐
│  读写分离     │ ← 读压力分散到多个从库
└──────┬───────┘
       │
       ▼
┌──────────────┐
│  分库分表     │ ← 数据量级突破单机上限
└──────────────┘

实施优先级建议：

1. 先把连接池配置调优，这个成本最低，效果最直接
2. 分析慢SQL，加对索引，这是性价比最高的优化
3. 引入读写分离，把读压力分流
4. 数据量持续增长后，再考虑分库分表

每一个环节都做好，你的MySQL才扛得住真正的高并发。记住，架构没有银弹，只有组合拳。