正文

高并发下MySQL卡顿数据库撑不住怎么办 读写分离索引优化分库分表实战方案

/0 浏览量
0925

说实话，看到这个问题我脑子里立刻浮现出那种凌晨三点报警铃声狂响的噩梦场景。数据库CPU飙到100%，连接数爆表，前端接口排队超时，老板还站在你后面问“怎么这么慢”。别慌，这事儿咱一步步拆解，从最轻量的优化到最彻底的架构改造，我给你捋清楚每一条路该怎么走，以及每一步背后的逻辑。咱们不整那些虚头巴脑的理论，直接上干货。

先别急着动手，你得先知道瓶颈到底在哪

很多初级工程师遇到数据库卡顿，第一反应就是“加缓存”或者“上分布式”，但这往往是治标不治本。在动手之前，你必须像医生看病一样，先做诊断。MySQL提供了非常丰富的性能监控工具，你得学会看它们。

最直接的工具是SHOW PROCESSLIST，它能告诉你当前正在执行什么SQL。如果看到大量Sending data或Creating sort index状态，说明查询正在处理数据或排序，这是IO或CPU压力的直接体现。如果看到大量Locked状态，那就是锁竞争问题。

更专业一点，开启慢查询日志（Slow Query Log）是必须的。在my.cnf里配置：

[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  # 记录没走索引的查询

配合mysqldumpslow工具，你可以快速分析慢日志，找出那些最耗时的SQL语句。比如：

mysqldumpslow -s t -n 10 /var/log/mysql/slow.log

这能列出执行时间最长的前10条SQL，让你知道优先优化哪一把“砍刀”。

还有一个被低估的神器是EXPLAIN。任何一条疑似有问题的SELECT语句，在执行前加上EXPLAIN关键字，MySQL就会告诉你它打算怎么执行这条SQL。输出结果里的type字段尤为关键，它代表访问类型，从最坏到最好依次是：ALL（全表扫描）> index（全索引扫描）> range（索引范围扫描）> ref（非唯一索引扫描）> eq_ref（唯一索引扫描）> const（常量查询）。如果你的查询type是ALL，那基本上就是性能杀手，必须优化。

第一道防线：索引优化，最廉价也最有效的提速手段

索引是MySQL性能的基石。很多业务初期的SQL写得并不严谨，随着数据量增长，索引缺失或设计不当的问题就会爆发。

索引设计的黄金法则

最左前缀原则是多列索引的核心。假设你有一个联合索引(a, b, c)，那么查询条件必须从左到右依次匹配。比如WHERE a=1 AND b=2能用到索引，但WHERE b=2 AND c=3则完全用不上，因为跳过了a。这就像去图书馆找书，索引目录是按“作者-书名-出版社”排序的，你直接翻到“出版社”去找，效率自然低。

覆盖索引是另一个提升性能的神技。当查询的列正好都在索引里时，MySQL不需要回表查询主键索引，直接从索引树中获取数据，这就是Using index。比如：

-- 假设 user表有主键id，联合索引 idx_age_status(age, status)
-- 查询只需要 age 和 status，可以直接走覆盖索引
EXPLAIN SELECT age, status FROM user WHERE age = 25 AND status = 1;

执行计划里type是range，key是idx_age_status，Extra显示Using index，说明完全不需要回表，性能极佳。

避免在索引列上做函数运算或类型转换。这是新手最容易踩的坑。比如：

-- 错误示范：对索引列使用函数，导致索引失效
SELECT * FROM orders WHERE YEAR(create_time) = 2023;

-- 正确做法：将函数移到常量侧，或者使用范围查询
SELECT * FROM orders WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01';

同理，如果user_id是字符串类型，但查询时传入的是数字，MySQL会隐式转换，导致索引失效。比如WHERE user_id = 123，如果user_id是VARCHAR，应该写成WHERE user_id = '123'。

分页查询的性能陷阱

深分页是线上系统的常见痛点。当数据量达到百万级，LIMIT 100000, 10这种查询会极其缓慢。因为MySQL需要扫描并丢弃前100000行数据，然后才返回后面的10行。解决方案有两种。

第一种是延迟关联，先通过索引查到主键，再回表查询其他字段：

-- 优化前：深分页慢查询
SELECT * FROM orders LIMIT 100000, 10;

-- 优化后：延迟关联
SELECT o.* FROM orders o
INNER JOIN (SELECT id FROM orders LIMIT 100000, 10) t ON o.id = t.id;

第二种是游标分页，记录上一页最后一条数据的ID，下一页从这个ID之后开始查：

-- 假设上一页最后一条数据的id是100000
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 10;

这种方式性能稳定，不随数据量增加而变慢，非常适合列表页这种场景。

第二道防线：读写分离，分担数据库压力

当读多写少成为常态，数据库的CPU往往被读请求打满，写操作反而排队等待。这时候，读写分离是最直接的解决方案。主库负责写操作，从库负责读操作，通过MySQL的主从复制机制保持数据同步。

主从复制原理与配置

MySQL主从复制的核心是-binlog（二进制日志）。主库将所有的写操作记录到binlog中，从库通过I/O线程读取binlog并写入本地的relay log，再由SQL线程重放relay log中的事件，从而保持数据一致。

配置相对简单，主库需要开启binlog：

[mysqld]
server-id=1
log-bin=mysql-bin
binlog-format=ROW  # 推荐ROW格式，安全性更高

从库配置：

[mysqld]
server-id=2

然后在主库创建复制用户：

CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

最后在从库执行CHANGE MASTER TO：

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可以查看同步状态，Seconds_Behind_Master字段显示从库落后主库的秒数，这个值越小越好。

应用层路由策略

读写分离不能只靠数据库配置，应用层需要配合实现。最常见的方案是使用中间件，比如官方推荐的MySQL Router，或者第三方的MyCat、ShardingSphere。这些中间件会根据SQL类型自动路由：写请求去主库，读请求去从库。

如果你不想引入额外组件，也可以在代码层面实现。用一个简单的数据源路由类：

public class DataSourceRouter {
    private static final ThreadLocal<Boolean> writeOnly = new ThreadLocal<>();
    
    public static void setWriteDataSource() {
        writeOnly.set(true);
    }
    
    public static void setReadDataSource() {
        writeOnly.set(false);
    }
    
    public static boolean isWriteDataSource() {
        Boolean value = writeOnly.get();
        return value != null && value;
    }
    
    public static DataSource getDataSource() {
        return isWriteDataSource() ? writeDataSource : readDataSource;
    }
}

使用时在Service层标记：

public class OrderService {
    public void createOrder(Order order) {
        DataSourceRouter.setWriteDataSource();
        orderMapper.insert(order);
        DataSourceRouter.setReadDataSource(); // 确保后续查询走从库
    }
    
    public Order getOrder(Long id) {
        DataSourceRouter.setReadDataSource();
        return orderMapper.selectById(id);
    }
}

需要注意的是，主从复制存在延迟，极端情况下刚写入的数据可能还没来得及同步到从库，这时候读从库会读到旧数据。对于强一致性的场景，比如支付成功后立即查询订单，必须强制读主库。可以通过在关键操作后调用DataSourceRouter.setWriteDataSource()来保证。

第三道防线：缓存层介入，减少数据库访问

读写分离解决了横向扩展读的问题，但如果缓存命中率不高，或者缓存穿透、击穿、雪崩，数据库依然会承受不住。这时候需要引入Redis等缓存层。

缓存架构设计

经典的缓存架构是Cache-Aside模式：读时先查缓存，命中则返回，未命中则查数据库并写入缓存；写时先更新数据库，再删除缓存（不是更新缓存，避免并发问题）。

public class CacheService {
    private static final long DEFAULT_EXPIRE = 3600L;
    
    public Object get(String key, Function<String, Object> dbLoader) {
        // 1. 查缓存
        Object value = redis.get(key);
        if (value != null) {
            return value;
        }
        
        // 2. 缓存未命中，查数据库
        value = dbLoader.apply(key);
        if (value != null) {
            // 3. 写入缓存，设置过期时间
            redis.set(key, value, DEFAULT_EXPIRE, TimeUnit.SECONDS);
        }
        return value;
    }
    
    public void put(String key, Object value) {
        // 先更新数据库
        database.update(key, value);
        // 再删除缓存
        redis.delete(key);
    }
}

缓存三大问题的应对

缓存穿透是指查询不存在的数据，缓存和数据库都查不到，恶意攻击者可以无限访问。解决方案是布隆过滤器或者缓存空值。缓存空值最简单，将不存在的key也写入缓存，设置较短的过期时间（如60秒），避免重复查询。

public Object get(String key, Function<String, Object> dbLoader) {
    Object value = redis.get(key);
    if (value == null) {
        // 缓存为空，可能是穿透
        if (redis.exists(key)) {
            // 过期了，重新加载
            value = dbLoader.apply(key);
            redis.set(key, value, DEFAULT_EXPIRE, TimeUnit.SECONDS);
        } else {
            // 确实不存在，缓存空值
            redis.set(key, null, 60, TimeUnit.SECONDS);
            return null;
        }
    }
    return value;
}

缓存击穿是指热点key在过期瞬间，大量请求同时打到数据库。解决方案是加互斥锁，只有一个线程去查数据库，其他线程等待或重试。

public Object getWithLock(String key, Function<String, Object> dbLoader) {
    Object value = redis.get(key);
    if (value == null) {
        // 尝试获取锁
        if (redis.setnx("lock:" + key, 1, 10, TimeUnit.SECONDS)) {
            try {
                // 双重检查，防止并发重复加载
                value = redis.get(key);
                if (value == null) {
                    value = dbLoader.apply(key);
                    redis.set(key, value, DEFAULT_EXPIRE, TimeUnit.SECONDS);
                }
            } finally {
                redis.delete("lock:" + key);
            }
        } else {
            // 等待后重试
            Thread.sleep(50);
            return getWithLock(key, dbLoader);
        }
    }
    return value;
}

缓存雪崩是指大量key同时过期，导致缓存大面积失效，数据库瞬间压力剧增。解决方案是设置不同的过期时间（比如基础时间+随机值），或者使用多级缓存（本地缓存+Redis缓存）。

终极方案：分库分表，打破单机瓶颈

当单表数据量超过千万级，或者单库QPS超过万级，即使做了索引优化和读写分离，性能依然会瓶颈。这时候，分库分表是必然选择。

分表策略：水平拆分

分表的核心思想是将一个大表拆分成多个小表，数据分布在不同的表或不同的库中。常见的拆分方式有取模拆分、范围拆分、哈希拆分等。

以用户表为例，假设用户ID是长整型，可以采用取模拆分：

-- 拆分为16张表
CREATE TABLE user_0 LIKE user;
CREATE TABLE user_1 LIKE user;
-- ... 省略中间...
CREATE TABLE user_15 LIKE user;

路由规则：table_index = userId % 16，比如用户ID为10001，则写入user_9表。

取模拆分的优点是数据分布均匀，缺点是新增表需要重新计算路由，迁移成本高。范围拆分是按ID范围划分，比如1-100万放一张表，100万-200万放另一张，优点是查询范围数据方便，缺点是热点数据可能集中在某几张表。

分库策略：多实例部署

分库是将不同业务模块或不同数据范围的表拆分到不同的数据库实例上。比如订单系统单独一个库，用户系统单独一个库，这样可以分散单实例的压力。

分库后，跨库查询成为难题。解决方案有几种：

  1. 应用层组装：在代码中分别查询多个库，然后在内存中JOIN。适合数据量不大的场景。
public OrderVO getOrderDetail(Long orderId) {
    // 查询订单信息
    Order order = orderMapper.selectById(orderId);
    
    // 查询用户信息（可能在另一个库）
    User user = userMapper.selectById(order.getUserId());
    
    // 组装结果
    OrderVO vo = new OrderVO();
    vo.setOrderId(order.getId());
    vo.setProductName(order.getProductName());
    vo.setUserName(user.getName());
    vo.setUserPhone(user.getPhone());
    return vo;
}
  1. 冗余字段：在订单表中冗余存储用户名、用户手机号等常用字段，避免跨库查询。牺牲一点存储和一致性，换取查询性能。
ALTER TABLE orders ADD COLUMN user_name VARCHAR(50);
ALTER TABLE orders ADD COLUMN user_phone VARCHAR(20);
  1. ES或数据仓库：将需要复杂查询的数据同步到Elasticsearch或ClickHouse等专门的分析引擎中，MySQL只负责核心交易。

分库分表中间件选择

自己实现分库分表路由逻辑复杂度很高，建议使用成熟中间件。ShardingSphere是目前最流行的开源解决方案，支持分库分表、读写分离、分布式事务等功能。

配置示例（YAML格式）：

dataSources:
  ds_0:
    url: jdbc:mysql://host1:3306/db0
    username: root
    password: password
  ds_1:
    url: jdbc:mysql://host2:3306/db1
    username: root
    password: password

rules:
- !SHARDING
  tables:
    orders:
      actualDataNodes: ds_$->{0..1}.orders_$->{0..3}
      tableStrategy:
        standard:
          shardingColumn: user_id
          shardingAlgorithmName: inline-table
      keyGenerateStrategy:
        column: id
        keyGeneratorName: snowflake
  shardingAlgorithms:
    inline-table:
      type: INLINE
      props:
        algorithm-expression: orders_$->{user_id % 4}
  keyGenerators:
    snowflake:
      type: SNOWFLAKE

这个配置将orders表拆分到4张子表，分布在两个数据库中，按user_id % 4路由。雪花算法生成全局唯一ID，避免分布式ID冲突。

ShardingSphere还支持分布式事务，基于Seata或X/OpenXA，对于金融级业务很有必要。

监控与运维：长治久安的关键

架构改造不是一劳永逸的，持续的监控和调优才能保证系统稳定。

MySQL的Performance Schema和sys schema提供了丰富的性能视图。sys.schema_unused_indexes可以找出没有用到的索引，及时清理可以减少写入开销。sys.host_summary可以按主机统计IO和CPU消耗。

推荐使用Percona Monitoring and Management (PMM)或者Prometheus + Grafana搭建监控体系。关键指标包括：

  • QPS/TPS：每秒查询数和事务数，反映数据库负载
  • 慢查询数：超过阈值时间的查询数量
  • 连接数：当前连接数和最大连接数，防止连接打满
  • 缓冲池命中率：InnoDB Buffer Pool的命中率，理想值应大于95%
  • 主从延迟：Seconds_Behind_Master，防止数据不一致
  • 锁等待：InnoDB锁等待次数，反映并发冲突程度

定期执行OPTIMIZE TABLE可以重建索引，减少碎片，但需要在低峰期进行，因为会锁表。更温和的方式是使用ALTER TABLE ... ALGORITHM=INPLACE, CHANGE COLUMN ...，在MySQL 5.6+中支持在线DDL，对业务影响较小。

实战案例：一个电商订单系统的演进之路

让我给你讲一个真实的演进故事，帮助大家理解这些技术是如何组合使用的。

某电商平台初期只有一个MySQL实例，订单表单表设计，包含用户ID、商品信息、金额、状态等字段。初期用户少，系统运行平稳。随着业务增长，订单表突破5000万行，查询变慢，高峰期经常超时。

第一阶段：索引优化。开发团队发现大部分查询都走user_id和

-- 展开阅读全文 --