MySQL

发布日期: 2024-07-27

作者: 小乐

文章字数: 2.3k

阅读时长: 10 分

阅读次数:

MySQL连接池配置与优化

MySQL 是业务系统中最常见的数据库，但用好它并不容易。索引设计、SQL 优化、事务处理都是日常开发中需要关注的点。本文从实际场景出发，讲常见问题和解决思路。

一、为什么需要连接池

1.1 数据库连接的成本

创建数据库连接是高成本操作：

创建连接的步骤：
1. TCP三次握手（1.5 RTT）
2. MySQL握手认证
3. 权限验证
4. 初始化连接资源和缓冲区

总耗时：约几十到几百毫秒

1.2 连接池的作用

复用连接：避免频繁创建和关闭连接
控制并发：限制同时访问数据库的连接数
快速响应：从池获取连接是微秒级
连接管理：自动检测和恢复无效连接

二、连接池原理

2.1 连接池的工作流程

应用请求连接
    │
    ├── 空闲连接？───是──→ 返回连接
    │       │
    │      否
    │       │
    ├── 连接数 < 最大连接数？───是──→ 创建新连接
    │       │
    │      否
    │       │
    └── 等待（或报错）

归还连接
    │
    ├── 连接有效？───是──→ 放回空闲池
    │       │
    │      否
    │       │
    └── 关闭连接

2.2 连接池核心参数

参数	说明
最小连接数（minIdle）	连接池保持的最小空闲连接
最大连接数（maxActive/maxPoolSize）	连接池允许的最大连接数
连接超时（connectionTimeout）	获取连接的最大等待时间
空闲检测（idleTimeout）	空闲连接回收时间
最大生命周期（maxLifetime）	连接的最大存活时间

三、主流连接池对比

3.1 HikariCP

性能最好的连接池，SpringBoot 2.0+ 默认。

<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>5.1.0</version>
</dependency>

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/mydb");
config.setUsername("root");
config.setPassword("password");
config.setMaximumPoolSize(20);
config.setMinimumIdle(5);
config.setConnectionTimeout(30000);
config.setIdleTimeout(600000);
config.setMaxLifetime(1800000);
config.addDataSourceProperty("cachePrepStmts", "true");
config.addDataSourceProperty("prepStmtCacheSize", "250");
config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");

HikariDataSource dataSource = new HikariDataSource(config);

特点：

性能极高（比其他连接池快数倍）
代码精简，功能专注
无额外监控功能

3.2 Druid

阿里巴巴开源，功能全面。

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.2.20</version>
</dependency>

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      url: jdbc:mysql://localhost:3306/mydb
      username: root
      password: password
      initial-size: 5
      min-idle: 5
      max-active: 20
      max-wait: 60000
      time-between-eviction-runs-millis: 60000
      min-evictable-idle-time-millis: 300000
      max-evictable-idle-time-millis: 900000
      validation-query: SELECT 1
      test-while-idle: true
      test-on-borrow: false
      test-on-return: false
      pool-prepared-statements: true
      max-pool-prepared-statement-per-connection-size: 20
      filters: stat,wall,slf4j
      connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
      stat-view-servlet:
        enabled: true
        login-username: admin
        login-password: admin

特点：

内置监控页面
SQL防注入（WallFilter）
SQL性能监控
扩展功能丰富

3.3 连接池性能对比

连接池	性能	功能	监控	推荐场景
HikariCP	★★★	基础	无	追求极致性能
Druid	★★☆	丰富	内置	需要监控和SQL防护
C3P0	★☆☆	一般	无	老项目维护
DBCP2	★★☆	基础	无	Apache生态

四、连接池参数配置

4.1 连接数计算

最大连接数公式：

连接数 = ((核心数 × 2) + 有效磁盘数) × 单服务器分片数

简化估算：
- 4核服务器：8-16连接
- 8核服务器：16-32连接
- 16核服务器：32-64连接

MySQL服务端最大连接数：

SHOW VARIABLES LIKE 'max_connections';
-- 默认151，生产建议500-2000

4.2 应用连接池配置

假设应用部署8台机器，MySQL服务端max_connections=500：

每台应用的最大连接数 = 500 / 8 ≈ 60
考虑冗余：每台配置 maxPoolSize = 50

4.3 HikariCP推荐配置

# 核心参数
spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.idle-timeout=600000
spring.datasource.hikari.max-lifetime=1800000

# 连接测试
spring.datasource.hikari.connection-test-query=SELECT 1

# MySQL优化
spring.datasource.hikari.data-source-properties.cachePrepStmts=true
spring.datasource.hikari.data-source-properties.prepStmtCacheSize=250
spring.datasource.hikari.data-source-properties.prepStmtCacheSqlLimit=2048
spring.datasource.hikari.data-source-properties.useServerPrepStmts=true
spring.datasource.hikari.data-source-properties.useLocalSessionState=true
spring.datasource.hikari.data-source-properties.rewriteBatchedStatements=true
spring.datasource.hikari.data-source-properties.cacheResultSetMetadata=true
spring.datasource.hikari.data-source-properties.cacheServerConfiguration=true
spring.datasource.hikari.data-source-properties.elideSetAutoCommits=true
spring.datasource.hikari.data-source-properties.maintainTimeStats=false

4.4 参数详解

参数	推荐值	说明
maximumPoolSize	10-30	最大连接数
minimumIdle	5-10	最小空闲连接
connectionTimeout	30000	获取连接等待30秒
idleTimeout	600000	空闲10分钟回收
maxLifetime	1800000	连接最大存活30分钟
leakDetectionThreshold	60000	连接泄漏检测60秒

maxLifetime注意：

应小于MySQL的wait_timeout
MySQL默认wait_timeout=28800（8小时）
连接池maxLifetime应比数据库端短一些

五、MySQL连接相关参数

5.1 服务端参数

[mysqld]
# 最大连接数
max_connections = 500

# 每个连接缓冲区
sort_buffer_size = 2M
read_buffer_size = 1M
join_buffer_size = 2M
read_rnd_buffer_size = 2M

# 连接超时
wait_timeout = 28800          # 非交互连接空闲超时（秒）
interactive_timeout = 28800   # 交互连接空闲超时（秒）

# 连接错误阈值（防暴力破解）
max_connect_errors = 100

5.2 连接数监控

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

-- 查看连接详情
SHOW PROCESSLIST;

-- 查看连接统计
SELECT 
    user,
    host,
    db,
    command,
    state,
    TIME as time_seconds,
    info
FROM information_schema.processlist
WHERE command != 'Sleep'
ORDER BY time_seconds DESC;

六、常见问题排查

6.1 连接池耗尽

现象：

java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30000ms

原因排查：

连接泄漏（未正确关闭）
慢SQL占用连接时间过长
连接数配置过小
突发流量

排查方法：

// 开启HikariCP泄漏检测
config.setLeakDetectionThreshold(60000);  // 60秒

// 日志输出：
// Apparent connection leak detected, owned by running SQL: SELECT ...

解决：

// 确保在finally中关闭连接
try (Connection conn = dataSource.getConnection();
     PreparedStatement ps = conn.prepareStatement(sql)) {
    // 执行查询
} catch (SQLException e) {
    // 处理异常
}
// try-with-resources 自动关闭

6.2 连接超时

现象：

Communications link failure: The last packet successfully received from the server was X milliseconds ago

原因：

网络中断
MySQL服务端关闭空闲连接
防火墙切断长连接

解决：

# 连接池配置
spring.datasource.hikari.max-lifetime=1800000  # 小于MySQL wait_timeout

# 测试连接有效性
spring.datasource.hikari.connection-test-query=SELECT 1

6.3 Too many connections

现象：

ERROR 1040 (08004): Too many connections

解决：

-- 临时增加（需权限）
SET GLOBAL max_connections = 1000;

-- 查看连接来源
SELECT user, host, COUNT(*) 
FROM information_schema.processlist 
GROUP BY user, host;

-- 杀掉空闲连接
SELECT CONCAT('KILL ', id, ';') 
FROM information_schema.processlist 
WHERE command = 'Sleep' AND time > 600;

6.4 连接数优化案例

场景：应用启动后连接数迅速增长到最大，且响应变慢。

分析：

-- 查看每个应用的连接数
SELECT 
    SUBSTRING_INDEX(host, ':', 1) AS app_host,
    COUNT(*) AS conn_count
FROM information_schema.processlist
GROUP BY SUBSTRING_INDEX(host, ':', 1)
ORDER BY conn_count DESC;

优化方案：

检查是否存在连接泄漏
优化慢SQL，减少连接占用时间
调整连接池大小
考虑增加MySQL从库分散读压力

七、SpringBoot连接池配置

7.1 HikariCP（默认）

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=utf8mb4&useSSL=false&serverTimezone=Asia/Shanghai
    username: root
    password: password
    driver-class-name: com.mysql.cj.jdbc.Driver
    type: com.zaxxer.hikari.HikariDataSource
    hikari:
      pool-name: MyHikariPool
      minimum-idle: 10
      maximum-pool-size: 50
      idle-timeout: 600000
      max-lifetime: 1800000
      connection-timeout: 30000
      connection-test-query: SELECT 1
      data-source-properties:
        cachePrepStmts: true
        prepStmtCacheSize: 250
        prepStmtCacheSqlLimit: 2048

7.2 多数据源配置

@Configuration
public class DataSourceConfig {
    
    @Primary
    @Bean(name = "masterDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.master")
    public DataSource masterDataSource() {
        return DataSourceBuilder.create().type(HikariDataSource.class).build();
    }
    
    @Bean(name = "slaveDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.slave")
    public DataSource slaveDataSource() {
        return DataSourceBuilder.create().type(HikariDataSource.class).build();
    }
}

八、连接池监控

8.1 HikariCP监控

@Autowired
private HikariDataSource dataSource;

public void printPoolStats() {
    HikariPoolMXBean poolMXBean = dataSource.getHikariPoolMXBean();
    
    System.out.println("活跃连接数: " + poolMXBean.getActiveConnections());
    System.out.println("空闲连接数: " + poolMXBean.getIdleConnections());
    System.out.println("等待连接数: " + poolMXBean.getPendingThreads());
    System.out.println("总连接数: " + poolMXBean.getTotalConnections());
}

8.2 Micrometer集成（Prometheus）

<dependency>
    <groupId>io.micrometer</groupId>
    <artifactId>micrometer-registry-prometheus</artifactId>
</dependency>

@Bean
public MeterRegistryCustomizer<MeterRegistry> metricsCustomizer() {
    return registry -> registry.config().commonTags("application", "myapp");
}

8.3 告警规则

# Prometheus告警规则
groups:
  - name: connection_pool
    rules:
      - alert: ConnectionPoolHighUsage
        expr: hikaricp_connections_active / hikaricp_connections_max > 0.8
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "连接池使用率超过80%"
          
      - alert: ConnectionPoolWaitTimeHigh
        expr: rate(hikaricp_connections_timeout_total[5m]) > 0
        for: 1m
        labels:
          severity: critical
        annotations:
          summary: "连接池出现获取连接超时"

九、总结

参数	HikariCP	Druid	说明
最大连接数	maximumPoolSize	maxActive	根据服务器配置估算
最小空闲	minimumIdle	minIdle	保持一定空闲连接
连接超时	connectionTimeout	maxWait	获取连接等待时间
空闲回收	idleTimeout	minEvictableIdleTimeMillis	空闲连接回收时间
最大生命周期	maxLifetime	maxEvictableIdleTimeMillis	连接最大存活时间

连接池优化的核心要点：

选择合适的连接池（推荐HikariCP或Druid）
合理配置连接数（避免过大或过小）
配置连接检测和超时参数
监控连接池使用情况
及时处理连接泄漏问题

核心要点

索引设计的原则：最左前缀原则、避免索引列参与计算、选择合适的索引类型
SQL 优化的方法：使用 EXPLAIN 分析执行计划、避免 SELECT *、优化 JOIN
事务隔离级别的选择：根据业务需求选择合适的级别
常见的锁问题：行锁、表锁、死锁的处理

总结

MySQL 优化是一个持续的过程，需要结合业务特点和数据量来调整。在实际项目中，定期分析慢查询日志、优化索引、调整配置参数，都能提升数据库性能。

转载规则

《MySQL连接池配置与优化》由小乐采用知识共享署名 4.0 国际许可协议进行许可。

Java String 为什么设计成不可变

详解Java String 为什么设计成不可变的核心概念与实践方法，结合真实开发场景说明使用注意事项。

2024-07-27 Java基础

学习笔记 JAVA

Java 中 equals 和 hashCode 的正确理解

详解Java 中 equals 和 hashCode 的正确理解的核心概念与实践方法，结合真实开发场景说明使用注意事项。

2024-07-26 Java基础