Почему STRAIGHT_JOIN так сильно улучшает этот запрос, и что это значит, когда он написан после ключевого слова SELECT?

У меня есть следующий запрос MySql:

select t1.*
from Table1 t1
inner join Table2 t2
on t1.CommonID = t2.CommonID
where t1.FilterID = 1

Для запуска требуется около 30 секунд, что было странно, потому что, если я прокомментирую предложение join или where, это займет меньше секунды: то есть.

select t1.*
from Table1 t1
where t1.FilterID = 1

или

select t1.*
from Table1 t1
inner join Table2 t2
on t1.CommonID = t2.CommonID

каждый занимает меньше секунды.

Тогда есть ключевое слово STRAIGHT_JOIN, в котором я могу найти одну ссылку, здесь: http://dev.mysql.com/doc/refman/5.0/en/join.html

STRAIGHT_JOIN похож на JOIN, за исключением того, что левая таблица всегда прочитайте перед правильным столом. Это может для тех (нескольких) случаев для который оптимизатор объединения ставит таблицы в неправильном порядке.

Что? Я могу написать:

select t1.*
from Table1 t1
STRAIGHT_JOIN  Table2 t2
on t1.CommonID = t2.CommonID
where t1.FilterID = 1

и запрос выполняется менее чем за секунду.

Даже незнакомец, я могу написать:

select STRAIGHT_JOIN  t1.*
from Table1 t1
inner join Table2 t2
on t1.CommonID = t2.CommonID
where t1.FilterID = 1

и это занимает менее секунды, и этот синтаксис не кажется даже законным.

Я бы предположил, что второй пример означает, что STRAIGHT_JOIN будет использоваться всякий раз, когда записывается INNER JOIN, но я не могу найти документацию об этом.

Что здесь происходит, и как "оптимизатор объединения" приводит к такой относительно низкой производительности? Должен ли я всегда использовать STRAIGHT_JOIN? Как я могу узнать, когда его использовать или нет?

Таблица 1 и Таблица 2 имеют целые первичные ключи; FilterID - это внешний ключ для другой таблицы; столбцы CommonID ​​являются внешними ключами к третьей таблице. У них обоих есть индексы. Ядром базы данных является InnoDB.

Спасибо

Ответы

Ответ 1

Что здесь происходит, и как оптимизатор объединения может привести к такой относительно низкой производительности?

STRAIGHT_JOIN заставляет порядок соединения таблиц, поэтому table1 сканируется во внешнем цикле и table2 во внутреннем цикле.

Оптимизатор не идеален (хотя и довольно приличный), и наиболее вероятной причиной является устаревшая статистика.

Должен ли я всегда использовать STRAIGHT_JOIN

Нет, только если оптимизатор ошибочен. Это может быть, если ваше распределение данных сильно искажено или не может быть правильно рассчитано (скажем, для пространственных или полнотекстовых индексов).

Как я могу узнать, когда его использовать или нет?

Вы должны собирать статистику, строить планы для обоих способов и понимать, что означают эти планы.

Если вы видите, что:

  • Автоматически созданный план не является оптимальным и не может быть улучшен стандартными способами,

  • Версия STRAIGHT_JOIN лучше, вы понимаете, что она всегда будет и понимает, почему она всегда будет

затем используйте STRAIGHT_JOIN.