SQLite 性能基准 - 为什么 memory 这么慢...只有磁盘速度的 1.5 倍

python
import sqlite3
import time
def create_in_memory_db():
    conn = sqlite3.connect(':memory:')
    cursor = conn.cursor()
    cursor.execute('''CREATE TABLE test (id INTEGER PRIMARY KEY, name TEXT)''')
    for i in range(10000):
        cursor.execute('''INSERT INTO test (name) VALUES (?)''', (f'Test {i}',))
    conn.commit()
    return conn
def create_disk_db():
    conn = sqlite3.connect('disk_db.sqlite')
    cursor = conn.cursor()
    cursor.execute('''CREATE TABLE test (id INTEGER PRIMARY KEY, name TEXT)''')
    for i in range(10000):
        cursor.execute('''INSERT INTO test (name) VALUES (?)''', (f'Test {i}',))
    conn.commit()
    return conn
def run_queries(conn):
    cursor = conn.cursor()
    start_time = time.time()
    cursor.execute('''SELECT COUNT(*) FROM test''')
    end_time = time.time()
    print(f"Query execution time: {end_time - start_time} seconds")
# 在内存中创建数据库并运行查询
in_memory_conn = create_in_memory_db()
print("In-memory database performance:")
run_queries(in_memory_conn)
# 在磁盘上创建数据库并运行查询
disk_conn = create_disk_db()
print("Disk-based database performance:")
run_queries(disk_conn)