mysql - SQL检查表中的值是否在另一个表中的2列范围内

Question

我正在尝试检查 TABLE_1 中的值是否在基于 TABLE_2 中的 2 列的范围内。我能够根据问题中提供的答案完成 SQL: Checking if a number within range of multiple ranges但是，当我将此方法用于大型数据集(两个表中约有 40K 行)时，它会在我的 SQL 服务器上用尽 CPU，并且查询需要超过 3 分钟。有没有办法优化这个查询并限制查询使用的 CPU？如果没有，是否有可能更有效的替代查询。

表_1:

mysql> SELECT * FROM TABLE_1;
+----+---------+-------+
| ID | FRUIT   | COUNT |
+----+---------+-------+
|  1 | Apples  |  2314 |
|  2 | Oranges |  3412 |
|  3 | Oranges |  1296 |
|  4 | Apples  |  2230 |
|  5 | Apples  |  5293 |
|  6 | Oranges |  1994 |
+----+---------+-------+
6 rows in set (0.00 sec)

表_2:

mysql> SELECT * FROM TABLE_2;
+----+---------+-------------+-----------+
| ID | FRUIT   | START_RANGE | END_RANGE |
+----+---------+-------------+-----------+
|  1 | Apples  |        2300 |      2400 |
|  2 | Apples  |        7000 |      8000 |
|  3 | Oranges |        1296 |      1296 |
|  4 | Apples  |        5000 |      6000 |
|  5 | Oranges |        9000 |      9999 |
|  6 | Oranges |        8000 |      9000 |
+----+---------+-------------+-----------+

查询:

SELECT *
FROM TABLE_1
WHERE NOT EXISTS (SELECT 1 FROM TABLE_2
                  WHERE TABLE_1.FRUIT = TABLE_2.FRUIT 
                  AND TABLE_1.COUNT BETWEEN TABLE_2.START_RANGE AND TABLE_2.END_RANGE);

输出:

+----+---------+-------+
| ID | FRUIT   | COUNT |
+----+---------+-------+
|  2 | Oranges |  3412 |
|  4 | Apples  |  2230 |
|  6 | Oranges |  1994 |
+----+---------+-------+
3 rows in set (0.00 sec)

Answer 1

not exists 可能是性能最好的版本，但您可以尝试等效的 left join:

select table1.*
from Table1
left join table2
on table1.fruit = table2.fruit
and table1.count between table2.start_range and table2.end_range
where table2.id is null