mysql - 使用带有 IN 子句的嵌套子查询提高 MySQL 查询的性能

Question

我想提高以下查询的性能。

SELECT  DISTINCT StringId 
FROM    translations 
WHERE   status = 1  
AND     TranslationId IN 
( 
    SELECT  Max(TranslationId) 
    FROM    translations  
    WHERE   languageId = 2 
    AND     TranslationId > 0  
    GROUP BY StringId;

此查询目前需要很长时间，因为 In 子句。

以下子查询

SELECT  Max(TranslationId) 
FROM    translations  
WHERE   languageId = 2 
AND     TranslationId > 0  
GROUP BY StringId;

返回 16,000 行，而包含 16,000 行的 IN 子句花费了这么长时间。

我提出的一个改进的查询是

SELECT  DISTINCT t1.StringId 
FROM    translations t1 
INNER JOIN 
(   
    SELECT  Max(TranslationId) MaxTranslationId, 
            StringId 
    FROM    translations  
    WHERE   languageId = 2 
    AND     TranslationId > 0  
    GROUP BY StringId
) t2    ON  t1.StringId = t2.StringId 
        AND t1.TranslationId = t2.MaxTranslationId 
WHERE   status = 1;

我希望内部联接仅返回与 StringId 和 Max(TranslationId) 匹配的行，就像使用 In 子句一样。

如果这是正确的方法，有人可以给我答案吗？

Answer 1

以下是使用 NOT EXISTS 的替代方法:

SELECT DISTINCT t.StringId 
FROM translations t
WHERE status = 1 AND
      NOT EXISTS (select 1
                  from translations t2
                  where t2.languageId = 2 and
                        t2.StringId = t.StringId and
                        t2.translationId > t.translationId
                 ) AND
      EXISTS (select 1
              from translations t2
              where t2.languageId = 2 and
                    t2.StringId = t.StringId and
                    t2.translationId > 0
                 )

为了提高性能，您需要在 translations(StringId, languageId, TranslationId) 上建立索引。这消除了其中一个聚合，将其替换为索引查找，这很可能会更快。

编辑:以上应该有效。但是，这也可能相对有效:

SELECT  DISTINCT StringId 
FROM    translations t
WHERE   status = 1 AND
        t.TranslationId = (SELECT  Max(t2.TranslationId) 
                           FROM    translations t2
                           WHERE   t2.languageId = 2 AND t2.TranslationId > 0 AND
                                   t2.StringId = t.StringId
                          );