10GB数据下MySQL与SQLite搜索速度差异问题求助

你遇到的这种查询速度大幅下降不是SQLite的正常表现，主要是因为MySQL默认针对大查询做了索引和配置优化，而SQLite转档后没有继承这些优化，且默认配置更偏向轻量场景。下面是具体的优化建议：

1. 检查并添加合适的索引 #

MySQL在迁移前大概率已经为column12创建了索引，而SQLite转档过程中可能没有同步创建索引，导致查询时全表扫描（10GB数据全扫必然缓慢）。

• 先查看表的索引情况：

  PRAGMA index_list(hugesql);
  

• 如果没有针对column12的索引，根据查询类型创建：
  • 若你的查询是前缀匹配（比如LIKE 'dataexample%'，无前置%），创建普通B树索引即可：

    CREATE INDEX idx_hugesql_column12 ON hugesql(column12);
    

  • 若需要任意位置的模糊查询（比如'%dataexample%'），普通索引无法生效，需创建FTS5全文索引：

    -- 创建虚拟全文索引表
    CREATE VIRTUAL TABLE hugesql_fts USING fts5(column12, content='hugesql', content_rowid='rowid');
    -- 同步现有数据到索引表
    INSERT INTO hugesql_fts(rowid, column12) SELECT rowid, column12 FROM hugesql;
    -- 后续查询用全文匹配
    SELECT * FROM hugesql WHERE rowid IN (SELECT rowid FROM hugesql_fts WHERE column12 MATCH 'dataexample');
    

2. 调整SQLite核心配置参数 #

SQLite默认配置针对小数据库优化，10GB的大库需要修改参数提升性能：
在查询前执行以下PRAGMA命令（可放到工具初始化脚本或Python连接初始化逻辑中）：

-- 设置缓存大小为2GB（负数单位是MB，根据你的内存调整，建议设为内存的1/4~1/2）
PRAGMA cache_size = -2000000;
-- 降低同步级别，减少磁盘IO等待（NORMAL比FULL快，数据安全性足够应对一般场景）
PRAGMA synchronous = NORMAL;
-- 启用WAL模式，提升读写并发和查询速度
PRAGMA journal_mode = WAL;
-- 开启内存映射，让SQLite直接映射整个数据库到内存，减少磁盘读取
PRAGMA mmap_size = 10737418240; -- 10GB，和你的数据库大小一致

注意：这些配置是会话级的，每次连接数据库都需要重新设置；若想永久生效，可将参数写入sqlite3配置文件，或在创建连接时自动执行。

3. 优化存储介质与文件位置 #

• 把SQLite数据库文件放到SSD上，HDD的随机读写速度远低于SSD，10GB数据的全表扫描或索引查询差异极大；
• 避免将数据库放到网络共享盘或USB存储设备，这类设备的IO延迟会严重拖慢查询速度。

4. 工具与代码层面的优化 #

• 使用Python的sqlite3模块时，确保使用参数化查询（避免SQL注入的同时，让SQLite缓存查询计划）：

  import sqlite3
  conn = sqlite3.connect('your_db.db')
  # 先执行配置参数
  conn.execute("PRAGMA cache_size = -2000000")
  conn.execute("PRAGMA synchronous = NORMAL")
  conn.execute("PRAGMA journal_mode = WAL")
  conn.execute("PRAGMA mmap_size = 10737418240")
  cursor = conn.cursor()
  # 参数化查询
  cursor.execute("SELECT * FROM hugesql WHERE column12 LIKE ?", ('dataexample',))
  results = cursor.fetchall()
  

• 在DB Browser for SQLite中，进入「编辑」→「首选项」→「SQLite」，调整缓存大小为更大的值，开启WAL模式。

完成以上优化后，SQLite的查询速度应该能接近甚至超过MySQL的表现——毕竟SQLite在单用户、本地存储场景下的性能并不弱，只是需要针对性调整配置和索引。

内容的提问来源于stack exchange，提问作者birisix