深夜两点，监控大屏上的QPS（每秒查询率）曲线像一条狂躁的心电图，直冲云霄。数据库CPU占用率瞬间飙升至98%，连接数爆满，应用服务器开始疯狂报错“Too many connections”。这时候，如果你还在纠结是加机器还是改代码，那就太晚了。高并发场景下的MySQL优化，从来不是单一维度的“打补丁”，而是一场从应用层到存储层的立体防御战。

我们要做的，不是去对抗流量，而是通过层层拆解，给数据库穿上防弹衣。下面这套方案，是我在无数次线上故障复盘后总结出的实战经验，不讲空洞的理论，只讲怎么落地，怎么救火，怎么预防。

第一道防线：缓存为王，挡掉80%的请求

很多开发者的误区是：数据库扛不住，那就直接上更贵的数据库。大错特错。在高并发架构中，缓存（Cache）是第一道也是最有效的一道防线。只要数据允许一定的最终一致性，缓存就能帮你挡住绝大部分读请求。

为什么需要多级缓存？

单级Redis缓存在高并发下依然存在热点Key击穿的风险。为了极致性能，我们通常采用 本地缓存（Caffeine/Guava） + Redis分布式缓存 的两级架构。

  • L1 本地缓存：响应速度在微秒级，适合存放极少变动的字典数据或高频热点数据。缺点是集群环境下数据不一致，且受限于JVM内存。
  • L2 分布式缓存：响应速度在毫秒级，数据全局一致，适合存放大部分业务数据。

实战代码：带过期时间的本地缓存封装

这里用Java示例，结合Caffeine实现一个简易的本地缓存工具类，注意设置合理的过期时间防止内存溢出：

import com.github.benmanes.caffeine.cache.Cache;
import com.github.benmanes.caffeine.cache.Caffeine;
import java.util.concurrent.TimeUnit;

public class LocalCacheUtil {
    // 构建本地缓存，最大容量10000，写入后5分钟过期，移除时打印日志
    private static final Cache<String, Object> cache = Caffeine.newBuilder()
            .maximumSize(10000)
            .expireAfterWrite(5, TimeUnit.MINUTES)
            .recordStats() // 开启统计功能，方便监控命中率
            .build();

    public static void put(String key, Object value) {
        cache.put(key, value);
    }

    public static Object get(String key) {
        return cache.getIfPresent(key);
    }
    
    // 获取缓存命中率，用于监控告警
    public static double getHitRate() {
        return cache.stats().hitRate();
    }
}

关键策略：

  1. 空值缓存：即使数据库查不到数据，也要缓存一个空对象（如null或特定标记），并设置较短的过期时间（如30秒），防止缓存穿透。
  2. 互斥锁重建：当缓存失效时，不要所有线程都去查数据库，使用分布式锁（Redis SETNX）确保只有一个线程去查询DB并回填缓存，其他线程等待或返回旧缓存。

第二道防线：读写分离，让主库只负责写

当读请求远大于写请求时（比如电商商品详情、新闻列表），主库的压力主要来自大量的SELECT语句。此时，读写分离是必经之路。

架构原理

  • Master节点：负责事务处理和数据修改（INSERT/UPDATE/DELETE）。
  • Slave节点：负责数据查询（SELECT），通过Binlog同步Master的数据。

注意事项与坑点

  1. 主从延迟：这是读写分离最大的痛点。用户刚下单，立刻去查订单状态，可能因为同步延迟查到“未支付”状态。
    • 解决方案：对于强一致性的场景（如余额扣减后的查询），强制路由到Master节点。对于非强一致性场景（如商品详情），可以容忍轻微延迟。
  2. 中间件选择：不要自己造轮子去解析Binlog做路由。使用成熟的中间件，如 MyCat、ShardingSphere-Proxy 或者云厂商提供的RDS读写分离实例。它们能自动处理SQL解析和路由。

配置示例（ShardingSphere YAML片段）

rules:
  readwrite-splitting:
    dataSources:
      ds_master:
        url: jdbc:mysql://master-host:3306/db
      ds_slave_0:
        url: jdbc:mysql://slave-host-1:3306/db
    rule:
      load-balancer-type: ROUND_ROBIN # 负载均衡策略：轮询
      write-data-source-name: ds_master
      read-data-source-names: 
        - ds_slave_0

第三道防线：索引优化，精准打击每一行数据

如果缓存挡不住，读写分离也做了，数据库依然慢，那问题大概率出在SQL执行效率上。MySQL是B+树索引结构，如果没有用好索引，全表扫描（Full Table Scan）会让CPU瞬间爆炸。

1. 联合索引的最左前缀原则

假设有一个用户表，经常根据 age 和 status 查询。

  • 错误做法：建两个单列索引 idx_age, idx_status。MySQL优化器只能选其中一个，另一个需要回表过滤，效率低。
  • 正确做法：建立联合索引 idx_age_status (age, status)。

注意顺序：区分度高的字段放在前面。例如，gender 只有男女两种值，区分度极低，不适合做索引的第一列；而 user_id 或 create_time 区分度高，更适合靠前。

2. 覆盖索引与回表

“回表”是指索引树中只存了主键，需要回到聚簇索引（主键索引）中去取其他字段数据。这会导致大量的随机IO。

  • 优化技巧：如果查询的字段都在索引树中，就不需要回表。这就是覆盖索引。
-- 假设 idx_user_status 是 (user_id, status) 的联合索引
-- 下面这条SQL可以直接利用覆盖索引，无需回表
SELECT user_id, status FROM users WHERE status = 'active';

3. 避免索引失效

以下几个常见写法会让索引形同虚设，务必在Code Review阶段杜绝：

  • 隐式类型转换：phone 字段是字符串类型，查询时写成 WHERE phone = 13800000000（数字），会导致全表扫描。
  • 函数计算：WHERE YEAR(create_time) = 2023，索引失效。应改为范围查询 WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01'。
  • 模糊查询前置%：LIKE '%abc' 不走索引，LIKE 'abc%' 走索引。

4. 使用EXPLAIN分析SQL

在优化任何慢SQL之前，先执行 EXPLAIN 命令。重点关注以下字段：

  • type：访问类型，越靠右越好。system > const > eq_ref > ref > range > index > ALL。如果是 ALL，说明全表扫描，必须优化。
  • key：实际使用的索引。
  • rows：预估扫描行数。
  • Extra：如果出现 Using filesort 或 Using temporary，说明需要额外的排序或临时表，性能较差。

第四道防线：连接池配置，细水长流

数据库连接是昂贵的资源。创建和销毁连接涉及网络握手和上下文切换。高并发下，如果每个请求都新建连接，数据库连接数会瞬间打满，导致 Too many connections 错误。

为什么需要连接池？

连接池（Connection Pool）复用已建立的数据库连接，减少创建开销。常用的工具有 HikariCP（Spring Boot默认）、Druid、Tomcat JDBC。

HikariCP 核心参数调优

很多开发者直接使用默认配置，这在生产环境往往是隐患。以下是基于压测经验的推荐配置：

spring:
  datasource:
    hikari:
      # 最小空闲连接数。建议设置为核心线程数的50%-100%。
      # 如果设太小，高并发到来时需要临时创建连接，导致延迟抖动。
      minimum-idle: 10
      
      # 最大连接数。公式参考：CPU核数 * 2 + 有效磁盘数。
      # 不要设置过大，否则上下文切换开销巨大。一般20-50足够支撑高并发。
      maximum-pool-size: 20 
      
      # 连接超时时间，单位毫秒。默认30秒。
      connection-timeout: 30000
      
      # 空闲连接存活最大时间，默认600000(10分钟)。
      idle-timeout: 600000
      
      # 连接最大存活时间，防止数据库重启导致连接失效。
      max-lifetime: 1800000
      
      # 连接测试查询，HikariCP会自动检测，但显式指定更安全
      connection-test-query: SELECT 1

避坑指南：

  1. 不要盲目调大 maximum-pool-size。连接数越多，数据库端的CPU用于维护连接的开销越大。通常单机MySQL能稳定支撑的连接数在几百个左右，应用端每个实例的连接池保持在20-50个即可，通过增加应用实例数量来横向扩展并发能力。
  2. 监控连接池状态。接入Prometheus + Grafana，监控 hikaricp_connections_active（活跃连接数）和 hikaricp_connections_idle（空闲连接数）。如果活跃连接长期接近最大值，说明数据库成为瓶颈，需要进一步优化SQL或增加缓存。

第五道防线：数据库内核参数微调

除了应用层，MySQL自身的配置也能提升性能。

1. InnoDB Buffer Pool

这是MySQL最重要的内存区域，用于缓存数据和索引。

  • 配置建议：设置为物理内存的 50%-70%。
  • 检查方法：监控 Innodb_buffer_pool_reads（从磁盘读取次数）和 Innodb_buffer_pool_read_requests（总读取次数）。如果比率过高，说明Buffer Pool太小或热点数据没命中。

2. 日志刷盘策略

  • innodb_flush_log_at_trx_commit：
    • 1：每次事务提交都刷盘，数据安全，性能最差。
    • 2：每次事务提交写OS缓存，每秒刷盘一次。性能较好，断电最多丢失1秒数据。
    • 0：每秒写OS缓存并刷盘，性能最好，但断电可能丢失更多数据。
    • 建议：金融交易类业务保持 1；普通业务可尝试 2 以换取性能提升。

3. 开启慢查询日志

永远不要猜哪些SQL慢，让日志告诉你。

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1 # 超过1秒的SQL记录
log_queries_not_using_indexes = 1 # 记录未使用索引的查询

定期分析慢查询日志，使用 pt-query-digest 工具生成报告，找出Top N的慢SQL进行优化。

结语：没有银弹，只有组合拳

面对高并发，没有任何单一技术能解决所有问题。缓存解决了读压力，读写分离分担了主库负载，索引优化提升了单次查询效率，连接池控制了资源消耗，内核调优挖掘了底层潜力。

这套体系就像人体的免疫系统：皮肤（缓存）阻挡大部分病毒，白细胞（索引优化）消灭入侵者，血液循环（连接池）保证营养输送，器官协同（读写分离）各司其职。

最后，记住一点：监控先行，优化跟进。在没有数据支撑的情况下进行的优化，往往是徒劳甚至有害的。搭建好完善的监控告警体系，当流量洪峰来临时，你才能从容不迫，层层化解，让数据库稳稳地托住你的业务大厦。