Golang中基于本地存储的队列查询表问题如何解决？

在Go中处理基于本地存储的队列查询表时，主要问题在于使用单个数据库连接处理所有请求，这确实会导致性能瓶颈和潜在的排队问题。以下是改进方案：

解决方案：使用连接池和预处理语句

package main

import (
    "database/sql"
    "fmt"
    "log"
    "net/http"
    "strings"
    "sync"

    _ "github.com/lib/pq"
)

var db *sql.DB
var stmtCache *sync.Map

func main() {
    Connect()
    
    // 初始化语句缓存
    stmtCache = &sync.Map{}
    
    http.HandleFunc("/", Query)
    log.Fatal(http.ListenAndServe(":8080", nil))
}

func Connect() {
    const (
        host     = "127.0.0.1"
        port     = 5432
        user     = "go"
        password = "test"
        dbname   = "lookup"
    )

    login := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable",
        host, port, user, password, dbname)

    var err error
    db, err = sql.Open("postgres", login)
    if err != nil {
        log.Fatalln(err)
    }

    // 配置连接池
    db.SetMaxOpenConns(25)
    db.SetMaxIdleConns(25)
    db.SetConnMaxLifetime(5 * time.Minute)

    err = db.Ping()
    if err != nil {
        panic(err)
    }
}

func Query(w http.ResponseWriter, r *http.Request) {
    id := strings.Trim(r.URL.Path, "/")
    
    // 使用预处理语句避免SQL注入和提高性能
    sqlx, err := getSQLFromCache(id)
    if err != nil {
        if err == sql.ErrNoRows {
            http.Error(w, "Not found", http.StatusNotFound)
            return
        }
        http.Error(w, "Internal server error", http.StatusInternalServerError)
        log.Printf("Query error: %v", err)
        return
    }

    fmt.Fprintf(w, "%s", sqlx)
}

func getSQLFromCache(id string) (string, error) {
    // 尝试从缓存获取预处理语句
    stmtKey := "get_sql"
    cachedStmt, ok := stmtCache.Load(stmtKey)
    
    var stmt *sql.Stmt
    var err error
    
    if ok {
        stmt = cachedStmt.(*sql.Stmt)
    } else {
        // 创建新的预处理语句
        stmt, err = db.Prepare("SELECT sqlx FROM get WHERE id = $1")
        if err != nil {
            return "", err
        }
        stmtCache.Store(stmtKey, stmt)
    }

    var sqlx string
    err = stmt.QueryRow(id).Scan(&sqlx)
    return sqlx, err
}

更高级的解决方案：使用本地缓存减少数据库查询

package main

import (
    "database/sql"
    "fmt"
    "log"
    "net/http"
    "sync"
    "time"

    _ "github.com/lib/pq"
)

var db *sql.DB
var cache *sync.Map
var cacheMutex sync.RWMutex

func main() {
    Connect()
    
    // 初始化内存缓存
    cache = &sync.Map{}
    
    // 定期刷新缓存
    go refreshCache()
    
    http.HandleFunc("/", Query)
    log.Fatal(http.ListenAndServe(":8080", nil))
}

func Query(w http.ResponseWriter, r *http.Request) {
    id := strings.Trim(r.URL.Path, "/")
    
    // 首先尝试从内存缓存获取
    if value, ok := cache.Load(id); ok {
        fmt.Fprintf(w, "%s", value.(string))
        return
    }
    
    // 缓存未命中，从数据库查询
    sqlx, err := getSQLFromDB(id)
    if err != nil {
        if err == sql.ErrNoRows {
            http.Error(w, "Not found", http.StatusNotFound)
            return
        }
        http.Error(w, "Internal server error", http.StatusInternalServerError)
        log.Printf("Query error: %v", err)
        return
    }
    
    // 更新缓存
    cache.Store(id, sqlx)
    fmt.Fprintf(w, "%s", sqlx)
}

func getSQLFromDB(id string) (string, error) {
    var sqlx string
    err := db.QueryRow("SELECT sqlx FROM get WHERE id = $1", id).Scan(&sqlx)
    return sqlx, err
}

func refreshCache() {
    ticker := time.NewTicker(5 * time.Minute)
    defer ticker.Stop()
    
    for range ticker.C {
        loadAllQueries()
    }
}

func loadAllQueries() {
    rows, err := db.Query("SELECT id, sqlx FROM get")
    if err != nil {
        log.Printf("Failed to refresh cache: %v", err)
        return
    }
    defer rows.Close()
    
    newCache := &sync.Map{}
    for rows.Next() {
        var id, sqlx string
        if err := rows.Scan(&id, &sqlx); err != nil {
            log.Printf("Failed to scan row: %v", err)
            continue
        }
        newCache.Store(id, sqlx)
    }
    
    // 原子性替换缓存
    cacheMutex.Lock()
    cache = newCache
    cacheMutex.Unlock()
}

关键改进点：

1. 连接池配置：设置适当的连接池参数避免连接耗尽
2. 预处理语句：使用参数化查询防止SQL注入和提高性能
3. 内存缓存：减少对数据库的直接查询
4. 错误处理：更健壮的错误处理而非直接panic
5. 并发安全：使用sync.Map处理并发访问

这些改进能显著提升高并发场景下的性能，避免查询排队问题。