python - 如何在Python 3.6中提高SQLite插入性能？

Question

背景
我想使用python向sqlite插入100万条记录。我尝试了很多方法来改进它，但仍然不太满意。数据库使用0.23秒（下面的搜索pass）将文件加载到内存，而SQLite使用1.77秒加载并插入到文件。
环境
英特尔酷睿i7-7700@3.6GHz
16GB内存
Micron 1100 256GB固态硬盘，Windows 10 x64
蟒蛇3.6.5明康达
sqlite3.2.6.0版
生成日期.py
我用与真实数据相同的格式生成100万个测试输入数据。

import time
start_time = time.time()
with open('input.ssv', 'w') as out:
    symbols = ['AUDUSD','EURUSD','GBPUSD','NZDUSD','USDCAD','USDCHF','USDJPY','USDCNY','USDHKD']
    lines = []
    for i in range(0,1*1000*1000):
        q1, r1, q2, r2 = i//100000, i%100000, (i+1)//100000, (i+1)%100000
        line = '{} {}.{:05d} {}.{:05d}'.format(symbols[i%len(symbols)], q1, r1, q2, r2)
        lines.append(line)
    out.write('\n'.join(lines))
print(time.time()-start_time, i)

输入.ssv
测试数据如下所示。

AUDUSD 0.00000 0.00001
EURUSD 0.00001 0.00002
GBPUSD 0.00002 0.00003
NZDUSD 0.00003 0.00004
USDCAD 0.00004 0.00005
...
USDCHF 9.99995 9.99996
USDJPY 9.99996 9.99997
USDCNY 9.99997 9.99998
USDHKD 9.99998 9.99999
AUDUSD 9.99999 10.00000
// total 1 million of lines, taken 1.38 second for Python code to generate to disk

Windows正确显示23999999字节的文件大小。
基线代码insertdata.py

import time
class Timer:
    def __enter__(self):
        self.start = time.time()
        return self
    def __exit__(self, *args):
        elapsed = time.time()-self.start
        print('Imported in {:.2f} seconds or {:.0f} per second'.format(elapsed, 1*1000*1000/elapsed)) 

with Timer() as t:
    with open('input.ssv', 'r') as infile:
        infile.read()

基本I/O

with open('input.ssv', 'r') as infile:
    infile.read()

0.13秒或7.6米每秒导入
它测试读取速度。

with open('input.ssv', 'r') as infile:
    with open('output.ssv', 'w') as outfile:
        outfile.write(infile.read()) // insert here

0.26秒或3.84米每秒导入
它在不解析任何内容的情况下测试读写速度

with open('input.ssv', 'r') as infile:
    lines = infile.read().splitlines()
    for line in lines:
        pass # do insert here

0.23秒或4.32米每秒导入
当我逐行解析数据时，它会获得很高的输出。
这让我们了解了IO和字符串处理操作在我的测试机器上的速度。
一写入文件

outfile.write(line)

0.52秒或1.93米每秒导入
2.拆分为浮动为字符串

tokens = line.split()
sym, bid, ask = tokens[0], float(tokens[1]), float(tokens[2])
outfile.write('{} {:.5f} {%.5f}\n'.format(sym, bid, ask)) // real insert here

在2.25秒或445 K/秒内导入
三。插入自动提交语句

conn = sqlite3.connect('example.db', isolation_level=None)
c.execute("INSERT INTO stocks VALUES ('{}',{:.5f},{:.5f})".format(sym,bid,ask))

当isolation_level=None（autocommit）时，程序需要很多小时才能完成（我等不到这么长的时间）
注意，输出数据库文件大小为32325632字节，即32MB。它比输入文件ssv文件大小23MB大10MB。
四。用BEGIN插入语句（延迟）

conn = sqlite3.connect('example.db', isolation_level=’DEFERRED’) # default
c.execute("INSERT INTO stocks VALUES ('{}',{:.5f},{:.5f})".format(sym,bid,ask))

7.50秒或每秒133296次导入
这与写入 BEGIN、 BEGIN TRANSACTION或 BEGIN DEFERRED TRANSACTION相同，而不是 BEGIN IMMEDIATE或 BEGIN EXCLUSIVE。
5个按准备好的报表插入
使用上面的事务可以得到令人满意的结果，但是应该注意的是，不希望使用python的字符串操作，因为它受到sql注入的影响。此外，与参数替换相比，使用字符串的速度较慢。

c.executemany("INSERT INTO stocks VALUES (?,?,?)", [(sym,bid,ask)])

以2.31秒或432124秒的速度导入
6。关闭同步
在数据到达物理磁盘表面之前，当synchronous未设置为 EXTRA或 FULL时，电源故障会损坏数据库文件当我们能够保证电源和操作系统的正常运行时，我们可以将synchronous设置为 OFF，这样在数据传输到操作系统层之后就不会同步。

conn = sqlite3.connect('example.db', isolation_level='DEFERRED')
c = conn.cursor()
c.execute('''PRAGMA synchronous = OFF''')

2.25秒或444247每秒导入
7号。关闭日志，这样既不会回滚也不会原子提交
在某些应用程序中，不需要数据库的回滚功能，例如时间序列数据插入。当我们可以确保电源和操作系统正常运行时，我们可以将 journal_mode转换为 off以便完全禁用回滚日志，并禁用原子提交和回滚功能。

conn = sqlite3.connect('example.db', isolation_level='DEFERRED')
c = conn.cursor()
c.execute('''PRAGMA synchronous = OFF''')
c.execute('''PRAGMA journal_mode = OFF''')

2.22秒或每秒450653次导入
8个。使用内存数据库
在某些应用程序中，不需要将数据写回磁盘，例如向web应用程序提供查询数据的应用程序。

conn = sqlite3.connect(":memory:")

以2.17秒或460405秒的速度导入
9号循环中更快的Python代码
我们应该考虑将每一位计算都保存在一个密集的循环中，例如避免对变量和字符串操作的赋值。
9a.避免变量赋值

tokens = line.split()
c.executemany("INSERT INTO stocks VALUES (?,?,?)", [(tokens[0], float(tokens[1]), float(tokens[2]))])

2.10秒或475964每秒导入
9b.避免字符串.split（）
当我们可以将空间分隔的数据作为固定宽度格式处理时，我们可以直接指示每个数据到数据头的距离。
意思是 line.split()[1]变成 line[7:14]

c.executemany("INSERT INTO stocks VALUES (?,?,?)", [(line[0:6], float(line[7:14]), float(line[15:]))])

以1.94秒或每秒514661秒的速度导入
9c.避免float（）到？
当我们将 executemany()与 ?占位符一起使用时，不需要事先将字符串转换为float。

executemany("INSERT INTO stocks VALUES (?,?,?)", [(line[0:6], line[7:14], line[15:])])

1.59秒或每秒630520次导入
10个迄今为止最快的全功能和健壮的代码

import time
class Timer:    
    def __enter__(self):
        self.start = time.time()
        return self
    def __exit__(self, *args):
        elapsed = time.time()-self.start
        print('Imported in {:.2f} seconds or {:.0f} per second'.format(elapsed, 1*1000*1000/elapsed))
import sqlite3
conn = sqlite3.connect('example.db')
c = conn.cursor()
c.execute('''DROP TABLE IF EXISTS stocks''')
c.execute('''CREATE TABLE IF NOT EXISTS stocks
             (sym text, bid real, ask real)''')
c.execute('''PRAGMA synchronous = EXTRA''')
c.execute('''PRAGMA journal_mode = WAL''')
with Timer() as t:
    with open('input.ssv', 'r') as infile:
        lines = infile.read().splitlines()
        for line in lines:
            c.executemany("INSERT INTO stocks VALUES (?,?,?)", [(line[0:6], line[7:14], line[15:])])
        conn.commit()
        conn.close()

1.77秒或每秒564611次导入
能快点吗？
我有一个23MB的文件，有100万条记录，其中一段文字作为符号名，两个浮点数作为出价和出价。当您在上面搜索 pass时，测试结果显示每秒向普通文件插入4.32 m。当我插入到一个健壮的sqlite数据库时，它会下降到每秒0.564m的插入。在sqlite中，还有什么可以让它更快的呢？如果不是sqlite而是其他数据库系统呢？

Answer 1

如果python的解释器实际上是计时（第9节）与sqlite性能的一个重要因素，您可能会发现PyPy可以显著提高性能（python的sqlite3接口是用纯python实现的）。但是，如果您正在执行更多的字符串操作或有for循环，那么从CPython切换是值得的。
显然，如果SQLite之外的性能真的很重要，你可以尝试用更快的语言编写类似C/C++的语言。多线程可能有帮助，也可能没有帮助，这取决于数据库锁是如何实现的。