背景
我想使用 Python 向 SQLite 插入 100 万条记录。我尝试了很多方法来改进它，但仍然不太满意。数据库使用 0.23 秒将文件加载到内存（搜索pass如下）但 SQLite 加载并插入文件需要 1.77 秒。
环境
英特尔酷睿 i7-7700 @ 3.6GHz
16GB内存
美光 1100 256GB 固态硬盘，Windows 10 x64
Python 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 正确显示 23,999,999 字节文件大小。
基线代码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()
基本输入/输出
with open('input.ssv', 'r') as infile:
infile.read()
导入时间为 0.13 秒或每秒 7.6 M
它测试读取速度。
with open('input.ssv', 'r') as infile:
with open('output.ssv', 'w') as outfile:
outfile.write(infile.read()) // insert here
导入时间为 0.26 秒或每秒 3.84 M
它测试读写速度而不解析任何内容
with open('input.ssv', 'r') as infile:
lines = infile.read().splitlines()
for line in lines:
pass # do insert here
导入时间为 0.23 秒或每秒 4.32 M
当我逐行解析数据时，它获得了非常高的输出。
这让我们了解了我的测试机器上 IO 和字符串处理操作的速度。
1. 写入文件
outfile.write(line)
导入时间为 0.52 秒或每秒 1.93 M
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
3. 使用自动提交插入语句
conn = sqlite3.connect('example.db', isolation_level=None)
c.execute("INSERT INTO stocks VALUES ('{}',{:.5f},{:.5f})".format(sym,bid,ask))
当isolation_level = None（自动提交）时，程序需要很多小时才能完成（我等不及这么长时间）
请注意，输出数据库文件大小为 32,325,632 字节，即 32MB。它比输入文件 ssv 文件大小 23MB 大 10MB。
4. 使用 BEGIN 插入语句（DEFERRED）
conn = sqlite3.connect('example.db', isolation_level=’DEFERRED’) # default
c.execute("INSERT INTO stocks VALUES ('{}',{:.5f},{:.5f})".format(sym,bid,ask))
导入时间为 7.50 秒，即每秒 133,296 个
这和写作是一样的BEGIN, BEGIN TRANSACTION or BEGIN DEFERRED TRANSACTION, not BEGIN IMMEDIATE nor BEGIN EXCLUSIVE.
5. 通过准备好的语句插入
使用上面的事务给出了令人满意的结果，但应该注意的是，使用Python的字符串操作是不受欢迎的，因为它会受到SQL注入。此外，与参数替换相比，使用字符串速度较慢。
c.executemany("INSERT INTO stocks VALUES (?,?,?)", [(sym,bid,ask)])
导入时间为 2.31 秒，即每秒 432,124 个
6. 关闭同步
当同步未设置为时，电源故障会损坏数据库文件EXTRA nor FULL在数据到达物理磁盘表面之前。当我们可以确保电源和操作系统正常时，我们可以将同步转为OFF这样数据交给OS层后就不同步了。
conn = sqlite3.connect('example.db', isolation_level='DEFERRED')
c = conn.cursor()
c.execute('''PRAGMA synchronous = OFF''')
导入时间为 2.25 秒，即每秒 444,247 个
7. 关闭日志，这样就没有回滚也没有原子提交
在某些应用中不需要数据库的回滚功能，例如时间序列数据插入。当我们可以确保电源和操作系统健康时，我们可以转向journal_mode to 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 秒，即每秒 450,653 个
8.使用内存数据库
在某些应用程序中，不需要将数据写回磁盘，例如向 Web 应用程序提供查询数据的应用程序。
conn = sqlite3.connect(":memory:")
导入时间为 2.17 秒，即每秒 460,405 个
9.循环中更快的Python代码
我们应该考虑将每一位计算保存在密集循环中，例如避免分配给变量和字符串操作。
9a.避免给变量赋值
tokens = line.split()
c.executemany("INSERT INTO stocks VALUES (?,?,?)", [(tokens[0], float(tokens[1]), float(tokens[2]))])
导入时间为 2.10 秒，即每秒 475,964 个
9b.避免 string.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 秒，即每秒 514,661 个
9c.避免 float() 到 ?
当我们使用时executemany() with ?占位符，我们不需要事先将字符串转为float。
executemany("INSERT INTO stocks VALUES (?,?,?)", [(line[0:6], line[7:14], line[15:])])
导入时间为 1.59 秒，即每秒 630,520 个
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 秒，即每秒 564,611 个
有可能变得更快吗？
我有一个 23MB 的文件，其中有 100 万条记录，由一段文本作为交易品种名称和 2 个浮点数作为出价和要价组成。当你搜索时pass上面，测试结果显示每秒向普通文件插入 4.32 M。当我插入到一个强大的 SQLite 数据库时，它下降到每秒 0.564 M 插入。您可能还会想到什么来使其在 SQLite 中变得更快？如果不是 SQLite 而是其他数据库系统怎么办？
