MySQL оптимизирует запрос UNION

Я пытаюсь оптимизировать запрос.

Мой вопрос похож на MySQL, Union ALL и LIMIT, и ответ может быть то же самое (боюсь). Однако в моем случае есть более строгое ограничение (1), а также индекс в столбце даты и времени.

Итак, приступим:

Для простоты возьмем только одну таблицу с тремя: столбцами:

  • md5 (варчар)
  • значение (varchar).
  • последнее обновление (дата-время)

Есть индекс (md5, обновлено), поэтому выбор ключа md5, упорядочение по обновлению и ограничение до 1 будут оптимизированы.

Поиск должен вернуть максимум одну запись, соответствующую одному из 10 ключей md5. Ключи имеют приоритет. Таким образом, если есть запись с приоритетом 1, она будет предпочтительнее любой записи с приоритетом 2, 3 и т. д.

В настоящее время UNION ALL используется:

select * from

(

(
select 0 prio, value
from mytable
where md5 = '7b76e7c87e1e697d08300fd9058ed1db'
order by lastupdated desc 
limit 1
)

union all

(
select 1 prio, value
from mytable
where md5 = 'eb36cd1c563ffedc6adaf8b74c259723'
order by lastupdated desc 
limit 1
)

) x

order by prio
limit 1;

Это работает, но UNION, кажется, выполняет все 10 запросов, если предоставлено 10 ключей.

Однако с точки зрения бизнеса было бы нормально запускать выборки последовательно и останавливаться после первого совпадения.

Возможно ли это, хотя простой SQL?

Или единственным вариантом будет хранимая процедура?


mysql sql optimization union explain
person user1946784    schedule 03.01.2013    source источник
comment
Union ALL не использует индекс, поэтому я не думаю, что вы можете слишком много оптимизировать.   -  person jcho360    schedule 04.01.2013
comment
Как определяется приоритет?   -  person Charles Burns    schedule 04.01.2013
comment
Чарльз - спасибо. В настоящее время приоритет определяется самим приложением. Теоретически он динамичен, но на самом деле он более или менее статичен, поэтому он может перейти в базу данных (на самом деле см. один из моих комментариев ниже). Я попытаюсь поместить приоритет в базу данных и использовать индекс + лимит + порядок   -  person user1946784    schedule 04.01.2013

Ответы (3)


arrow_upward
0
arrow_downward

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

Групповой максимум

Упорядочить по FIELD()

person Gavin Towey    schedule 03.01.2013

arrow_upward
0
arrow_downward

Оптимизатор для UNION ALL никак не может понять, чем вы занимаетесь.

Я не знаю, сможете ли вы это сделать, но предположим, что у вас есть таблица md5prio со списком хеш-кодов, которые вы ищете. Например.

prio   md5
0      '7b76e7c87e1e697d08300fd9058ed1db'
1      'eb36cd1c563ffedc6adaf8b74c259723'
etc

in it.

Тогда ваш запрос может быть:

    select mytable.*
      from mytable
      join md5prio on mytable.md5 = md5prio.md5
  order by md5prio.prio, mytable.lastupdated desc
     limit 1

Это может сэкономить повторные запросы. Вам обязательно понадобится ваш индекс на mytable.md5. Я не уверен, поможет ли ваш составной индекс lastupdated; вам нужно будет попробовать.

person O. Jones    schedule 03.01.2013
comment
Олли - Спасибо - на самом деле дизайн таков, что приоритеты могут меняться во время выполнения, поэтому они предоставляются в запросе. - person user1946784; 04.01.2013
comment
Олли, возможно, ты прав. Таблицы, вероятно, можно реструктурировать так, чтобы у нас был приоритет где-то в базе данных с индексом, и приложение могло просто использовать индекс с лимитом + порядком без какой-либо магии UNION. Завтра посмотрим... - person user1946784; 04.01.2013

arrow_upward
0
arrow_downward

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

К сожалению, групповой максимум, на который ссылается Гэвин, будет создавать несколько строк, когда есть повторяющиеся значения lastupdated (по общему признанию, возможно, это не проблема в вашем случае).

На самом деле существует способ MySQL получить этот ответ, используя group_concat и substring_index:

select p.prio,
       substring_index(group_concat(mt.value order by mt.lastupdated desc), ',', 1)
from mytable mt join
     (select 0 as prio, '7b76e7c87e1e697d08300fd9058ed1db' as md5 union all
      select 1 as prio, 'eb36cd1c563ffedc6adaf8b74c259723' as md5 union all
      . . .
     ) p
     on mt.md5 = p.md5
person Gordon Linoff    schedule 03.01.2013
comment
Гордон - спасибо. Но все же каждый выбор в UNION будет выполняться отдельно, верно? Это то, чего я пытаюсь избежать, но, похоже, на UNION нельзя повлиять таким образом. - person user1946784; 04.01.2013
comment
@user1946784 . . . В первом примере с индексом каждый выбор выполняется отдельно, но очень эффективно. Таблица не читается; только одна запись из индекса и связанная с ней страница. Во втором примере либо mytable будет просканирован, либо ему потребуется индекс для md5. - person Gordon Linoff; 04.01.2013