通过使用临时变量来优化MySQL中的NOT IN查询

Question

我在尝试优化mysql中的NOT IN子句时：最终得到了以下查询：

SELECT @i:=(SELECT correct_option_word_id FROM sent_question WHERE msisdn='abc');
SELECT * FROM word WHERE  @i IS NULL OR word_id NOT IN (@i);

sent_question表和word表之间没有关系。而且我无法在correct_option_word_id上放置索引。

有人能解释一下，这种方法是否能优化查询吗？

更新：如此处所述，NOT IN和LEFT JOIN / IS NULL两种方法几乎同样有效。这就是为什么我不想使用LEFT JOIN / IS NULL方法。

更新2： 原始查询的解释结果：

EXPLAIN SELECT * FROM word WHERE word_id NOT IN (SELECT correct_option_word_id FROM sent_question WHERE msisdn='abc');
+----+--------------------+---------------+------+-------------------------+-------------------------+---------+-------+------+-------------+
| id | select_type        | table         | type | possible_keys           | key                     | key_len | ref   | rows | Extra       |
+----+--------------------+---------------+------+-------------------------+-------------------------+---------+-------+------+-------------+
|  1 | PRIMARY            | word          | ALL  | NULL                    | NULL                    | NULL    | NULL  |   10 | Using where |
|  2 | DEPENDENT SUBQUERY | sent_question | ref  | fk_question_subscriber1 | fk_question_subscriber1 | 48      | const |    1 | Using where |
+----+--------------------+---------------+------+-------------------------+-------------------------+---------+-------+------+-------------+

- user517491

---

sent_question和word之间有关系吗？最初的查询是什么？ - Marcus Adams

@MarcusAdams，sent_question表和word表之间没有关联。此外，我也无法在correct_option_word_id上创建索引。 - user517491

请展示您原始查询的 EXPLAIN 结果。 - Marcus Adams

谢谢您的解释。看起来您在“word”表上没有任何索引。请查看我的更新答案以获取详细信息。 - Marcus Adams

Answer 1

你说得对，NOT IN 和 LEFT JOIN/IS NULL 方法都是同样有效的，但不幸的是，没有更快的选项，只有更慢的选项（NOT EXISTS）。

这是你的查询，简化版：

SELECT *
FROM word
  WHERE
  word_id NOT IN (SELECT correct_option_word_id FROM sent_question WHERE msisdn='abc')

你知道，MySQL会先执行子查询并使用返回的结果集来处理NOT IN子句，然后它将扫描所有行中的word，以查看每一行中是否存在word_id列表。

对于这种情况，索引是包容性的而非排除性的，不能帮助NOT查询。在word上的覆盖索引可能仍然可以用于避免访问实际表，并提供一些IO的好处，但它不会被用于传统的"查找"意义上。但是，由于你正在返回word表上的所有列，可能无法拥有这样一个大的索引。

这里将使用最重要的索引是sent_question.msisdn的索引子查询。确保你已定义了该索引。在(msisdn, correct_option_word_id)上的多列"覆盖"索引将是最佳的选择。

如果你分享你的设计，我们可能可以为优化提供一些设计解决方案。

- Marcus Adams

---

我已经为原始的“向上优化”查询添加了解释结果，请查看。 - user517491

@djaqeel，谢谢，我已经更新了我的答案并提供了更多信息。你的查询没有什么严重的问题，但是一个覆盖索引可能会有所帮助。 - Marcus Adams

谢谢解释。我有点害羞地问，“你确定我的查询确切地像你所说的那样结束了吗？”我在某个地方读到，NOT IN 子查询会为每一行执行一次。这是真的吗？ - user517491

如果子查询引用了主查询中的列，那么它将为每一行执行。但在这种情况下，它并没有引用主查询，因此不会为每一行执行。 - Marcus Adams

Answer 2

我怀疑它根本不会起作用。

尝试一下

SELECT * 
FROM word  AS w
LEFT JOIN sent_question AS sq
ON w.word_id = sq.correct_option_word_id  AND sq.msisdn='abc'
WHERE sq.correct_option_word_id IS NULL

- Mchl

Answer 3

试试这个简单的查询

SELECT 
    sent_question.*, 
    word.word_id AS foundWord
FROM sent_question 
LEFT JOIN word
    ON word.word_id = sent_question.correct_option_word_id

WHERE sent_question.msisdn='abc'

// GROUP BY sent_question.correct_option_word_id // This shouldn't be needed but included for completion
HAVING foundWord IS NULL

- Simon at My School Portal