Оптимизация запроса (MySQL)

← →
Piter © (2006-08-08 22:32) [0]

Очень интересно, почему такой запрос не нехилой машине выполняется порядка 40 секунд:

SELECT p.id, p.name, p.title, p.added_date, p.added_time, ca.name AS category_name, COUNT(f.publication_id) AS count FROM cb_publications AS p LEFT JOIN cb_categories AS ca ON (p.category_id = ca.id) LEFT JOIN cb_favorites AS f ON (p.id = f.publication_id) GROUP BY p.id HAVING count > 0 ORDER BY count DESC LIMIT 10;

Записей в cb_publications: 7644
Записей в cb_categories: 9
Записей в cb_favorites: 5257

База MySQL.

CREATE TABLE IF NOT EXISTS `cb_publications` ( `id` int(10) unsigned NOT NULL auto_increment, `category_id` int(10) unsigned NOT NULL default "0", `rubric_id` int(10) unsigned NOT NULL default "0", `user_id` int(10) unsigned NOT NULL default "0", `name` varchar(255) NOT NULL default "", `unixtime` int(10) unsigned NOT NULL default "0", `added_date` date NOT NULL default "0000-00-00", `added_time` time NOT NULL default "00:00:00", `activated_unixtime` int(10) unsigned NOT NULL default "0", `is_active` tinyint(3) unsigned NOT NULL default "0", `is_moderated` tinyint(3) unsigned NOT NULL default "0", `front_page` tinyint(3) unsigned NOT NULL default "0", `title` varchar(100) NOT NULL default "", `content` text NOT NULL, `poll_question` varchar(255) NOT NULL default "", `source` varchar(255) NOT NULL default "", `source_url` varchar(255) NOT NULL default "", `views` int(10) unsigned NOT NULL default "0", `rating` float unsigned NOT NULL default "0", `rating_votes` int(10) unsigned NOT NULL default "0", `comments_count` int(10) unsigned NOT NULL default "0", `keywords` varchar(255) NOT NULL default "", `allow_comments` tinyint(3) unsigned NOT NULL default "1", PRIMARY KEY (`id`), KEY `front_page` (`front_page`), KEY `is_active` (`is_active`), KEY `is_moderated` (`is_moderated`), KEY `category_id` (`category_id`), KEY `rubric_id` (`rubric_id`), KEY `user_id` (`user_id`), KEY `keywords` (`keywords`), KEY `name` (`name`,`added_date`) ) TYPE=MyISAM AUTO_INCREMENT=1226 ;

CREATE TABLE IF NOT EXISTS `cb_categories` ( `id` int(10) unsigned NOT NULL auto_increment, `position` smallint(5) unsigned NOT NULL default "0", `name` varchar(255) NOT NULL default "", `title` varchar(100) NOT NULL default "", `description` text NOT NULL, `adv_title` varchar(100) NOT NULL default "", `adv_text` text NOT NULL, `adv_show` tinyint(3) unsigned NOT NULL default "0", PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) ) TYPE=MyISAM AUTO_INCREMENT=16 ;

CREATE TABLE IF NOT EXISTS `cb_favorites` ( `user_id` int(10) unsigned NOT NULL default "0", `publication_id` int(10) unsigned NOT NULL default "0", `unixtime` int(10) unsigned NOT NULL default "0", PRIMARY KEY (`user_id`,`publication_id`) ) TYPE=MyISAM;

← →
Johnmen © (2006-08-09 00:52) [1]

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

← →
Piter © (2006-08-09 01:02) [2]

Johnmen © (09.08.06 0:52) [1]

и это при десятке тысяч записей? Ничего себе... Ведь есть таблицы по миллиону записей!

HAVING count > 0 ORDER BY count DESC LIMIT 10;

поскольку есть точно 10 записей с count > 10, то значит HAVING можно убрать. Это заметно может ускорить запрос?

Как можно еще оптимизировать?

Johnmen © (09.08.06 0:52) [1]
А вот лимит - это ерунда

да просто больше 10 записей не нужно, это статистика - топ.

← →
Johnmen © (2006-08-09 01:12) [3]

> Piter © (09.08.06 01:02) [2]
> поскольку есть точно 10 записей с count > 10, то значит HAVING можно убрать.

Не понял!? У тебя же count>0 ограничение!

Ускорит правильное использование группировки. Т.е. в ней участвуют все неагрегируемые поля запроса. Но, видимо, результат будет не тот, который ожидается :)
Но самые тормоза - вот такая сортировка...

← →
Piter © (2006-08-09 02:26) [4]

Johnmen © (09.08.06 1:12) [3]
Не понял!? У тебя же count>0 ограничение!

я в SQL очень слаб. Но объясню на пальцах, что нужно - так думаю будет понятнее, чем абстрактные таблицы.

Итак, есть таблица cb_publications - хранит информацию о публикациях (текст публикации, дату, ID автора публикации, заголовок и все такое прочее).

Публикацию пользователи могут добавлять в свой профиль, в избранное. Хранится это уже в таблице: cb_favorites, состоящей из USER_ID пользователя и publication_id заметки. Что соответствует тому, что у такого-то пользователя такая-то публикация в закладках.

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

Посмотреть это можно тут: http://ss.sovietservers.com/dc/statistics.php (страница может открываться долго, около минуты). Смотри там таблицу: "Рейтинг публикаций по количеству закладок"

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

Поэтому HAVING вроде как можно убрать?

Как еще ускорить этот запрос?

← →
Sergey13 © (2006-08-09 08:18) [5]

Я бы JOIN-ы убрал, ибо все равно 0 отсекаются HAVING-ом

SELECT p.id, p.name, p.title, p.added_date, p.added_time,
ca.name AS category_name,
COUNT(f.publication_id) AS p_count
FROM cb_publications AS p, cb_categories AS ca, cb_favorites AS f
where p.category_id = ca.id and p.id = f.publication_id
GROUP BY p.id, p.name, p.title, p.added_date, p.added_time, ca.name
ORDER BY p_count DESC
LIMIT 10;

← →
evvcom © (2006-08-09 08:55) [6]

Кроме того, количество считается по фаворитам, сортируется и по нему же отсекается (LIMIT). Так какой смысл джойнить cb_publications (7644) и cb_favorites (5257), а потом оставлять только 10 записей, если можно сразу из фаворитов отобрать 10 записей и уже их сджойнить с публикациями?! Это существенное сокращение потерь времени.

← →
Sam Stone © (2006-08-09 10:57) [7]

Индексы сделать?..

← →
evvcom © (2006-08-09 11:25) [8]

> [7] Sam Stone © (09.08.06 10:57)

Панацея?
Не знаю, как MySQL с ними работает, но большинство советов типа "сделай индекс по полю такому-то" эффекта в оракле с данным запросом иметь не будут. Тогда как сокращение количества записей перед джойном актуально для любого сервера.

← →
Desdechado © (2006-08-09 12:04) [9]

> "сделай индекс по полю такому-то" эффекта в оракле с данным
> запросом иметь не будут.
Если по внешним ключам сделать, то еще как будут использоваться.
Не знаю, как в MySQL, а в Оракле по FK индексы автоматически не строятся. А в соединениях таблиц по FK очень полезны.

← →
Sergey13 © (2006-08-09 12:06) [10]

> А в соединениях таблиц по FK очень полезны.

Не то, что полезны, а необходимы. Без них идет блокирование дочерних таблиц целиком.

← →
Desdechado © (2006-08-09 12:16) [11]

> Без них идет блокирование дочерних таблиц целиком.
Откуда данные?
У меня иногда бывают ситуации, когда поле FK включено в часть UQ первым. При этом отдельный индекс по FK уже не нужен, используется по UQ. Будет ли там блокирование таблицы целиком?

← →
evvcom © (2006-08-09 12:20) [12]

> Если по внешним ключам сделать, то еще как будут использоваться.

Рассмотрим часть запроса:

> SELECT p.id, p.name, p.title, p.added_date, p.added_time,
> ca.name AS category_name
> FROM cb_publications AS p
> LEFT JOIN cb_categories AS ca
> ON (p.category_id = ca.id)

Ни индекс по внешнему ключу (foreign key) p.category_id (если его создать), ни по первичному ca.id (индекс уже есть) использован оптимизатором не будет (если только хинтом его принудительно не заставишь), потому как не выгодно это в данном запросе. Да и смысл какой использовать индекс, если читается все равно ВСЯ таблица? Вот если в where поместить условие на выбор небольшого процента записей, то другое дело. Потому я и сказал

> эффекта в оракле с данным запросом иметь не будут

Или если создать составной индекс, в котором присутствуют ВСЕ поля, которые упоминаются в запросе. И то не факт, что оптимизатор выберет индекс, т.к. в этом случае FULL INDEX SCAN и FULL TABLE SCAN в большинстве случаев равноценны.

← →
evvcom © (2006-08-09 12:23) [13]

> Не то, что полезны, а необходимы. Без них идет блокирование
> дочерних таблиц целиком.

Поясни мысль. Блокирование при селекте? Что за случаи такие?

← →
Sergey13 © (2006-08-09 13:05) [14]

> [11] Desdechado © (09.08.06 12:16)
> У меня иногда бывают ситуации, когда поле FK включено в
> часть UQ первым.
Этого достаточно.

> [13] evvcom © (09.08.06 12:23)
Нет, только при модификации главной таблицы.

← →
Piter © (2006-08-09 13:25) [15]

Sergey13 © (09.08.06 8:18) [5]
Я бы JOIN-ы убрал, ибо все равно 0 отсекаются HAVING-ом

Что значит убрать JOIN"ы?!?!?!
Вы вообще на запрос смотрели? Вы не заметили случайно, что извлекаются данные из разных таблиц?!

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

Sam Stone © (09.08.06 10:57) [7]
Индексы сделать?..

вы меня спрашиваете? :)
Я вообще-то сам спрашиваю как оптимизировать :) Структуру таблиц я представил... По какому полю / полям сделать?

← →
Sergey13 © (2006-08-09 13:38) [16]

> [15] Piter © (09.08.06 13:25)
> Что значит убрать JOIN"ы?!?!?!
> Вы вообще на запрос смотрели?

А Вы на мой вариант запроса? Пробовали его? Как результат?

← →
Piter © (2006-08-09 13:38) [17]

Народ! Плиз, по сабжу.

В принципе, задача вроде простая. Можно сделать:

SELECT COUNT(f.publication_id) AS count, f.publication_id FROM cb_favorites AS f GROUP BY f.publication_id ORDER BY count DESC LIMIT 10;

На 5 тысячах записей вроде должно отработать быстро. Вернется 10 ID публикаций самых популярных.

А потом просто 10 раз выполнить запрос аля:

SELECT p.name, p.title, p.added_date, p.added_time FROM cb_publications AS p WHERE p.id = ID_X

Но неужели это средствами SQL сделать нельзя?
Может, подзапросы использовать?

Подзапросы быстрее джойнов работают?

← →
Piter © (2006-08-09 13:40) [18]

Sergey13 © (09.08.06 13:38) [16]
А Вы на мой вариант запроса?

сори, по шрифту [5] подумал что это цитата, то есть цитата моего запроса :)

Я подумал предлагаешь просто убрать JOIN"ы... Сейчас буду пробовать.

← →
evvcom © (2006-08-09 14:20) [19]

> [17] Piter © (09.08.06 13:38)
> А потом просто 10 раз выполнить запрос аля:

Нет! Не 10 раз выполнить запрос, а сджойнить cb_publications и результат этого подзапроса все в одном запросе.

> [15] Piter © (09.08.06 13:25)
> Что значит убрать JOIN"ы?!?!?!

Видимо, все-таки имелись ввиду ключевые слова. То что показано в [5] аналогично с текстом, в котором используются ключевые слова INNER JOIN. Или другими словами в [0] убрать HAVING, внешние джойны заменить внутренними. Ну и естественно замечание [3] про участие в GROUP BY всех неагрегируемых полей запроса должно быть учтено.

← →
evvcom © (2006-08-09 14:23) [20]

> [14] Sergey13 © (09.08.06 13:05)
> Нет, только при модификации главной таблицы.

При модификации чего? Пользовательских данных (не первичного ключа) или все же первичного ключа с каскадным констрейнтом?

← →
Sergey13 © (2006-08-09 14:28) [21]

> [20] evvcom © (09.08.06 14:23)

Насколько я помню - пофиг. Запись в главной модифицирована, значит дочки в "подвешенном" состоянии. Если индекса нет, значит будет залочена вся дочерняя таблица.

← →
evvcom © (2006-08-09 14:37) [22]

> [21] Sergey13 © (09.08.06 14:28)

Сильно сомневаюсь... Надо проверить. Неужели за полтора года я бы на это не наткнулся? Ни на один FK у меня нет индексов, которые актуальны только (как я думал до сих пор) с каскадными констрейнтами, чего мы тоже не используем. Правда коммиты у нас всегда идут практически сразу за апдейтом. Надо попробовать...

← →
Sergey13 © (2006-08-09 14:41) [23]

> [22] evvcom © (09.08.06 14:37)

Так а это в принципе не так и страшно, краха не будет. Просто другие транзакции будут ждать завершения "этой". Если "эта" шустрая, то и задержки будут незаметны. Но будут.

ЗЫ: Надо будет тоже проверить, а то сомневаться начал после твоих сомнений. 8-)
Помню вроде на курсах про это говорили или читал где то, а увернности уже меньше стало.

← →
evvcom © (2006-08-09 14:54) [24]

> Так а это в принципе не так и страшно, краха не будет

Это понятно.

> Просто другие транзакции будут ждать завершения "этой".

Вот это и думаю проверить. Вот в это и не верится. Не вижу причин, по которым так вынуждены были (бы) сделать. Ну поменялись данные и что? Чем это препятствует менять данные в зависимых таблицах, чем это чревато? Даже записи, ссылающиеся на главную.

← →
evvcom © (2006-08-09 15:04) [25]

Сейчас проделал опыт.
create table SL_PEOPLE ( ID_SL_PEOPLE NUMBER(12) not null, SLP_FIRSTNAME VARCHAR2(20), SLP_MIDDLENAME VARCHAR2(20), SLP_LASTNAME VARCHAR2(20) not null, SLP_BIRTHDAY DATE, SLP_SEX NUMBER(1) not null ); create table SL_WORKER ( ID_SL_WORKER NUMBER(12) not null, ID_SLW_PARENT NUMBER(12), ID_SL_PEOPLE NUMBER(12) not null, ID_SL_POST NUMBER(12) not null, SLW_CLOCK_NUMBER VARCHAR2(20), SLW_PHONE VARCHAR2(20), SLW_CALL_SIGN VARCHAR2(20), SLW_DATE_FROM DATE, SLW_DATE_TO DATE ); alter table SL_WORKER add constraint FK#SLW_PEOPLE foreign key (ID_SL_PEOPLE) references SL_PEOPLE (ID_SL_PEOPLE);
Далее в разных сессиях:
1. Сессия1: Изменяю запись в SL_PEOPLE. Ок.
2. Сессия2: Изменяю запись в SL_WORKER. Ок.
3. Сессия1: Commit; Ок.
4. Сессия2: Commit; Ок.

Поменял 1 и 2, 3 и 4 местами в разных комбинациях, все Ок. Никаких ожиданий/блокировок. Что я неправильно делаю?

← →
Sergey13 © (2006-08-09 15:13) [26]

> [25] evvcom © (09.08.06 15:04)

Все ты правильно делаешь. У меня тот-же результат.
То-ли мне это привиделось, то-ли это было справедливо для более старых версий (до 8-ки), то-ли еще чего. Надо будет поковыряться в доках/тырнете на этот счет.

← →
Piter © (2006-08-09 15:15) [27]

evvcom © (09.08.06 14:37) [22]
Ни на один FK у меня нет индексов

а по-моему для FK индекс создается автоматически...

← →
Slym © (2006-08-09 15:20) [28]

Сначала подсчет потом красоты наводим
псевдокод не проверял:
SELECT p.id, p.name, p.title, p.added_date, p.added_time, ca.name AS category_name, MostFavor.count as count FROM (SELECT cb_favorites.publication_id, count(cb_favorites.publication_id) as count FROM (cb_favorites GROUP BY cb_favorites.publication_id ORDER BY count(cb_favorites.publication_id) LIMIT 10) as MostFavor INNER JOIN cb_publications AS p ON (MostFavor. publication_id = p.id)) INNER JOIN cb_categories AS ca ON (p.category_id = ca.id) ORDER BY count DESC;

← →
Slym © (2006-08-09 15:29) [29]

или
SELECT p.id, p.name, p.title, p.added_date, p.added_time,
ca.name AS category_name, MostFavor.count as count
FROM
(SELECT TOP 10 cb_favorites.publication_id, count(cb_favorites.publication_id) as count
FROM (cb_favorites
GROUP BY cb_favorites.publication_id
ORDER BY count(cb_favorites.publication_id)) as MostFavor
INNER JOIN cb_publications AS p
ON (MostFavor. publication_id = p.id))
INNER JOIN cb_categories AS ca
ON (p.category_id = ca.id)
ORDER BY count DESC;

← →
Slym © (2006-08-09 15:30) [30]

ORDER BY count(cb_favorites.publication_id) desc
а строку ORDER BY count DESC; убрать

← →
Desdechado © (2006-08-09 15:32) [31]

Piter © (09.08.06 15:15) [27]
> а по-моему для FK индекс создается автоматически.
В IB - да, в Оракле - нет, в MySQL - не знаю.

← →
evvcom © (2006-08-09 15:37) [32]

> [27] Piter © (09.08.06 15:15)

См. [31]. У меня Оракл.

← →
Piter © (2006-08-09 16:49) [33]

Sergey13, спасибо! Вместо 45 секунд запрос стал выполняться 1,5 секунды. Правда, при одинаковых Count чуть сортировка поменялась, да хрен с ней :)

И зачем придумали внешние JOIN"ы если можно в 10 раз быстрее?

Мой вариант не пробывал?

> [33] Piter © (09.08.06 16:49)
> И зачем придумали внешние JOIN"ы если можно в 10 раз быстрее?

Действительно! Нафига зад... бог человеку придумал, ведь через горло гланды гораздо удобнее выдирать! :-)
Затем, что весь спектр задач не ограничивается только твоей задачей.

> Ни на один FK у меня нет индексов, которые актуальны только
> (как я думал до сих пор) с каскадными констрейнтами, чего
> мы тоже не используем.

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

> [37] ANB © (09.08.06 17:03)
> Для проверки возможности апдейта/удаления.

Конечно же. Удаления согласен полностью. Апдейта - только ключа по которому констрейнт, обычно это primary key. Неточно оформил свою мысль, т.к. удалением подобных записей мы тоже не занимаемся, а вводим дополнительное поле флага, дабы обезопасить себя от "нечаянных" действий пользователя.

Slym © (09.08.06 16:57) [34]
Мой вариант не пробывал?

не, не пробовал. Меня полностью устроило ускорение в 10 раз :)

Хотя если интересно - могу попробовать...

попробовал:

SELECT p.id, p.name, p.title, p.added_date, p.added_time, ca.name AS category_name, MostFavor.count as count FROM (SELECT TOP 10 cb_favorites.publication_id, count(cb_favorites.publication_id) as count FROM (cb_favorites GROUP BY cb_favorites.publication_id ORDER BY count(cb_favorites.publication_id)) as MostFavor INNER JOIN cb_publications AS p ON (MostFavor. publication_id = p.id)) INNER JOIN cb_categories AS ca ON (p.category_id = ca.id) ORDER BY count(cb_favorites.publication_id) desc;

Результат:

"#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near "10 cb_favorites . publication_id , count( cb_favorites . publication_id ) as cou" at line 1 "

> [25] evvcom © (09.08.06 15:04)

Таки нашел я где видел сие утверждение. Вернее видел это и ранее, но нашел подтверждение. У Кайта в его "Оракл для профессионалов" в главе 7 "Индексы" есть подраздел "Индексы для внешних ключей".

> [41] Sergey13 © (10.08.06 14:36)

Да, вижу теперь у Кайта. Попробовал [25] еще раз, но уже с "изменением" первичного ключа. Результат тот же. Все ок. У меня 9.2.0.6, видимо Кайт описывал что-то более раннее.

> [42] evvcom © (10.08.06 15:51)
> У меня 9.2.0.6, видимо Кайт описывал что-то более раннее.
Возможно. Хотя у меня 8.1.7. Не по 7-ке же она написана.

C Oracle 9-ки блокировка таблицы при отсутствии индекса по FK канула в лету

> [43] Sergey13 © (10.08.06 16:32)
> Не по 7-ке же она написана.

Не, не по 7-ке. Я видел там упоминание 8-ки.

> [44] roottim © (10.08.06 16:46)

Вероятно, так и есть. Потому я и упомянул свою версию.

Piter © (10.08.06 13:35) [40]
"#1064 - You have an error in your SQL syntax
Я же заранее предупредил "Это псевдокод" и копипастом не сработает.
И привел принцип: выбираем сначала данные (подзапросом)
а уж к ним цепляем расшифровку жойним Титлы и Наймы

Оптимизация запроса (MySQL) Найти похожие ветки