数据库连接池与性能调优详解

张

张建站

2026/5/11 1:06:41

10分钟阅读

数据库连接池与性能调优详解1. 连接池基础1.1 连接池原理数据库连接池预先建立一定数量的连接当应用需要访问数据库时从池中获取空闲连接使用完毕后归还连接池避免频繁创建和销毁连接的开销。1.2 Go数据库连接池import ( database/sql _ github.com/go-sql-driver/mysql ) db, err : sql.Open(mysql, user:passwordtcp(localhost:3306)/dbname) defer db.Close() // 设置连接池参数 db.SetMaxOpenConns(100) // 最大打开连接数 db.SetMaxIdleConns(10) // 最大空闲连接数 db.SetConnMaxLifetime(time.Hour) // 连接最大生命周期 db.SetConnMaxIdleTime(30 * time.Minute) // 空闲连接最大存活时间2. 连接池配置2.1 MySQL连接池配置// DSN配置 dsn : user:passwordtcp(localhost:3306)/dbname?parseTimetruelocLocal // 连接池配置 db.SetMaxOpenConns(100) db.SetMaxIdleConns(20) db.SetConnMaxLifetime(5 * time.Minute)2.2 PostgreSQL连接池配置import github.com/lib/pq dsn : postgres://user:passwordlocalhost:5432/dbname?sslmodedisable // lib/pq使用更细粒度的连接管理 // 推荐使用pgx连接池 import github.com/jackc/pgx/v4/pgxpool config, _ : pgxpool.ParseConfig(dsn) config.MaxConns 100 config.MinConns 10 config.MaxConnLifetime time.Hour config.MaxConnIdleTime 30 * time.Minute pool, _ : pgxpool.NewWithConfig(context.Background(), config) defer pool.Close()3. 连接泄漏检测3.1 连接泄漏示例func BadQuery(ctx context.Context, db *sql.DB) error { // 错误没有关闭rows rows, err : db.QueryContext(ctx, SELECT * FROM users) if err ! nil { return err } // 处理数据 for rows.Next() { // ... } // 忘记rows.Close() return nil }3.2 正确写法func GoodQuery(ctx context.Context, db *sql.DB) error { rows, err : db.QueryContext(ctx, SELECT * FROM users) if err ! nil { return err } defer rows.Close() // 确保关闭 for rows.Next() { // 处理数据 } return rows.Err() }3.3 监控连接池状态import expvar func init() { // 导出连接池指标 expvar.Publish(db_open_conns, expvar.Func(func() interface{} { return db.stats.OpenConnections })) expvar.Publish(db_in_use, expvar.Func(func() interface{} { return db.stats.InUse })) expvar.Publish(db_idle, expvar.Func(func() interface{} { return db.stats.Idle })) }4. SQL性能分析4.1 慢查询日志-- 查看慢查询配置 SHOW VARIABLES LIKE slow_query_log%; SHOW VARIABLES LIKE long_query_time; -- 开启慢查询日志 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; -- 查看慢查询日志 SHOW GLOBAL STATUS LIKE Slow_queries;4.2 EXPLAIN分析EXPLAIN SELECT u.name, o.total FROM users u JOIN orders o ON u.id o.user_id WHERE u.status active; -- 更详细的分析 EXPLAIN ANALYZE SELECT * FROM users WHERE email johnexample.com;4.3 Profiling-- 开启profiling SET profiling 1; -- 执行查询 SELECT COUNT(*) FROM orders; -- 查看profile SHOW PROFILES; SHOW PROFILE FOR QUERY 1;5. 查询优化5.1 避免SELECT *-- 优化前 SELECT * FROM users WHERE id 1; -- 优化后 SELECT id, name, email FROM users WHERE id 1;5.2 使用LIMIT-- 分页查询 SELECT * FROM orders ORDER BY created_at DESC LIMIT 100; -- 带偏移的分页大数据量时效率低 SELECT * FROM orders ORDER BY created_at DESC LIMIT 100 OFFSET 10000; -- 优化使用游标分页 SELECT * FROM orders WHERE created_at 2023-01-01 ORDER BY created_at DESC LIMIT 100;5.3 批量操作// 优化前循环插入 for _, user : range users { db.Exec(INSERT INTO users (name) VALUES (?), user.Name) } // 优化后批量插入 values : make([]string, len(users)) args : make([]interface{}, len(users)) for i, u : range users { values[i] (?) args[i] u.Name } query : fmt.Sprintf(INSERT INTO users (name) VALUES %s, strings.Join(values, ,)) db.Exec(query, args...)6. 连接复用6.1 事务连接func Transfer(db *sql.DB, fromID, toID int, amount float64) error { tx, err : db.BeginTx(ctx, nil) if err ! nil { return err } defer func() { if err ! nil { tx.Rollback() } }() // 所有操作使用同一个连接 _, err tx.ExecContext(ctx, UPDATE accounts SET balance balance - ? WHERE id ?, amount, fromID) if err ! nil { return err } _, err tx.ExecContext(ctx, UPDATE accounts SET balance balance ? WHERE id ?, amount, toID) if err ! nil { return err } return tx.Commit() }6.2 连接复用避免创建新连接// 在请求处理中复用连接 func HandleRequest(w http.ResponseWriter, r *http.Request) { ctx : r.Context() // 从连接池获取连接 row : db.QueryRowContext(ctx, SELECT COUNT(*) FROM users) var count int row.Scan(count) // 连接自动归还连接池 }7. 性能调优参数7.1 MySQL参数[mysqld] # 连接池 max_connections 1000 wait_timeout 600 interactive_timeout 600 # 缓冲池 innodb_buffer_pool_size 4G innodb_log_file_size 1G # 查询缓存MySQL 8已移除 # query_cache_type 1 # query_cache_size 128M # 临时表 tmp_table_size 256M max_heap_table_size 256M7.2 PostgreSQL参数postgresql.conf # 连接 max_connections 1000 # 内存 shared_buffers 4GB work_mem 64MB maintenance_work_mem 512MB # 查询规划 effective_cache_size 12GB random_page_cost 1.1 # 写入 wal_buffers 16MB checkpoint_completion_target 0.98. 总结数据库连接池是保障数据库性能的重要基础设施合理配置连接池参数可以显著提升系统吞吐量。同时需要注意SQL性能优化包括避免全表扫描、合理使用索引、优化查询语句等并定期监控数据库性能指标进行调优。