为什么在sqlite中 :memory: 如此缓慢？
我一直试图查看使用内存版的sqlite和磁盘版sqlite是否有任何性能改进。基本上，我想要通过消耗更多的启动时间和内存来获得极快的查询速度，而这些查询在应用程序运行期间不会访问磁盘。
然而，以下基准测试只给出了1.5倍的提速。在这里，我生成了100万个随机数据行，并将其加载到相同表格的磁盘版本和内存版本中。然后我对两个数据库运行随机查询，返回大约300k大小的集合。我希望内存版会快得多，但正如之前提到的，我只得到了1.5倍的加速效果。
我尝试使用了其他大小的数据库和查询集；随着数据库中的行数增加， :memory: 的优势似乎确实会增加。但我不确定为什么优势如此之小。我有一些假设：
- 表格（按行数计算）不够大， :memory: 无法显著地胜出
- 更多的连接/表格会使 :memory: 的优势更为明显
- 可能存在某种连接或操作系统级别的缓存，以某种方式使得先前的结果仍然可访问，这会影响基准测试的准确性
- 可能存在一些隐藏的磁盘访问行为，我还没有看到（尚未尝试使用 lsof，但我已关闭了日志记录的 PRAGMA）
我做错了什么吗？有任何关于为什么 :memory: 没有产生几乎即时查找的想法吗？以下是基准测试：
==> sqlite_memory_vs_disk_benchmark.py <==
#!/usr/bin/env python
"""Attempt to see whether :memory: offers significant performance benefits.
"""
import os
import time
import sqlite3
import numpy as np
def load_mat(conn,mat):
c = conn.cursor()
#Try to avoid hitting disk, trading safety for speed.
#https://dev59.com/3HVC5IYBdhLWcg3wZwTj
c.execute('PRAGMA temp_store=MEMORY;')
c.execute('PRAGMA journal_mode=MEMORY;')
# Make a demo table
c.execute('create table if not exists demo (id1 int, id2 int, val real);')
c.execute('create index id1_index on demo (id1);')
c.execute('create index id2_index on demo (id2);')
for row in mat:
c.execute('insert into demo values(?,?,?);', (row[0],row[1],row[2]))
conn.commit()
def querytime(conn,query):
start = time.time()
foo = conn.execute(query).fetchall()
diff = time.time() - start
return diff
#1) Build some fake data with 3 columns: int, int, float
nn = 1000000 #numrows
cmax = 700 #num uniques in 1st col
gmax = 5000 #num uniques in 2nd col
mat = np.zeros((nn,3),dtype='object')
mat[:,0] = np.random.randint(0,cmax,nn)
mat[:,1] = np.random.randint(0,gmax,nn)
mat[:,2] = np.random.uniform(0,1,nn)
#2) Load it into both dbs & build indices
try: os.unlink('foo.sqlite')
except OSError: pass
conn_mem = sqlite3.connect(":memory:")
conn_disk = sqlite3.connect('foo.sqlite')
load_mat(conn_mem,mat)
load_mat(conn_disk,mat)
del mat
#3) Execute a series of random queries and see how long it takes each of these
numqs = 10
numqrows = 300000 #max number of ids of each kind
results = np.zeros((numqs,3))
for qq in range(numqs):
qsize = np.random.randint(1,numqrows,1)
id1a = np.sort(np.random.permutation(np.arange(cmax))[0:qsize]) #ensure uniqueness of ids queried
id2a = np.sort(np.random.permutation(np.arange(gmax))[0:qsize])
id1s = ','.join([str(xx) for xx in id1a])
id2s = ','.join([str(xx) for xx in id2a])
query = 'select * from demo where id1 in (%s) AND id2 in (%s);' % (id1s,id2s)
results[qq,0] = round(querytime(conn_disk,query),4)
results[qq,1] = round(querytime(conn_mem,query),4)
results[qq,2] = int(qsize)
#4) Now look at the results
print " disk | memory | qsize"
print "-----------------------"
for row in results:
print "%.4f | %.4f | %d" % (row[0],row[1],row[2])
这是结果。请注意，在相当广泛的查询大小范围内，磁盘所需的时间大约是内存的1.5倍。
[ramanujan:~]$python -OO sqlite_memory_vs_disk_clean.py
disk | memory | qsize
-----------------------
9.0332 | 6.8100 | 12630
9.0905 | 6.6953 | 5894
9.0078 | 6.8384 | 17798
9.1179 | 6.7673 | 60850
9.0629 | 6.8355 | 94854
8.9688 | 6.8093 | 17940
9.0785 | 6.6993 | 58003
9.0309 | 6.8257 | 85663
9.1423 | 6.7411 | 66047
9.1814 | 6.9794 | 11345
相对于磁盘，内存不应该几乎是即时的吗？出了什么问题吗？
编辑
这里有一些好的建议。
我想对我来说最重要的一点是，**可能没有办法使 :memory: 绝对更快，但可以使磁盘访问相对更慢。**
换句话说，基准测试足以衡量内存的实际性能，但并不能衡量磁盘的实际性能（例如因为cache_size pragma太大或者我没有进行写操作）。我会尝试调整这些参数，并在有机会时发布我的发现。
话虽如此，如果有人认为我可以从内存数据库中挤取更多速度（除了通过增加cache_size和default_cache_size之外，我将这样做），我非常愿意听取建议...