Remember the optimization of the Golang database query component.

Welcome to my blog to view

There is a piece of business online that needs to do a lot of database query and coding tasks. The database query is about 20 minutes, and about 2kw sql is executed. If you can optimize the database query method, you can save a lot of overhead.

Because the code is relatively long, it is not possible to verify why the data query selection at the time is not suitable for orm, but uses the original method to build it by itself. The following is the core data query code:

func QueryHelperOne(db *sql.DB, result interface{}, query string, args ...interface{}) (err error) {

    // 数据库查询
        var rows *sql.Rows
        log.Debug(query, args)
        rows, err = db.Query(query, args...)
        if err != nil {
                return err
        }
        defer rows.Close()

        // 获取列名称，并转换首字母大写，用于和struct Field 匹配
        var columns []string
        columns, err = rows.Columns()
        if err != nil {
                return err
        }

        fields := make([]string, len(columns))
        for i, columnName := range columns {
                fields[i] = server.firstCharToUpper(columnName)
        }

    // 传参必须是数组 slice 指针
        rv := reflect.ValueOf(result)
        if rv.Kind() == reflect.Ptr {
                rv = rv.Elem()
        } else {
                return errors.New("Parameter result must be a slice pointer")
        }
        if rv.Kind() == reflect.Slice {
                elemType := rv.Type().Elem()
                if elemType.Kind() == reflect.Struct {
                        ev := reflect.New(elemType)
            // 申请slice 数据，之后赋值给result
                        nv := reflect.MakeSlice(rv.Type(), 0, 0)
                        ignoreData := make([][]byte, len(columns))

                        for rows.Next() { // for each rows
                // scanArgs 是扫描每行数据的参数
                // scanArgs 中存储的是 struct 中field 的指针
                                scanArgs := make([]interface{}, len(fields))
                                for i, fieldName := range fields {
                                        fv := ev.Elem().FieldByName(fieldName)
                                        if fv.Kind() != reflect.Invalid {
                                                scanArgs[i] = fv.Addr().Interface()
                                        } else {
                                                ignoreData[i] = []byte{}
                                                scanArgs[i] = &ignoreData[i]
                                        }
                                }
                                err = rows.Scan(scanArgs...)
                                if err != nil {
                                        return err
                                }
                                nv = reflect.Append(nv, ev.Elem())
                        }
                        rv.Set(nv)
                }
        } else {
                return errors.New("Parameter result must be a slice pointer")
        }

        return
}

The method is called as follows:

type TblUser struct {
    Id          int64
    Name        string
    Addr        string
    UpdateTime  string
}

result := []TblUser{}
QueryHelperOne(db, &result, query, 10)

Looking directly at the above code, I found that there is no major problem, but continuous tuning in the details can squeeze the performance to the extreme.

Network Optimization

The internal implementation of the db.Query(sql, args...) method provided by golang is also based on the prepare method.
Prepare has three advantages:

- 可以让 mysql 省去每次语法分析的过程
- 可以避免出现sql 注入
- 可以重复使用prepare 的结果，只发送参数即可做查询

However, there are also downsides. There will be three network requests for one db.Query.

• prepare
• execute
• closing

And if there are multiple same SQL queries, this method is very dominant. Therefore, you can use prepare to replace db.Query to reduce network consumption.

var stmts = sync.Map{}
func QueryHelperOne(db *sql.DB, result interface{}, query string, args ...interface{}) (err error) {

    // 使用sync.Map 缓存 query 对应的stmt
    // 减少不必要的prepare 请求
    var stmt *sql.Stmt
    if v, ok := stmts.Load(query); ok {
        stmt = v.(*sql.Stmt)
    } else {
        if stmt, err = db.Prepare(query); err != nil {
            return err
        } else {
            stmts.Store(query, stmt)
        }
    }

    var rows *sql.Rows
    log.Debug(query, args)
    rows, err = stmt.Query(args...)
    if err != nil {
        _ = stmt.Close()
        stmts.Delete(query)
        return err
    }
    defer rows.Close()

    // 后面代码省略 ...
}

Through this modification, the performance of the job has been improved by 17%, and the effect is still very obvious.

GC optimization

Optimization 1

In the service, slice space will be pre-applied, so there is no need to re-apply for slice memory every time it is built.

// old code
// nv := reflect.MakeSlice(rv.Type(), 0, 0)
// new code
nv := rv.Slice(0, 0)

Optimization 2

As you can see from line 56 of the code, data is appended to the array each time. Since the structure slice is in the append, it is a memory copy; the data of scanArgs will be overwritten every scan, so it can be reused, and there is no need to map every rows.

ev := reflect.New(elemType)
// 申请slice 数据，之后赋值给result
nv := reflect.MakeSlice(rv.Type(), 0, 0)
ignoreData := make([][]byte, len(columns))
// scanArgs 是扫描每行数据的参数
// scanArgs 中存储的是 struct 中field 的指针
scanArgs := make([]interface{}, len(fields))
for i, fieldName := range fields {
        fv := ev.Elem().FieldByName(fieldName)
        if fv.Kind() != reflect.Invalid {
                scanArgs[i] = fv.Addr().Interface()
        } else {
                ignoreData[i] = []byte{}
                scanArgs[i] = &ignoreData[i]
        }
}
for rows.Next() { // for each rows
    err = rows.Scan(scanArgs...)
        if err != nil {
                return err
        }
        nv = reflect.Append(nv, ev.Elem())
}
rv.Set(nv)

Reduce the time when each line is scanned, new application scanArgs

Optimization 3

For the data not in the field, you need to use an empty value instead. The above code uses a []byte slice, in fact, only one []byte is needed. code show as below:

ignoreData := []byte{}
// scanArgs 是扫描每行数据的参数
// scanArgs 中存储的是 struct 中field 的指针
scanArgs := make([]interface{}, len(fields))
for i, fieldName := range fields {
        fv := ev.Elem().FieldByName(fieldName)
        if fv.Kind() != reflect.Invalid {
                scanArgs[i] = fv.Addr().Interface()
        } else {
                scanArgs[i] = &ignoreData
        }
}

Optimization 4

Because the same sql will query the number of tens of millions; therefore, the line element ev required for each scan line and the corresponding scan parameter list scanArgs can be cached, and then loaded from the memory when it is used.

// 定义数据池，用于存储每个sql 对应的扫描行item 以及扫描参数
// 全局代码
var datapools = sync.Map{}

type ReflectItem struct {
    Item     reflect.Value
    scanArgs []interface{}
}


///////// 方法调用内部

// 从数据池中加载query 对应的 ReflectItem
if v, ok := datapools.Load(query); ok {
    pool = v.(*sync.Pool)
} else {
    // 构建reflectItem
        var columns []string
        columns, err = rows.Columns()
        if err != nil {
                return err
        }

    pool = &sync.Pool{
        New: func() interface{} {
            fields := make([]string, len(columns))
            for i, columnName := range columns {
                fields[i] = server.firstCharToUpper(columnName)
            }

            ev := reflect.New(elemType) // New slice struct element
            // nv := reflect.MakeSlice(rv.Type(), 0, 0) // New slice for fill
            ignored := []byte{}
            scanArgs := make([]interface{}, len(fields))
            for i, fieldName := range fields {
                fv := ev.Elem().FieldByName(fieldName)
                if fv.Kind() != reflect.Invalid {
                    scanArgs[i] = fv.Addr().Interface()
                } else {
                    scanArgs[i] = &ignored
                }
            }
            return ReflectItem{
                Item:     ev,
                scanArgs: scanArgs,
            }
        },
    }
    datapools.Store(query, pool)
}
ri = pool.Get().(ReflectItem)

// 复用 ev 和 scanArgs
ev = ri.Item
scanArgs = ri.scanArgs

// 开始扫描
nv := rv.Slice(0, 0)
for rows.Next() { // for each rows
    err = rows.Scan(scanArgs...)
    if err != nil {
        return err
    }
    nv = reflect.Append(nv, ev.Elem())
}
rv.Set(nv) // return rows data back to caller
pool.Put(ri)
// 结束扫描

After several optimizations, the job that was executed in 24 minutes was successfully reduced to 18 minutes.

Summarize

• The implementation of golang prepare requires further understanding. In the case of using prepare, how the connection is reused is rather confusing.
• In the case of the same query, but the scanning struct type is different, there will be problems. The data pool of the scan parameter should be keyed by the structure type.