Использование индекса MySQL при соединении

Question

Я знаю, что есть несколько вопросов, похожих на этот, но те, которые я нашел, не имеют прямого отношения к моей проблеме.

Некоторый начальный контекст: у меня есть таблица фактов, называемая ft_booking, с записями около 10 мм. У меня есть измерение, называемое dm_date, с примерно 11k записями, которые являются датами. Эти таблицы связаны, как обычно, через внешние ключи. В таблице ft_booking есть 3 внешних ключа даты, один для посадки, один для бронирования и другой для отмены. Все столбцы имеют одно и то же определение, и количество отдельных записей для каждого одинаково (от 2,5 до 3 тысяч различных значений в каждом столбце).

Там я иду:

EXPLAIN SELECT
*
FROM dw.ft_booking b
LEFT JOIN dw.dm_date db ON db.sk_date = b.fk_date_booking
WHERE date (db.date) = '2018-05-05'

Как видите, индекс используется при бронировании таблицы, и запрос выполняется очень быстро, хотя в моем фильтре я использую функцию date(). Для краткости я просто скажу, что то же самое происходит с использованием столбца fk_date_boarding. Но, проверьте это:

EXPLAIN SELECT
*
FROM dw.ft_booking b
LEFT JOIN dw.dm_date db ON db.sk_date = b.fk_date_cancellation
WHERE date (db.date) = '2018-05-05';

По какой-то таинственной причине планировщик решает не использовать индекс. Теперь я понимаю, что использование некоторой функции над столбцом заставляет базу данных выполнить полное сканирование таблицы, чтобы можно было применить эту функцию к столбцу, минуя индекс. Но, в этом случае, функция не находится над фактическим столбцом внешнего ключа, где должен происходить поиск в таблице бронирования.

Если я удалю функцию date(), индекс будет использоваться в любом из этих столбцов, как и ожидалось. Тогда можно сказать: "Ну, почему бы вам просто не избавиться от функции date()?" - Я использую метабазу, интерфейс, который позволяет пользователям использовать графический интерфейс для создания запросов, не зная MySQL, и одно из текущих ограничений этого инструмента заключается в том, что он всегда использует функцию date() при построении запросов, не написанных непосредственно в MySQL - следовательно, у меня нет возможности удалить функцию в запросах, которые я выполняю.

Актуальный вопрос: почему MySQL использует индекс в первых двух случаях, но не во втором, учитывая, что количество различных значений одинаково для всех столбцов, и у них есть точное определение smae, кроме имени? Я что-то здесь упускаю?

РЕДАКТИРОВАТЬ: Вот CREATE статус каждой таблицы. Есть еще несколько, но нам просто нужны здесь таблицы ft_booking и dm_date (первые две таблицы файла).

Answer 1

Вы прячетесь date в вызове функции ". Если db.date объявлен DATE, затем

    date (db.date) = '2018-05-05'

может быть просто

    db.date = '2018-05-05'

Если db.date объявлен DATETIME, затем измените на

        db.date >= '2018-05-05'
    AND db.date  < '2018-05-05' + INTERVAL 1 DAY

В любом случае, убедитесь, что есть индекс на db.date,

Если под "у меня есть измерение с именем dm_date", вы имеете в виду, что вы построили таблицу измерений для хранения только дат, и тогда вы JOINing на главный стол с некоторыми id... Если говорить прямо, не делай этого! Не нормализуйте "непрерывные" вещи, такие как DATE, DATETIME, FLOATили другие числовые значения.

Если вам нужно обсудить это дальше, пожалуйста, предоставьте SHOW CREATE TABLE для соответствующей таблицы (ей). (И, пожалуйста, используйте текст, а не снимки экрана.)

Зачем??

Ответ прост: оптимизатор не знает, как распознать какую-либо функцию. Возможно это могло бы; возможно, так и должно быть. Но это не так. Возможно, ответ заключается в нежелании видеть, как будет использоваться результат функции... сравнивая с DATE? против DATETIME? используется как строка? Другой?

Тем не менее, я полагаю, что реальным убийцей производительности является существование dm_date вместо индексации и использования даты в основной таблице.

Кроме того, основной стол больше, чем нужно! fk_date_booking это 4 байта INT SIGNED вместо 3-х байт DATE,