当前位置：首页 > news >正文

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

news 2026/5/11 1:14:00

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

1. 连接池基础

1.1 连接池原理

数据库连接池预先建立一定数量的连接，当应用需要访问数据库时，从池中获取空闲连接，使用完毕后归还连接池，避免频繁创建和销毁连接的开销。

1.2 Go数据库连接池

import ( "database/sql" _ "github.com/go-sql-driver/mysql" ) db, err := sql.Open("mysql", "user:password@tcp(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:password@tcp(localhost:3306)/dbname?parseTime=true&loc=Local" // 连接池配置 db.SetMaxOpenConns(100) db.SetMaxIdleConns(20) db.SetConnMaxLifetime(5 * time.Minute)

2.2 PostgreSQL连接池配置

import "github.com/lib/pq" dsn := "postgres://user:password@localhost:5432/dbname?sslmode=disable" // 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 = 'john@example.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 = 256M

7.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.9