mysql - 如何使sqlite in-memory db join查询和MySQL一样快

Question

我有一个非常复杂的 sql 查询 - 逻辑很简单，但我需要连接 17 个表(每个表有 10-20 个字段和 100 到 100 万条记录)所以有很多(LEFT)JOIN 和 WHERE条款。

SELECT table1.column_A
       table2.column_B
       table3.column_C
       table4.column_D
       ....
FROM table1
LEFT JOIN table2 ON table1.column_a = table2.column_b
JOIN table3 ON table3.column_c = table1.column_d
LEFT JOIN table4.column_e = table3.column_f
    AND LENGTH(table4.column_g) > 6 AND (table4.column_h IN (123,234))
LEFT JOIN ....
....
WHERE table1.column_i = 21 
    AND (table1.column_j IS NULL OR DATE(table1.column_k) <> DATE(table1.column_l))

上述查询在 MySQL 中运行仅需 5 秒。但是当我在 sqlite 内存数据库中运行它时(在 Linux 上使用 Perl)，大约需要 20 分钟。这仍然是可以接受的。

当我添加一个 ORDER BY 子句(我确实需要这个)时，执行时间急剧增加。

ORDER BY table1.column_m, table6.column_n, table7.column_o IS NULL;

在 MySQL 中需要 40 秒。在sqlite in-memory db(在Linux上使用Perl)中，我等了一个多小时，但仍然没有完成。

我需要做什么样的调整才能使查询更快？我的阈值在 1 小时内。

我将其设为内存数据库的原因是我收到 SQL 生成的规范化数据，但我们最终需要将数据加载到非 SQL 数据库中，所以我不想创建中间 SQL db 仅用于数据加载 - 这使代码变得丑陋并增加了维护复杂性。另外，我目前面临的时间问题只是一次性的事情。在未来，我们每天收到的数据量会小得多(不到我今天的 1%)

在此先感谢您的帮助!

Answer 1

您的 ORDER BY 子句位于 3 个不同表的列上。再多的查询优化或索引创建也不会改变 DBMS 必须在生成结果集之后(或生成结果集时)进行外部排序的事实。如果您限制了 SQLite 可以使用的内存量(我不是 SQLite 专家，但我认为这至少是可能的，如果不需要的话)，那么这可能是原因(例如，它正在经历一些令人难以置信的阴谋)在其范围内完成工作)。或者它只是挂起。您等待的那个小时的 CPU 利用率是多少？ I/O 怎么样(它是否因为 SQLite 可以使用的内存量没有限制而不稳定，正如 Sinan 所暗示的那样)？