Модераторы: skyboy, SamDark
  

Поиск:

Ответ в темуСоздание новой темы Создание опроса
> Как изменить запрос? 
:(
    Опции темы
maxipub
Дата 10.5.2018, 14:37 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


Профиль
Группа: Участник
Сообщений: 504
Регистрация: 22.10.2009

Репутация: 1
Всего: 1



Столкнулся с тупняком.

Выбираем из БД данные о юзерах, у которых есть посты, запрос вида:

Код
SELECT u.data FROM users AS u INNER JOIN posts AS p ON p.user_id=u.user_id GROUP BY u.user_id LIMIT pos, num;


Таблицы разраслись. Такой примитив начал выполняться по 0,1 сек. и выше. Зашел в профилирование, 99% - sending data, посмотрел на количество подходящих записей - вся проблема в сильном пересечении данных по ключу.

Можно переделать проверку наличия записей INNER JOIN posts AS p ON p.user_id=u.user_id (нужна проверка в принципе их наличия, хоть 1, количество не важно, просто факт наличия, т.е. вида SELECT 1 FROM...) чтоб в запросе не джойнилась за зря таблица. В подзапрос или еще как-то? Мне кажется, это возможно. А как сделать - не соображу.
PM MAIL   Вверх
Akina
Дата 10.5.2018, 14:46 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Советчик
****


Профиль
Группа: Модератор
Сообщений: 20354
Регистрация: 8.4.2004
Где: Зеленоград

Репутация: 104
Всего: 447



maxipub, перепишите вопрос поаккуратнее. Чтобы "запрос вида" имел хоть какой-то смысл. Чтобы профилирование было цитатой, а не сочинением по мотивам. Структуру хранения покажите, статистику соответствия (или хотя бы план запроса). Потому что сейчас ничего, кроме запроса типа
Код

SELECT u.data 
FROM users u
WHERE EXISTS (SELECT 1
              FROM posts p
              WHERE p.user_id=u.user_id)
 или
Код

SELECT u.data 
FROM users u, (SELECT DISTINCT posts.user_id
               FROM posts) p
WHERE p.user_id=u.user_id
и посоветовать-то нечего... 


--------------------
 О(б)суждение моих действий - в соответствующей теме, пожалуйста. Или в РМ. И высшая инстанция - Администрация форума.

PM MAIL WWW ICQ Jabber   Вверх
maxipub
Дата 10.5.2018, 15:24 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


Профиль
Группа: Участник
Сообщений: 504
Регистрация: 22.10.2009

Репутация: 1
Всего: 1



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

EXISTS выполняется 0.09 - 0.12 сек.

От второго варианта ожидал хорошего результата, но получилось вообще непойми что. Запрос с ним выполняется 0.05 сек. - все равно жутко долго для такого запроса. Но дело даже не в этом. Сам:

Код
SELECT DISTINCT posts.user_id FROM posts


Выполняется 0.15 сек. А весь запрос:

Код
SELECT u.data 
FROM users u, (SELECT DISTINCT posts.user_id
               FROM posts) p
WHERE p.user_id=u.user_id


За 0.05 сек. Как такое вообще возможно? Кеширование отключено.
PM MAIL   Вверх
Akina
Дата 10.5.2018, 15:27 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Советчик
****


Профиль
Группа: Модератор
Сообщений: 20354
Регистрация: 8.4.2004
Где: Зеленоград

Репутация: 104
Всего: 447



Цитата(maxipub @  10.5.2018,  16:24 Найти цитируемый пост)
Как такое вообще возможно?

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


--------------------
 О(б)суждение моих действий - в соответствующей теме, пожалуйста. Или в РМ. И высшая инстанция - Администрация форума.

PM MAIL WWW ICQ Jabber   Вверх
maxipub
Дата 11.5.2018, 12:39 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


Профиль
Группа: Участник
Сообщений: 504
Регистрация: 22.10.2009

Репутация: 1
Всего: 1



Ок, думал так получится. Просто не люблю вываливать кучу данных, "а вы разбирайтесь как хотите". Обычно описываю на схожем примере, мне подсказывают, и далее уже реализацию доделываю сам.

Ок, раз надо, ловите:

Код

id  select_type  table  partitions  type  possible_keys  key  key_len  ref  rows  filtered  Extra
1 PRIMARY c NULL ref store_cat_id,store_cat_disabled,store_cat_id_2 store_cat_disabled 1 const 81 10.00 Using where; Using temporary; Using filesort 
1 PRIMARY g NULL ref user_id,store_goods_cat_id,store_goods_parent_id,store_goods_total_parent_id store_goods_cat_id 4 lb.c.store_cat_id 140 28.04 Using where 
1 PRIMARY <derived2> NULL ref <auto_key0> <auto_key0> 4 lb.g.store_goods_total_parent_id 10 100.00 Using where; Using index 
2 DERIVED mod_store_items NULL ref real_goods_id,cart_id cart_id 4 const 15300 100.00 Using index condition; Using temporary 


таблицы:

Код

--
-- Структура таблицы `mod_store_cats`
--

CREATE TABLE IF NOT EXISTS `mod_store_cats` (
  `store_cat_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `store_cat_name` varchar(64) NOT NULL,
  `store_cat_count` smallint(5) unsigned NOT NULL DEFAULT '0',
  `store_cat_priority` smallint(5) unsigned NOT NULL DEFAULT '0',
  `store_cat_disabled` tinyint(4) NOT NULL DEFAULT '0',
  `store_cat_title` varchar(96) DEFAULT NULL,
  `store_cat_is_gifts` tinyint(3) unsigned NOT NULL DEFAULT '0',
  UNIQUE KEY `store_cat_id` (`store_cat_id`),
  KEY `user_id` (`user_id`),
  KEY `store_cat_disabled` (`store_cat_disabled`)
) ENGINE=MyISAM  DEFAULT CHARSET=cp1251 AUTO_INCREMENT=15142 ;

-- --------------------------------------------------------

--
-- Структура таблицы `mod_store_goods`
--

CREATE TABLE IF NOT EXISTS `mod_store_goods` (
  `store_goods_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `store_goods_name` varchar(64) NOT NULL,
  `store_goods_affected_date` int(10) unsigned NOT NULL,
  `store_goods_cat_id` int(10) unsigned NOT NULL,
  `store_goods_price` int(10) unsigned NOT NULL,
  `store_goods_about` text NOT NULL,
  `store_goods_foto` varchar(64) NOT NULL,
  `store_goods_priority` smallint(5) unsigned NOT NULL DEFAULT '0',
  `store_goods_art` varchar(32) DEFAULT NULL,
  `store_goods_disabled` tinyint(4) NOT NULL DEFAULT '0',
  `store_goods_parent_id` int(11) NOT NULL,
  `store_goods_total_parent_id` int(11) NOT NULL,
  UNIQUE KEY `store_goods_id` (`store_goods_id`),
  KEY `user_id` (`user_id`),
  KEY `store_goods_cat_id` (`store_goods_cat_id`),
  KEY `store_goods_parent_id` (`store_goods_parent_id`),
  KEY `store_goods_affected_date` (`store_goods_affected_date`),
  KEY `store_goods_total_parent_id` (`store_goods_total_parent_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=cp1251 AUTO_INCREMENT=7012 ;

-- --------------------------------------------------------

--
-- Структура таблицы `mod_store_items`
--

CREATE TABLE IF NOT EXISTS `mod_store_items` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `real_goods_id` int(10) unsigned NOT NULL,
  `spec_id` int(10) unsigned NOT NULL,
  `cost` int(10) unsigned NOT NULL,
  `cart_id` int(10) unsigned NOT NULL DEFAULT '0',
  UNIQUE KEY `id` (`id`),
  KEY `user_id` (`user_id`),
  KEY `real_goods_id` (`real_goods_id`),
  KEY `cart_id` (`cart_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=cp1251 AUTO_INCREMENT=173640 ;


текущий запрос:

Код

SELECT g.* FROM mod_store_goods AS g 
INNER JOIN mod_store_cats AS c ON c.store_cat_id=g.store_goods_cat_id 
INNER JOIN (SELECT DISTINCT real_goods_id FROM mod_store_items WHERE cart_id=0) AS i ON i.real_goods_id=g.store_goods_total_parent_id 
WHERE 
g.user_id=972 AND 
c.store_cat_disabled=0 AND 
c.store_cat_is_gifts=0 AND 
g.store_goods_parent_id=0 
ORDER BY g.store_goods_affected_date DESC


первая таблица - категории сайта
вторая - товары сайта
третья - товарные позиции (наличие)

задача - получить список товаров, которые:
1) находятся не в отключенных разделах (c.store_cat_disabled=0) и не в разделах для подарков (c.store_cat_is_gifts)
2) в наличии (по ним i.real_goods_id=g.store_goods_total_parent_id есть позиции, которые не в заказах cart_id=0)

как-то так...
PM MAIL   Вверх
Akina
Дата 11.5.2018, 13:17 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Советчик
****


Профиль
Группа: Модератор
Сообщений: 20354
Регистрация: 8.4.2004
Где: Зеленоград

Репутация: 104
Всего: 447



Из очевидного:
Код

CREATE INDEX idx ON mod_store_cats (store_cat_disabled, store_cat_is_gifts /* , store_cat_id */ );

Из менее очевидного:
Код

CREATE INDEX idx ON mod_store_items (cart_id, real_goods_id);


А так больше ничего в голову не приходит... 


--------------------
 О(б)суждение моих действий - в соответствующей теме, пожалуйста. Или в РМ. И высшая инстанция - Администрация форума.

PM MAIL WWW ICQ Jabber   Вверх
maxipub
Дата 11.5.2018, 16:08 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


Профиль
Группа: Участник
Сообщений: 504
Регистрация: 22.10.2009

Репутация: 1
Всего: 1



Akina, спасибо за идеи.

Индекс на категории при запросе игнорируется, пытался навязать его принудительно - время выполнения запроса выросло вполовину.

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

В итоге с Вашей помощью сейчас имеем в пределах 0.03-0.04 сек. на продакшине.

Неплохо, но хотелось бы лучшего результата.  Буду искать еще варианты, если что получится - отпишусь.

Кстати, если убрать сортировку, скорость возрастает еще в 4-5 раз. Первым делом попробую копать в эту сторону.
PM MAIL   Вверх
_zorn_
Дата 14.5.2018, 16:06 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


Профиль
Группа: Участник
Сообщений: 945
Регистрация: 21.8.2007

Репутация: 2
Всего: 12



EXPLAIN перед SELECT посмотри что не так в твоих запросах.

Это сообщение отредактировал(а) _zorn_ - 14.5.2018, 16:07
PM MAIL   Вверх
maxipub
Дата 23.5.2018, 10:32 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


Профиль
Группа: Участник
Сообщений: 504
Регистрация: 22.10.2009

Репутация: 1
Всего: 1



_zorn_, двумя постами выше есть EXPLAIN. И что не так в моих запросах?
PM MAIL   Вверх
maxipub
Дата 25.5.2018, 17:08 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


Профиль
Группа: Участник
Сообщений: 504
Регистрация: 22.10.2009

Репутация: 1
Всего: 1



Есть новости. Вынес пару не критичных полей за пределы таблицы items, получил почти двукратный прирост производительности. Пусть не то ускорение, которого хочу. Пусть экстенсивный путь. Но кое-какой результат. smile 
PM MAIL   Вверх
_zorn_
Дата 28.5.2018, 15:15 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


Профиль
Группа: Участник
Сообщений: 945
Регистрация: 21.8.2007

Репутация: 2
Всего: 12



Цитата(maxipub @ 23.5.2018,  17:32)
_zorn_, двумя постами выше есть EXPLAIN. И что не так в моих запросах?

Я имел ввиду что это ГЛАВНЫЙ инструмент.
Например "Using where; Using temporary; Using filesort" ЭТО ОЧЕНЬ ПЛОХО. Как минимум "filesort". Из информации которую дает EXPLAIN вы должны почерпнуть ЧТО НЕ ТАК в вашем запросе и где можно ключей наворотить, а где запрос упростить.

Главное не смотреть на эту инфу как баран на новые ворота. Там в принципе понятно же все.

ЗЫ. filesort обычно когда нет нужных индексов.

Это сообщение отредактировал(а) _zorn_ - 28.5.2018, 15:22
PM MAIL   Вверх
Akina
Дата 28.5.2018, 16:13 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Советчик
****


Профиль
Группа: Модератор
Сообщений: 20354
Регистрация: 8.4.2004
Где: Зеленоград

Репутация: 104
Всего: 447



Цитата(_zorn_ @  28.5.2018,  16:15 Найти цитируемый пост)
Например "Using where; Using temporary; Using filesort" ЭТО ОЧЕНЬ ПЛОХО. Как минимум "filesort".

Ну не надо так категорично-то... а то тут недавно наблюдал, как товарищ интенсивно боролся с filesort - и всё удивлялся, что как только поборет, так почему-то дольше получается... ага... пока ему не сказали, что бороться с filesort-ом ТРЁХ записей как минимум неразумно.


--------------------
 О(б)суждение моих действий - в соответствующей теме, пожалуйста. Или в РМ. И высшая инстанция - Администрация форума.

PM MAIL WWW ICQ Jabber   Вверх
maxipub
Дата 30.5.2018, 17:07 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


Профиль
Группа: Участник
Сообщений: 504
Регистрация: 22.10.2009

Репутация: 1
Всего: 1



Цитата(_zorn_ @  28.5.2018,  15:15 Найти цитируемый пост)
Там в принципе понятно же все.

Ну если Вам все понятно, подскажите. Мне не понятно.
PM MAIL   Вверх
Google
  Дата 25.9.2018, 09:53 (ссылка)  





  Вверх
  
Ответ в темуСоздание новой темы Создание опроса
1 Пользователей читают эту тему (1 Гостей и 0 Скрытых Пользователей)
0 Пользователей:
« Предыдущая тема | MySQL | Следующая тема »


 




[ Время генерации скрипта: 0.1033 ]   [ Использовано запросов: 21 ]   [ GZIP включён ]


Реклама на сайте     Информационное спонсорство

 
По вопросам размещения рекламы пишите на vladimir(sobaka)vingrad.ru
Отказ от ответственности     Powered by Invision Power Board(R) 1.3 © 2003  IPS, Inc.