MySQL JOIN替换子查询(或不在)

Question

我无法将我的sql查询转换为性能更高的JOIN查询。这是查询

SELECT * FROM `Link`
WHERE idConceptStart = 31 AND flag = 0 AND (
    idConceptLink IN (
        SELECT idConceptStart FROM Link 
         WHERE idConceptTarget = 13
         AND idConceptLink IN (11, 315 ) 
    ) 
    OR idConceptLink NOT IN ( 
       SELECT idConceptStart FROM `Link` WHERE idConceptLink IN (11, 315) 
    ) 
)

我设法返回查询的第一部分

SELECT * FROM `Link` l 
JOIN `Link` j ON l.idConceptLink = j.idConceptStart  
LEFT JOIN Link k ON k.idConceptStart = l.idConceptLink 
WHERE l.idConceptStart = 31 
AND j.flag = 0 AND j.idConceptTarget IN(13) 
AND j.idConceptLink IN (11, 315 )
AND k.idConceptLink IN (11, 315) AND k.flag != 1107   
AND l.flag = 0 
AND k.`idConceptStart` IS NULL

但我没有得到第二部分

OR idConceptLink  NOT IN ( SELECT idConceptStart FROM `Link` WHERE idConceptLink IN (11, 315) )

添加我的左连接后，我没有得到任何补偿

我该如何管理？我还尝试使用 UNION 进行另一个查询来替换 OR，但我无法进行此查询

Answer 1

请给

SELECT * FROM `Link` l 
inner join
(
   select idConceptStart FROM `Link` 
   WHERE idConceptLink NOT IN (11, 315) 
       OR ( idConceptTarget = 13 AND idConceptLink IN (11, 315) )
) as secondCondition
on l.idConceptLink = secondCondition.idConceptStart
WHERE l.idConceptStart = 31 AND l.flag = 0

试一试。如果你不明白所有这些属性背后的原因，你的问题既复杂又抽象（这里涉及相当多的"幻数"）。

如果查询对您来说仍然太慢，请在其前面加上EXPLAIN并发布结果以供进一步分析。

Answer 2

为了提高性能，您将需要重写查询并创建索引。 我建议使用 not exists 和 exists ，而不是in：

SELECT l.*
FROM `Link` l
WHERE idConceptStart = 31 AND flag = 0 AND (
      (EXISTS (SELECT 1
               FROM link l2
               WHERE l.idConceptLink  = l2.idConceptStart AND
                     l2.idConceptTarget = 13 AND l2.idConceptLink IN (11, 315) 
              ) OR
       NOT EXISTS (SELECT 1
                   FROM link l2
                   WHERE l.idConceptLink = l2.idConceptStart AND
                         l2.idConceptLink IN (11, 315) 
                  ) 
     );

对于此查询，您需要索引：link(idConceptStart, flag)、link(idConceptStart, idConceptTarget, idConceptLink) 和 link(idConceptStart, idConceptLink) 。