第 57 章：数据源调优
学习目标
- 理解连接池的工作原理
- 掌握 HikariCP 关键参数
- 学会慢 SQL 定位与索引优化
一、为什么需要连接池？
连接池：预先建好一批连接放着复用，用完还回池子而不是关闭。
性能差距：不用连接池时，一次数据库操作 90% 以上的时间花在建连接上。
二、HikariCP 配置
Spring Boot 2.0+ 默认就是 HikariCP（号称最快的 Java 连接池）。
yaml
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/taskflow?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true&cachePrepStmts=true&prepStmtCacheSize=250&prepStmtCacheSqlLimit=2048
username: root
password: ${DB_PASSWORD}
hikari:
pool-name: TaskflowHikariCP
minimum-idle: 10 # ① 最小空闲连接
maximum-pool-size: 20 # ② 最大连接数 ← 最关键的参数
idle-timeout: 600000 # ③ 空闲连接超时 10 分钟
max-lifetime: 1740000 # ④ 连接最大存活 29 分钟
connection-timeout: 30000 # ⑤ 获取连接超时 30 秒
validation-timeout: 5000
connection-test-query: SELECT 1
auto-commit: true
leak-detection-threshold: 60000 # ⑥ 连接泄漏检测（超过 60 秒未归还就告警）关键参数解读
② maximum-pool-size 怎么定？
连接数 = CPU 核数 × 2 + 有效磁盘数这是 PostgreSQL 官方的经验公式。8 核 + SSD → 约 17-20。
⚠️ 反直觉但重要：连接数不是越大越好。
- 数据库能真正并行处理的请求受限于 CPU 核数和磁盘 IO
- 连接数过多会导致数据库上下文切换开销剧增，吞吐反而下降
- 100 个连接的性能常常不如 20 个连接
实测方法：从 20 开始压测，逐步加大，观察 QPS 的拐点。
④ max-lifetime 为什么是 29 分钟？
MySQL 的 wait_timeout 默认 8 小时，但很多生产环境（尤其是有 LVS/HAProxy/云 RDS）会在 30 分钟或 60 分钟强制断开空闲连接。
如果连接池不知道连接已被服务端关掉，拿出来用时就会报 Communications link failure。
规则：
max-lifetime必须比数据库的wait_timeout和网络设备的空闲超时都小几分钟。
sql
-- 查看 MySQL 配置
SHOW VARIABLES LIKE 'wait_timeout'; -- 默认 28800 秒（8 小时）
SHOW VARIABLES LIKE 'interactive_timeout';⑥ leak-detection-threshold
连接借出后超过这个时间没归还，就打印堆栈告警。能帮你找出忘记关闭连接的代码。
生产建议开启，设为 60 秒。正常的 SQL 不可能执行 1 分钟。
minimum-idle 设成和 maximum-pool-size 一样？
HikariCP 官方推荐这么做：固定大小的池子性能更稳定，避免频繁创建销毁连接带来的延迟毛刺。
三、连接池监控
java
@Component
@RequiredArgsConstructor
@Slf4j
public class HikariMonitor {
private final DataSource dataSource;
@Scheduled(fixedDelay = 60000)
public void monitor() {
if (dataSource instanceof HikariDataSource hikari) {
HikariPoolMXBean pool = hikari.getHikariPoolMXBean();
log.info("连接池 | 总数={} 活跃={} 空闲={} 等待线程={}",
pool.getTotalConnections(),
pool.getActiveConnections(), // ① 正在使用的
pool.getIdleConnections(),
pool.getThreadsAwaitingConnection()); // ② 最重要！
// ③ 有线程在等连接 = 连接池不够用了
if (pool.getThreadsAwaitingConnection() > 0) {
log.warn("连接池告急，有 {} 个线程在等待连接",
pool.getThreadsAwaitingConnection());
}
}
}
}接入 Prometheus：
yaml
management:
endpoints:
web:
exposure:
include: health,metrics,prometheus
metrics:
tags:
application: ${spring.application.name}关键指标：hikaricp_connections_active、hikaricp_connections_pending、hikaricp_connections_timeout_total。
四、慢 SQL 定位
开启 MySQL 慢查询日志
sql
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- 超过 1 秒记录
SET GLOBAL log_queries_not_using_indexes = 'ON'; -- 未走索引的也记录
SHOW VARIABLES LIKE 'slow_query_log_file';bash
# 分析慢查询日志（MySQL 自带工具）
mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log
# 更强大的工具（Percona Toolkit）
pt-query-digest /var/lib/mysql/slow.log应用层拦截慢 SQL
java
@Intercepts({
@Signature(type = StatementHandler.class, method = "query",
args = {Statement.class, ResultHandler.class}),
@Signature(type = StatementHandler.class, method = "update",
args = {Statement.class})
})
@Slf4j
@Component
public class SlowSqlInterceptor implements Interceptor {
private static final long THRESHOLD_MS = 1000;
@Override
public Object intercept(Invocation invocation) throws Throwable {
long start = System.currentTimeMillis();
try {
return invocation.proceed();
} finally {
long cost = System.currentTimeMillis() - start;
if (cost > THRESHOLD_MS) {
StatementHandler handler = (StatementHandler) invocation.getTarget();
BoundSql boundSql = handler.getBoundSql();
log.warn("慢 SQL [{}ms]: {}", cost,
boundSql.getSql().replaceAll("\\s+", " "));
}
}
}
}MyBatis-Plus 也自带
p6spy集成，可以打印带真实参数的完整 SQL 和耗时。仅用于开发环境（有性能开销）。
五、EXPLAIN 分析
sql
EXPLAIN SELECT * FROM sys_user WHERE username = '张三';| 列 | 关注点 |
|---|---|
type | 最重要。system > const > eq_ref > ref > range > index > ALL |
key | 实际用的索引，NULL 说明没走索引 |
rows | 预估扫描行数，越小越好 |
filtered | 过滤后剩余百分比 |
Extra | Using index（覆盖索引，好）、Using filesort（额外排序，差）、Using temporary（临时表，差） |
红线：
type = ALL（全表扫描）+rows很大 → 必须优化。
sql
-- 优化前
EXPLAIN SELECT * FROM sys_user WHERE phone = '13800138000';
-- type: ALL, rows: 1000000 ← 全表扫描
-- 加索引
ALTER TABLE sys_user ADD INDEX idx_phone (phone);
-- 优化后
-- type: ref, rows: 1 ← 秒查六、索引优化要点
最左前缀原则
sql
KEY idx_a_b_c (a, b, c)
WHERE a = 1 -- ✅ 用到 a
WHERE a = 1 AND b = 2 -- ✅ 用到 a, b
WHERE a = 1 AND b = 2 AND c = 3 -- ✅ 全部用到
WHERE b = 2 -- ❌ 跳过了 a，索引失效
WHERE a = 1 AND c = 3 -- ⚠️ 只用到 a（c 断层了）
WHERE a > 1 AND b = 2 -- ⚠️ 范围查询后面的字段失效，只用到 a联合索引字段顺序原则：等值查询的字段放前面，范围查询的放最后。
索引失效的常见场景
sql
-- ① 函数/运算
WHERE YEAR(create_time) = 2026 -- ❌
WHERE create_time >= '2026-01-01' AND create_time < '2027-01-01' -- ✅
WHERE age + 1 = 20 -- ❌
WHERE age = 19 -- ✅
-- ② 隐式类型转换（phone 是 varchar）
WHERE phone = 13800138000 -- ❌ 数字，触发类型转换
WHERE phone = '13800138000' -- ✅ 字符串
-- ③ 前导模糊
WHERE name LIKE '%张' -- ❌
WHERE name LIKE '张%' -- ✅
-- ④ OR 连接非索引列
WHERE indexed_col = 1 OR no_index_col = 2 -- ❌ 全表扫描
-- ✅ 改用 UNION，或给两个列都建索引
-- ⑤ NOT IN / != （视数据分布，可能不走索引）
-- ⑥ 索引列参与计算或使用了不同的字符集/排序规则② 隐式类型转换是最隐蔽的坑。SQL 语法完全正确、结果也对，就是慢，
EXPLAIN一看才发现全表扫描了。
覆盖索引
sql
-- 索引：idx_status_username (status, username)
-- ✅ 覆盖索引：所需字段都在索引里，不用回表
SELECT status, username FROM sys_user WHERE status = 1;
-- Extra: Using index
-- ❌ 需要回表拿其他字段
SELECT * FROM sys_user WHERE status = 1;这就是为什么不该写
SELECT *。除了传输浪费，还失去了覆盖索引优化的机会。
索引数量的权衡
| 太少 | 太多 |
|---|---|
| 查询慢 | 写入慢（每次增删改都要维护所有索引） |
| 占用磁盘空间 | |
| 优化器选择困难，可能选错索引 |
经验值：单表索引不超过 5-6 个。定期用
sys.schema_unused_indexes查出从未被使用的索引并删除。
七、批量操作优化
java
// ❌ 循环单条插入：1 万条要几十秒
for (User user : users) {
userMapper.insert(user);
}
// ⚠️ saveBatch：本质还是循环，但共用 session，快一些
userService.saveBatch(users, 1000);
// ✅ 最快：自定义 XML 拼多值 INSERTxml
<insert id="batchInsert">
INSERT INTO sys_user (id, username, phone, create_time) VALUES
<foreach collection="list" item="item" separator=",">
(#{item.id}, #{item.username}, #{item.phone}, #{item.createTime})
</foreach>
</insert>java
// ⚠️ 但要分批，一次别超过 500-1000 条
// 否则 SQL 过长会超过 max_allowed_packet（默认 4MB）
Lists.partition(users, 500).forEach(userMapper::batchInsert);性能对比（插入 1 万条）：
| 方式 | 耗时 |
|---|---|
| 循环 insert | ~30s |
saveBatch（无 rewriteBatchedStatements） | ~15s |
saveBatch（有 rewriteBatchedStatements） | ~2s |
| 多值 INSERT（500/批） | ~1s |
别忘了连接串加
rewriteBatchedStatements=true，这一个参数就能带来近 10 倍提升。
八、常见问题排查
bash
# ① 连接数满了
SHOW PROCESSLIST; -- 看当前连接
SHOW VARIABLES LIKE 'max_connections'; -- 上限
SELECT * FROM information_schema.processlist WHERE command != 'Sleep';
# ② 找出锁等待
SELECT * FROM performance_schema.data_lock_waits;
SHOW ENGINE INNODB STATUS\G -- 看 LATEST DETECTED DEADLOCK
# ③ 表大小和索引大小
SELECT table_name,
ROUND(data_length/1024/1024, 2) AS 'Data(MB)',
ROUND(index_length/1024/1024, 2) AS 'Index(MB)',
table_rows
FROM information_schema.tables
WHERE table_schema = 'taskflow'
ORDER BY data_length DESC;
# ④ 未使用的索引
SELECT * FROM sys.schema_unused_indexes;九、本章小结
| 要点 | 关键 |
|---|---|
| 连接池 | 避免重复建连接（20-50ms/次） |
maximum-pool-size | CPU×2+磁盘数，约 20；不是越大越好 |
max-lifetime | 必须小于 MySQL wait_timeout |
| 泄漏检测 | 生产开启，60 秒 |
| 关键指标 | threadsAwaitingConnection > 0 就是告警 |
| EXPLAIN | type=ALL 必须优化 |
| 最左前缀 | 等值在前，范围在后 |
| 索引失效 | 函数、隐式转换、前导模糊 |
| 覆盖索引 | 别写 SELECT * |
| 批量插入 | rewriteBatchedStatements=true |
动手练习
练习 1：基础题
给 100 万条数据的表，对比加索引前后 WHERE phone = ? 的查询耗时，用 EXPLAIN 观察 type 和 rows 的变化。
练习 2：进阶题
写一个慢 SQL 拦截器，超过 500ms 的 SQL 记录到单独的日志文件，包含 SQL 语句、参数、耗时、调用栈。
下一章：第 58 章：事务管理 →