MySQL "IN" запрашивает ужасно медленно с подзапросом, но быстро с явными значениями

У меня есть запрос MySQL (Ubu 10.04, Innodb, Core i7, 16Gb RAM, SSD-диски, оптимизированные параметры MySQL):

SELECT
COUNT(DISTINCT subscriberid)
FROM
em_link_data
WHERE
linkid in (SELECT l.id FROM em_link l WHERE l.campaignid = '2900' AND l.link != 'open')

В таблице em_link_data имеется около 7 миллионов строк, у em_link - несколько тысяч. Этот запрос займет около 18 секунд. Однако, если подставить результаты подзапроса и выполните следующее:

SELECT
COUNT(DISTINCT subscriberid)
FROM
em_link_data
WHERE
linkid in (24899,24900,24901,24902);

тогда запрос будет выполняться менее чем за 1 миллисекунду. Только в подзапросе работает менее 1 мс, индексируется индекс linkid.

Если я переписал запрос как соединение, то и меньше 1 мс. Почему запрос "IN" настолько медленный с подзапросом в нем и почему так быстро со значениями в нем? Я не могу переписать запрос (купленное программное обеспечение), поэтому я надеялся, что есть некоторые подсказки или подсказки, чтобы ускорить этот запрос! Любая помощь приветствуется.

Ответы

Ответ 1

Подзапросы выполняются каждый раз, когда вы их оцениваете (в любом случае в MySQL, а не во всех RDBMS), т.е. вы в основном запускаете 7 миллионов запросов! Использование JOIN, если возможно, уменьшит это до 1. Даже если добавление индексирования улучшает производительность тех, вы все еще выполняете их.

Ответ 2

Да, IN с подзапросами медленно. Вместо этого используйте соединение.

SELECT
COUNT(DISTINCT subscriberid)
FROM em_link_data JOIN em_link ON em_link_data.linkid=em_link.id
WHERE em_link.campaignid = '2900' AND em_link.link != 'open'

И убедитесь, что вы указали индексы на em_link_data.linkid и em_link.id.

Ответ 3

Проблема заключается в том, что MySQL выполняет запросы извне внутрь, в то время как вы можете подумать, что ваш подзапрос выполняется один раз, а затем его результаты передаются выражению WHERE внешнего запроса (см. Документация по MySQL).

Если вы не можете переписать свой запрос, вы должны сделать следующие оптимизации:

  • добавьте индекс на campaignid и link, так как FrustratedWithFormsDesigner сказал
  • убедитесь, что подзапрос правильно использует индексы, выполнив EXPLAIN SELECT ...
  • включить и настроить кеш запросов, поскольку это должно ускорить подзапрос, вызываемый несколько раз

Еще одна идея - установить прокси-сервер MySQL и написать немного script, который перехватывает ваш запрос и перезаписывает его для использования соединения.

Ответ 4

Если ваш подзапрос работает быстро, значит, кампания и ссылка абсолютно индексируются. Таким образом, l.id является PK и кластеризуется быстро. Но, насколько я помню (с последнего раза я проверил этот вопрос), mysql описывает свои внутренние оптимизации для подзапросов "in", чтобы использовать индексный результат подзапроса для повышения производительности, а также использует кеш для левой стороны "IN" , чтобы перетащить его внутри подзапроса быстрее, и если индексы установлены верно, он не должен иметь такой разницы, чтобы использовать внутреннее соединение или "IN" , а не кеширование, и это может быть связано с проблемой кеша и массивными данными. http://dev.mysql.com/doc/internals/en/transformation-scalar-in.html

Я не знаю ситуации с программным обеспечением, но если вы можете использовать INNER JOIN, и у вас есть (возможно) некоторые дополнительные определения перед предложением IN в предложении WHERE вашего внешнего запроса, обязательно переместите эти предложения до ваш основной INNER JOIN через временный INNER JOIN ведет себя подобно последовательному "where" предложению последовательно и уменьшает количество перекрестных сравнений в JOIN следующим образом:

SELECT ... FROM t
INNER JOIN (SELECT 1) AS tmp ON t.asd=23
INNER JOIN t2 ON ...

Примеры сравнения нормального и временного поиска: 1000 * 1000 > 1000 + (100 * 1000)

Также кажется, что подзапрос фильтруется постоянными vals, поэтому, если бы это был я, я собирался помещать предложения в подзапрос, генерирующий набор результатов, и уменьшать количество сравнений в JOIN следующим образом:

SELECT ... FROM t
INNER JOIN (SELECT ... FROM t2 WHERE constant clauses) AS tbl2 ON ...

В любом случае, в запросе "IN" сравнение любого столбца таблицы в подзапросе с любым столбцом таблицы во внешнем запросе требует, чтобы столбцы обеих сторон были точно проиндексированы (в отношении составных индексов), но все же это может проблема с кешем.

EDITED: Также мне было любопытно спросить: может ли сделать составной индекс на l.campaignid, l.link и l.id какой-нибудь смысл?