java - 提高 SQL 查询执行速度

Question

我有一个通过 Java 中的 JDO 运行的 SQL 查询:

SELECT idPath, token, isTV, rel
FROM path
LEFT OUTER JOIN relation ON (path.idTokenN=relation.idTokenN)
WHERE path.idPath IN( SELECT DISTINCT path.idPath
                      FROM path
                      WHERE path.isTV='true'
                    )
  AND path.idPath IN( SELECT DISTINCT path.idPath
                      FROM path
                      GROUP BY path.idPath
                      HAVING COUNT(*) >= 2 AND COUNT(*) <= 3
                    )

运行大约需要3个小时，如何减少执行时间？

编辑

使用 MySQL 数据库管理系统

路径表:

     idTokenN idPath  token      isTV
       1      p1      test1      true
       2      p1      test2      false
       3      p2      test3      true
       4      p3      test4      false
       5      p3      test5      false
       6      p4      test6      false
       7      p4      test7      true
       8      p4      test8      false
       9      p4      test9      false
       10     p5      test10     true
       11     p5      test11     false
       12     p5      test12     false

关系表:

      idTokenN  idTokenN2  rel
       10       11         test

当然我的DB中的数据很多，这只是一个例子!

Answer 1

优化查询取决于几个因素。最重要的是数据库引擎。二是数据的特征。您的问题没有提供有关这两个方面的信息。

一条非常重要的信息是两个表的大小、每个表中的行数以及每个表中 idTokenN 的不同值的数量。 左外连接很可能决定查询的性能特征。

您可以做的第一件事就是删除 distinct 关键字。 in 子查询中永远不需要它，并且某些数据库引擎可能不会忽略它。

优化查询的另一个步骤是删除 in 子查询。在某些数据库中，这些优化效果不佳。它们可以被 join 和聚合子查询替换:

SELECT p.idPath, p.token, p.isTV, r.rel
FROM path p LEFT OUTER JOIN
     relation r
     ON p.idTokenN = r.idTokenN JOIN
     (select idPath, max(case when p.isTV = 'true' then 1 else 0 end) as HasTv,
             (case when COUNT(*) between 2 and 3 then 1 else 0 end) as Has2_3
      from path p
      group by idpath
     ) pf
     on p.idpath = pf.idpath and
        pf.HasTv = 1 and pf.Has2_3  = 1;

您肯定还可以做其他事情，但除此之外，它们变得依赖于数据库。