优化 SQLite 千万级 IP 地址范围查询：从 7 小时到秒级的实战指南-Python教程-PHP中文网

优化 SQLite 千万级 IP 地址范围查询：从 7 小时到秒级的实战指南

本文详解如何将 10,000 次 sqlite 范围查询（ip 地理定位）从耗时数小时优化至秒级，涵盖索引失效根因、批量查询重构、执行计划分析及生产级避坑建议。

本文详解如何将 10,000 次 sqlite 范围查询（ip 地理定位）从耗时数小时优化至秒级，涵盖索引失效根因、批量查询重构、执行计划分析及生产级避坑建议。

在处理 IP 地理定位这类“范围匹配”场景（如 ip_from = ?）时，盲目执行大量单条 SELECT 查询是典型的性能反模式。原始代码中对每个 IP 地址单独执行一次查询，触发了 10,000 次独立的 SQLite 查询解析、计划生成、索引扫描与结果返回开销——即使硬件尚可，I/O 和上下文切换成本也会指数级放大，导致总耗时长达 6–7 小时。

根本问题不在 Python 或 SQLite 本身，而在于查询设计与索引策略的错配。复合索引 CREATE INDEX ix_ip_between ON ip2location(ip_from, ip_to) 看似合理，但 SQLite 的 B-tree 索引无法高效支持“双边界范围查询”（即两个字段均为 = 条件）。EXPLAIN QUERY PLAN 显示该索引未被使用，正是关键线索——SQLite 仅能利用索引的最左前缀进行范围扫描，而 ip_from = ? 无法形成连续的索引区间遍历。

✅ 正确解法：批量预加载 + 内存过滤

避免 N+1 查询，改用单次宽幅查询 + Python 端二次过滤：

# 步骤1：获取所有待查IP的极值（整型IP）
min_ip = min(ips)
max_ip = max(ips)

# 步骤2：一次性查询覆盖全部可能区间的记录
query = """
    SELECT ip_from, ip_to, country_name, city_name 
    FROM ip2location 
    WHERE ip_from = ? 
      AND country_name != '-'
"""
cur.execute(query, (max_ip, min_ip))  # 注意：WHERE ip_from = min_ip
candidates = cur.fetchall()  # 返回数百~数千行（远少于10,000）

# 步骤3：Python 中精确匹配（O(1) 字典查找 or O(n) 过滤）
results = []
for ip in ips:
    for row in candidates:
        if row[0] <blockquote><p>⚠️ 注意：WHERE ip_from = ? 中的参数顺序需与条件严格对应（此处 ?1=max_ip, ?2=min_ip），否则逻辑错误。</p></blockquote><p><strong>进阶优化建议：</strong></p>

添加覆盖索引：若只查询 country_name, city_name，创建覆盖索引减少回表：
```
CREATE INDEX ix_ip_covering ON ip2location(ip_from, ip_to, country_name, city_name);
```
预计算哈希分桶（超大数据集）：当 candidates 仍过大时，可在表中新增 ip_range_hash 列（如 FNV-1a(ip_from, ip_to)），对目标 IP 计算哈希后用 IN 批量筛选，再精细过滤：
```
hashes = [hash_pair(ip, ip) for ip in ips]  # 哈希函数需与SQL端一致
cur.execute("SELECT ... FROM ip2location WHERE ip_range_hash IN ({})".format(','.join('?'*len(hashes))), hashes)
```
启用 WAL 模式 & 关闭自动提交（提升写入密集场景并发性，本例虽为读取，但开启后可减少锁争用）：
```
conn.execute("PRAGMA journal_mode = WAL")
conn.execute("PRAGMA synchronous = NORMAL")
```