数据库层面优化：索引优化策略、SQL语句重构、读写分离架构、分库分表方案、连接池参数调优

各位同学，咱们直接进入正题。数据库优化，说白了就是跟慢查询死磕。我做了十几年高并发系统，见过太多系统被数据库拖垮的案例。今天这一章，我把压箱底的经验掏出来，咱们一个一个说。

一、索引优化策略

索引这东西，用好了是神器，用不好就是累赘。我个人习惯，上线前必做一次索引评审。

1. 复合索引的字段顺序

这是最容易被忽视的点。你想想看，复合索引就像一本电话簿——先按姓氏排，再按名字排。如果你查“叫张三的人”，姓氏是精确匹配，名字是范围查询，那索引效率就高。反过来，如果你跳过姓氏直接查名字，索引就废了。

核心原则：等值条件放前面，范围条件放后面。

-- 错误示范
CREATE INDEX idx_status_time ON orders(status, create_time);
-- 查询：WHERE status = 1 AND create_time > '2024-01-01'
-- 这样写没问题，但如果查询是 WHERE create_time > '2024-01-01'，索引就失效了

-- 正确做法
-- 根据实际查询模式，把最常用的等值条件放前面

2. 覆盖索引

我在项目中遇到过这样一个场景：一个查询要返回10个字段，但索引只覆盖了3个。结果每次查询都要回表，性能直接掉了80%。

解决办法？把查询需要的字段都塞进索引里。这叫覆盖索引，查询时不需要回表，速度飞快。

-- 假设查询经常需要：user_id, order_id, amount, status
-- 创建覆盖索引
CREATE INDEX idx_cover ON orders(user_id, order_id, amount, status);
-- 这样查询时，索引本身就能提供所有数据，无需回表

小技巧：用 EXPLAIN 看 Extra 列，如果显示 "Using index"，说明用上了覆盖索引。如果显示 "Using index condition"，说明还有回表操作。

3. 索引下推

MySQL 5.6 引入的特性，很多人不知道。简单说，就是存储引擎在索引层面先过滤掉不符合条件的行，减少回表次数。

嗯，这里要注意：索引下推只对二级索引有效。我见过有人建了索引，但查询还是很慢，一查发现是没开启索引下推。默认是开启的，但有些老版本或者特殊配置会关掉。

-- 检查是否开启
SHOW VARIABLES LIKE 'optimizer_switch';
-- 找到 index_condition_pushdown=on 就是开启了

二、SQL语句重构

SQL写得好不好，直接决定数据库的生死。我见过最离谱的，一个查询跑了30秒，重构后变成0.1秒。

1. 避免SELECT *

这个我说过无数次了。你想想看，你只需要两个字段，却把整行数据都拉回来，网络传输、内存占用都是浪费。

-- 错误
SELECT * FROM users WHERE id = 100;

-- 正确
SELECT id, name, email FROM users WHERE id = 100;

2. 分页优化

传统的 LIMIT OFFSET 分页，越往后越慢。为什么？因为数据库要扫描前面所有的行，然后扔掉。

我曾经优化过一个后台报表，翻到第100页要等5秒。改成游标分页后，秒开。

-- 传统分页（越往后越慢）
SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 100000;

-- 游标分页（推荐）
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 10;

3. 避免隐式类型转换

这个坑我踩过。字段是 VARCHAR 类型，传参传了个数字，MySQL 会做隐式转换，索引就失效了。

-- 错误（phone是varchar类型）
SELECT * FROM users WHERE phone = 13800138000;

-- 正确
SELECT * FROM users WHERE phone = '13800138000';

避坑指南：我曾经因为这个问题，导致一个核心查询从毫秒级变成秒级。排查了半天，最后发现是传参类型不对。从那以后，我要求团队所有SQL参数必须显式指定类型。

三、读写分离架构

高并发系统，读写分离是标配。说白了，就是把写操作（INSERT/UPDATE/DELETE）发给主库，读操作（SELECT）发给从库。

1. 主从延迟问题

这是读写分离最大的坑。你刚写完数据，立刻去读，结果从库还没同步过来，读到的还是旧数据。

我个人的解决方案：

  • 强制读主库：对于实时性要求高的场景，比如支付结果查询，直接读主库
  • 延迟容忍：对于报表、统计类查询，可以接受几秒的延迟
  • 缓存兜底：用Redis缓存刚写入的数据，避免直接读从库

2. 负载均衡策略

从库多了，怎么分配读请求？轮询？随机？最少连接？

我建议用最少连接数策略。因为每个从库的负载不一样，轮询可能导致某些从库过载。

-- 以ProxySQL为例
-- 配置查询路由规则
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (1, 1, '^SELECT', 1, 1);
-- 这样所有SELECT请求都路由到从库组（hostgroup 1）

四、分库分表方案

当单表数据量超过千万，或者QPS超过几千，就该考虑分库分表了。

1. 垂直分库

按业务模块拆分。比如用户库、订单库、商品库。这个比较简单，但要注意跨库事务的问题。

2. 水平分表

按某个字段取模分片。最常见的是按用户ID分片。

-- 分片规则：user_id % 16
-- 查询时带上分片键
SELECT * FROM orders_8 WHERE user_id = 12345;
-- 如果不带分片键，就要全库扫描，性能极差

核心原则：分片键必须出现在查询条件中，否则就是灾难。

3. 分库分表中间件

我推荐 ShardingSphere 或 MyCat。ShardingSphere 更轻量，支持多种分片策略。

-- ShardingSphere 配置示例
sharding:
  tables:
    orders:
      actualDataNodes: ds$->{0..1}.orders_$->{0..15}
      tableStrategy:
        inline:
          shardingColumn: user_id
          algorithmExpression: orders_$->{user_id % 16}

五、连接池参数调优

连接池是数据库的第一道防线。参数调不好，再好的SQL也白搭。

1. 核心参数

参数 说明 推荐值
maximumPoolSize 最大连接数 CPU核心数 * 2 + 1
minimumIdle 最小空闲连接 maximumPoolSize的10%-20%
connectionTimeout 连接超时时间 30000ms（30秒）
idleTimeout 空闲连接超时 600000ms（10分钟）
maxLifetime 连接最大存活时间 1800000ms（30分钟）

2. 连接数不是越多越好

很多人以为连接数越大越好，其实不然。连接数过多，数据库上下文切换开销巨大，反而降低性能。

我记得有一次，一个团队把连接池设成200，结果数据库CPU直接飙到100%。我让他们降到50，性能反而提升了30%。

调优建议：从CPU核心数 * 2开始，逐步增加，观察数据库的活跃连接数和响应时间。找到拐点，那就是最佳值。

3. 连接池监控

HikariCP 提供了丰富的监控指标。我习惯在监控面板上加上这几个指标：

  • 活跃连接数
  • 等待获取连接的线程数
  • 连接获取平均耗时
  • 连接超时次数

一旦发现等待线程数持续增加，或者连接获取耗时变长，就要考虑扩容或者优化SQL了。

// Spring Boot 配置 HikariCP
spring:
  datasource:
    hikari:
      maximum-pool-size: 50
      minimum-idle: 10
      connection-timeout: 30000
      idle-timeout: 600000
      max-lifetime: 1800000
      pool-name: MyPool
      # 开启监控
      metrics-enabled: true

好了，数据库层面的优化就讲到这里。下一章咱们聊聊缓存策略，那可是高并发系统的另一大杀器。

← 上一章 📖 返回目录 下一章 →