正文

高并发下MySQL崩了怎么办5种实战优化策略帮你稳住数据库

/0 浏览量
0908

哎，这事儿我熟。前阵子我帮一个做电商的朋友排查问题，凌晨三点，大促开始刚十分钟，监控系统疯狂报警，MySQL的CPU直接飙到100%，连接数瞬间顶满，整个后台服务全都超时。那一刻，真的能听到心跳漏一拍的声音。

很多开发者觉得MySQL扛不住是硬件不行，其实大部分时候，是我们的“姿势”不对。今天不聊虚的，就给你拆解5个我在实战中验证过、最能立竿见影的优化策略。这几个招数，从浅到深，配合着用，基本能解决90%的高并发崩库问题。

第一步：先别急着重启，看看是谁在“搞事”

系统崩了，第一反应往往是“重启试试”，但在高并发场景下，重启可能导致流量洪峰再次涌入时瞬间再次打挂，甚至引发主从延迟或数据不一致。

正确的做法是：先止血，再诊断。

1.1 如何快速定位“元凶”SQL？

使用 performance_schema 和 sys 库是最直观的方法。如果你正在经历高负载，运行下面这个查询，找出执行时间最长、占用资源最多的SQL：

-- 查看最近等待时间最长的SQL事件（基于performance_schema）
SELECT 
    DIGEST_TEXT AS sql_text,
    COUNT_STAR AS exec_count,
    SUM_TIMER_WAIT/1000000000000 AS total_wait_sec,
    AVG_TIMER_WAIT/1000000000 AS avg_wait_us,
    SUM_ROWS_SENT AS rows_sent,
    SUM_ROWS_EXAMINED AS rows_examined
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

人话解释：这段代码会告诉你，哪几条SQL最拖后腿。重点看 total_wait_sec 和 rows_examined。如果某条SQL rows_examined（扫描行数）巨大，但 rows_sent（返回行数）很小，说明它在做全表扫描或者没用到索引，这就是性能杀手。

1.2 查看当前正在运行的连接和慢查询

-- 查看当前活跃线程
SHOW PROCESSLIST;

-- 或者更详细的线程状态（MySQL 5.7+）
SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO 
FROM information_schema.processlist 
WHERE COMMAND != 'Sleep' 
ORDER BY TIME DESC;

如果看到大量 Sending data 或 Sorting result 状态的线程，说明查询正在处理复杂逻辑或扫描大量数据，这就是瓶颈所在。

第二步：索引优化——性价比最高的“救命稻草”

80%的MySQL性能问题，归根结底都是索引问题。高并发下，一条没有索引的查询会锁定整张表或扫描全表，直接拖垮数据库。

2.1 常见的索引误区

  • 过度索引：建了太多索引，导致写入性能下降（每次INSERT/UPDATE都要更新索引树）。
  • 索引失效：对索引列做了函数操作、类型隐式转换、或使用了 !=、NOT IN 等。
  • 最左前缀原则没理解透：联合索引 (a, b, c)，查询时如果跳过了 a，b 和 c 的索引就失效了。

2.2 实战案例：一个订单查询引发的血案

假设我们有一张订单表 orders，字段包括 user_id, status, create_time。业务需求是：查询某个用户最近7天状态为“待支付”的订单。

错误写法（无索引或索引不合理）：

SELECT * FROM orders 
WHERE user_id = 12345 
  AND status = 'PENDING' 
  AND create_time > DATE_SUB(NOW(), INTERVAL 7 DAY)
ORDER BY create_time DESC;

如果这张表有上千万数据，且没有合适的索引，MySQL可能只会用到 user_id 索引，然后对结果集进行文件排序（filesort）和过滤，并发一高，内存爆掉。

优化方案：

  1. 创建联合索引：idx_user_status_time (user_id, status, create_time)

    • 为什么这样建？因为 user_id 区分度高，先过滤；status 其次；create_time 用于排序。
    • 遵循最左前缀原则，查询条件能完全命中索引。

  2. 覆盖索引（Covering Index）：

    • 如果业务只需要 id, order_no, amount 这几个字段，可以把它们也加进索引，或者确保查询只选这几列。
    • 避免 SELECT *，这会导致回表（Bookmark Lookup），在高并发下是巨大的IO开销。
    -- 优化后的查询
SELECT id, order_no, amount 
FROM orders 
WHERE user_id = 12345 
  AND status = 'PENDING' 
  AND create_time > DATE_SUB(NOW(), INTERVAL 7 DAY)
ORDER BY create_time DESC;

    配合索引 idx_user_status_time，这条查询几乎可以在毫秒级完成，即使并发再高，也不会拖慢数据库。

2.3 如何检查索引是否生效？

使用 EXPLAIN 命令，永远不要猜，要看执行计划。

EXPLAIN SELECT id, order_no, amount 
FROM orders 
WHERE user_id = 12345 
  AND status = 'PENDING' 
  AND create_time > DATE_SUB(NOW(), INTERVAL 7 DAY);

重点关注 type 列：

  • ALL：全表扫描，最差。
  • index：全索引扫描，比全表稍好。
  • range：索引范围扫描，常见于 BETWEEN, >, <。
  • ref：非唯一索引扫描，很好。
  • eq_ref：唯一索引扫描，最佳之一。
  • const：最多一行，最优。

如果 type 是 ALL 或 range 且 rows 很大，就需要重新设计索引了。

第三步：分库分表——纵向扩展的必经之路

当单表数据量超过千万级，或者QPS（每秒查询率）超过单机MySQL的承载极限（通常认为单表500万-1000万行，QPS 2000-5000为临界点），就需要考虑分库分表了。

3.1 为什么需要分库分表？

  • 单表瓶颈：索引树过大，查询效率下降；IO性能达到极限。
  • 写瓶颈：单机CPU、内存、磁盘IO都有上限。
  • 锁竞争：高并发下，行锁、表锁竞争激烈。

3.2 分片策略选择

常见的分片键有：

  • 用户ID（user_id）：适合电商、社交场景，同一用户的数据在同一分片，方便事务和查询。
  • 订单ID（order_id）：适合交易系统，ID本身具有趋势性，便于时间范围查询。
  • 时间（create_time）：适合日志、流水表，按时间分片便于数据清理和归档。

3.3 实战：使用 ShardingSphere 进行分表

这里用一个简单的 Spring Boot + MyBatis + ShardingSphere 的例子来说明。假设我们按 order_id 的奇偶性分两张表：orders_0 和 orders_1。

1. 引入依赖（Maven）：

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

2. 配置文件（application.yml）：

spring:
  shardingsphere:
    datasource:
      names: ds0
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/mydb?useSSL=false&serverTimezone=UTC
        username: root
        password: 123456
    rules:
      sharding:
        tables:
          orders:
            actual-data-nodes: ds0.orders_$->{0..1}
            table-strategy:
              standard:
                sharding-column: order_id
                sharding-algorithm-name: orders-inline
        sharding-algorithms:
          orders-inline:
            type: INLINE
            props:
              algorithm-expression: orders_$->{order_id % 2}
    props:
      sql-show: true  # 打印SQL，方便调试

3. 实体类和Mapper：

@Data
public class Order {
    private Long orderId;
    private Long userId;
    private Integer status;
    private BigDecimal amount;
    private LocalDateTime createTime;
}

@Mapper
public interface OrderMapper {
    @Insert("INSERT INTO orders (order_id, user_id, status, amount, create_time) VALUES (#{orderId}, #{userId}, #{status}, #{amount}, #{createTime})")
    int insert(Order order);
    
    @Select("SELECT * FROM orders WHERE order_id = #{orderId}")
    Order selectById(@Param("orderId") Long orderId);
}

4. 测试：

@Service
public class OrderService {
    @Autowired
    private OrderMapper orderMapper;

    public void createOrder(Order order) {
        orderMapper.insert(order);
    }

    public Order getOrder(Long orderId) {
        return orderMapper.selectById(orderId);
    }
}

关键点说明：

  • order_id % 2 决定了数据落在哪张表。偶数进 orders_0，奇数进 orders_1。
  • 业务代码无需关心分表逻辑，ShardingSphere 会在底层自动路由。
  • 分库分表后，跨分片查询（如 SELECT * FROM orders WHERE create_time > '2023-01-01'）会变复杂，可能需要分布式查询或绑定表，需根据业务场景权衡。

注意：分库分表不是银弹，它会引入分布式事务、跨片查询、数据迁移等复杂问题。建议在单库单表性能还能支撑，但增长趋势明显时，提前规划。

第四步：读写分离——让主库专心写，从库专心读

对于读多写少的业务（如电商商品详情页、新闻列表），读写分离是最简单有效的扩展手段。主库处理写操作，多个从库处理读操作，通过主从复制保持数据同步。

4.1 架构原理

用户请求 -> 负载均衡/中间件 -> 路由到从库（读）
                              -> 路由到主库（写）
主库 ->  Binlog -> 从库（异步/半同步复制）

4.2 实战：使用 MyBatis Plus 的动态数据源

假设我们有一个主库 master 和两个从库 slave1, slave2。

1. 定义数据源配置类：

@Configuration
public class DataSourceConfig {

    @Primary
    @Bean(name = "masterDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.master")
    public DataSource masterDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "slave1DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.slave1")
    public DataSource slave1DataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "slave2DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.slave2")
    public DataSource slave2DataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    public DynamicDataSource dynamicDataSource(
            @Qualifier("masterDataSource") DataSource masterDataSource,
            @Qualifier("slave1DataSource") DataSource slave1DataSource,
            @Qualifier("slave2DataSource") DataSource slave2DataSource) {
        
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put(DBTypeEnum.MASTER, masterDataSource);
        targetDataSources.put(DBTypeEnum.SLAVE_1, slave1DataSource);
        targetDataSources.put(DBTypeEnum.SLAVE_2, slave2DataSource);

        return new DynamicDataSource(masterDataSource, targetDataSources);
    }
}

public enum DBTypeEnum {
    MASTER, SLAVE_1, SLAVE_2
}

2. 动态数据源切换逻辑：

public class DynamicDataSource extends AbstractRoutingDataSource {
    
    private static final ThreadLocal<DBTypeEnum> contextHolder = new ThreadLocal<>();

    @Override
    protected Object determineCurrentLookupKey() {
        return getDataSourceType();
    }

    public static void setDataSourceType(DBTypeEnum dataSourceType) {
        contextHolder.set(dataSourceType);
    }

    public static DBTypeEnum getDataSourceType() {
        return contextHolder.get() != null ? contextHolder.get() : DBTypeEnum.MASTER;
    }
}

3. AOP 切面自动路由（关键）：

@Aspect
@Component
public class DataSourceAspect {

    @Pointcut("@annotation(org.springframework.transaction.annotation.Transactional)" +
              " || execution(* com.example.service..*.*(..))")
    public void dataSourcePointCut() {}

    @Around("dataSourcePointCut()")
    public Object around(ProceedingJoinPoint point) throws Throwable {
        MethodSignature signature = (MethodSignature) point.getSignature();
        Method method = signature.getMethod();

        // 写操作强制路由到主库
        if (method.isAnnotationPresent(Transactional.class) || 
            method.getName().startsWith("save") || 
            method.getName().startsWith("insert") || 
            method.getName().startsWith("update") || 
            method.getName().startsWith("delete")) {
            DynamicDataSource.setDataSourceType(DBTypeEnum.MASTER);
        } else {
            // 读操作随机路由到从库，实现负载均衡
            DBTypeEnum randomSlave = DBTypeEnum.SLAVE_1;
            if (Math.random() > 0.5) {
                randomSlave = DBTypeEnum.SLAVE_2;
            }
            DynamicDataSource.setDataSourceType(randomSlave);
        }

        try {
            return point.proceed();
        } finally {
            // 清除ThreadLocal，防止内存泄漏
            DynamicDataSource.clearDataSourceType();
        }
    }
}

4. application.yml 配置：

spring:
  datasource:
    master:
      jdbc-url: jdbc:mysql://master:3306/mydb?useSSL=false&serverTimezone=UTC
      username: root
      password: 123456
    slave1:
      jdbc-url: jdbc:mysql://slave1:3306/mydb?useSSL=false&serverTimezone=UTC
      username: root
      password: 123456
    slave2:
      jdbc-url: jdbc:mysql://slave2:3306/mydb?useSSL=false&serverTimezone=UTC
      username: root
      password: 123456

效果：

  • 所有写操作（save, update, delete 及事务方法）都去主库。
  • 所有读操作随机分布在 slave1 和 slave2 上。
  • 这样，主库的压力大大减轻，从库分担了读取流量，整体并发能力提升数倍。

注意：读写分离存在数据延迟问题。如果业务要求强一致（如支付结果查询），必须读主库。可以通过强制读主库的注解或参数来控制。

第五步：缓存层——挡在前面的第一道防线

如果读写分离还不够，或者数据库本身就扛不住巨大的读压力，那就必须引入缓存。Redis 是最常用的选择，它能将热点数据的查询从磁盘IO提升到内存IO，速度提升几个数量级。

5.1 缓存击穿、穿透、雪崩问题

在高并发下，缓存本身也可能成为瓶颈，需要处理好以下三个经典问题：

  • 缓存穿透：查询根本不存在的数据，缓存和DB都查不到，每次请求都打到DB。
    • 解决：布隆过滤器，或对空值也进行缓存（设置短过期时间）。
  • 缓存击穿：热点key过期瞬间，大量请求涌入DB。
    • 解决：热点数据永不过期，或使用互斥锁（分布式锁）只让一个请求去查DB重建缓存。
  • 缓存雪崩：大量key同时过期，或Redis宕机。
    • 解决：过期时间加随机值，Redis集群高可用，本地缓存兜底。

5.2 实战：Spring Boot + Redis 缓存商品库存

假设我们有一个商品详情接口，QPS高达1万+，但数据变更不频繁。

1. 依赖：

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-redis</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-cache</artifactId>
</dependency>

2. 配置 Redis：

”`java @Configuration @EnableCaching public class RedisConfig {

@Bean
public RedisTemplate<String, Object> redisTemplate(RedisConnectionFactory factory) {
    RedisTemplate<String, Object> template = new RedisTemplate<>();
    template.setConnectionFactory(factory);
    // 使用Jackson序列化
    Jackson2JsonRedisSerializer<Object> serializer = new Jackson2JsonRedisSerializer<>(Object.class);
    ObjectMapper mapper = new ObjectMapper();
    mapper.setVisibility(PropertyAccessor.ALL, JsonAutoDetect.Visibility.ANY);
    mapper.activateDefaultTyping(LaissezFaireSubTypeValidator.instance, ObjectMapper.DefaultTyping.NON_FINAL);
    serializer
-- 展开阅读全文 --