为什么我的SQLite3查询语句很慢？如何优化最近时间戳查找效率？

首先你当前的性能瓶颈核心是逐行查询的IO开销，哪怕单条查询耗时1s，数百次循环累计耗时必然很高，以下是两种可落地的优化路径：

路径1：用Pandas原生向量化操作（推荐，性能最高） #

单表500万行分钟级数据单表内存占用仅几十MB，完全可以加载到内存后用merge_asof做最近时间戳匹配，是所有方案中效率最高的。

实现代码 #

import pandas as pd
import sqlite3

conn = sqlite3.connect(path)
# 提前加载三张汇率表，注意必须按时间戳排序
eurusd = pd.read_sql("SELECT TimestampUnix, Open FROM EURUSD ORDER BY TimestampUnix ASC", conn)
btcusd = pd.read_sql("SELECT TimestampUnix, Open FROM BTCUSD ORDER BY TimestampUnix ASC", conn)
ethusd = pd.read_sql("SELECT TimestampUnix, Open FROM ETHUSD ORDER BY TimestampUnix ASC", conn)
conn.close()

result_groups = []
# 按货币类型拆分目标表分别匹配
for currency, group in dataframe.groupby("Currency"):
    # 匹配对应汇率表
    if currency == "EURUSD":
        rate_table = eurusd
    elif currency == "BTCUSD":
        rate_table = btcusd
    elif currency == "ETHUSD":
        rate_table = ethusd
    else:
        continue
    # 最近时间戳匹配，默认取小于等于目标时间的最近值，完全符合你的需求
    matched_group = pd.merge_asof(
        group.sort_values("Timestamp"),
        rate_table,
        left_on="Timestamp",
        right_on="TimestampUnix",
        direction="backward"
        # 若需要取前后绝对最近的时间戳，把direction改成"nearest"即可
    )
    result_groups.append(matched_group)

# 合并所有匹配结果，恢复原索引顺序
final_dataframe = pd.concat(result_groups).sort_index()

性能说明 #

百万级数据匹配耗时通常在秒级，远快于任何循环查询方案。

路径2：单条SQL批量查询（适合内存不足无法加载全表的场景） #

SQLite不支持动态表名，所以先通过视图合并三张表的逻辑结构，再通过临时表批量传入待匹配数据，一次性完成所有查询。

步骤1：创建临时表和视图 #

-- 创建临时表存储待匹配的时间戳和货币类型
CREATE TEMP TABLE temp_target (
    TargetTimestamp INTEGER,
    Currency TEXT
);

-- 创建逻辑视图合并三张汇率表，新增货币类型标识，不实际存储数据无额外开销
CREATE TEMP VIEW all_rates AS
SELECT 'EURUSD' AS Currency, TimestampUnix, Open FROM EURUSD
UNION ALL
SELECT 'BTCUSD' AS Currency, TimestampUnix, Open FROM BTCUSD
UNION ALL
SELECT 'ETHUSD' AS Currency, TimestampUnix, Open FROM ETHUSD;

步骤2：批量导入待匹配数据 #

用pandas的to_sql方法将目标表中的Timestamp和Currency列批量写入temp_target临时表，比逐行插入快几个数量级。

步骤3：单条SQL完成所有匹配 #

SELECT
    t.TargetTimestamp,
    t.Currency,
    r.Open
FROM temp_target t
LEFT JOIN all_rates r
ON r.Currency = t.Currency
AND r.TimestampUnix = (
    -- 子查询走TimestampUnix索引直接定位最近时间戳，无全表扫描
    SELECT MAX(TimestampUnix)
    FROM all_rates
    WHERE Currency = t.Currency
    AND TimestampUnix <= t.TargetTimestamp
)

将查询结果读入dataframe后和原目标表按Timestamp、Currency关联即可完成填充。

额外优化建议 #

• 你原来的逐行查询存在字符串拼接SQL的问题，既可能有注入风险，也会导致SQLite无法缓存查询计划，应该改成参数化写法：cur.execute("SELECT Open FROM EURUSD WHERE TimestampUnix <= ? ORDER BY TimestampUnix DESC LIMIT 1", (timestamp,))，单条查询速度会进一步提升。
• 确认每张汇率表的TimestampUnix字段是主键或者唯一索引，索引效率最高。
• 如果数据库文件很久没有优化，可以执行VACUUM命令清理碎片，降低查询IO开销。

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