Оптимизация сложного поискового запроса SQL

Я пытаюсь придумать простой, производительный запрос для следующей проблемы:

Допустим, есть несколько сущностей (элементов), каждый из которых имеет уникальный идентификатор. У сущностей переменный набор атрибутов (свойств), поэтому они вынесены в отдельную таблицу:

T_Items_Props
=======================
Item_ID  Prop_ID  Value
-----------------------
101      1        'abc'
101      2        '123'
102      1        'xyz'
102      2        '123'
102      3        '102'
...      ...      ...

Теперь я хочу найти элемент, который соответствует некоторым указанным критериям поиска, например:

<<Pseudo-SQL>>

SELECT Item_Id(s)
  FROM T_Items_Props
 WHERE Prop 1 = 'abc'
   AND Prop 2 = '123'
     ...
   AND Prop n = ...

Это было бы довольно легко, если бы у меня была такая таблица, как Items(Id, Prop_1, Prop_2, ..., Prop_n). Затем я мог бы сделать простой SELECT, где критерии поиска можно было бы просто (даже программно) вставить в пункт WHERE, но в этом случае мне пришлось бы сделать что-то вроде:

SELECT t1.Item_ID
  FROM T_Items_Props t1
     , T_Items_Props t2
     , ...
     , T_Items_Props tn -- (depending on how many properties to compare)
   AND t1.Item_ID = t2.Item_ID
   AND t1.Prop_ID = 1 AND t1.Value = 'abc'
   AND t2.Prop_ID = 2 AND t2.Value = '123'
     ...
   AND tn.Prop_ID = n AND tn.Value = ...

Есть ли лучший/более простой/быстрый способ сделать это?


sql optimization query-optimization sql-optimization
person marsze    schedule 13.06.2013    source источник
comment
Вот как вы запрашиваете базы данных EAV. Оптимизировать особо нечего.   -  person gbn    schedule 13.06.2013
comment
не уверен, что понимаю это, но я думаю, что вам нужен PIVOT   -  person Mzf    schedule 13.06.2013
comment
@Mzf Да, я пробовал. Работает довольно хорошо с точки зрения удобочитаемости и гибкости, но, к сожалению, намного медленнее.   -  person marsze    schedule 13.06.2013

Ответы (4)


arrow_upward
0
arrow_downward

Я предложил это в предыдущем посте о подобных запросах. У пользователя могло быть 2 критерия в один раз и пять критериев в другой, и ему нужен был простой способ построить команду SQL. Чтобы упростить необходимость добавления таблиц FROM и обновления предложения WHERE, вы можете упростить, выполнив соединения и поместив эти критерии прямо на уровень соединения... Таким образом, каждый критерий представляет собой собственный набор, добавленный к смеси.

SELECT 
      t1.Item_ID
   FROM 
      T_Items_Props t1

         JOIN T_Items_Props t2
            on t1.Item_ID = t2.Item_ID
           AND t2.Prop_ID = 2
           AND t2.Value = '123'

         JOIN T_Items_Props t3
            on t1.Item_ID = t3.Item_ID
           AND t3.Prop_ID = 6
           AND t3.Value = 'anything'

         JOIN T_Items_Props t4
            on t1.Item_ID = t4.Item_ID
           AND t4.Prop_ID = 15
           AND t4.Value = 'another value'
   WHERE
          t1.Prop_ID = 1
      AND t1.Value = 'abc'

Обратите внимание, что основной запрос всегда будет начинаться с минимума критериев свойства/значения "T1", но затем обратите внимание на предложения JOIN... они практически одинаковы, поэтому их очень легко реализовать с помощью цикла... Просто продолжайте псевдонимы T2, T3, T4... по мере необходимости. Это начнется с любых предметов, которые соответствуют критериям T1, но затем также потребуется найти все остальные.

person DRapp    schedule 13.06.2013
comment
Спасибо, что обратили мое внимание на это, вероятно, столь же производительное, но более легкое масштабируемое решение! - person marsze; 13.06.2013
comment
Это просто некоторые самосоединения. - person Gigamegs; 13.06.2013
comment
@Phpdna, на самом деле нет, это не так ... Первый экземпляр является квалификатором как минимум для ОДНОГО критерия ... Остальные объединяются по одному и тому же идентификатору, но ищут дополнительные критерии, иначе пользователь вместо этого начнет с верхней таблицы ITEM свойств at... Зачем пытаться запрашивать ВСЕ ЭЛЕМЕНТЫ, если вы знаете как минимум один критерий, а затем просто присоединитесь к этому для дополнительных критериев.. Соединения подходят для КАЖДОГО желаемого КРИТЕРИЯ... и если вы проголосовал против, я не считаю, что это было оправдано. - person DRapp; 13.06.2013
comment
@drapp: я не понимаю? В теле много самосоединений? Что в этом плохого? Я не проголосовал за первого квалификатора, может быть, я не прочитал его вопрос? - person Gigamegs; 13.06.2013

arrow_upward
2
arrow_downward

Чтобы сделать запрос более читабельным, вы можете сделать что-то вроде:

SELECT 
    t1.Item_ID
FROM 
    T_Items_Props t1
where convert(varchar(10), t1.Item_ID) + ';' + t1.Value in (
    '1;abc',
    '2;123',
    ...
)

ПРИМЕЧАНИЕ. Предполагается, что ваши идентификаторы не будут содержать более 10 цифр. Это также может замедлить ваш запрос из-за дополнительного преобразования типов и объединения строк.

person LuigiEdlCarno    schedule 13.06.2013
comment
Я искал способ сделать это быстрее, а не улучшить читаемость. Это на самом деле делает обратное. Кроме того, вы имеете в виду convert(varchar(10), t1.Prop_ID) + ...? Кроме того, хорошее решение! - person marsze; 13.06.2013

arrow_upward
1
arrow_downward

Вы можете подсчитать количество правильных реквизитов. Это не очень хорошо, если могут быть дубликаты. Например.:

Prop_ID = 1 AND Value = 'abc'
Prop_ID = 2 AND Value = '123'

и таблица будет выглядеть так:

T_Items_Props
=======================
Item_ID  Prop_ID  Value
-----------------------
101      1        'abc'
101      1        'abc'

тогда это было бы правдой, хотя и не должно.

Но если вы хотите попробовать, вот как:

SELECT nested.* FROM (
SELECT item_id, count(*) AS c FROM t_items_props
WHERE ((prop = 1 AND value = 'abc')
OR (prop = 2 AND value = '123')
... more rules here ...)
GROUP BY item_id) nested
WHERE nested.c > 2 ... number of rules ...
person marty bourque    schedule 13.06.2013
comment
я был бы очень заинтересован в результатах. marsze, не могли бы вы опубликовать некоторые результаты, если вы пробовали это? PS: указанная проблема, вероятно, может быть решена с помощью отдельной или другой группы. - person marty bourque; 13.06.2013
comment
Не должно быть дубликатов, потому что Item_ID + Prop_ID будет первичным ключом. Это работает примерно так же быстро, как и мое решение (хотя результаты сильно различаются). Я думаю, что его легче изменить, и использование такого подзапроса определенно быстрее, чем использование HAVING count(...), так что спасибо! - person marsze; 14.06.2013

arrow_upward
0
arrow_downward

Вы можете использовать оператор соединения вместе с фильтрацией или фасетным поиском. Это дает лучшую производительность, потому что вы можете ограничить пространство поиска. Вот хороший пример: Faceted Search (solr) vs Good old фильтрация через PHP?.

person Gigamegs    schedule 13.06.2013
comment
Спасибо, но я уже пробовал комбинацию из WHERE-условий с OR вместо AND и последующего добавления HAVING COUNT(IDs) < n, но это тоже намного медленнее! - person marsze; 13.06.2013
comment
Тогда вы пробовали конкатенацию, как предлагает Луиджи? ИМО, вы не можете так сильно улучшить соединения. Многое зависит от данных. - person Gigamegs; 13.06.2013