使用Pandas read_sql查询SQLite速度极慢，此情况是否正常？

首先明确说：这个查询速度绝对不正常——统计单表行数花50秒，且CPU峰值仅6%-7%，明显是数据库没有利用好资源，大概率是缺少关键索引或者配置/存储层面存在瓶颈，咱们一步步拆解问题：

一、先排查你的数据库可能存在的核心问题 #

• 缺少主键/索引：你的查询是COUNT(ID)，如果ID是主键，SQLite会直接从主键索引的元数据中读取行数，完全不需要扫全表，正常应该是毫秒级完成。但如果ID没设为主键、甚至没有任何索引，数据库只能逐行扫描整个2.2GB的表——这是磁盘IO密集型操作，CPU大部分时间在等待磁盘数据，所以使用率极低，速度自然慢到离谱。
• 存储介质拖后腿：如果数据库存在机械硬盘（HDD）上，大表全表扫描确实会慢，但50秒还是偏长；如果是SSD的话，这个速度就完全不合理，说明肯定有其他问题。
• SQLite默认配置未优化：SQLite默认的缓存大小、日志模式等参数是为小表设计的，大表场景下会限制IO效率。

二、具体优化手段，按优先级排序 #

1. 给ID字段添加主键/索引（最关键！） #

这是解决COUNT(ID)慢的核心方案，加完索引后查询速度会有质的飞跃：

-- 如果ID是唯一标识，直接设为主键
ALTER TABLE MY_TABLE ADD PRIMARY KEY (ID);

-- 如果ID不适合做主键，至少加普通索引
CREATE INDEX idx_my_table_id ON MY_TABLE(ID);

加完索引后再执行COUNT(ID)，正常应该能降到1秒以内，甚至毫秒级。

2. 优化SQLite连接配置 #

创建SQLAlchemy引擎时，添加针对大表的优化参数，尤其是开启WAL模式（大幅提升读写性能）：

from sqlalchemy import create_engine

engine = create_engine(
    'sqlite:///my_path/my_db.db',
    connect_args={
        'check_same_thread': False,  # 单线程场景也可设置，避免线程限制
        'cache_size': -2097152,  # 负数表示KB单位，这里设置2GB缓存，根据你的内存调整
        'journal_mode': 'WAL',  # 写提前日志，读操作不被写阻塞，IO效率飙升
        'synchronous': 'NORMAL',  # 比默认FULL模式快，安全性平衡大部分场景需求
        'timeout': 30
    }
)

其中journal_mode=WAL是重中之重，对大表的查询、写入性能提升非常明显。

3. 绕过Pandas直接执行查询（减少 overhead） #

其实统计行数不需要通过Pandas中转，直接用SQLite原生连接执行，能节省一点中间环节的开销：

import sqlite3

conn = sqlite3.connect('my_path/my_db.db', detect_types=sqlite3.PARSE_DECLTYPES)
cursor = conn.cursor()
cursor.execute('SELECT COUNT(ID) FROM MY_TABLE')
row_count = cursor.fetchone()[0]
conn.close()

4. 检查并更换存储介质 #

如果数据库在HDD上，尽快迁移到SSD——SSD的随机读写速度是HDD的几十倍，即使全表扫描，速度也会大幅提升。

5. 验证查询计划，确认优化效果 #

用EXPLAIN QUERY PLAN查看SQLite的执行逻辑，确认索引是否生效：

EXPLAIN QUERY PLAN SELECT COUNT(ID) FROM MY_TABLE;

如果结果显示SEARCH TABLE MY_TABLE USING INDEX ...，说明索引正常工作；如果还是SCAN TABLE MY_TABLE，那就要检查索引是否真的创建成功。

三、后续复杂查询的进阶优化 #

如果之后还有更复杂的查询需求，比如多条件过滤、聚合，还可以：

• 给常用过滤字段添加联合索引
• 考虑将每个数据库内的表再按业务维度（比如时间、地区）拆分成分表，进一步降低单表大小
• 开启SQLite的temp_store = MEMORY，让临时表存在内存中，提升聚合操作速度

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