NOT IN查询太慢，行数太多

Question

我下面的SQL查询一直很好，直到thirdparty_token_airdrops最终有20万行，thirdparty_token_holders有90万行。在这种规模下，优化它的最佳方式是什么？

select id, owner
from thirdparty_token_holders
where id not in (
select holder_id
from thirdparty_token_airdrops
where status=1
)
and amount > 10000000000
limit 300

在索引方面，我创建了以下内容：

CREATE UNIQUE INDEX holder_id on thirdparty_token_airdrops (id, holder_id);
CREATE UNIQUE INDEX owner_address on thirdparty_token_holders (owner, address);
CREATE UNIQUE INDEX owner_id on thirdparty_token_holders (owner, id);

Answer 1

我通过将查询更改为以下内容来修复它：

SELECT a.id, a.owner
FROM thirdparty_token_holders a
LEFT JOIN thirdparty_token_airdrops b
ON a.id = b.holder_id
AND b.status IS NULL
and amount > 10000000000
limit 300

Answer 2

您可以试用NOT EXISTS。这也比LEFT jOIN好。因为左联接获取左表的所有可能记录，而不存在的联接首先只获取匹配的行。

SELECT a.id, a.owner
FROM thirdparty_token_holders AS a
WHERE NOT EXISTS 
(
SELECT * FROM thirdparty_token_airdrops AS b
WHERE a.id = b.holder_id
)
AND b.status IS NULL
and a.amount > 10000000000
limit 300
;