Сучасна диджитал-освіта для дітей — безоплатне заняття в GoITeens ×
Mazda CX 5
×

Как сделать теги правильно? НЕ облако!

Підписуйтеся на Telegram-канал «DOU #tech», щоб не пропустити нові технічні статті

Как спроектировать бд для тегов? Какой запрос?
Я сделал, таблицу тегов + таблицу тег id и id запись. Плохое решение? С запросом проблемы. При пагинации как выбрать одним запросом все статьи и нужные теги? Без тегов такой запрос.

>select article.title, article.content, article.dates, authors.name, cathegory.name as cathegory from article, authors, cathegory where article.id_author = authors.id and cathegory.id = article.id_cathegory limit  20 order_by article.id desc;
👍ПодобаєтьсяСподобалось0
До обраногоВ обраному0
LinkedIn
Дозволені теги: blockquote, a, pre, code, ul, ol, li, b, i, del.
Ctrl + Enter
Дозволені теги: blockquote, a, pre, code, ul, ol, li, b, i, del.
Ctrl + Enter

Коментар порушує правила спільноти і видалений модераторами.

Коментар порушує правила спільноти і видалений модераторами.

общая идея такая: не используйте join

для этого условия и пагинация делается над одной таблицей — articles, т.е. без JOIN-a.

После получения набора этих записей (их будет не больше 10, где 10 — кол-во записей на странице при пагинации), далее идем в остальные справочные таблицы (категории, теги, авторы) и вытягиваем нужные данные.

ниже подробности:

— первый запрос
select ... from articles
where __условие_поля_таблицы_articles__

order by .. limit

данный запрос вернет 10 нужных статей

— делаем запросы на получение тегов, авторов и категорий для полученных статей

— чтобы получить категории:

получаем массив category_id этих статей (в коде, не в SQL) — просто пройтись в коде по массиву статей.

select .. from cathegory where id IN ( массив этих cathegory_id)

— для тегов чуть сложнее

получаем список id статей из массива статей — аналогично как для категорий — в коде.

select ... from tags

where id IN (select tag_id FROM tags_articles WHERE article_id IN (список id статей) )

тут join можно сделать, потому что структура запроса другая.

** если у вас условия более сложные и не только на поля из таблицы статей, то данную идею тоже можно модифицировать, чтобы не было join..

чем плохи join-ы?

вот рабочий запрос

select tag from tags where id in (select id_tag from tagarticle where id_article in ((тут array) ));
правильно ? при таком запросе, выводит все теги для всех статей. методами sql конвертировать для каждой статьи в один кортеж нужные теги, нельзя?

вот мой вариант решения с join.

$this->data[’query’] = $this->db
->select(’article.id, article.title, article.content, article.dates,
authors.name, cathegory.name as cathegory’)
->from(’article left join authors on article.id_author = authors.id 
left join cathegory on article.id_cathegory = cathegory.id’)
->order_by(’article.id’, ’desc’)  
->limit(20, $url_segment)
->get ();
foreach ($this->data[’query’]->result() as $value)
{
try
{
$value->tags = $this->getTags($value->id);

} catch (UnexpectedValueException $exc)
{
echo $exc->getMessage();
}
}
private function getTags($num)
{
if(is_numeric($num))
{
$tags_query = $this->db
->select(’tag’)
->from(’tags join tagarticle on tags.id = tagarticle.id_tag and tagarticle.id_article =’ . $num)
->get();
$tags  =  ’’;
foreach ($tags_query->result() as $value)
{
$tags .= ’, ’ . $value->tag; 
}
return $tags;
} else
{
throw new UnexpectedValueException(’not numeric, Ops!’);
}
}

в сумме два цикла от 40 до 180 итераций и 21 запрос. как можно улучшить решение?

21 запрос??? вы шутите?

не делайте в цикле запросы, как у вас

foreach ($this->data[’query’]->result() as $value)
{
$value->tags = $this->getTags($value->id); — тут подразумевается запрос к базе?

}

см. выше — я написал решение, как сделать

— проблем с join нет, если условия в WHERE используются
т.е. этот запрос вполне нормальный

select tag from tags where id in (select id_tag from tagarticle where id_article in ((тут array) ));

а какие проблемы возникают, если использовать ansi join?

select tag, id_article from tags left join tagarticle on tags.id = id_tag where tags.id in (select id_tag from tagarticle where id_article in (142, 141)) and id_article in (142, 141);

работает!

select .. from cathegory where id IN ( массив этих cathegory_id)

практически хрестоматийный пример как делать не стоит. это касается всех этих IN.
чем плохи джойны?

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

Т.е. ИН-ы нельзя использовать потому что реляционные базы данных называются реляционными? Гениально. Хорошо хоть что не потому что гладиолус.

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

человек как-то писал что-то похожее. оно как-то работало. он решил поделиться реализацией. получилось что-то вроде вредных советов

Я не понял почему ин-ы это обязательно последовательное сканирование?

вы просто посмотрите план запроса.

Конкретный план запроса ни о чем не говорит, так как может варьироваться от базы к базе и даже в рамках одной и той же БД в зависимости от собранной статистики. Какие конкретные ограничения добавляют ИН-ы, которые исключают index scan?

вы уже посмотрели план запроса? :)

для того, чтобы был индекс скан нужен индекс.

Я план запроса не собиртаюсь смотреть по описанным мной причинам. Ну и если у тебя индекса нет то как джойн вдруг сделает индекс скан?

так в том то и дело, что в таблице есть индекс, а вот в выборке из этой таблицы уже не будет индекса.

Ну точно такая же ситуация будет и с джойном. Ин-ы вообще если ты не в курсе разворачиваются оптимизатором в джойны в нормальных базах и работают абсолютно одинаково

да не будет такой же ситуации.

ин и джойн не всегда дает одинаковый результат

если в ине повторяются значения.

Шота я неосилил понять какая там разница будет. Не затруднит ли уважаемого джентельмена все таки привести пример?

ну вот смотрите, если выборка в IN будет вроде этой (1,1,3), а в таблице будут значения 1,1,4,5 — то IN вернет 2 строки, а JOIN 4

Очевидно что такой in развернется в join с ключевым словом distinct в select clause

и выйдет еще 3 выборка. отлично

Выйдет абсолютно тоже самое если бы ты решил написать такой же джойн руками

оно слепит дублирующиеся строки из таблицы.
вот для примера в таблице строки 2, А; 2; А, а в выборке 2, 2

в результате запрос с IN вернет вам 2 строки, запрос с джойном 4, запрос с дистинктом и джойном 1 строку

Ну вот в твоем последнем примере оптимизатор развернет IN в операцию называемую semi-join, которая хоть не имеет выражения в SQL-e но имеет место быть внутри во всех распространенных БД(mysql, postgre, ms sql, oracle, etc)

причем тут сравнение IN и JOIN ? не в этом суть.
просьба прочесть идею решения до конца.

см. коммент выше.

Ну да, производительность in vs join это офтопик.

да не будет такой же ситуации.

ин и джойн не всегда дает одинаковый результат

так и есть, разные связи бывают.
Например, у каждой статьи может быть теги, могут не быть.

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

но ведь речь сейчас не об этом.

речь сейчас о конкретной структуре БД и как будет движок БД выполнять IN по сравнению с JOIN.

Коментар порушує правила спільноти і видалений модераторами.

Ну точно такая же ситуация будет и с джойном. Ин-ы вообще если ты не в курсе разворачиваются оптимизатором в джойны в нормальных базах и работают абсолютно одинаково

так точно!

а я вот посмотрел план запроса.

и проверил все эти запросы в SQL Server, где он не обманывает и более правильно выбирает индексы.
так вот, планы запроса ОДИНАКОВЫЕ.

и тут двух мнений быть не может.

вы неправильно поняли идею. идея не в том, чтобы заменить JOIN на IN.

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

а не делать в одном запросе кучу JOIN и там делать кучу условий и пагинацию.

а почему вы так упорно не хотите всё это сделать на уровне базы данных за один запрос?

я постарался объъяснить в этом комменте

dou.ua/...ic/6067/#228675

еще раз повторю, что идея не в том, чтобы просто взять JOIN и заменить на IN.

суть в другом.

человек как-то писал что-то похожее. оно как-то работало. он решил поделиться реализацией. получилось что-то вроде вредных советов

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

Моя реализация требует вместо одного большого JOIN к 4 таблицам (а может и больше), заменить на 4 отдельных запроса. С одной стороны 4 запроса медленнее, чем 1, но с другой стороны это решение имеет свои плюсы. А в случаях со сложными условиями, решение с одним большим JOIN — просто будет тормозить.

А в случаях со сложными условиями, решение с одним большим JOIN — просто будет тормозить.

то-есть вы всех обхитрили. сделали тот же джойн, только на стороне php и всё вдруг заработало быстрее?

Коментар порушує правила спільноти і видалений модераторами.

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

— движок БД джойнит две таблицы (одна из которых огромная), .

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

полученные отфильтрованные результаты будут достаточно большими

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

то-есть вы хотите сказать, что вы нашли лучшее решение, чем архитекторы СУБД?

мое решение основано на использовании средств СУБД.

но любой инструмент (СУБД) нужно понимать как он работает и как использовать.

например, как вы будете делать следующий запрос:
получить список статей с заданными авторами (например, у которых рейтинг = 5, либо начинающиеся на букву A%) + условия на сами статьи

(например, за 2011 год) + пагинация (по дате публикации).

отчего возникли системы NOSQL ? они что нашли более лучше архитектуру, чем реляционные СУБД? нет конечно, они сделали архитектуру, оптимизированную под конкретные задачи..

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

конечно же джойн на стороне сервера БД работает быстрее, чем в коде.

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

например,
запрос, чтобы получить список статей с заданными авторами (например, у которых рейтинг = 5, либо начинающиеся на букву A%) + условия на сами статьи

(например, за 2011 год) + пагинация (по дате публикации).

В этом случае обычный джойн будет тормозить!!!
потому что доступ к данным будет примерно следующий:
— движок БД будет джойнить два достаточно больших набора данных.
с одной стороны набор авторов отфильтрованный по рейтингу = 5 (может быть достаточно большим),
с другой стороны набор статей за 2011 год тоже достаточно большой.

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

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

— и уже дальше джойнить эти 10 статей с другими связанными таблицами.

Стараются максимально сократить объем данным, с которым работают, на первом этапе.

Конечно, для этого нужно сделать денормализацию.

Также когда нужно делать джойны кучи таблиц (скажем 7-10 таблиц), то возникают много мелких вопросов:
— сделать LEFT JOIN, чтобы не выпали статьи

— дублирование записей (например, если к статьям приджойнить теги, когда у одной статьи будет много тегов)

Если ваш слой доступа к БД все это умеет делать — то не проблема.
Но мне удобнее сначала сделать один запрос к статьям, получить эти 10 статей,

а дальше уже получить все справочные данные для этих статей. И такое решение будет более масштабируемым.

Но в достаточно простом примере, который привел ТС, все эти мои аргументы не имеют смысла.

в этом комменте я привел другие нюансы:

dou.ua/...ic/6067/#228675

да не обманывайте вы себя. субд в разы лучше работают с данными, чем какой-то пхп. и будет работать медленней только в том случае, если вы плохо напишете запрос

т.е. данный запрос

запрос, чтобы получить список статей с заданными авторами (например, у которых рейтинг = 5, либо начинающиеся на букву A%) + условия на сами статьи

(например, за 2011 год) + пагинация (по дате публикации).

вы бы делали обычным JOIN и ничего бы не меняли в структуре БД ?

select .. where id IN (select ..)

РАВНОСИЛЬНО (план выполнения запросов будет идентичен)

select ... FROM ..
JOIN .. ON id = ..

нет. равносильно при небольшой выборке. при большой разница просто колосальна

нет же. учите теорию и проверяйте ее на практике.

Я теорию знаю отлично, и с практикой у меня Ок. Сам учи и проверяй.

ну тогда наверное стоит прекратить этот спор

Эти запросы _семантически_ не эквивалентны — они возвращают разные результаты. Говорить об одинаковых планах выполнения для двух семантически разных запросов бессмысленно — они не могут быть одинаковыми.

Я могу допустить, что в каких-то СУБД планы могут _отображаться_ одинаково, но работают в любом случае по-разному. В Oracle они и отображаться будут различно: без доп. фильтров будет, например, hash join для варианта с join и hash join semi для варианта с in.

Так спор о том что человек(Дмитрий Рябов) доказывает что in это всегда тормоз и full scan, а ему говорят что in разворачивается в джойн на самом деле, и никаких принципиальных различий с обычным джойном нету, естественно кроме дедуплицакии строк в semi join, из-за которого in как раз работает быстрее.

сейчас попробовал различные варианты.
я реально не прав. в sql 2008 r2 действительно план с EXISTS и с IN один в один даже при количестве строк 100 000+
просто неоднократно меня предостерегали от использования IN и предлагали использовать EXISTS вместо этого.

В Informix даже последних версиях IN работает жутко, а в нормальных субд видимо решили эту проблему вместо того, чтобы ограничивать разработчиков

спасибо за понимание :)

в курсе Microsoft по SQL Server, там так и говорят, что порядок доступа к данным, когда делается подзапрос с IN и когда делается JOIN, — будет одинаковым.
и эту «проблему» решили лет так 10 назад еще в SQL Server 7.

в MySQL же долгое время вообще не было подзапросов..

насчет сравнения EXISTS и IN, там, наверное, про другое — что EXISTS найдет одну запись и остановится, сразу вернув true, а для IN он будет получать все записи в подзапросе и потом проверять. Поэтому запросы на проверку существования записей в связанной таблице лучше делать с помощью EXISTS, а не IN .. и тем более не COUNT(..) >0. но это совсем другая история..

Тогда объясню с самого начала, откуда возникло это решение.

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

DB Structure:

articles: id, title, content, .., author_id, category_id, pub_date (дата публикации), rating, etc.

(могут быть еще другие вторичные ключи)

authors: id, .. (обычные справочные поля, нужные для отображения информации о статье)

categories: id, title, ... (куча справочных полей)

Теги: (связь со статьями many-to-many)

tagarticle: tag_id, article_id

tags: id, title

При выполнении запроса для получения списка статей возникают такие задачки:
— фильтр (условия на столбцы в таблице articles, либо (!) условия на другие столбцы в других таблицах)
— сортировка (обычно по столбцу в articles, но иногда сортировка может быть сложнее)
— пагинация (пагинация связана с сортировка).
В данных примерах предполагается, что сортировка будет по столбцу в articles.
— выборка разных полей из разных таблиц.
Некоторые поля могут иметь одно значение для заданной записи статьи (название категории = categories.title, имя автора)

а некоторые «поля» могут иметь набор значений. Например, список тегов для заданной статьи.

Сразу скажу основную идею, когда избегают JOIN.

JOIN лучше не делать, когда используют условия WHERE над столбцами из небазовой таблицы.

1. Условия над столбцами articles:

1a

select ..(* столбцы из articles *), (* столбцы из других таблиц, связанных с базовой 1-к-1 *)
FROM articles
JOIN authors ON ..
JOIN categories ON ..
WHERE
pub_date .. (дата за последнюю неделю)
AND rating > 4 (рейтинг достаточно большой)

и т.д.

ORDER BY pub_date DESC

LIMIT ..

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

Лучше (быстрее), чтобы это было ОДНИМ запросом, как и показано выше.

1b.

Можно вместо этого запроса сделать сначала первый запрос над таблицей articles

select ..(* столбцы из articles *)
FROM articles
WHERE
pub_date .. (дата за последнюю неделю)
AND rating > 4 (рейтинг достаточно большой)

и т.д.

ORDER BY pub_date DESC

LIMIT ..

Получить 10 нужных статей.

Далее идем в другие таблицы и забираем оттуда справочные данные.

Т.е. будет еще по ОДНОМУ запросу в каждую таблицу (authors, categories).

SELECT **
FROM authors

WHERE id IN (id авторов из найденных раннее статей, будет не больше 10)

* тут не важно, что номера id в IN будут повторяться. Уверен, что движок БД достаточно грамотный, чтобы не делать лишнюю работу.

SELECT **
FROM categories

WHERE id IN (id категорий)

Разница между 1a и 1b — минимальная.
Вариант 1a с одним запросом будет быстрее работать, т.к. все это делается движок БД, а не в коде.
Потому что движок БД выполняя JOIN по сути будет делать то же самое, что и в решении b,
т.е. сначала выполнит условия над articles, отберет 10 записей, а потом уже пойдет в другие связанные таблицы.
Но т.к. это все делается на уровне БД, то скорее всего движок БД это сделает более эффективно, чем мы в коде,

т.к. БД как раз и предназначена для подобных манипуляций с данными.

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

В решении b, данные более удобны для дальнейшего использования, т.к. собраны вокруг статьи.

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

Когда делается JOIN конечный результат будет в таком виде:

article.id | article. title | ...другие столбцы articles | author.name | другие столбцы authors | ... | столбцы из categories| и еще куча столбцов

1 hello world | ... | Jack London | ...

2 вторая статья | ... | Jack London | ...

т.е. может дублироваться очень много текстовых данных (один и тот же автор). И эти данные перегоняются из БД в код.

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

article.id | article. title | ...другие столбцы articles | tag.id | tag.name | и еще куча столбцов

1 hello world | ... | 1 | tag1
1 hello world | ... | 2 | tag2
1 hello world | ... | 5 | tag5

1 hello world | ... | 10 | tag10

2 вторая статья | ... | 1 | tag1

2 вторая статья | ... | 5 | tag5

В случае 1b такой проблемы нет. Из БД перегоняются только сами данные из самих таблиц.

Но может понадобиться сделать чуть больше работы в коде, чтобы собрать это все в единую структуру, удобную для отображения.

3. Условия над столбцами из других связанных таблиц.

Например, выбрать все статьи, у которых имя автора начинается на букву A, либо рейтинг автора = 5.

тут разница между решением 1a с JOIN и 1b c IN — огромная!
В данном случае решение JOIN не получится заменить на IN.
НО! Делать такой JOIN не рекомендуется.

Лучше сделать денормализацию, чтобы все-таки условия были над столбцами из базовых таблицы.

ИДЕЯ!

Идея заключается в том, чтобы избегать такие JOIN с условиями над связанными таблицами (т.е. небазовой таблицей).

теги и категории лучше гонять через что-то более скоростное — sphinx или antlr

Підписатись на коментарі