Почему условие IN будет медленнее, чем «=» в sql?
Проверьте вопрос Этот запрос SELECT занимает 180 секунд, чтобы завершить (проверьте комментарии к самому вопросу).
IN можно сравнивать только с одним значением, но разница во времени огромна.
Почему это так?
3 ответа
Резюме. Это известная проблема в 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;
Речь идет о внутренних запросах, таких как подзапросы и соединения, а не о IN vs =, и причины объясняются в этом посте. Предполагается, что версия MySQL 5.4 представляет улучшенный оптимизатор, который может переписывать некоторые подзапросы в более эффективную форму.
Худшее, что вы можете сделать, - это использовать так называемый коррелированный подзапрос. http://dev.mysql.com/doc/refman/5.1/en/correlated-subqueries.html
Оптимизаторы SQL не всегда делают то, что вы от них ожидаете. Я не уверен, что есть лучший ответ, чем этот. Вот почему вам необходимо изучить выходные данные EXPLAIN PLAN и профилировать свои запросы, чтобы узнать, на что тратится время.