Mysql嵌套查询耗时较长

Question

我们正在使用以下问题从大型 mysql 表中获取日期。

SELECT fullPath, Permissiontype, DinstinguishedName 
from cdm.test 
where fullPath in 
  (SELECT distinct fullPath 
   FROMcdm.test 
   WHERE (Permissiontype = 'EXPLICIT' and not DinstinguishedName ='') 
   OR(Permissiontype = 'INHERITED' 
     AND (length(fullPath) - length(replace(fullPath,'/','')) < 4)) 
   OR(Permissiontype = 'EXPLICIT' 
     AND NOT DinstinguishedName='' 
     AND LEFT(fullPath,length(fullPath)-Length(RIGHT(fullPath,INSTR(reverse(fullPath),'/')))) 
AND(length(fullPath) - length(replace(fullPath,'/','')) > 2) ))

当我将需要显示的结果限制为 270 条时，它运行得非常快，但例如 500 行，它就无法运行。我在表中有 1 个案例 7700 万行(需要在 1 个表中)。然后运行了8个多小时，仍然没有完成。有没有办法优化这个？

wkr。

Answer 1

对于测试表中的每条记录，您将在子查询中再次查询整个表。不要在 where 子句中使用子查询，而是尝试在同一个表上使用内部联接。这将极大地提高你的表现。

我还没有尝试过，但它可能看起来像:

SELECT fullPath, Permissiontype, DinstinguishedName from cdm.test 
INNER JOIN (
SELECT distinct fullPath from cdm.test 
where (Permissiontype = 'EXPLICIT' and not DinstinguishedName ='') 
or (Permissiontype = 'INHERITED' AND (length(fullPath) - length(replace(fullPath,'/','')) < 4)) OR(Permissiontype = 'EXPLICIT' 
AND NOT DinstinguishedName='' AND LEFT(fullPath,length(fullPath)-length(RIGHT(fullPath,INSTR(reverse(fullPath),'/')))) 
and(length(fullPath) - length(replace(fullPath,'/','')) > 2) )
) AS SQ1 
ON SQ1.fullpath = cdm.test.fullpath