Почему условие IN будет медленнее, чем "=" в sql?

Question

Проверка вопроса Этот запрос SELECT занимает 180 секунд (проверьте комментарии к самому вопросу).
IN можно сравнивать только с одним значением, но разница во времени огромна.
Почему это так?

Answer 1

Описание: Это известная проблема в MySQL, исправленная в MySQL 5.6.x. Проблема связана с отсутствующей оптимизацией, когда подзапрос с использованием IN неправильно идентифицируется как зависимый подзапрос, а не как независимый подзапрос.

---

Когда вы запускаете EXPLAIN для исходного запроса, он возвращает это:

1 'ПЕРВИЧНЫЙ'             'question_law_version'  'ALL'  ''  ''  ''  ''  10148  'Использование где'
2  'ЗАВИСИМАЯ ПОДПИСЬ' 'question_law_version' 'ALL' '' '' '' '' '10148' Использование где '
3  'ЗАВИСИМАЯ ПОДПИСЬ' 'question_law' 'ALL' '' '' '' '' 10040  'Using where'

Когда вы меняете IN в = Вы получаете это:

1 'ПЕРВИЧНЫЙ'             'question_law_version'  'ALL'  ''  ''  ''  ''  10148  'Использование где'
2  'SUBQUERY'  'question_law_version'  'ALL'  ''  ''  ''  ''  10148  'Использование где'
3  'SUBQUERY'  'question_law'          'ALL'  ''  ''  ''  '' '10040' Использование где '

Каждый зависимый подзапрос выполняется один раз для каждой строки в запросе, в котором он содержится, тогда как подзапрос выполняется только один раз. MySQL иногда может оптимизировать зависимые подзапросы, когда есть условие, которое может быть преобразовано в соединение, но здесь это не так.

Теперь это, конечно, оставляет вопрос о том, почему MySQL считает, что версия IN должна быть зависимым подзапросом. Я сделал упрощенную версию запроса, чтобы помочь исследовать это. Я создал две таблицы 'foo' и 'bar', где первая содержит только столбец id, а вторая содержит и id, и идентификатор foo (хотя я не создала ограничение внешнего ключа). Затем я заполнил обе таблицы 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;

Answer 2

Оптимизаторы SQL не всегда делают то, что от них ожидают. Я не уверен, что есть лучший ответ, чем это. Вот почему вы должны изучить выходные данные EXPLAIN PLAN и профилировать свои запросы, чтобы узнать, на что тратится время.

Answer 3

Речь идет о внутренних запросах, то есть подзапросах против объединений, а не о IN vs =, и причины этого объясняются в этом посте. Предполагается, что в версии MySQL 5.4 появился улучшенный оптимизатор, который может переписать некоторые подзапросы в более эффективную форму.

Худшее, что вы можете сделать, это использовать так называемый коррелированный подзапрос http://dev.mysql.com/doc/refman/5.1/en/correlated-subqueries.html

Answer 4

Это интересно, но проблему также можно решить с помощью подготовленных высказываний (не уверен, подходит ли это всем), например:

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;