Python调用SQLite执行1万条SELECT查询耗时过长求助

问题背景 #

用Python的sqlite3库对单表执行约1万条SELECT查询，普通配置笔记本上耗时6至7小时，创建的复合索引完全无效。

相关代码与结构 #

表结构、索引及查询语句

CREATE TABLE ip2location (ip_from INT, ip_to INT, country_code VARCHAR(2), country_name VARCHAR(255), region_name VARCHAR(255), city_name VARCHAR(255));
CREATE INDEX `ix_ip_between` ON `ip2location`(`ip_from`, `ip_to`);

SELECT country_name, city_name FROM ip2location 
                    WHERE ip_from <= ? and ip_to >= ? 
                    AND country_name != '-';

Python执行代码

query = "SELECT country_name, city_name FROM ip2location \
                    WHERE ip_from <= ? and ip_to >= ? \
                    AND country_name != '-'"
results = []
for ip in ips: 
    cur.execute(query, [ip, ip])
    result = cur.fetchone()
    if result:
        results.append(result)

问题根源 #

1. 复合索引不匹配查询逻辑：你建的(ip_from, ip_to)复合索引，无法适配ip_from <= ? AND ip_to >= ?的查询。SQLite复合索引是按前缀顺序生效的，这种同时对两个字段做范围判断的条件，优化器没法有效利用该索引，导致每次查询都做全表扫描，1万次全表扫描直接拉满了耗时。
2. 循环单查的额外开销：Python循环里每次调用cur.execute都会产生一次SQLite交互的开销，虽然不是主因，但叠加1万次后也会放大耗时。

解决办法 #

1. 调整索引适配查询

根据你的查询条件，单独为ip_from和ip_to建索引，或者建覆盖索引进一步优化：

-- 单独索引方案（你采用的有效方案）
CREATE INDEX ix_ip_from ON ip2location(ip_from);
CREATE INDEX ix_ip_to ON ip2location(ip_to);

-- 覆盖索引（包含返回字段，避免回表查询，效率更高）
CREATE INDEX ix_ip_range_cover ON ip2location(ip_from, ip_to, country_name, city_name)
WHERE country_name != '-';

单独索引能让优化器快速定位符合ip_from <= ?和ip_to >= ?条件的数据，解决全表扫描的问题。

2. 批量查询减少交互次数

把1万条IP合并成一次查询，大幅降低Python与SQLite的交互开销：

# 批量查询示例（假设ips是待查询的IP列表）
placeholders = ', '.join(['(?)'] * len(ips))
query = f"""
SELECT temp.ip, country_name, city_name
FROM (SELECT * FROM (VALUES {placeholders}) AS temp(ip))
LEFT JOIN ip2location ON ip_from <= temp.ip AND ip_to >= temp.ip
WHERE country_name != '-'
"""
cur.execute(query, ips)
results = cur.fetchall()

这种方式把1万次单查询合并成1次，能进一步压缩耗时。

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