更快的sqlite3查询？我需要尽可能快地处理100万行

作者：xiaomanni521125655 | 来源：互联网 | 2023-01-03 15:43

如何解决《更快的sqlite3查询？我需要尽可能快地处理100万行》经验，为你挑选了1个好方法。

在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很慢.在python fetchall中只需4秒!我在GO中重写以获得性能而不是失去性能.

这是python代码,我找不到相应的fetchallgo:

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

我觉得应该更接近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

编辑:

看起来我会分叉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)
}

Markus W Mah.. 23

介绍

我的假设是我们在这里测量性能的方法存在问题,因此我编写了一个Go程序来生成记录并将它们保存到SQLite数据库中,以及Python和Go实现这些记录的小任务. .

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

数据模型

生成的记录包括

ID:SQLite生成的行ID

rand:十六进制编码的 8字节伪随机值

hash:未编码的十六进制编码 SHA256哈希rand

表的架构相对简单:

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

首先,我生成了1.5M记录,然后使用了sqlite数据库

$ ./sqlite3perf generate -r 1500000 -v

接下来,我针对那些1.5M记录调用了Go实现.Go以及Python实现基本上都执行相同的简单任务:

读取数据库中的所有条目.

对于每一行,从十六进制解码随机值,然后从结果中创建一个SHA256十六进制.

将生成的SHA256十六进制字符串与存储在数据库中的字符串进行比较

如果匹配,继续,否则打破.

假设

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

结果

去实施

$ ./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实现比实际访问第一个结果集(5.372329ms vs 2719.312ms)快了500倍,并且任务速度快了两倍手头上的Python实现.

笔记

为了证明Python实际上对结果集进行延迟加载的假设,必须访问每一行和每一行,以确保Python被强制实际读取数据库中的值.

我选择了一个散列任务,因为可能是SHA256的实现在两种语言中都得到了高度优化.

结论

Python似乎确实延迟加载结果集,甚至可能甚至不执行查询,除非实际访问了相应的结果集.在这个模拟场景中,对于Go来说,mattn的SQLite驱动程序的性能大约在100%到几个数量级之间,具体取决于你想要做什么.

编辑:因此,为了快速处理,请在Go中执行您的任务.虽然发送实际查询需要更长时间,但访问结果集的各个行的速度要快得多.我建议从一小部分数据开始,比如50k记录.然后,为了进一步改进您的代码,使用分析来确定您的瓶颈.根据您在处理期间要执行的操作,例如,管道可能会有所帮助,但如果没有实际代码或详细说明,很难说如何提高手头任务的处理速度.

1> Markus W Mah..：

介绍