正文

MySQL高并发处理策略从索引优化读写分离到缓存层设计实战避坑指南

/0 浏览量
0920

MySQL高并发处理策略从索引优化读写分离到缓存层设计实战避坑指南

先聊聊高并发这个”烫手山芋”

去年我给一家做社区团购的小公司做了数据库改造，他们的MySQL服务器一到晚上七八点就卡成PPT，老板急得跳脚。我接过来一看，好家伙，光表就几百张，没几张建了索引，查询全走全表扫描。那时候我才意识到，高并发这事儿真不是加服务器就能解决的，得从根儿上梳理。

今天咱们就掰开揉碎了聊聊，怎么让MySQL在高并发场景下跑得飞快。

索引优化：别小看这一招

为什么要索引？

打个比方，你要在字典里找一个字，你是直接翻还是先看目录？索引就是数据库的目录。没有索引，MySQL只能全表扫描，数据量一大，查询就像大海捞针。

实战：一个真实的慢查询案例

我见过最典型的问题是这样的：

-- 这张订单表有500万数据，每次查询都要等好几分钟
SELECT * FROM orders 
WHERE user_id = 12345 
AND status = 1 
AND create_time > '2024-01-01'
ORDER BY create_time DESC
LIMIT 20;

解决方案：联合索引

-- 错误示范：单独建索引
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_status ON orders(status);
CREATE INDEX idx_create_time ON orders(create_time);

-- 正确做法：建联合索引，遵循最左前缀原则
CREATE INDEX idx_user_status_time ON orders(user_id, status, create_time);

为什么这样建？

最左前缀原则的意思是，你查询的时候必须从左往右用这个索引。比如：

  • WHERE user_id = ? ✅ 能用上索引
  • WHERE user_id = ? AND status = ? ✅ 能用上索引
  • WHERE status = ? ❌ 用不上索引

覆盖索引：让查询快得飞起

-- 如果只需要这几个字段，可以用覆盖索引
-- 这样MySQL直接从索引里取数据，不用回表查整行
SELECT id, user_id, status, create_time 
FROM orders 
WHERE user_id = 12345 
AND status = 1 
AND create_time > '2024-01-01'
ORDER BY create_time DESC
LIMIT 20;

避开索引的坑

  1. 不要在索引列上做函数运算
-- 错误：会导致索引失效
SELECT * FROM orders WHERE YEAR(create_time) = 2024;

-- 正确：用范围查询
SELECT * FROM orders WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';
  1. 不要在索引列上使用类型转换
-- 错误：user_id是字符串类型，但查询用了数字
SELECT * FROM orders WHERE user_id = 12345;  -- user_id是varchar类型

-- 正确
SELECT * FROM orders WHERE user_id = '12345';
  1. LIKE查询要以常量开头
-- 错误：前缀通配符会导致索引失效
SELECT * FROM orders WHERE user_name LIKE '%张三%';

-- 正确
SELECT * FROM orders WHERE user_name LIKE '张三%';

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

什么是读写分离？

想象一下餐厅：厨师负责做菜（写操作），服务员负责端菜（读操作）。如果让厨师同时干两份活，那肯定忙不过来。读写分离就是把写操作放到主库，读操作放到从库。

架构示意图

                    ┌─────────────┐
                    │   应用层     │
                    └──────┬──────┘
                           │
              ┌────────────┼────────────┐
              │            │            │
        ┌─────┴─────┐ ┌───┴───┐ ┌─────┴─────┐
        │  主库(写)   │ │从库1  │ │  从库2    │
        │  Master   │ │Slave  │ │  Slave   │
        └───────────┘ └───────┘ └───────────┘

实战配置：MySQL主从复制

主库配置（my.cnf）：

[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW
expire_logs_days = 7
max_binlog_size = 100M

从库配置（my.cnf）：

[mysqld]
server-id = 2
relay-log = mysql-relay-bin
read-only = 1  -- 从库只读，防止误写

创建复制账号：

-- 在主库上执行
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

启动从库复制：

-- 在从库上执行
CHANGE MASTER TO
    MASTER_HOST = '主库IP',
    MASTER_USER = 'repl',
    MASTER_PASSWORD = 'password',
    MASTER_LOG_FILE = 'mysql-bin.000001',
    MASTER_LOG_POS = 154;

START SLAVE;
SHOW SLAVE STATUS\G

应用层改造：路由读写

这里给个Java的示例：

// 使用路由策略，写操作走主库，读操作走从库
public class DataSourceRouter extends AbstractRoutingDataSource {
    
    @Override
    protected Object determineCurrentLookupKey() {
        // 从ThreadLocal中获取当前数据源
        String dataSource = DataSourceContextHolder.getDataSource();
        
        if ("write".equals(dataSource)) {
            return "master";  // 写操作走主库
        } else {
            return "slave";   // 读操作走从库
        }
    }
}

// 拦截写操作，自动切换到主库
@Aspect
@Component
public class DataSourceAspect {
    
    @Around("execution(* com.example.service.*.*(..))")
    public Object around(ProceedingJoinPoint point) throws Throwable {
        MethodSignature signature = (MethodSignature) point.getSignature();
        String methodName = signature.getMethod().getName();
        
        // 判断是否是写操作方法
        if (methodName.startsWith("insert") || 
            methodName.startsWith("update") || 
            methodName.startsWith("delete") ||
            methodName.startsWith("save")) {
            DataSourceContextHolder.setDataSource("write");
        } else {
            DataSourceContextHolder.setDataSource("read");
        }
        
        try {
            return point.proceed();
        } finally {
            DataSourceContextHolder.clearDataSource();
        }
    }
}

读写分离的坑

  1. 主从延迟问题：主库写完后，从库可能还没同步过来，这时候读到的数据可能是旧的。

    • 解决方案：关键数据（比如订单状态）强制走主库查询
    • 或者设置read_from_master = true

  2. 从库太多会增加同步延迟：一般建议2-3个从库就够了

  3. 从库也要做好监控：用SHOW SLAVE STATUS查看延迟情况

缓存层设计：给数据库减负

为什么要缓存？

缓存就像你书包里的课本，经常用的放外面，不常用的放家里。把热点数据放在Redis里，查询速度能从几百毫秒降到几毫秒。

经典缓存架构

用户请求
    │
    ▼
┌─────────┐     缓存命中     ┌─────────┐
│  应用层   │ ──────────────► │  Redis   │
└────┬────┘                  └─────────┘
     │ 缓存未命中
     ▼
┌─────────┐     查询结果     ┌─────────┐
│  MySQL   │ ◄────────────── │  Redis   │
│  数据库   │                 └─────────┘
└─────────┘                      │
    │                    写回缓存
    ▼
┌─────────┐
│ 响应数据  │
└─────────┘

缓存策略选择

方案一：Cache-Aside（旁路缓存）

// 最常见的缓存策略
public Order getOrder(Long orderId) {
    // 1. 先查缓存
    String cacheKey = "order:" + orderId;
    Order order = redisClient.get(cacheKey);
    
    if (order != null) {
        return order;  // 缓存命中，直接返回
    }
    
    // 2. 缓存未命中，查数据库
    order = orderMapper.selectById(orderId);
    
    if (order != null) {
        // 3. 写入缓存，设置过期时间
        redisClient.setex(cacheKey, 3600, JSON.toJSONString(order));
    }
    
    return order;
}

方案二：Read-Through（读写穿透）

// 缓存和数据库绑定，透明化处理
public class CacheLoader implements CacheLoader<String, Order> {
    @Override
    public Order load(String key) {
        Long orderId = Long.parseLong(key.replace("order:", ""));
        return orderMapper.selectById(orderId);
    }
}

// 使用示例
Cache<String, Order> cache = Caffeine.newBuilder()
    .expireAfterWrite(10, TimeUnit.MINUTES)
    .maximumSize(10000)
    .build(new CacheLoader<String, Order>() {
        @Override
        public Order load(String key) {
            Long orderId = Long.parseLong(key.replace("order:", ""));
            return orderMapper.selectById(orderId);
        }
    });

方案三：Write-Through（写穿透）

// 写数据时同时更新缓存
public void updateOrder(Order order) {
    // 1. 更新数据库
    orderMapper.updateById(order);
    
    // 2. 同步更新缓存
    String cacheKey = "order:" + order.getId();
    redisClient.setex(cacheKey, 3600, JSON.toJSONString(order));
}

实战：Redis配置

# Redis配置示例
bind 127.0.0.1
port 6379
requirepass yourpassword
maxmemory 2gb
maxmemory-policy allkeys-lru

# 持久化配置
appendonly yes
appendfsync everysec

缓存设计的坑

坑1：缓存穿透

// 问题：查询不存在的数据，每次都打到数据库
// 解决：缓存空值
public Order getOrder(Long orderId) {
    String cacheKey = "order:" + orderId;
    
    // 先查缓存
    Object result = redisClient.get(cacheKey);
    
    if (result == null) {
        // 数据库查询
        Order order = orderMapper.selectById(orderId);
        
        if (order == null) {
            // 缓存空值，防止穿透
            redisClient.setex(cacheKey, 60, "");
            return null;
        }
        
        // 缓存有效数据
        redisClient.setex(cacheKey, 3600, JSON.toJSONString(order));
        return order;
    }
    
    // 处理空值情况
    if ("".equals(result)) {
        return null;
    }
    
    return JSON.parseObject((String) result, Order.class);
}

坑2：缓存击穿

// 问题：热点key过期，大量请求打到数据库
// 解决：使用分布式锁
public Order getOrderWithLock(Long orderId) {
    String cacheKey = "order:" + orderId;
    
    // 先查缓存
    Order order = (Order) redisClient.get(cacheKey);
    if (order != null) {
        return order;
    }
    
    // 获取分布式锁
    String lockKey = "lock:" + cacheKey;
    boolean locked = redisClient.setnx(lockKey, "1", 5);
    
    if (locked) {
        try {
            // 双重检查
            order = (Order) redisClient.get(cacheKey);
            if (order != null) {
                return order;
            }
            
            // 查数据库
            order = orderMapper.selectById(orderId);
            
            // 写入缓存
            if (order != null) {
                redisClient.setex(cacheKey, 3600, JSON.toJSONString(order));
            }
            
            return order;
        } finally {
            // 释放锁
            redisClient.del(lockKey);
        }
    } else {
        // 没抢到锁， sleep后重试
        Thread.sleep(50);
        return getOrderWithLock(orderId);
    }
}

坑3：缓存雪崩

// 问题：大量key同时过期
// 解决：过期时间加随机值
public void cacheOrder(Order order) {
    String cacheKey = "order:" + order.getId();
    
    // 基础过期时间 + 随机时间（防止同时过期）
    int expireTime = 3600 + new Random().nextInt(300);
    
    redisClient.setex(cacheKey, expireTime, JSON.toJSONString(order));
}

坑4：缓存一致性

// 更新数据库后，必须删除缓存（不是更新缓存）
public void updateOrder(Order order) {
    // 1. 更新数据库
    orderMapper.updateById(order);
    
    // 2. 删除缓存（让下次查询时重新加载）
    String cacheKey = "order:" + order.getId();
    redisClient.del(cacheKey);
}

连接池配置：别让数据库累死

连接池的重要性

连接池就像公交车，一次性拉很多人，比每个人坐出租车效率高得多。

Druid连接池配置

spring:
  datasource:
    druid:
      # 初始连接数
      initial-size: 5
      # 最小空闲连接数
      min-idle: 10
      # 最大活跃连接数
      max-active: 50
      # 获取连接超时时间（毫秒）
      max-wait: 3000
      # 检测连接是否有效的SQL
      validation-query: SELECT 1
      # 空闲连接是否检测
      test-while-idle: true
      # 借出连接时是否检测
      test-on-borrow: false
      # 归还连接时是否检测
      test-on-return: false
      # 间隔多久检测一次需要关闭的空闲连接
      time-between-eviction-runs-millis: 60000
      # 连接保持空闲而不被驱逐的最长时间
      min-evictable-idle-time-millis: 300000

分库分表：终极武器

什么时候需要分库分表？

当单表数据超过500万，或者单库QPS超过1万时，就要考虑分库分表了。

ShardingSphere实战

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

spring:
  shardingsphere:
    datasource:
      names: ds0,ds1
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/db0
        username: root
        password: password
      ds1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/db1
        username: root
        password: password
    rules:
      sharding:
        tables:
          orders:
            actual-data-nodes: ds$->{0..1}.orders_$->{0..1}
            table-strategy:
              standard:
                sharding-column: order_id
                sharding-algorithm-name: orders-table-inline
            database-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: users-db-inline
        sharding-algorithms:
          users-db-inline:
            type: INLINE
            props:
              algorithm-expression: ds$->{user_id % 2}
          orders-table-inline:
            type: INLINE
            props:
              algorithm-expression: orders_$->{order_id % 2}

分库分表的坑

  1. 跨库查询困难：跨分片的查询性能会下降，需要避免
  2. 全局ID问题：使用雪花算法生成唯一ID
  3. 分片键选择：要根据业务查询场景选择分片键

监控与优化：知己知彼

关键监控指标

-- 查看当前连接数
SHOW STATUS LIKE 'Threads_connected';

-- 查看慢查询
SHOW VARIABLES LIKE 'slow_query_log%';

-- 查看表锁情况
SHOW STATUS LIKE 'Table_locks%';

-- 查看InnoDB状态
SHOW ENGINE INNODB STATUS;

-- 查看索引使用情况
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    CARDINALITY
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'your_database'
ORDER BY TABLE_NAME, INDEX_NAME;

慢查询日志分析

# 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  # 超过1秒的查询记录

# 使用mysqldumpslow分析
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

总结：高并发处理的组合拳

高并发处理不是单一技术能解决的，需要打组合拳：

策略 适用场景 效果
索引优化 查询慢 提升5-10倍
读写分离 读多写少 提升2-3倍
缓存层 热点数据 提升10-100倍
连接池 连接频繁 提升2-5倍
分库分表 数据量大 水平扩展

记住，没有银弹。要根据你的业务场景，选择合适的组合策略。最重要的是：先监控，再优化，不要盲目上手段。

希望这篇文章能帮你在高并发的战场上少踩坑，多立功！

-- 展开阅读全文 --