更快的sqlite 3查询？我需要尽快处理100万行

发布时间：2020-12-12 19:13:44 所属栏目：百科来源：网络整理

导读：在golang中读取sqlite3表的最快方法是什么？ package mainimport ( "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/themati

在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

我觉得应该更接近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,c74 from datax00"),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)
}

介绍

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

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

数据模型

生成的记录包括

> ID：A row ID generated by SQLite
>兰特：A hex encoded 8 byte pseudo-random value
> hash：A hex encoded SHA256 hash of the unencoded 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记录。然后，为了进一步改进您的代码，请使用profiling来识别您的瓶颈。例如，根据您在处理期间要执行的操作，pipelines可能会有所帮助，但如果没有实际代码或详细说明，很难说如何提高手头任务的处理速度。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!