MySQL JOIN не фильтрует предложение WHERE с операторами ‹ › после перехода с MySQL 5.6 на 5.7

В MySQL 5.7 производные таблицы (подзапросы в предложении FROM) будут объединены во внешний запрос, если это возможно. Обычно это является преимуществом, поскольку позволяет избежать сохранения результата подзапроса во временной таблице. Однако для вашего запроса MySQL 5.6 создаст индекс для этой временной таблицы, который можно будет использовать для выполнения соединения.

Проблема с объединенным запросом заключается в том, что индекс в TableB.created_at нельзя использовать, когда столбец является параметром функции. Если вы можете изменить запрос так, чтобы преобразование выполнялось в столбце с левой стороны соединения, для доступа к таблице с правой стороны можно использовать индекс. Что-то типа:

   select * from TableA as A
            left join
            (
                select created_at as time
                FROM TableB
                WHERE created_at < DATE_ADD(CURDATE(), INTERVAL -3 HOUR) 
            )
            as V ON V.time = func(A.time)

В качестве альтернативы, если вы можете использовать внутреннее соединение вместо левого соединения, MySQL может изменить порядок соединения, чтобы для соединения можно было использовать индекс в tableA.time.

Если подзапрос использует LIMIT, его нельзя объединить. Следовательно, используя LIMIT, вы получите тот же план запроса, что и в MySQL 5.6.

Вывод explain row — это всего лишь предположение о том, сколько строк будет затронуто. Он основан на статистических данных, которые были сброшены вместе с вашим обновлением. И если бы мне пришлось угадывать, сколько строк из всех ваших существующих строк старше вчерашних 9 вечера, я бы тоже предположил, что это ближе ко «всем строкам», чем к «только некоторым строкам». Причина, по которой «limit 99999999» отображает другой счетчик строк, та же самая: он просто предполагает, что ограничение будет иметь эффект; в этом случае mysql предполагает, что это будет ровно половина строк (что было бы, если это правда, странное совпадение), и, конечно, на самом деле он не смотрит на предельное значение, поскольку 999999999 ничего не ограничивает, когда у вас всего 500 тыс. строк; и даже «1» в случае «=» - это просто предположение (и чаще может быть 0, чем 1, а иногда и больше).

Эта оценка поможет выбрать правильный план выполнения, и ошибиться в этом предположении — просто проблема, если он выберет неправильный; ваш план выполнения выглядит хорошо, и в противном случае не так много вариантов сделать это. Он делает именно так, как и ожидалось: сканирует индекс на все даты, используя индекс на created_at. Поскольку вы выполняете левое соединение, вы не можете пропустить значения из tableA, даже если вы начнете с внутреннего запроса, поэтому альтернативного плана выполнения действительно нет. (На самом деле оптимизатор был изменен в 5.7., но здесь это не имеет никакого эффекта.)

Если это ваш фактический запрос, нет реальной причины, по которой он должен быть медленнее, чем раньше (только в отношении этого запроса; конечно, есть много общих параметров производительности, которые могут иметь косвенный эффект, например, стратегии кэширования, размеры буферов и т. д. ., но со стандартными опциями здесь это не должно влиять).

Если нет, и вы, например. на самом деле использовать дополнительные столбцы из TableB в подзапросе (часто трудно угадать, какие, возможно, важные вещи были «упрощены» в вопросах), и, следовательно, требуется доступ к фактической таблице, это может зависеть от того, как структурированы ваши данные (или лучше: в каком порядке вы это добавили). И вы можете попробовать Optimize table TableB, чтобы сделать вашу таблицу и индексы свежими и новыми, это не повредит (но заблокирует вашу таблицу на некоторое время).

С mysql 5.7. теперь вы можете добавлять сгенерированные столбцы, поэтому, возможно, стоит попробовать сгенерировать очищенный столбец time as DATE_FORMAT(created_at,'%H:%i:00'), чтобы вам больше не приходилось его вычислять. И, возможно, добавьте его в свой индекс, чтобы вам больше не нужно было его сортировать, чтобы улучшить block nested join, но это может зависеть от вашего фактического запроса и от того, как часто вы его используете (индексы со спамом увеличивают накладные расходы и занимают место).

Используйте JOIN вместо LEFT JOIN, если только вам не нужно, чтобы "правильная" таблица была необязательной.

Избегайте JOIN ( SELECT ... ). Хотя в версиях 5.6 и 5.7 были добавлены некоторые возможности для его обработки, обычно лучше превратить подзапрос в более простой JOIN.

Ваше выражение времени ведет к 9 вечера вчера; вы имели в виду "3 часа назад" вместо этого?

Посмотрите, дает ли это желаемые результаты и работает быстрее:

select  A.*, DATE_FORMAT(B.created_at,'%H:%i:00') as `time`
    from  TableA as A
    JOIN  TableB as B  ON B.time = A.time
    WHERE  B.created_at < NOW() - INTERVAL 3 HOUR   -- (assuming "3 hours ago")

Что касается 5.6 против 5.7... 5.7 имеет новый, "лучший" оптимизатор, основанный на "стоимостной модели". Однако ваш конкретный запрос делает практически невозможным для оптимизатора получение хороших затрат. Я предполагаю, что 5,6 произошло на лучшем EXPLAIN, а 5,7 произошло на худшем. Упрощая запрос, я думаю, что у обоих оптимизаторов будет больше шансов выполнить запрос быстрее.

Вам нужны эти индексы:

B:  INDEX(time, created_at) -- in that order
A:  INDEX(time)