Блог

У меня есть этот запрос, с которым мне нужна помощь. Итак, есть таблица insertjobticket , вызываемая с помощью столбца DEL , который представляет собой длинное символьное поле, в котором может быть несколько дат. Мне нужно создать выходную таблицу, которая содержит одну строку для каждого времени, когда в DEL поле есть дата для определенного диапазона дат.

Причина, по которой я не могу просто сделать более простой select ... where ... DEL like "%my_date%" , заключается в том, что DEL столбец может содержать несколько дат, и если это так, мне нужно вернуть несколько строк в выходной набор, по одной строке для каждой даты, которая появляется в DEL столбце.

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

 create temporary table jobtrack.ship_helpert3 as 
select * from 
(
    (
    select  
        date_format(now() - interval 3 day, '%m/%d/%Y') as `Ship_Date`,
        more_columns 
    from   
        jobticket.insertjobticket 
    where         
        DEL like concat('%',date_format(now() - interval 3 day, '%m/%d/%Y'),'%')
    ) union (
    select  
        date_format(now()   interval 2 day, '%m/%d/%Y') as `Ship_Date`,
        more_columns 
    from   
        jobticket.insertjobticket 
    where         
        DEL like concat('%',date_format(now()   interval 2 day, '%m/%d/%Y'),'%')
    ) union (
    select  
        date_format(now()   interval 1 day, '%m/%d/%Y') as `Ship_Date`, 
        more_columns 
    from   
        jobticket.insertjobticket 
    where         
        DEL like concat('%',date_format(now()   interval 1 day, '%m/%d/%Y'),'%')
    ) union ...
) t;
  

Каждый select запрос проверяет, есть ли какие-либо строки с определенной строкой даты ( date_format(now() interval @x day, '%m/%d/%Y') ) в DEL поле. Запрос создается программно и может быть очень длинным, так как я хотел бы иметь возможность проверять запрос для многих дат.

insertjobticket Таблица содержит 40 тыс. строк и растет, поэтому выполнение приведенного выше запроса занимает слишком много времени. Я понимаю, почему это занимает так много времени, потому что каждый union фактически должен создавать свой собственный подзапрос, который сканирует всю таблицу снова и снова для каждой даты. Я просто не знаю, как сделать эту работу более эффективной.

Кто-нибудь знает, как ускорить этот запрос?

Спасибо за помощь и дайте мне знать, если нам нужны дополнительные разъяснения.

Как уже говорилось в комментариях, единственным правильным решением было бы нормализовать ваши данные, то есть создать новую таблицу с одной датой доставки и первичным ключом insertjobticket для каждой строки, и позволить приложению использовать эту таблицу напрямую вместо столбца del или, по крайней мере, косвенно с помощью триггера, который обновляет этотаблица обновляется каждый раз, когда столбец DEL обновляется.

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

 select 
  del_dates.Ship_Date, 
  othercolumns 
from insertjobticket
join (
    select concat(date_format(now()   interval 2 day, '%m/%d/%Y')) 
           collate utf8_general_ci as Ship_Date 
    union select concat(date_format(now()   interval 1 day, '%m/%d/%Y')) 
    union select concat(date_format(now()   interval -15 day, '%m/%d/%Y')) 
    ...  
) del_dates
on insertjobticket.del like concat('%', del_dates.Ship_Date, '%');
  

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

Это в основном будет выполнять необходимый шаг нормализации (для запрошенных дат) каждый раз, когда вы выполняете запрос, и не сможет использовать индексы. Просто убедитесь, что ваш explain вывод отображается using join buffer для производной таблицы, а не для insertjobticket , в противном случае замените join на a straight_join .

Для 40 тыс. строк это может быть не большой проблемой, и в любом случае другого пути нет, кроме реальной нормализации. Имейте в виду, что ваш запрос будет линейно замедляться с количеством строк (400 тыс. строк будут занимать примерно в 10 раз больше времени, чем 40 тыс.), что предотвратит эффект индексов. Поэтому, если он слишком медленный сейчас (или иногда в будущем), вам в конечном итоге придется его нормализовать (или, в качестве обходного пути для проблем, создаваемых этим обходным путем, добавьте столбец, чтобы пометить старые записи и исключить их из условия соединения).

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

При использовании базы данных вы должны учитывать возможное использование хранимых данных.

В этом случае вам нужно было выполнить синтаксический DEL анализ, когда вы собирались его сохранить, и создать другую таблицу из пар дат (похороненных в DEL) и идентификаторов (insertjobticket).

Попытка выполнить синтаксический анализ после факта намного медленнее и приводит к проблемам масштабирования.