Pandas read_sql查询SQLite速度极慢，此表现是否符合预期？

兄弟，这种50秒的简单计数查询完全不符合预期，你的数据库大概率是缺少关键索引，再加上没配置SQLite的性能参数，才导致慢成这样。下面给你拆解清楚，再给你实打实的优化方案：

一、性能是否符合预期？ #

绝对不符合。正常情况下，哪怕是400万行的表，只要ID列有主键/索引，COUNT(ID)这类查询应该在几十毫秒到几秒内完成。你现在CPU使用率只有6%-7%，说明大部分时间都卡在磁盘IO上——因为没有索引的话，SQLite要做全表扫描，得把整个2.2GB的数据库文件从头到尾读一遍，机械硬盘的话这个过程确实会很慢，CPU只能等着磁盘喂数据。

二、数据库是否存在问题？ #

大概率是以下几个问题：

• ID列没有主键/索引：SQLite不会自动给普通列建索引，如果你没把ID设为主键，也没手动建索引，那查询COUNT(ID)就必须全表扫描
• 存储介质是机械硬盘（HDD）：HDD的顺序读取速度一般只有100-200MB/s，读2.2GB的文件至少要10秒以上，再加上SQLite的处理开销，50秒就不奇怪了
• SQLite性能参数没优化：默认配置的缓存很小、同步模式保守，会拖慢查询速度

三、具体优化手段 #

按优先级从高到低来：

1. 给ID列添加主键/索引（最核心） #

这是能让速度飙升的关键一步。如果ID是唯一且非空的，直接设为主键：

ALTER TABLE MY_TABLE ADD PRIMARY KEY (ID);

如果ID有重复不能设主键，就建普通索引：

CREATE INDEX idx_my_table_id ON MY_TABLE(ID);

加完索引后，COUNT(ID)会直接利用索引的统计信息，不需要全表扫描，速度能快几十上百倍。

2. 调整SQLite的性能参数 #

通过连接时设置PRAGMA参数，大幅提升IO效率：

• 开启WAL模式：这是SQLite性能提升的黄金配置，能减少磁盘锁开销，提升读写速度：

  # SQLAlchemy方式
  engine = sqlalchemy.create_engine(
      'sqlite:///my_path/my_db.db',
      connect_args={
          "check_same_thread": False,
          "timeout": 30,
          "journal_mode": "WAL"
      }
  )
  # 原生sqlite3方式
  conn = sqlite3.connect('my_path/my_db.db')
  conn.execute('PRAGMA journal_mode = WAL;')
  

• 增大缓存大小：把更多数据缓存到内存，减少磁盘IO。比如设置2GB缓存（负数表示KB）：

  # SQLAlchemy在connect_args里加
  "cache_size": -2000000
  # 原生sqlite3
  conn.execute('PRAGMA cache_size = -2000000;')
  

• 降低同步模式：如果你的数据不是极端重要（比如可以接受小概率的丢失），可以把同步模式设为NORMAL或OFF，减少磁盘同步的等待时间：

  PRAGMA synchronous = NORMAL;
  

3. 升级存储介质到SSD #

如果现在用的是HDD，换成SSD后，顺序读取速度能达到500MB/s以上，哪怕全表扫描2.2GB的文件也只需要几秒，整体性能会有质的飞跃。

4. 优化查询语句 #

如果ID是主键，COUNT(*)比COUNT(ID)更快——因为SQLite可以直接从主键索引里获取总行数，不需要遍历ID列的值：

SELECT COUNT(*) FROM MY_TABLE;

5. 其他可选优化 #

• 用原生sqlite3连接而非SQLAlchemy：虽然你说结果类似，但原生连接可以更直接地控制参数，减少ORM的额外开销
• 定期清理数据库：执行VACUUM;命令，整理数据库文件碎片，提升读取效率

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