Индекс MySQL и план объяснения

У меня есть этот запрос:

SELECT * FROM dwDimDate d 
LEFT JOIN tickets t FORCE INDEX FOR JOIN (idx_tickets_id_and_date) ON 
DATE_FORMAT(t.ticket_date, '%Y%m%d') = d.date_key 
LEFT JOIN sales s ON s.ticket_id = t.ticket_id
WHERE d.date_key BETWEEN 20130101 AND 20131231
GROUP BY d.date_key

и я ищу помощь в его оптимизации. Я читал все, что мог, чтобы понять план объяснения и оптимизировать его, но я не могу запретить MySQL использовать поиск типа ALL в таблице билетов.

ИНДЕКСЫ:

введите здесь описание изображения

ОБЪЯСНИТЕ ПЛАН:

введите здесь описание изображения

Я пытался использовать FORCE INDEX FOR JOIN, чтобы попытаться заставить его индексировать дату, но, похоже, он не понял намека.

dwDimDate — это измерение даты с днями года, поэтому в этом сценарии я думаю, что было бы быстро ограничиться 365 днями, а затем найти все билеты в этом диапазоне дат. В этот промежуток времени должно быть около 5 тысяч билетов.

Любая помощь будет принята с благодарностью. Я не знаю, как выяснить, какую стратегию использовать для удаления ВСЕХ поисков. Я хотел бы понять, как это делать в будущем, поэтому, если вы поможете научить меня ловить рыбу, это будет здорово.

EDIT В настоящее время выполнение запроса занимает 11 секунд, что может стать проблемой в рабочей среде.


mysql sql-execution-plan explain
person hyphen    schedule 22.11.2017    source источник
comment
Вам действительно нужно LEFT?   -  person Rick James    schedule 20.01.2018
comment
Это неправильно сформировано - какие значения из tickets должны быть доставлены, когда есть более одного билета на данную дату??   -  person Rick James    schedule 20.01.2018

Ответы (3)


arrow_upward
3
arrow_downward 

ON DATE_FORMAT(t.ticket_date, '%Y%m%d') = d.date_key

Это никогда не будет использовать индекс, когда вы используете функцию в столбце t.ticket_date, подобную этой.

FORCE INDEX не превращает волшебным образом выражения, не подлежащие анализу, в выражения, допускающие анализ. Это просто намекает оптимизатору, что сканирование таблицы бесконечно дорого. Поэтому оптимизатор скажет: «Ну, это отстой для вас, потому что это выражение соединения должно выполнять сканирование таблицы».

Одним из решений было бы хранить t.ticket_date и d.date_key в общем формате. Используйте либо столбец DATE, либо строку «ГГГГммдд» для обоих вариантов.

Второе возможное решение: создать виртуальный столбец на основе t.ticket_date и проиндексировать виртуальный столбец.

ALTER TABLE tickets 
  ADD COLUMN ticket_date_yyyymmdd AS (DATE_FORMAT(ticket_date, '%Y%m%d'),
  ADD INDEX (ticket_date_yyyymmdd);
person Bill Karwin    schedule 22.11.2017
comment
отлично спасибо! Оглядываясь назад, это кажется очевидным, но я кое-чему научился. Я изменил свою таблицу измерения даты и добавил столбец в формате таблицы билетов, и теперь я сократился до 0,156 секунды! - person hyphen; 23.11.2017

arrow_upward
2
arrow_downward

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

В идеале вы должны убедиться, что ticket_date находится в формате, совместимом с date_key, чтобы вы могли просто выполнить простое сравнение или запрос диапазона. Если это абсолютно не вариант для вас, и вы используете относительно новую версию MySQL (5.7.8+) с InnoDB, вы можете создать виртуальный столбец и эффективно создать для этого функциональный индекс.

person Vinay Pai    schedule 22.11.2017

arrow_upward
0
arrow_downward

Это может быть ближе к действительному запросу и должно быть несколько быстрее, по крайней мере, в MySQL 5.6 или новее:

SELECT  *
    FROM  dwDimDate AS d
    LEFT JOIN  
        ( SELECT  MIN(ticket_id) AS one_tic_id,
                  COUNT(*) AS num_tickets,
                  DATE(ticket_date) AS date_key
            FROM  tickets t
            LEFT JOIN  sales s
               ON s.ticket_id = t.ticket_id
        ) AS ts USING (date_key)
    WHERE  d.date_key >= '2013-01-01'
      AND  d.date_key <  '2013-01-01' + INTERVAL 1 MONTH
    GROUP BY  d.date_key;
person Rick James    schedule 20.01.2018