postgresql - 使用 `database/sql` 查询比直接查询数据库慢很多

Question

我正在使用 golang 应用程序和 psql 对本地 postgresql 实例运行相同的查询。时间差异很大，我想知道为什么。使用 explain/analyze 查询耗时 1ms，使用 golang 中的 database/sql 耗时 24ms。我在下面添加了我的代码片段。我意识到解释/分析可能不等同于直接查询数据库，也可能涉及一些网络延迟，但差异仍然很大。为什么会有这样的差异？

编辑:我已经用 10 多个查询的样本量尝试了上面的方法，但差异仍然存在。

postgres=# \timing
Timing is on.
postgres=# select 1;
 ?column?
----------
        1
(1 row)

Time: 2.456 ms
postgres=# explain analyze select 1;
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1)
 Planning Time: 0.017 ms
 Execution Time: 0.012 ms
(3 rows)

Time: 3.748 ms

package main

    import (
        "database/sql"
        "fmt"
        _ "github.com/lib/pq"
        "time"
    )

    func main() {
        // setup database connection
        db, err := sql.Open("postgres", "host='localhost' port=5432 user='postgres' password='' dbname='postgres' sslmode=disable")
        if err != nil {
            panic(err)
        }

        // query database
        firstQueryStart := time.Now()
        _, err = db.Query("select 1;")
        firstQueryEnd := time.Now()
        if err != nil {
            panic(err)
        }
        fmt.Println(fmt.Sprintf("first query took %s", firstQueryEnd.Sub(firstQueryStart).String()))

        //run the same query a second time and measure the timing
        secondQueryStart := time.Now()
        _, err = db.Query("select 1;")
        secondQueryEnd := time.Now()
        if err != nil {
            panic(err)
        }
        fmt.Println(fmt.Sprintf("second query took %s", secondQueryEnd.Sub(secondQueryStart).String()))
    }

first query took 13.981435ms
second query took 13.343845ms

Answer 1

注意#1: sql.DB 不代表一个连接，而是代表一个连接池。

注 #2: sql.Open 初始化池，但它不需要实际打开连接，它只允许验证 dsn 输入，然后连接的打开将由池延迟处理。

你第一的原因db.Query很慢是因为您从一个新的连接池开始，该连接池有 0 个空闲(但打开)连接，因此 1st db.Query需要先与服务器建立新的连接，才能执行sql语句。

你第二的原因db.Query也很慢是因为 1st db.Query 创建的连接还没有被释放回池中，因此你的2 db.Query也需要先与服务器建立新的连接，才能执行sql语句。

---

要将连接释放回池中，您需要首先保留 db.Query 的主要返回值然后调用 Close 方法。

要从至少有一个可用连接的池开始，请调用 Ping 在初始化池之后。

---

例子:

func main() {
    // setup database connection
    db, err := sql.Open("postgres", "postgres:///?sslmode=disable")
    if err != nil {
        panic(err)
    } else if err := db.Ping(); err != nil {
        panic(err)
    }

    for i := 0; i < 5; i++ {
        // query database
        firstQueryStart := time.Now()
        rows, err := db.Query("select 1;")
        firstQueryEnd := time.Now()
        if err != nil {
            panic(err)
        }

        // put the connection back to the pool so
        // that it can be reused by next iteration
        rows.Close()

        fmt.Println(fmt.Sprintf("query #%d took %s", i, firstQueryEnd.Sub(firstQueryStart).String()))
    }
}

我机器上的时间(没有 db.Ping 只有 #0 很慢)

query #0 took 6.312676ms
query #1 took 102.88µs
query #2 took 66.702µs
query #3 took 64.694µs
query #4 took 208.016µs

我机器上的时间(带db.Ping#0 比不带快很多)

query #0 took 284.846µs
query #1 took 78.349µs
query #2 took 76.518µs
query #3 took 81.733µs
query #4 took 103.862µs

---

关于预处理语句的注释:

如果您正在执行不带参数的简单查询，例如db.Query("select 1 where true")那么您实际上只是在执行一个简单的查询。

但是，如果您正在执行带有参数的查询，例如db.Query("select 1 where $1", true)那么，实际上，您正在创建并执行准备好的语句。

参见 4.2. Value Expressions ，它说:

A value expression is one of the following: ...

• A positional parameter reference, in the body of a function definition or prepared statement
  ...

还有 Positional Parameters说:

A positional parameter reference is used to indicate a value that issupplied externally to an SQL statement. Parameters are used in SQLfunction definitions and in prepared queries. Some client librariesalso support specifying data values separately from the SQL commandstring, in which case parameters are used to refer to the out-of-linedata values.

postgres 的消息流协议(protocol)如何指定 simple queries 和 extended queries

The extended query protocol breaks down the above-described simplequery protocol into multiple steps. The results of preparatory stepscan be re-used multiple times for improved efficiency. Furthermore,additional features are available, such as the possibility ofsupplying data values as separate parameters instead of having toinsert them directly into a query string.

最后，在 lib/pq 的掩护下司机:

    ...

    // Check to see if we can use the "simpleQuery" interface, which is
    // *much* faster than going through prepare/exec
    if len(args) == 0 {
        return cn.simpleQuery(query)
    }

    if cn.binaryParameters {
        cn.sendBinaryModeQuery(query, args)

        cn.readParseResponse()
        cn.readBindResponse()
        rows := &rows{cn: cn}
        rows.rowsHeader = cn.readPortalDescribeResponse()
        cn.postExecuteWorkaround()
        return rows, nil
    }
    st := cn.prepareTo(query, "")
    st.exec(args)
    return &rows{
        cn:         cn,
        rowsHeader: st.rowsHeader,
    }, nil

    ...