mysql - 使用临时变量优化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 上放置索引。

有人可以解释一下，这种方法是否会优化查询？

更新:如前所述here这两种方法: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 |
+----+--------------------+---------------+------+-------------------------+-------------------------+---------+-------+------+-------------+

Answer 1

你是对的，因为 NOT IN 和 LEFT JOIN/IS NULL 方法都是 equally efficient , 然而，不幸的是，没有更快的选择，只有更慢的选择(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 查询没有帮助。 covering index on word 可能仍可用于避免访问实际表，并提供一些 IO 优势，但它不会用于传统的“查找”意义上。但是，由于您要返回 word 表中的所有列，因此拥有如此大的索引可能不可行。

此处将使用的最重要的索引是 sent_question.msisdn 上用于子查询的索引。确保您已定义该索引。 (msisdn, correct_option_word_id) 上的多列“覆盖”索引最好。

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