mysql - 减少嵌套 mysql 查询的时间

Question

我有一个嵌套的 MySQL 查询，在每个表中有超过 500000 条记录的关系。查询需要 60 秒来获取结果，并且已在所有表中完成索引。

请建议减少其执行时间。提前致谢。

    SELECT t1.col1,t1.col2
    FROM table1 AS t1
    WHERE t1.col2 IN
    (
        SELECT DISTINCT(t2.col1) FROM table2 AS t2 WHERE t2.col2 IN
        (
            SELECT t3.col1
            FROM  table3 AS t3
            WHERE t3.col2 = '04' ORDER BY t3.col1 ASC
        )
        ORDER BY t2.col1 ASC
    )

Answer 1

我之前的评论是:

You should only order your final result set. Try profiling the query to see what is causing the long execution time; It could well be the DISTINCT clause you have as they can often cause delay. In addition you could try using a JOIN instead of WHERE ... IN as that can often be faster.

未测试代码，但根据我之前的评论，使用 JOIN 的相同查询看起来类似于:

SELECT  t1.col1,
        t1.col2
  FROM  table1 t1
    INNER JOIN table2 t2 ON t2.col1 = t1.col2
    INNER JOIN table3 t3 ON t3.col1 = t2.col2
  WHERE t3.col2 = '04'
  ORDER BY t2.col1, t3.col1

我希望这样的查询比使用 WHERE ... IN 执行得快得多。