在SQL中用NOT IN会影响性能,导致的主要原因就是索引无效,所以最好是将NOT IN改为其他方式实现。
没修改之前的NOT IN写法:
SELECT DISTINCT
t2.a_id
FROM
temp_b t2
WHERE
t2.a_id NOT IN(
SELECT DISTINCT
t4.a_id
FROM
b AS t3,
temp_b AS t4
WHERE
t4.a_id = t3.a_id
)
修改为LEFT JOIN写法:
SELECT DISTINCT
t6.a_id
FROM
(
SELECT
t2.a_id,
t5.a_id AS tempId
FROM
temp_b t2
LEFT JOIN(
SELECT DISTINCT
t4.a_id
FROM
b AS t3,
temp_b AS t4
WHERE
t4.a_id = t3.a_id
)AS t5 ON t2.a_id = t5.a_id
)AS t6
WHERE
t6.tempId IS NULL
修改为NOT EXISTS写法:
SELECT DISTINCT
t2.a_id
FROM
temp_b t2
WHERE
NOT EXISTS(
SELECT
1
FROM
(
SELECT DISTINCT
t4.a_id
FROM
b AS t3,
temp_b AS t4
WHERE
t4.a_id = t3.a_id
)AS t5
WHERE
t5.a_id = t2.a_id
)