Go重写API后Go-SQL-Driver致MariaDB CPU占用过高求助

原本基于Python Flask开发的网站及APP后端API运行稳定，近期用Go完成了API全量重写。预期Go二进制文件的CPU、内存占用会更低，但实际运行后MariaDB的CPU占用率接近99%。

已尝试调整Go-SQL-Driver的最大连接数、超时时间、最大空闲时间等参数，但无明显改善。代码中使用全局数据库连接变量，每次执行db.Prepare和db.Query后均通过defer result.Close()关闭资源。明确Go性能优于Python，会处理更多请求，但当前处于测试环境，请求量有限，不应导致数据库CPU过载。

补充说明：原站点自2015年上线，数据量超百万行，不想通过GORM重建数据库，希望仅用纯SQL解决问题。

相关代码：

func getfulldate(c *fiber.Ctx) error {

    pid := c.FormValue("pid")

    result, err := db.Prepare("select concat(p.firstName, ' ', p.middle, ' ', p.lastName, ' ', p.forthName) as fullname,gender,bID,married,barcode,comment,address,if(p2.phone is null, 0, p2.phone)  as phone,rName,occupation,weight,height,cast(Birthdate as date) as Birthdate from profile p left join (select phID, pID, phone from phonefix group by pID) p2 on p.pID = p2.pID left join (select pID, weight, height from bmifix group by pID) B on p.pID = B.pID, religion r where r.rgID = p.rgID and p.pID = ? ")
    defer result.Close()
    if err != nil {
        return c.JSON(fiber.Map{"access_token": "wrong"})

    }

    rows, err := result.Query(pid)
    defer rows.Close()
    if err != nil {
        return c.JSON(fiber.Map{"access_token": "wrong"})

    }
    columns, err := rows.Columns()
    if err != nil {
        return err
    }
    count := len(columns)
    tableData := make([]map[string]interface{}, 0)
    values := make([]interface{}, count)
    valuePtrs := make([]interface{}, count)
    for rows.Next() {
        for i := 0; i < count; i++ {
            valuePtrs[i] = &values[i]
        }
        rows.Scan(valuePtrs...)
        entry := make(map[string]interface{})
        for i, col := range columns {
            var v interface{}
            val := values[i]
            b, ok := val.([]byte)
            if ok {
                v = string(b)
            } else {
                v = val
            }
            entry[col] = v
        }
        tableData = append(tableData, entry)
    }

    currentTime := time.Now().Format("2006-01-02")
    result, err = db.Prepare("select viID,state as done,dob from visitfix where  patientID = ?")
    defer result.Close()
    if err != nil {
        return c.JSON(fiber.Map{"access_token": "wrong"})

    }

    rows, err = result.Query(pid)
    defer rows.Close()
    if err != nil {
        return c.JSON(fiber.Map{"access_token": "wrong"})

    }
    columns = []string{"viID", "done", "dob"}

    count = len(columns)
    tableDatas := make([]map[string]interface{}, 0)
    values = make([]interface{}, count)
    valuePtrs = make([]interface{}, count)
    for rows.Next() {
        for i := 0; i < count; i++ {

            valuePtrs[i] = &values[i]
        }

        rows.Scan(valuePtrs...)

        entry := make(map[string]interface{})

        for i, col := range columns {
            var v interface{}
            val := values[i]

            b, ok := val.([]byte)

            if ok {
                v = string(b)
            } else {
                v = val
            }
            if i == 2 {
                var state string
                format := "2006-1-2"

                datea, err := time.Parse(format, string(b))
                if err != nil {
                    return err
                }
                mydate := datea.Format("2006-01-02")
                if mydate == currentTime {
                    state = "today"
                }
                if mydate < currentTime {
                    state = "older"
                }
                if mydate > currentTime {
                    state = "newer"
                }

                entry["state"] = state

            }

            entry[col] = v
        }

        tableDatas = append(tableDatas, entry)
    }
    alldata := [][]map[string]interface{}{tableData, tableDatas}
    dat, err := json.Marshal(alldata)
    if err != nil {
        return err
    }

    return c.SendString(string(dat))
}

1. 优化SQL查询，消除低效子查询 #

第一个查询中，phonefix和bmifix的子查询使用GROUP BY pID但未搭配聚合函数，这会导致数据库返回随机行，且大表上会触发全表扫描。建议修改为直接关联表，若需去重则在主查询添加GROUP BY p.pID：

SELECT CONCAT(p.firstName, ' ', p.middle, ' ', p.lastName, ' ', p.forthName) as fullname,
       gender, bID, married, barcode, comment, address,
       IF(p2.phone IS NULL, 0, p2.phone) as phone,
       rName, occupation, weight, height, CAST(Birthdate AS DATE) as Birthdate
FROM profile p
LEFT JOIN phonefix p2 ON p.pID = p2.pID
LEFT JOIN bmifix B ON p.pID = B.pID
JOIN religion r ON r.rgID = p.rgID
WHERE p.pID = ?
GROUP BY p.pID

同时检查以下字段是否存在索引：profile.pID、phonefix.pID、bmifix.pID、religion.rgID，缺失索引会导致大表全表扫描，直接拉满CPU。

2. 避免每次请求重复预处理语句 #

当前代码每次请求都调用db.Prepare，会重复创建预处理语句，浪费数据库资源。建议直接使用db.Query带参数执行，数据库会自动缓存语句执行计划：

rows, err := db.Query(sqlStr, pid)

替代db.Prepare+result.Query的组合，减少数据库端的语句解析开销。

3. 转移日期判断逻辑到数据库端 #

第二个查询中的日期状态判断（today/older/newer）无需在Go代码中解析字符串处理，直接在SQL中完成，减少数据传输和Go端计算开销：

SELECT viID, state as done, dob,
       CASE 
           WHEN DATE(dob) = CURDATE() THEN 'today'
           WHEN DATE(dob) < CURDATE() THEN 'older'
           ELSE 'newer'
       END as state
FROM visitfix 
WHERE patientID = ?

4. 合理配置数据库连接池 #

即使调整过参数，仍需确认连接池配置是否匹配测试环境：

• SetMaxOpenConns：测试环境请求量小，设置为10-20即可，避免数据库同时处理过多连接导致CPU过载
• SetMaxIdleConns：建议设置为与SetMaxOpenConns相同或略小，避免空闲连接占用资源
• SetConnMaxLifetime：设置合理的连接生命周期，避免长期占用连接

5. 开启慢查询日志定位瓶颈 #

开启MariaDB慢查询日志，捕获耗时查询：

slow_query_log = 1
long_query_time = 0.1
slow_query_log_file = /var/log/mysql/slow.log

使用EXPLAIN分析慢查询的执行计划，确认是否存在全表扫描、索引未命中等问题。

func getfulldate(c *fiber.Ctx) error {
    pid := c.FormValue("pid")

    // 优化后的用户信息查询
    sqlProfile := `
        SELECT CONCAT(p.firstName, ' ', p.middle, ' ', p.lastName, ' ', p.forthName) as fullname,
               gender, bID, married, barcode, comment, address,
               IF(p2.phone IS NULL, 0, p2.phone) as phone,
               rName, occupation, weight, height, CAST(Birthdate AS DATE) as Birthdate
        FROM profile p
        LEFT JOIN phonefix p2 ON p.pID = p2.pID
        LEFT JOIN bmifix B ON p.pID = B.pID
        JOIN religion r ON r.rgID = p.rgID
        WHERE p.pID = ?
        GROUP BY p.pID
    `
    rows, err := db.Query(sqlProfile, pid)
    if err != nil {
        return c.JSON(fiber.Map{"access_token": "wrong"})
    }
    defer rows.Close()

    columns, err := rows.Columns()
    if err != nil {
        return err
    }
    count := len(columns)
    tableData := make([]map[string]interface{}, 0)
    values := make([]interface{}, count)
    valuePtrs := make([]interface{}, count)
    for i := range valuePtrs {
        valuePtrs[i] = &values[i]
    }

    for rows.Next() {
        if err := rows.Scan(valuePtrs...); err != nil {
            return err
        }
        entry := make(map[string]interface{})
        for i, col := range columns {
            val := values[i]
            if b, ok := val.([]byte); ok {
                entry[col] = string(b)
            } else {
                entry[col] = val
            }
        }
        tableData = append(tableData, entry)
    }
    if err := rows.Err(); err != nil {
        return err
    }

    // 优化后的就诊记录查询（数据库处理日期状态）
    sqlVisit := `
        SELECT viID, state as done, dob,
               CASE 
                   WHEN DATE(dob) = CURDATE() THEN 'today'
                   WHEN DATE(dob) < CURDATE() THEN 'older'
                   ELSE 'newer'
               END as state
        FROM visitfix 
        WHERE patientID = ?
    `
    rows, err = db.Query(sqlVisit, pid)
    if err != nil {
        return c.JSON(fiber.Map{"access_token": "wrong"})
    }
    defer rows.Close()

    tableDatas := make([]map[string]interface{}, 0)
    visitColumns := []string{"viID", "done", "dob", "state"}
    visitCount := len(visitColumns)
    visitValues := make([]interface{}, visitCount)
    visitValuePtrs := make([]interface{}, visitCount)
    for i := range visitValuePtrs {
        visitValuePtrs[i] = &visitValues[i]
    }

    for rows.Next() {
        if err := rows.Scan(visitValuePtrs...); err != nil {
            return err
        }
        entry := make(map[string]interface{})
        for i, col := range visitColumns {
            val := visitValues[i]
            if b, ok := val.([]byte); ok {
                entry[col] = string(b)
            } else {
                entry[col] = val
            }
        }
        tableDatas = append(tableDatas, entry)
    }
    if err := rows.Err(); err != nil {
        return err
    }

    alldata := [][]map[string]interface{}{tableData, tableDatas}
    return c.JSON(alldata) // 直接使用Fiber内置JSON序列化，无需手动Marshal
}

内容的提问来源于stack exchange，提问作者Ravyar Tahir