MYSQL在表本身的子查询中不删除

Question

我有以下查询，它显示具有一定保证金级别的帐户列表：

SELECT
    crm_margincall.id,
    crm_margincall.CreationTime,
    ba.name AS crm_bankaccount_id,
    crm_margincall.name,
    crm_margincall.MarginCallLevel,
    crm_margincall.UseOfEquityForMargin,
    crm_margincall.MarginRequired,
    crm_margincall.NetEquityForMargin,
    crm_margincall.MarginDeficit,
    crm_margincall.balance,
    crm_margincall.deposited,
    crm_margincall.prefunded,
    crm_margincall.required
FROM 
    crm_margincall
LEFT JOIN
    crm_bankaccount ba ON crm_margincall.crm_bankaccount_id = ba.id
WHERE 
    crm_margincall.name = 'MarginCall' 
AND 
    crm_margincall.MarginCallLevel >= 100
AND 
    crm_margincall.crm_account_id NOT IN 
    (
    SELECT 
        x.crm_account_id 
    FROM 
        crm_margincall x 
    WHERE 
        x.crm_account_id = crm_margincall.crm_account_id 
    AND 
        x.name = 'LevelDrop' 
    AND 
        x.MarginCallLevel < 100 
    AND 
        x.id > crm_margincall.id
    )
ORDER BY 
    id
DESC

在~22.500记录的表上，这个查询需要>10秒才能运行，这是由于子查询定义了NOT IN部分(尝试不存在，速度不会更快)。我怎样才能加入这张桌子来达到同样的效果呢？

Answer 1

在~22.500记录的表上，这个查询需要>10秒才能运行，这是由于子查询定义了NOT IN部分(尝试不存在，速度不会更快)。我怎样才能加入这张桌子来达到同样的效果呢？

这可以通过几种方式完成，但是扫描22500条记录需要10“意味着要么是硬件问题，要么是非常低效率的连接。

后者最有可能的原因是缺少索引或配置不当的索引，要对此进行调查，您需要发出解释：

EXPLAIN SELECT ...

完全在黑暗中拍摄，从所选的柱子上判断，我会尝试

 CREATE INDEX test_index ON crm_margincall(name, crm_account_id, MarginCallLevel, id)

其他改进可能是可能的，但是您需要在SQLfiddle中准备一个包含一些假数据的样例结构，才能真正地进行调试。

Answer 2

试着做这样的事情：

    SELECT
    crm_margincall.id,
    crm_margincall.CreationTime,
    ba.name AS crm_bankaccount_id,
    crm_margincall.name,
    crm_margincall.MarginCallLevel,
    crm_margincall.UseOfEquityForMargin,
    crm_margincall.MarginRequired,
    crm_margincall.NetEquityForMargin,
    crm_margincall.MarginDeficit,
    crm_margincall.balance,
    crm_margincall.deposited,
    crm_margincall.prefunded,
    crm_margincall.required
FROM 
    crm_margincall
LEFT JOIN
    crm_bankaccount ba ON crm_margincall.crm_bankaccount_id = ba.id
INNER JOIN 
(
    SELECT 
        x.crm_account_id 
    FROM 
        crm_margincall x 
    WHERE 
        x.name = 'LevelDrop' 
    AND 
        x.MarginCallLevel < 100 
    AND 
        x.id > crm_margincall.id
    ) tt ON crm_margincall.crm_account_id = tt.crm_account_id 
WHERE 
    crm_margincall.name = 'MarginCall' 
AND 
    crm_margincall.MarginCallLevel >= 100

ORDER BY 
    id
DESC