python - 如何快速找到 sqlite 数据库中最接近给定输入的行？

Question

我有一个函数可以访问我的 Sqlite 数据库，该数据库在 100 个表中包含大约 100,000 行。 (数据库文件大小约为 2-3GB)我试图给我的函数一个时间点，我希望它返回与给定时间点最接近的行。按照我现在设置的方式，我必须运行此函数数百万次，并且我发现查询的运行时间约为 30-35 毫秒。我需要显着加快速度，有人可以建议一些修复吗？

我尝试过的事情:如果我删除查询的“ORDER BY”部分，它的速度几乎会提高一个数量级，但我不确定如何在没有它的情况下获得最接近的时间点。我尝试将数据库加载到内存中，但这实际上使它变慢，因为将数据库加载到内存中花费了额外的时间。我已经并行化了 44 个进程的调用，但设置进程需要很长时间(将变量复制到内存？)，但更重要的是，只有 ~8 个进程 100% 工作，而且仍然太慢。

我确实只为 _timestamp 列索引了数据库。

有谁知道一种方法可以绕过我的整个问题，重新组织我的数据库，更改我的查询，提出一种更好的方法在数据库内部或外部进行搜索，甚至帮助我确定限制因素是什么？我正在开发一台 24 核/48 线程服务器，内存约为 140GB。

def getClosestRow(conn, table, timepoint):
    query = "SELECT * FROM "+str(table)+" ORDER BY ABS("+str(timepoint)+" - _timestamp) LIMIT 1"

    result = conn.execute(query)

    for x in result: 
        return x

    return None

这是我用来索引数据库的函数:

def indexAllTablesWithColumn(conn, column):
    tableList = getListOfTablesInDatabase(conn)
    for tableName in tableList:
        command = "CREATE INDEX idx_"+column+tableName+" ON "+tableName+"("+column+");"
        results = conn.execute(command)
    conn.commit()

我做了 sqlite3 database.db .schema > schema.sql 建议并得到了巨大的输出。我将把头和尾放在这里，因为中间的所有内容似乎只是具有不同名称的各种表。该数据库包含抓取的加密货币数据，每个表都是不同的“硬币”，每一列都是不同的特征，每一行都是不同的时间点。

Head: 
CREATE TABLE _BTC_BITCOIN(id integer PRIMARY KEY,_timestamp TEXT,_24h_volume_usd REAL,_available_supply REAL,_id TEXT,_last_updated INTEGER,_market_cap_usd INTEGER,_max_supply REAL,_name TEXT,_percent_change_1h REAL,_percent_change_24h REAL,_percent_change_7d REAL,_price_btc REAL,_price_usd REAL,_rank INTEGER,_symbol TEXT,_total_supply REAL);
CREATE TABLE _ETH_ETHEREUM(id integer PRIMARY KEY,_timestamp TEXT,_24h_volume_usd REAL,_available_supply REAL,_id TEXT,_last_updated INTEGER,_market_cap_usd INTEGER,_max_supply REAL,_name TEXT,_percent_change_1h REAL,_percent_change_24h REAL,_percent_change_7d REAL,_price_btc REAL,_price_usd REAL,_rank INTEGER,_symbol TEXT,_total_supply REAL);
CREATE TABLE _XRP_RIPPLE(id integer PRIMARY KEY,_timestamp TEXT,_24h_volume_usd REAL,_available_supply REAL,_id TEXT,_last_updated INTEGER,_market_cap_usd INTEGER,_max_supply REAL,_name TEXT,_percent_change_1h REAL,_percent_change_24h REAL,_percent_change_7d REAL,_price_btc REAL,_price_usd REAL,_rank INTEGER,_symbol TEXT,_total_supply REAL);
CREATE TABLE _BCH_BITCOIN_CASH(id integer PRIMARY KEY,_timestamp TEXT,_24h_volume_usd REAL,_available_supply REAL,_id TEXT,_last_updated INTEGER,_market_cap_usd INTEGER,_max_supply REAL,_name TEXT,_percent_change_1h REAL,_percent_change_24h REAL,_percent_change_7d REAL,_price_btc REAL,_price_usd REAL,_rank INTEGER,_symbol TEXT,_total_supply REAL);
CREATE TABLE _LTC_LITECOIN(id integer PRIMARY KEY,_timestamp TEXT,_24h_volume_usd REAL,_available_supply REAL,_id TEXT,_last_updated INTEGER,_market_cap_usd INTEGER,_max_supply REAL,_name TEXT,_percent_change_1h REAL,_percent_change_24h REAL,_percent_change_7d REAL,_price_btc REAL,_price_usd REAL,_rank INTEGER,_symbol TEXT,_total_supply REAL);
CREATE TABLE _ADA_CARDANO(id integer PRIMARY KEY,_timestamp TEXT,_24h_volume_usd REAL,_available_supply REAL,_id TEXT,_last_updated INTEGER,_market_cap_usd INTEGER,_max_supply REAL,_name TEXT,_percent_change_1h REAL,_percent_change_24h REAL,_percent_change_7d REAL,_price_btc REAL,_price_usd REAL,_rank INTEGER,_symbol TEXT,_total_supply REAL);
CREATE TABLE _NEO_NEO(id integer PRIMARY KEY,_timestamp TEXT,_24h_volume_usd REAL,_available_supply REAL,_id TEXT,_last_updated INTEGER,_market_cap_usd INTEGER,_max_supply REAL,_name TEXT,_percent_change_1h REAL,_percent_change_24h REAL,_percent_change_7d REAL,_price_btc REAL,_price_usd REAL,_rank INTEGER,_symbol TEXT,_total_supply REAL);
CREATE TABLE _XLM_STELLAR(id integer PRIMARY KEY,_timestamp TEXT,_24h_volume_usd REAL,_available_supply REAL,_id TEXT,_last_updated INTEGER,_market_cap_usd INTEGER,_max_supply REAL,_name TEXT,_percent_change_1h REAL,_percent_change_24h REAL,_percent_change_7d REAL,_price_btc REAL,_price_usd REAL,_rank INTEGER,_symbol TEXT,_total_supply REAL);
CREATE TABLE _XMR_MONERO(id integer PRIMARY KEY,_timestamp TEXT,_24h_volume_usd REAL,_available_supply REAL,_id TEXT,_last_updated INTEGER,_market_cap_usd INTEGER,_max_supply REAL,_name TEXT,_percent_change_1h REAL,_percent_change_24h REAL,_percent_change_7d REAL,_price_btc REAL,_price_usd REAL,_rank INTEGER,_symbol TEXT,_total_supply REAL);
CREATE TABLE _EOS_EOS(id integer PRIMARY KEY,_timestamp TEXT,_24h_volume_usd REAL,_available_supply REAL,_id TEXT,_last_updated INTEGER,_market_cap_usd INTEGER,_max_supply REAL,_name TEXT,_percent_change_1h REAL,_percent_change_24h REAL,_percent_change_7d REAL,_price_btc REAL,_price_usd REAL,_rank INTEGER,_symbol TEXT,_total_supply REAL);

Tail: 
CREATE INDEX idx__timestamp_VZT_VEZT ON _VZT_VEZT(_timestamp);
CREATE INDEX idx__timestamp_SKB_SAKURA_BLOOM ON _SKB_SAKURA_BLOOM(_timestamp);
CREATE INDEX idx__timestamp_FID_FIDELIUM ON _FID_FIDELIUM(_timestamp);
CREATE INDEX idx__timestamp_MOAC_MOAC ON _MOAC_MOAC(_timestamp);
CREATE INDEX idx__timestamp_NKN_NKN ON _NKN_NKN(_timestamp);
CREATE INDEX idx__timestamp_CLO_CALLISTO_NETWORK ON _CLO_CALLISTO_NETWORK(_timestamp);
CREATE INDEX idx__timestamp_SWTH_SWITCHEO ON _SWTH_SWITCHEO(_timestamp);
CREATE INDEX idx__timestamp_TUBE_BITTUBE ON _TUBE_BITTUBE(_timestamp);
CREATE INDEX idx__timestamp_BETR_BETTERBETTING ON _BETR_BETTERBETTING(_timestamp);
CREATE INDEX idx__timestamp_LYL_LOYALCOIN ON _LYL_LOYALCOIN(_timestamp);

Answer 1

SELECT *
FROM MyTable
ORDER BY ABS(? - _timestamp)
LIMIT 1;

该查询简单且正确，但无法使用索引优化查找。 (即使使用 expression index 也不行，因为函数参数发生变化，因此无法存储结果。)

如果您计算其他内容，您将获得快速查询:等于或晚于时间戳的最近行:

SELECT *
FROM MyTable
WHERE _timestamp >= ?
ORDER BY _timestamp
LIMIT 1;

要获得正确的结果，请计算最近的两行之前和之后，然后计算时间戳，然后取差异最小的行(对两行进行排序很快) :

SELECT *
FROM (SELECT *
      FROM MyTable
      WHERE _timestamp >= ?
      ORDER BY _timestamp ASC
      LIMIT 1)

UNION ALL

SELECT *
FROM (SELECT *
      FROM MyTable
      WHERE _timestamp <= ?
      ORDER BY _timestamp DESC
      LIMIT 1)

ORDER BY ABS(? - _timestamp)
LIMIT 1;