mysql - 优化 mysql 查询以查找所有重复条目

Question

我正在运行这样的查询:

SELECT DISTINCT `tableA`.`field1`,
                `tableA`.`filed2` AS field2Alias,
                `tableA`.`field3`,
                `tableB`.`field4` AS field4Alias,
                `tableA`.`field6` AS field6Alias
FROM (`tableC`)
RIGHT JOIN `tableA` ON `tableC`.`idfield` = `tableA`.`idfield`
JOIN `tableB` ON `tableB`.`idfield` = `tableA`.`idfield`
AND tableA.field2 IN
  (SELECT field2
   FROM tableA
   GROUP BY tableA. HAVING count(*)>1)
ORDER BY tableA.field2

这是为了找到所有重复的条目，但是现在执行起来需要很多时间。有什么优化建议吗？

Answer 1

看起来您正在尝试查找 TableA 中 field2 的所有重复项。第一步是将 in 子查询移动到 from 子句:

SELECT DISTINCT a.`field1`, a.`filed2` AS field2Alias,
       a.`field3`, b.`field4` AS field4Alias, a.`field6` AS field6Alias 
FROM tableA a left join
     tableC c
     on c.`idfield` = a`.`idfield` join
     `tableB` b
     ON b.`idfield` = a.`idfield` join
     (SELECT field2 
      FROM tableA
      group by field2 
      having count(*) > 1
     ) asum
     on asum.field2 = a.field2
ORDER BY tableA.field2

可能会有额外的优化，但很难说。您的问题“查找重复项”和您的查询“将一堆表连接在一起并过滤它们”并不完全匹配。了解哪些表具有哪些索引和唯一/主键也很有帮助。