sql - 更快的 sqlite3 查询？我需要尽快处理 100 万行

Question

在 golang 中读取 sqlite3 表的最快方法是什么？

package main

import (
    "fmt"
    "database/sql"
    _ "github.com/mattn/go-sqlite3"
    "log"
    "time"
)

func main() {
    start := time.Now()

    db, err := sql.Open("sqlite3", "/Users/robertking/go/src/bitbucket.org/thematicanalysis/optimization_test/robs.db")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()
    rows, err := db.Query("select * from data")
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()

    for rows.Next() {
    }
    err = rows.Err()
    if err != nil {
        log.Fatal(err)
    }
    fmt.Println(time.Since(start))
}

这在 Go 中需要 8 秒，因为 .Next 是 slow .在 python 中，fetchall 只需要 4 秒!我在 GO 中重写以获得性能而不是失去性能。

这是 python 代码，我在 go 中找不到 fetchall 的等价物:

import time

start = time.time()
import sqlite3
conn = sqlite3.connect('/Users/robertking/go/src/bitbucket.org/thematicanalysis/optimization_test/robs.db')
c = conn.cursor()
c.execute("SELECT * FROM data")
x = c.fetchall()
print time.time() - start

编辑:添加赏金。我正在读取 go、python 和 C 中的数据，这是结果。不想用 C，但如果 GO 不快，我会坚持用 python。:

py: 2.45s
go: 2.13s (using github.com/mxk/go-sqlite/sqlite3 instead of github.com/mattn/go-sqlite3)
c:  0.32s

我觉得 go 应该更接近 c 方面的东西？有谁知道如何让它更快？是否可以避免使用只读模式的互斥锁？

编辑:

似乎所有的 sqlite3 实现都很慢(反射太多，转换的 cgo 调用太多)。所以我只需要编写自己的界面。

这是架构:

CREATE TABLE mytable
(
  c0   REAL,
  c1   INTEGER,
  c15  TEXT,
  c16  TEXT,
  c17  TEXT,
  c18  TEXT,
  c19  TEXT,
  c47  TEXT,
  c74  REAL DEFAULT 0,
  c77  TEXT,
  c101 TEXT,
  c103 TEXT,
  c108 TEXT,
  c110 TEXT,
  c125 TEXT,
  c126 TEXT,
  c127 REAL DEFAULT 0,
  x    INTEGER
    PRIMARY KEY
);

查询是动态的，但通常是这样的:

SELECT c77,c77,c125,c126,c127,c74 from mytable

编辑:

看起来我会 fork sqlite3 实现并制作一些专注于性能的方法，

这是一些更快的代码示例:。

package main


/*
 #cgo LDFLAGS: -l sqlite3

#include "sqlite3.h"
*/
import "C"

import (
    //"database/sql"
    "log"
    "reflect"
    "unsafe"
)

type Row struct {
    v77 string
    v125 string
    v126 string
    v127 float64
    v74 float64
}

// cStr returns a pointer to the first byte in s.
func cStr(s string) *C.char {
    h := (*reflect.StringHeader)(unsafe.Pointer(&s))
    return (*C.char)(unsafe.Pointer(h.Data))
}

func main() {
    getDataFromSqlite()
}

func getDataFromSqlite() {
    var db *C.sqlite3
    name := "../data_dbs/all_columns.db"
    rc := C.sqlite3_open_v2(cStr(name+"\x00"), &db, C.SQLITE_OPEN_READONLY, nil)

  var stmt *C.sqlite3_stmt;
  rc = C.sqlite3_prepare_v2(db, cStr("SELECT c77,c125,c126,c127,c74 from data\x00"), C.int(-1), &stmt, nil);
  rc = C.sqlite3_reset(stmt);

    var result C.double
    result = 0.0
    rc = C.sqlite3_step(stmt)
    for rc == C.SQLITE_ROW {
    C.GoString((*C.char)(unsafe.Pointer(C.sqlite3_column_text(stmt, 0))))
    C.GoString((*C.char)(unsafe.Pointer(C.sqlite3_column_text(stmt, 1))))
    C.GoString((*C.char)(unsafe.Pointer(C.sqlite3_column_text(stmt, 2))))
    C.sqlite3_column_double(stmt, 3)
    result += C.sqlite3_column_double(stmt, 4)
        rc = C.sqlite3_step(stmt)
  }
    log.Println(result)
}

Answer 1

简介

我的假设是我们在这里如何衡量性能有问题，所以我编写了一个小 Go 程序来生成记录并将它们保存到 SQLite 数据库以及一个 Python 和 Go 实现的小任务在那些记录上。

您可以在 https://github.com/mwmahlberg/sqlite3perf 找到相应的存储库

数据模型

生成的记录包括

• ID:A row ID generated by SQLite
• rand:A hex encoded 8 byte pseudo-random value
• 哈希:A hex encoded SHA256 hash of the unencoded rand

表的schema比较简单:

sqlite> .schema
CREATE TABLE bench (ID int PRIMARY KEY ASC, rand TEXT, hash TEXT);

首先我生成了 150 万条记录，然后用

清空了 sqlite 数据库

$ ./sqlite3perf generate -r 1500000 -v

接下来，我针对这 150 万条记录调用了 Go 实现。 Go 和 Python 实现基本上都执行相同的简单任务:

1. 从数据库中读取所有条目。
2. 对于每一行，从十六进制解码随机值，然后根据结果创建一个 SHA256 十六进制。
3. 将生成的 SHA256 十六进制字符串与存储在数据库中的字符串进行比较
4. 如果匹配，则继续，否则中断。

假设

我的明确假设是 Python 执行了某种类型的延迟加载和/或什至可能执行 SQL 查询。

结果

Go 实现

$ ./sqlite3perf bench
2017/12/31 15:21:48 bench called
2017/12/31 15:21:48 Time after query: 4.824009ms
2017/12/31 15:21:48 Beginning loop
2017/12/31 15:21:48 Acessing the first result set 
    ID 0,
    rand: 6a8a4ad02e5e872a,
    hash: 571f1053a7c2aaa56e5c076e69389deb4db46cc08f5518c66a4bc593e62b9aa4
took 548.32µs
2017/12/31 15:21:50 641,664 rows processed
2017/12/31 15:21:52 1,325,186 rows processed
2017/12/31 15:21:53 1,500,000 rows processed
2017/12/31 15:21:53 Finished loop after 4.519083493s
2017/12/31 15:21:53 Average 3.015µs per record, 4.523936078s overall

注意“查询后的时间”的值(查询命令返回所用的时间)以及在开始对结果集进行迭代后访问第一个结果集所用的时间。

Python 实现

$ python bench.py 
12/31/2017 15:25:41 Starting up
12/31/2017 15:25:41 Time after query: 1874µs
12/31/2017 15:25:41 Beginning loop
12/31/2017 15:25:44 Accessing first result set
    ID: 0
    rand: 6a8a4ad02e5e872a
    hash: 571f1053a7c2aaa56e5c076e69389deb4db46cc08f5518c66a4bc593e62b9aa4
took 2.719312 s
12/31/2017 15:25:50 Finished loop after 9.147431s
12/31/2017 15:25:50 Average: 6.098µs per record, 0:00:09.149522 overall

再次注意“查询后的时间”的值以及访问第一个结果集所用的时间。

总结

在发送 SELECT 查询后，Go 实现需要相当长的时间才能返回，而相比之下，Python 似乎快得惊人。然而，从实际访问第一个结果集的时间来看，我们可以看到 Go 实现实际访问第一个结果集的速度提高了 500 多倍(5.372329 毫秒 vs 2719.312 毫秒)，任务的速度大约是原来的两倍手头上的 Python 实现。

注意事项

• 为了证明 Python 实际上对结果集进行延迟加载的假设，必须访问每一行和每一列，以确保 Python 被迫实际从数据库中读取值。<
• 我选择了一个哈希任务，因为大概 SHA256 的实现在两种语言中都得到了高度优化。

结论

Python 似乎确实会延迟加载结果集，甚至可能不执行查询，除非实际访问了相应的结果集。在这个模拟场景中，mattn 的 Go 语言 SQLite 驱动程序的性能比 Python 高出大约 100% 到几个数量级，具体取决于您想要做什么。

编辑:因此，为了快速处理，请在 Go 中实现您的任务。虽然发送实际查询需要更长的时间，但访问结果集的各个行要快得多。我建议从您数据的一小部分开始，比如 50k 条记录。然后，为了进一步改进您的代码，请使用 profiling找出你的瓶颈。根据您在处理过程中要执行的操作，pipelines例如可能会有所帮助，但是如果没有实际的代码或详尽的描述，如何提高手头任务的处理速度很难说。