如何提高查询 NOT IN 的性能

Question

我有以下的SQL查询。

SELECT em.employeeid, tsi.timestamp
FROM timesheet_temp_import tsi
JOIN employee emp ON emp.employeeid = tsi.credentialnumber
WHERE
tsi.masterentity = 'MASTER' AND
tsi.timestamp NOT IN
(
    SELECT ea.timestamp 
    FROM employee_attendance ea 
    WHERE 
    ea.employeeid = em.employeeid
    AND ea.timestamp =  tsi.timestamp
    AND ea.ismanual = 0
)
GROUP BY em.employeeid, tsi.timestamp

这个查询比较了一个导入表（包含员工出勤时间戳）。有时，timesheet_temp_import 表格会超过95,000行，我的查询必须仅显示对于员工而言是新的时间戳，如果时间戳已经存在于员工记录中，则需要将其排除。 查询已经在工作，但是时间超过4分钟，因此我想知道是否可以改进使用 NOT IN 语句以减少这个时间。

- VAAA

---

有关查询性能的问题，请同时指定表结构和索引，以及这些表中数据量的指示。 - GolezTrol

你可以通过使用 WHERE 子句来限制主要的 SELECT 语句，仅获取 timesheet_temp_import 中最新的条目吗？你可以使用某种书签... - ForguesR

Answer 1

使用NOT EXISTS可能会对您有帮助。

SELECT 
    em.employeeid,
    tsi.timestamp
    FROM timesheet_temp_import tsi
    join employee emp ON emp.employeeid = tsi.credentialnumber
    WHERE
    tsi.masterentity = 'MASTER' AND

    NOT EXISTS 
    (
        SELECT NULL  
        FROM employee_attendance ea 
        WHERE 
        ea.employeeid = em.employeeid
        AND ea.timestamp =  tsi.timestamp
        AND ea.ismanual = 0
    )
    GROUP BY 
    em.employeeid,
    tsi.timestamp

- Luc M

---

@LucM，你能解释一下为什么应该使用它而不是 LEFT JOIN 并检查列是否为空吗？ - John Odom

@JohnOdom not in vs not exists vs left join - Lamak

Answer 2

您有这个查询：

SELECT em.employeeid, tsi.timestamp
FROM timesheet_temp_import tsi JOIN
     employee emp
     ON emp.employeeid = tsi.credentialnumber
WHERE tsi.masterentity = 'MASTER' AND
      tsi.timestamp NOT IN (SELECT ea.timestamp 
                            FROM employee_attendance ea 
                            WHERE ea.employeeid = em.employeeid AND
                                  ea.timestamp =  tsi.timestamp AND
                                  ea.ismanual = 0
                           )
GROUP BY em.employeeid, tsi.timestamp;

在重写查询之前（而不是重新格式化），我建议先检查索引和逻辑。是否需要使用GROUP BY？也就是说，外部查询是否会产生重复项？我的猜测是不会，但我不了解你的数据。

其次，您需要索引。我认为以下索引可能有用：timesheet_temp_import(masterentity, credentialnumber, timestamp)、employee(employeeid)、employee_attendance(employeeid, timestamp, ismanual)。

第三，我想问一下您是否有非员工的时间表。我认为您可以摆脱外部join。因此，这可能是您想要的查询：

SELECT tsi.credentialnumber as employeeid, tsi.timestamp
FROM timesheet_temp_import tsi
WHERE tsi.masterentity = 'MASTER' AND
      tsi.timestamp NOT IN (SELECT ea.timestamp 
                            FROM employee_attendance ea 
                            WHERE ea.employeeid = tsi.credentialnumber AND
                                  ea.timestamp =  tsi.timestamp AND
                                  ea.ismanual = 0
                           );

如果将NOT IN替换为NOT EXISTS，您可能会获得微小但有所改善。

- Gordon Linoff

Answer 3

另一种方法是使用except。

select whatever
from wherever
where somefield in 
(select all potential values of that field
except
select the values you want to exlude)

这在逻辑上等同于not in，但速度更快。

- Dan Bracuk

Answer 4

尝试这个，我认为你的意思是“空”。

SELECT distinct tsi.credentialnumber, tsi.timestamp
  FROM timesheet_temp_import tsi
  JOIN employee emp 
    ON emp.employeeid = tsi.credentialnumber
   and tsi.masterentity = 'MASTER' 
  left join employee_attendance ea 
    on ea.employeeid = emp.employeeid
   AND ea.timestamp = tsi.timestamp
   AND ea.ismanual = 0
 where ea.employeeid is null

根据索引的不同，这可能会更快。

SELECT distinct tsi.credentialnumber, tsi.timestamp
  FROM timesheet_temp_import tsi
  JOIN employee emp 
    ON emp.employeeid = tsi.credentialnumber
   and tsi.masterentity = 'MASTER' 
  left join employee_attendance ea 
    on ea.employeeid = tsi.credentialnumber
   AND ea.timestamp = tsi.timestamp
   AND ea.ismanual = 0
 where ea.employeeid is null

- paparazzo

Answer 5

使用LEFT JOIN和WHERE子句进行过滤，而不是使用NOT IN。

SELECT 
    em.employeeid,
    tsi.timestamp
    FROM timesheet_temp_import tsi
    join employee emp ON emp.employeeid = tsi.credentialnumber
    left join 
    (
        SELECT ea.timestamp 
        FROM employee_attendance ea 
        WHERE 
        ea.employeeid = em.employeeid
        AND ea.timestamp =  tsi.timestamp
        AND ea.ismanual = 0
    ) t on t.timestamp = tsi.timestamp
    WHERE
    tsi.masterentity = 'MASTER' AND
    t.timestamp is null
    GROUP BY 
    em.employeeid,
    tsi.timestamp

- Max

---

@Blam 你不能使用 LEFT JOIN 将条件与外部表相关联。 - Lamak