为什么在SQL中，“IN”条件比“=”慢？

Question

为什么在SQL中，“IN”条件比“=”慢？

sqlmysqlperformancecomparison

30

请查看问题This SELECT query takes 180 seconds to finish（请在问题本身的评论中检查）。

IN操作符只与一个值进行比较，但时间差异仍然巨大。
为什么会这样呢？

- Itay Moav -Malimovka

@nos提到将“IN”更改为“=”后，时间从180秒减少到0.00008秒。 - NullUserException

4个回答

1

SQL优化器并不总是按照你的期望执行。我不确定是否有比这更好的答案。这就是为什么你需要检查EXPLAIN PLAN输出，并对查询进行分析，以找出时间花费在哪里。

- Ned Batchelder

建议将EXPLAIN作为分析查询性能的起点，可以得到+1。 - Cumbayah

1

这是关于内部查询，也就是子查询与连接的比较，而不是IN与=的比较，原因在该文章中有解释。 MySQL 5.4版本应该会引入一个改进的优化器，可以将一些子查询重写为更高效的形式。

最糟糕的事情是使用所谓的相关子查询 http://dev.mysql.com/doc/refman/5.1/en/correlated-subqueries.html

- Mchl

0

这很有趣，但问题也可以使用预处理语句解决（不确定是否适用于每个人），例如：

mysql> EXPLAIN SELECT * FROM words WHERE word IN (SELECT word FROM phrase_words);
+----+--------------------+--------------+...
| id | select_type        | table        |...
+----+--------------------+--------------+...
|  1 | PRIMARY            | words        |...
|  2 | DEPENDENT SUBQUERY | phrase_words |...
+----+--------------------+--------------+...
mysql> EXPLAIN SELECT * FROM words WHERE word IN ('twist','rollers');
+----+-------------+-------+...
| id | select_type | table |...
+----+-------------+-------+...
|  1 | SIMPLE      | words |...
+----+-------------+-------+...

所以只需在存储过程中准备好语句，然后执行它。以下是思路：

SET @words = (SELECT GROUP_CONCAT(word SEPARATOR '\',\'') FROM phrase_words);
SET @words = CONCAT("'", @words, "'");
SET @query = CONCAT("SELECT * FROM words WHERE word IN (", @words, ");";
PREPARE q FROM @query;
EXECUTE q;

- Maksim

如果您想走这条路，那么请在存储过程中创建一个临时表，只包含您想要的IN值，并将其与主表连接。 - Itay Moav -Malimovka

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Mark Byers · Accepted Answer

摘要：这是MySQL中一个已知问题，在MySQL 5.6.x 中得到了修复。该问题是由于使用IN的子查询被错误地识别为依赖子查询而不是独立子查询时缺少优化导致的。

当您在原始查询上运行EXPLAIN时，它返回以下内容：

1  'PRIMARY'             'question_law_version'  'ALL'  ''  ''  ''  ''  10148  'Using where'
2  'DEPENDENT SUBQUERY'  'question_law_version'  'ALL'  ''  ''  ''  ''  10148  'Using where'
3  'DEPENDENT SUBQUERY'  'question_law'          'ALL'  ''  ''  ''  ''  10040  'Using where'

当您将IN更改为=时，您会得到以下结果：

1  'PRIMARY'   'question_law_version'  'ALL'  ''  ''  ''  ''  10148  'Using where'
2  'SUBQUERY'  'question_law_version'  'ALL'  ''  ''  ''  ''  10148  'Using where'
3  'SUBQUERY'  'question_law'          'ALL'  ''  ''  ''  ''  10040  'Using where'

每个依赖子查询都会针对包含它的查询中的每一行运行一次，而子查询仅运行一次。当条件可以转换为连接时，MySQL有时可以优化依赖子查询，但这里不是这种情况。

当然，这也引出了一个问题，为什么MySQL认为IN版本需要是一个从属子查询。我制作了一个简化版本的查询来帮助调查这个问题。我创建了两个表'foo'和'bar'，前者只包含一个id列，后者包含一个id和一个foo id（虽然我没有创建外键约束）。然后，我将这两张表填充了1000行：

CREATE TABLE foo (id INT PRIMARY KEY NOT NULL);
CREATE TABLE bar (id INT PRIMARY KEY, foo_id INT NOT NULL);

-- populate tables with 1000 rows in each

SELECT id
FROM foo
WHERE id IN
(
    SELECT MAX(foo_id)
    FROM bar
);

这个简化的查询与之前一样存在问题——内部选择被视为依赖子查询，没有进行优化，导致内部查询每行运行一次。查询需要近一秒钟才能运行。再次将IN更改为=可以使查询几乎瞬间运行。

如果有人想要复制结果，下面是我用来填充表格的代码。

CREATE TABLE filler (
        id INT NOT NULL PRIMARY KEY AUTO_INCREMENT
) ENGINE=Memory;

DELIMITER $$

CREATE PROCEDURE prc_filler(cnt INT)
BEGIN
        DECLARE _cnt INT;
        SET _cnt = 1;
        WHILE _cnt <= cnt DO
                INSERT
                INTO    filler
                SELECT  _cnt;
                SET _cnt = _cnt + 1;
        END WHILE;
END
$$

DELIMITER ;

CALL prc_filler(1000);

INSERT foo SELECT id FROM filler;
INSERT bar SELECT id, id FROM filler;