SQLite大体积数据库简单查询极慢 性能优化排查方案

核心是三个问题叠加导致慢查询，索引配置已经生效（执行计划显示正确命中(year, source)联合索引），问题和CPU、日志模式配置无关：

• 存储IO性能严重不足：ioping测试中1.65分钟生成100个请求是执行时加了请求间隔参数导致的，结果不准；以fio和cp测试结果为准，8GB文件顺序拷贝耗时150秒，算下来顺序读吞吐仅约55MB/s，4k随机读吞吐仅42MiB/s、IOPS约1万，属于低性能网络云盘的水平。SQLite是文件型数据库，走索引回表查询属于随机读场景，你的查询要返回45万行数据，单表总大小272GB，平均每行数据约31KB，待读取的总数据量约14GB，仅随机读数据的理论耗时就超过5分钟，加上随机IO寻址开销、Python端数据转换开销，耗时到1小时符合当前硬件性能表现。
• SQLite配置完全未适配大内存服务器：默认SQLite的页缓存仅2MB，相当于所有读请求全部直接打磁盘，完全没用到服务器的128GB大内存；之前调整的synchronous、WAL模式都是写入优化参数，对读性能没有任何提升，优化方向错误。
• 额外代码损耗：用字符串格式化拼接SQL，没有用到sqlite3的预编译优化；select *会返回占存储90%以上的transcript长文本字段，不必要地放大了IO量。

零成本配置优化（先做，预计性能提升5-10倍） #

• 调大SQLite缓存：连接数据库后先执行PRAGMA cache_size = -20971520;，设置20GB页缓存（参数为负时单位是KB），热点数据第一次读盘后会常驻内存，后续重复查询直接走内存。
• 开启内存映射：执行PRAGMA mmap_size = 107374182400;，分配100GB内存映射空间，让操作系统直接接管数据库文件的缓存调度，随机读效率比SQLite自带缓存更高。
• 优化SQL写法：不要用字符串格式化拼接查询，改用参数化查询，利用预编译能力减少解析开销；非必要不查transcript大字段，只返回需要的列，能把IO量降低一个数量级。示例代码：

def select_articles_by_year_and_sources(self, year, sources):
    cur = self.conn.cursor()
    placeholders = ','.join(['?']*len(sources))
    query = f"select * from articles where year=? and source in ({placeholders})"
    cur.execute(query, (year, *sources))
    return iter(ResultIterator(cur))

• 清理冗余索引：之前建的source、year单字段索引和查询用到的(year, source)联合索引重复，直接删掉单字段索引，减少数据库文件空间占用。

存储层优化（预计性能提升10-100倍） #

• 最快验证方式：如果服务器本地盘有足够空间，把272GB的数据库文件拷贝到本地NVMe盘上再执行查询，本地NVMe随机读吞吐通常可达2-3GB/s，相同查询耗时大概率会降到1分钟以内。
• 如果必须用云盘存储，升级为高性能SSD云盘/极速型云盘，保证4k随机读吞吐不低于1GB/s、随机读IOPS不低于3万，不要用低性能的HDD云盘或者基础型网络云盘。

是否需要切换Redshift类数据仓库 #

• 当前9000万行的单表规模完全不需要上Redshift，这类云数仓成本高、冷查询延迟高，对于批量拉取明细数据的场景性价比极低。做完上面两步优化后，SQLite完全可以满足需求。
• 如果后续需要做多维度聚合分析（比如按主题、情感值、时间粒度做分组统计）、多用户并发查询，优先换单节点部署的ClickHouse，32核128G的配置跑这个量级的数据，聚合查询基本都是秒级返回，运维和使用成本远低于云数仓。

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