Мастера DELPHI (документация, исходники, скачать)

Основная («Начинающим»)/ Базы / WinAPI / Компоненты / Сети / Media / Игры / Corba и COM / KOL / FreePascal / .Net / Прочее / RSDN.ORG

« предыдущая ветвь | форум | следующая ветвь »

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

Страницы: < 1 2 3 >
Piter © (2006-08-08 22:32:00)
Очень интересно, почему такой запрос не нехилой машине выполняется порядка 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;

Piter (2006-08-09 15:15:00) [21]
evvcom © (09.08.06 14:37) [22]
Ни на один FK у меня нет индексов

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

Sergey13 (2006-08-09 15:13:00) [22]
> [25] evvcom © (09.08.06 15:04)

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

evvcom (2006-08-09 15:04:00) [23]
Сейчас проделал опыт.
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 местами в разных комбинациях, все Ок. Никаких ожиданий/блокировок. Что я неправильно делаю?

evvcom (2006-08-09 14:54:00) [24]
> Так а это в принципе не так и страшно, краха не будет

Это понятно.

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

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

Sergey13 (2006-08-09 14:41:00) [25]
> [22] evvcom © (09.08.06 14:37)

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

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

evvcom (2006-08-09 14:37:00) [26]
> [21] Sergey13 © (09.08.06 14:28)

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

Sergey13 (2006-08-09 14:28:00) [27]
> [20] evvcom © (09.08.06 14:23)

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

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

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

evvcom (2006-08-09 14:20:00) [29]
> [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 всех неагрегируемых полей запроса должно быть учтено.

Piter (2006-08-09 13:40:00) [30]
Sergey13 © (09.08.06 13:38) [16]
А Вы на мой вариант запроса?

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

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

Piter (2006-08-09 13:38:00) [31]
Народ! Плиз, по сабжу.

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

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 сделать нельзя?
Может, подзапросы использовать?

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

Sergey13 (2006-08-09 13:38:00) [32]
> [15] Piter © (09.08.06 13:25)
> Что значит убрать JOIN'ы?!?!?!
> Вы вообще на запрос смотрели?

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

Piter (2006-08-09 13:25:00) [33]
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:05:00) [34]
> [11] Desdechado © (09.08.06 12:16)
> У меня иногда бывают ситуации, когда поле FK включено в
> часть UQ первым.
Этого достаточно.

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

evvcom (2006-08-09 12:23:00) [35]
> Не то, что полезны, а необходимы. Без них идет блокирование
> дочерних таблиц целиком.

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

evvcom (2006-08-09 12:20:00) [36]
> Если по внешним ключам сделать, то еще как будут использоваться.

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

> 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 в большинстве случаев равноценны.

Desdechado (2006-08-09 12:16:00) [37]
> Без них идет блокирование дочерних таблиц целиком.
Откуда данные?
У меня иногда бывают ситуации, когда поле FK включено в часть UQ первым. При этом отдельный индекс по FK уже не нужен, используется по UQ. Будет ли там блокирование таблицы целиком?

Sergey13 (2006-08-09 12:06:00) [38]
> А в соединениях таблиц по FK очень полезны.

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

Desdechado (2006-08-09 12:04:00) [39]
> "сделай индекс по полю такому-то" эффекта в оракле с данным
> запросом иметь не будут.
Если по внешним ключам сделать, то еще как будут использоваться.
Не знаю, как в MySQL, а в Оракле по FK индексы автоматически не строятся. А в соединениях таблиц по FK очень полезны.

evvcom (2006-08-09 11:25:00) [40]
> [7] Sam Stone © (09.08.06 10:57)

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

Страницы: < 1 2 3 >

Наверх

	« предыдущая ветвь \| форум \| следующая ветвь »