Новости
Документация
Download
Webboard
Поиск
FAQ/ЧаВо
Обратная связь




MySQL.RU - Webboard



Вернуться
Оптимизация запроса Select (sfairet) 10/12/2008 - 17:16:44
      Re: Оптимизация запроса Select (Pavel) 15/12/2008 - 12:16:01

> Original message text:
> From: sfairet - 10/12/2008 - 17:16:44
> Subject:Оптимизация запроса Select
> -----------------
> Есть запрос в базу. Суть запроса выдавать записи по темам. Одна запись из таблицы может
>
> принадлежать ко многим темам.
> Таблица stories - содержит сами записи, имеет текстовые поля и поле sid(INT), записей порядка
>
> 200
> Таблица stories_topics - привязка sid записи(поле news) к id темы(поле topic), записей примерно
>
> 400
> Таблица topics_big - обеспечивает древо тем и подтем(степень вложенности - 3). Поле big -
>
> родительская тема, small - подтема. Записей порядка 200.
>
> Смысл sql запроса выдать все записи из stories, которые находятся в теме с id переданным
>
> скриптом(в данном случае 4) или в любой дочерней теме, все дочерние темы записаны в таблице
>
> topics_big (максимальная степень вложенности - 3).
>
> Сам запрос:
>
> SELECT SQL_CALC_FOUND_ROWS * FROM stories WHERE sid IN (
> SELECT DISTINCT(s.sid) from stories as s
> INNER JOIN stories_topics as top ON (s.sid = top.news)
> INNER JOIN topics_big as big ON (top.topic = big.small)
> LEFT JOIN topics_big as big2 ON (big.big = big2.small)
> WHERE (big.big=4
> OR big.small=4
> OR big2.big=4
> OR big2.small=4)
> )
> ORDER BY anchor, sid DESC limit 0, 20;
>
>
> Ну и смысл поста в том, что запрос может отрабатывать и по одной минуте.
> Раньше вместо него было порядка 300 мелких запросов, пока над этим думал голову сломал - в sql
>
> не очень понимаю, но сейчас нагрузка на сервер возрасла и опять стало все тормозить.
>
> Буду рад любой подсказке!
>


From: Pavel - 15/12/2008 - 12:16:01
Subject:Оптимизация запроса Select
-----------------
Мдя...
Попытаюсь объяснить почему медленно работает.

JOIN объединяет таблицы по какому-то полю/полям. При этом в общем случае, если присоединить таблицу с числом строк N саму к себе - в результате получится таблица с N^2 строк. Если еще одну присоединить - получится N^3 и т.д.

т.е. из таблиц с числом строк порядка 100, ты получаешь таблицу размером порядка 100^4, которую потом нужно будет еще DISTINCT`ом обработать - неудивительно что запрос тормозит.

Сервер старается соптимизировать запрос, уменьшая число строк в соединяемых таблицах, до выполнения JOIN`а
т.е. если есть строка типа
JOIN topics_big as big ON ((top.topic=big.small)and(big.big=4))
то будет присоединена уже не topics_big целиком, а только те строки из нее, которые удовлетворяют условию (big.big=4), т.е. результат будет существенно меньше.

Теперь о том как нужно делать.
Запрос нужно писать начиная "снизу", при этом постараться максимально сократить число записей еще в самом начале.

Теперь о том как нужно делать. Не совсем понял структуру таблицы topics_big, поэтому приведу пример для следующей структуры:

Tree - таблица со следующими полями:
id - собственный ID
id_parent - ссылка на родительский топик
topic - ссылка на информацию по теме из другой таблицы

1. Выбираем интересующую тему:

SELECT * FROM Tree
WHERE Tree.id=4

РЕЗУЛЬТАТ: <TEMA4>


2. Выбираем дочерние темы, для :

SELECT * FROM Tree
LEFT JOIN Tree as Child1 ON ( Child1.id_parent=Tree.id )
WHERE Tree.id=4

РЕЗУЛЬТАТ: Tree Child1
<TEMA4> <TEMA4_1>
<TEMA4> <TEMA4_2>
<TEMA4> <TEMA4_3>
...

3. Выбираем дочерние темы, для следующего уровня вложенности:

SELECT * FROM Tree
LEFT JOIN Tree as Child1 ON ( Child1.id_parent=Tree.id )
LEFT JOIN Tree as Child2 ON ( Child1.id_parent=Tree.id )
WHERE Tree.id=4

РЕЗУЛЬТАТ: Tree Child1 Child2
<TEMA4> <TEMA4_1> <TEMA4_1_1>
<TEMA4> <TEMA4_1> <TEMA4_1_2>
<TEMA4> <TEMA4_2> <NULL>
<TEMA4> <TEMA4_3> <TEMA4_3_1>
<TEMA4> <TEMA4_3> <TEMA4_3_2>
...

4. Выбираем топик для тем тем второго уровня вложенности:

SELECT Child2.topic FROM Tree
LEFT JOIN Tree as Child1 ON ( Child1.id_parent=Tree.id )
LEFT JOIN Tree as Child2 ON ( Child1.id_parent=Tree.id )
WHERE Tree.id=4

РЕЗУЛЬТАТ: <TEMA4_1_1.topic>
<TEMA4_1_2.topic>
<NULL>
<TEMA4_3_1.topic>
<TEMA4_3_2.topic>
...

5. Добавляем топики первого уровня вложенности

(
SELECT Child1.topic as topic FROM Tree
LEFT JOIN Tree as Child1 ON ( Child1.id_parent=Tree.id )
WHERE Tree.id=4
)
UNION ALL
(
SELECT Child2.topic as topic FROM Tree
LEFT JOIN Tree as Child1 ON ( Child1.id_parent=Tree.id )
LEFT JOIN Tree as Child2 ON ( Child1.id_parent=Tree.id )
WHERE Tree.id=4
)

РЕЗУЛЬТАТ: <TEMA4_1.topic>
<TEMA4_2.topic>
<TEMA4_3.topic>
<TEMA4_1_1.topic>
<TEMA4_1_2.topic>
<NULL>
<TEMA4_3_1.topic>
<TEMA4_3_2.topic>
...

6. Добавляем самый первый уровень вложенности

(
SELECT Tree.topic as topic FROM Tree
WHERE Tree.id=4
)
UNION ALL
(
SELECT Child1.topic as topic FROM Tree
LEFT JOIN Tree as Child1 ON ( Child1.id_parent=Tree.id )
WHERE Tree.id=4
)
UNION ALL
(
SELECT Child2.topic as topic FROM Tree
LEFT JOIN Tree as Child1 ON ( Child1.id_parent=Tree.id )
LEFT JOIN Tree as Child2 ON ( Child1.id_parent=Tree.id )
WHERE Tree.id=4
)

РЕЗУЛЬТАТ: <TEMA4.topic>
<TEMA4_1.topic>
<TEMA4_2.topic>
<TEMA4_3.topic>
<TEMA4_1_1.topic>
<TEMA4_1_2.topic>
<NULL>
<TEMA4_3_1.topic>
<TEMA4_3_2.topic>
...

Теоретически, здесь уже не должно быть повторений, но на всякий можно сделать DISTINCT и убрать NULL`ы

SELECT DISTINCT(topic) FROM
(...)
WHERE topic IS NOT NULL

А уж теперь, используя список топиков, можно выбирать sid из stories_topics и далее сам текст из stories


P.S. Вообще-то не всегда следует стремиться до конца нормализовать таблицы - это усложняет базу и далеко не всегда ускоряет ее.
В твоем случае гораздо оптимальнее было бы использовать не дерево а список:

List - таблица со следующими полями:
id_0 - верхний уровень
id_1 - 2-й уровень
id_2 - 3-й уровень
topic - ссылка на информацию по теме из другой таблицы

тогда
4 NULL NULL topic = <ТEMA4>
4 401 NULL topic = <ТEMA4_1>
4 402 40201 topic = <ТEMA4_2_1>
и т.д.

все запросы в этом случае становятся проще:

SELECT * FROM List WHERE id_0 = 4 - Тема4 со всеми подтемамим
SELECT * FROM List WHERE id_1 = 401 - Тема4_1 с подтемамим
SELECT * FROM List WHERE ((id_0=N)OR(id_1=N)OR(id_2=N)) - тема любого уровня с ID=N и всеми подтемами.


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


P.P.S Родной Формат для реляционных БД - список (все таблицы - по сути списки).
Так зачем же туда дерево пихать когда без него гораздо лучше все выходит?!!




[Это сообщение - спам!]

Последние сообщения из форума

Уважаемые посетители форума MySQL.RU!
Убедительная просьба, прежде чем задавать свой вопрос в этом форуме, обратите внимание на разделы:
- ответы на наиболее часто задаваемые вопросы - FAQ
- раздел документация
- раздел поиск по сообщениям форума и документации
Также, старайтесь наиболее подробно указывать свою ситуацию (версию операционной системы, версию MySQL, версию программного обеспечения, по которому возникает вопрос, текст возникающих ошибок, и др.)
Помните, чем конкретнее Вы опишете ситуацию, тем больше шансов получить реальную помощь.
 Имя:
 E-mail:
 Тема:
 Текст:
Код подтверждения отправки: Code
34715



РЕКЛАМА НА САЙТЕ
  Создание сайтов | |