连接池与连接复用

一、连接池概述

数据库连接是一种昂贵的资源，创建和销毁连接会消耗大量时间和资源。连接池通过预先创建一组连接，供应用程序复用，显著提升性能。

二、连接池原理

2.1 连接池架构

2.2 连接池核心组件

三、连接池工作流程

3.1 获取连接流程

3.2 释放连接流程

四、连接池实现

4.1 简单连接池实现

public class ConnectionPool
{
    private readonly Queue<DbConnection> _idleConnections = new Queue<DbConnection>();
    private readonly HashSet<DbConnection> _activeConnections = new HashSet<DbConnection>();
    private readonly string _connectionString;
    private readonly int _maxPoolSize;
    private readonly object _lock = new object();
    
    public ConnectionPool(string connectionString, int maxPoolSize = 100)
    {
        _connectionString = connectionString;
        _maxPoolSize = maxPoolSize;
        InitializePool();
    }
    
    private void InitializePool()
    {
        for (int i = 0; i < 10; i++)
        {
            _idleConnections.Enqueue(CreateConnection());
        }
    }
    
    private DbConnection CreateConnection()
    {
        var connection = new SqlConnection(_connectionString);
        connection.Open();
        return connection;
    }
    
    public DbConnection GetConnection()
    {
        lock (_lock)
        {
            if (_idleConnections.Count > 0)
            {
                var connection = _idleConnections.Dequeue();
                if (connection.State == ConnectionState.Open)
                {
                    _activeConnections.Add(connection);
                    return connection;
                }
                connection.Dispose();
            }
            
            if (_activeConnections.Count < _maxPoolSize)
            {
                var connection = CreateConnection();
                _activeConnections.Add(connection);
                return connection;
            }
            
            throw new InvalidOperationException("连接池已满");
        }
    }
    
    public void ReleaseConnection(DbConnection connection)
    {
        lock (_lock)
        {
            if (_activeConnections.Remove(connection))
            {
                if (_idleConnections.Count < _maxPoolSize)
                {
                    _idleConnections.Enqueue(connection);
                }
                else
                {
                    connection.Dispose();
                }
            }
        }
    }
}

4.2 使用连接池

var pool = new ConnectionPool("Server=localhost;Database=app;", 100);

using (var connection = pool.GetConnection())
{
    using (var command = connection.CreateCommand())
    {
        command.CommandText = "SELECT * FROM users";
        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine(reader["username"]);
            }
        }
    }
}

pool.ReleaseConnection(connection);

五、连接池配置

5.1 关键配置参数

5.2 ADO.NET连接池配置

// 连接字符串配置
"Server=localhost;Database=app;User Id=sa;Password=123456;" +
"Max Pool Size=100;Min Pool Size=10;Connection Timeout=15;" +
"Idle Timeout=300;Connection Lifetime=600;"

// 禁用连接池
"Server=localhost;Database=app;Pooling=false;"

// 清空连接池
SqlConnection.ClearPool(connection);
SqlConnection.ClearAllPools();

5.3 JDBC连接池配置

// HikariCP配置
spring.datasource.hikari.maximum-pool-size=100
spring.datasource.hikari.minimum-idle=10
spring.datasource.hikari.connection-timeout=15000
spring.datasource.hikari.idle-timeout=300000
spring.datasource.hikari.max-lifetime=600000

// Apache Commons DBCP配置
BasicDataSource dataSource = new BasicDataSource();
dataSource.setMaxTotal(100);
dataSource.setMinIdle(10);
dataSource.setMaxWaitMillis(15000);

六、连接池监控

6.1 监控指标

public class ConnectionPoolMonitor
{
    public PoolMetrics GetMetrics(ConnectionPool pool)
    {
        return new PoolMetrics
        {
            TotalConnections = pool.TotalConnections,
            ActiveConnections = pool.ActiveConnections,
            IdleConnections = pool.IdleConnections,
            MaxPoolSize = pool.MaxPoolSize,
            MinPoolSize = pool.MinPoolSize,
            WaitCount = pool.WaitCount,
            WaitTime = pool.WaitTime
        };
    }
    
    public void LogMetrics(PoolMetrics metrics)
    {
        _logger.LogInformation($"连接池状态: 总数={metrics.TotalConnections}, " +
            $"活跃={metrics.ActiveConnections}, 空闲={metrics.IdleConnections}, " +
            $"等待次数={metrics.WaitCount}, 平均等待时间={metrics.WaitTime}ms");
    }
}

public class PoolMetrics
{
    public int TotalConnections { get; set; }
    public int ActiveConnections { get; set; }
    public int IdleConnections { get; set; }
    public int MaxPoolSize { get; set; }
    public int MinPoolSize { get; set; }
    public long WaitCount { get; set; }
    public double WaitTime { get; set; }
}

6.2 监控告警

public class ConnectionPoolAlertService
{
    public void CheckAndAlert(PoolMetrics metrics)
    {
        var usageRate = (double)metrics.ActiveConnections / metrics.MaxPoolSize;
        
        if (usageRate > 0.9)
        {
            _alertService.SendAlert("连接池使用率过高", 
                $"使用率: {usageRate:P}, 活跃连接: {metrics.ActiveConnections}");
        }
        
        if (metrics.WaitCount > 100)
        {
            _alertService.SendAlert("连接池等待次数过多", 
                $"等待次数: {metrics.WaitCount}, 平均等待时间: {metrics.WaitTime}ms");
        }
        
        if (metrics.IdleConnections == 0)
        {
            _alertService.SendAlert("连接池无空闲连接", 
                $"活跃连接: {metrics.ActiveConnections}, 最大连接数: {metrics.MaxPoolSize}");
        }
    }
}

七、连接池最佳实践

7.1 合理设置最大连接数

最大连接数不是越大越好，需要考虑数据库服务器的承载能力：

7.2 及时释放连接

// 正确方式：使用using语句自动释放连接
using (var connection = pool.GetConnection())
{
    // 使用连接
}

// 错误方式：忘记释放连接
var connection = pool.GetConnection();
// 使用连接但忘记释放
// 连接泄漏！

7.3 连接验证

public bool ValidateConnection(DbConnection connection)
{
    try
    {
        if (connection.State != ConnectionState.Open)
            return false;
        
        using (var command = connection.CreateCommand())
        {
            command.CommandText = "SELECT 1";
            command.ExecuteScalar();
            return true;
        }
    }
    catch
    {
        return false;
    }
}

7.4 连接池预热

public void WarmUpPool(int connections)
{
    var warmUpConnections = new List<DbConnection>();
    
    for (int i = 0; i < connections; i++)
    {
        warmUpConnections.Add(_pool.GetConnection());
    }
    
    foreach (var connection in warmUpConnections)
    {
        _pool.ReleaseConnection(connection);
    }
}

7.5 连接池隔离

public class ConnectionPoolManager
{
    private readonly Dictionary<string, ConnectionPool> _pools = new Dictionary<string, ConnectionPool>();
    
    public ConnectionPool GetPool(string poolName)
    {
        if (!_pools.TryGetValue(poolName, out var pool))
        {
            pool = new ConnectionPool(GetConnectionString(poolName));
            _pools[poolName] = pool;
        }
        return pool;
    }
}

八、连接池问题排查

8.1 连接泄漏排查

-- 查看MySQL连接状态
SHOW PROCESSLIST;

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

-- 查看连接池状态（HikariCP）
SELECT * FROM information_schema.processlist 
WHERE USER = 'app_user';

8.2 连接超时排查

// 启用连接池日志
spring.datasource.hikari.leak-detection-threshold=60000
spring.datasource.hikari.log-leak-detection=true

// 监控连接等待时间
poolMetrics.WaitTime > 500ms // 需要关注

九、总结

连接池是数据库性能优化的重要手段，通过复用连接减少连接创建开销。合理配置连接池参数、及时释放连接、监控连接池状态，能够确保数据库连接的高效利用，提升系统整体性能。