Когда использовать прямое соединение с MySQL

с MySQL JOIN reference:

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

MySQL не обязательно хорош в выборе порядка соединения в сложных запросах. Указав сложный запрос в виде straight_join, запрос выполняет соединения в указанном порядке. Поместив таблицу в первый наименьший общий знаменатель и указав straight_join, вы сможете повысить производительность запроса.

STRAIGHT_JOIN, используя это предложение, вы можете управлять JOIN порядок: какая таблица сканируется во внешнем цикле, а какая-во внутреннем цикле.

вот сценарий, который появился совсем недавно на работе.

рассмотрим три таблицы, A, B, C.

а имеет 3000 строк; Б 300,000,000 строк; и C и 2000 строк.

внешние ключи определены: B(a_id), B (c_id).

предположим, что у вас есть запрос, который выглядит следующим образом:

select a.id, c.id
from a
join b on b.a_id = a.id
join c on c.id = b.c_id

по моему опыту, MySQL может выбрать, чтобы пойти C - > B - > A в этом случае. C меньше, чем A, А B огромен, и все они равнозначны.

в беда в MySQL не обязательно учитывать площадь пересечения (С. ID и Б. основные достижения и итоговые документы) против (ИД А. и Б. распределения a_id). Если соединение между B и C возвращает столько же строк, сколько и B, то это очень плохой выбор; если бы начиная с A отфильтровывалось B до такого количества строк, как A, то это был бы гораздо лучший выбор.

Как правило, вы хотите сделать ваши соединения в порядке, который минимизирует количество строк в результирующем наборе. Итак, начнем с небольшого стола и соединение таким образом, что результирующее соединение также будет небольшим, идеально. Вещи идут грушевидной формы, если, начиная с небольшого стола и соединяя его с большим столом заканчивается так же, как большой стол.

Это зависит от статистики, хотя. Если распределение данных изменяется, расчет может измениться. Это также зависит от деталей реализации механизма соединения.

Я скажу вам, почему я должен был использовать STRAIGHT_JOIN:

• Я производительность проблема с запросом.
• упрощая запрос, запрос был неожиданно более эффективным
• пытаясь выяснить, какая конкретная часть приносила проблему, я просто не мог. (2 левых соединения были медленными, и каждый из них был независимо быстрым)
• затем я выполнил объяснение с медленным и быстрым запросом (addind один из левых присоединяется)
• Удивительно, но MySQL полностью изменил порядок соединения между двумя запросами.

поэтому я заставил одно из соединений быть straight_join, чтобы заставить предыдущее соединение быть прочитанным первым. Это помешало MySQL изменить порядок выполнения и работало как шарм !

по моему короткому опыту, одна из ситуаций, которые STRAIGHT_JOIN уменьшил мой запрос с 30 секунд до 100 миллисекунд заключается в том, что первая таблица в плане выполнения не была таблицей, которая имеет порядок по столбцам

-- table sales (45000000) rows
-- table stores (3) rows
SELECT whatever
FROM 
    sales 
    INNER JOIN stores ON sales.storeId = stores.id
ORDER BY sales.date, sales.id 
LIMIT 50;
-- there is an index on (date, id)

если оптимизатор выбирает нажмите storesпервый это вызовет Using index; Using temporary; Using filesort, потому что

если ORDER BY или GROUP BY содержит столбцы из других таблиц первая таблица в очереди соединения-это временная таблица создан.

источник

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

sales STRAIGHT_JOIN stores

если ваш запрос заканчивается ORDER BY... LIMIT..., это мая оптимально переформулировать запрос, чтобы обмануть оптимизатор в выполнении LIMITдо the JOIN.

(этот ответ не относится только к исходному вопросу о STRAIGHT_JOIN, и это не относится ко всем случаям STRAIGHT_JOIN.)

начиная с пример @Accountantم, это должно работать быстрее в большинстве ситуаций. (И это позволяет избежать необходимости полунамеки.)

SELECT  whatever
    FROM  ( SELECT id FROM sales
                ORDER BY  date, id
                LIMIT  50
          ) AS x
    JOIN  sales   ON sales.id = x.id
    JOIN  stores  ON sales.storeId = stores.id
    ORDER BY  sales.date, sales.id;

Примечания:

• во-первых, 50 идентификаторов извлекаются. Это будет особенно быстро с INDEX(date, id).
• затем присоединиться обратно к sales позволяет получить только 30 "whatevers"без тащить их вокруг в таблице temp.
• поскольку подзапрос, по определению, неупорядочен, то ORDER BY необходимо повторить.
• Да, это Мессье. Но это обычно быстрее.

Я против использования хиты, потому что " даже если это быстрее сегодня, это может не быть быстрее завтра."

--use 120s, 18 million data
    explain SELECT DISTINCT d.taid
    FROM tvassist_recommend_list_everyday_diverse d, tvassist_taid_all t
    WHERE d.taid = t.taid
      AND t.client_version >= '21004007'
      AND t.utdid IS NOT NULL
      AND d.recommend_day = '20170403'
    LIMIT 0, 10000

--use 3.6s repalce by straight join
 explain SELECT DISTINCT d.taid
    FROM tvassist_recommend_list_everyday_diverse d
    STRAIGHT_JOIN 
      tvassist_taid_all t on d.taid = t.taid 
    WHERE 
     t.client_version >= '21004007'
       AND d.recommend_day = '20170403'

      AND t.utdid IS NOT NULL  
    LIMIT 0, 10000