为什么这个sql语句非常慢？

Question

我正在向sqlite数据库写入大量数据。我使用一个临时数据框来查找唯一的值

这段sql代码在conn.execute(sql)中永远使用

if upload_to_db == True:
    print(f'########################################WRITING TO TEMP TABLE: {symbol} #######################################################################')
    master_df.to_sql(name='tempTable', con=engine, if_exists='replace')

    with engine.begin() as cn:
        sql = """INSERT INTO instrumentsHistory (datetime, instrumentSymbol, observation, observationColName)
                SELECT t.datetime, t.instrumentSymbol, t.observation, t.observationColName
                FROM tempTable t
                WHERE NOT EXISTS 
                    (SELECT 1 FROM instrumentsHistory f
                     WHERE t.datetime = f.datetime
                     AND t.instrumentSymbol = f.instrumentSymbol
                     AND t.observation = f.observation
                     AND t.observationColName = f.observationColName)"""
        print(f'##############################################WRITING TO FINAL TABLE: {symbol} #################################################################')

        cn.execute(sql)

运行此操作将永远无法写入数据库。有人能帮我理解如何加速吗

---

编辑1：

大概有多少行-一次大约15000人。基本上，它将数据拉入pandas数据框并进行一些转换，然后将其写入sqlite数据库。可能有600种不同的仪器，每种仪器大约有15000行，最终有900行。给或拿一百万

Answer 1

根据您的SQL数据库，您可以尝试使用类似INSERT INTO IGNORE（MySQL）或MERGE（例如在Oracle上）的内容，只有在不违反主键或唯一约束的情况下，才会执行插入。这将假定您正在检查的4列上存在这样的约束

在没有merge的情况下，可以尝试将以下索引添加到instrumentsHistory表中：

CREATE INDEX idx ON instrumentsHistory (datetime, instrumentSymbol, observation,
                                        observationColName);

此索引允许快速查找来自tempTable的每个传入记录，因此可能会加快插入过程

Answer 2

此子查询

WHERE NOT EXISTS 
        (SELECT 1 FROM instrumentsHistory f
         WHERE t.datetime = f.datetime
         AND t.instrumentSymbol = f.instrumentSymbol
         AND t.observation = f.observation
         AND t.observationColName = f.observationColName)

必须检查表中的每一行，并匹配四列，直到找到匹配项。在最坏的情况下，没有匹配项，必须完成完整的表扫描。因此，查询的性能将随着表大小的增加而恶化

正如Tim在回答中提到的，解决方案是在四列上创建一个索引，以便db能够快速确定是否存在匹配