Почему условие IN было бы медленнее, чем «=» в sql?

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

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

 1 'PRIMARY' 'question_law_version' 'ALL' '' '' '' '' 10148 'Использование где'
 2 «ЗАВИСИМОЕ СУБКЕРУ» 'question_law_version' 'ALL' '' '' '' '' 10148 'Использование где'
 3 «ЗАВИСИМОЕ ПОДТВЕРЖДЕНИЕ» 'question_law' 'ALL' '' '' '' '' 10040 'Использование, где'

Когда вы меняете IN на = вы получаете следующее:

 1 'PRIMARY' 'question_law_version' 'ALL' '' '' '' '' 10148 'Использование где'
 2 'SUBQUERY' 'question_law_version' 'ALL' '' '' '' '' 10148 'Использование где'
 3 'SUBQUERY' 'question_law' 'ALL' '' '' '' '' 10040 'Использование где'

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

Теперь это, конечно, оставляет вопрос о том, почему MySQL считает, что версия IN должна быть зависимым подзапросом. Я сделал упрощенную версию запроса, чтобы помочь расследовать это. Я создал две таблицы «foo» и «bar», где первый содержит только столбец идентификаторов, а второй содержит 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 to = снова позволяет запросить выполнение почти мгновенно.

Код, который я использовал для заполнения таблиц, приведен ниже, если кто-то хочет воспроизвести результаты.

 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 =, и причины объясняются в этом сообщении. В версии 5.4 MySQL предлагается ввести улучшенный оптимизатор, который может переписать некоторые подзапросы в более эффективную форму.

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

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

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

 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;