ReSQL.ru
     
powered by simpleCommunicator - 2.0.59     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Подскажите по медленному JOIN: проблема оптимизации или железа?
17 сообщений из 17, страница 1 из 1
Подскажите по медленному JOIN: проблема оптимизации или железа?
    #39386712
Dimec
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimec
Гость
Добрый день. Есть две таблицы `company` и `company_category`, у одной компании может быть несколько категорий т.е. обычная many-to-many.
Строк 4 млн и 6,5 млн соответственно.

Проблема в том, что запрос, например, когда мне нужно найти организации по категории, городу и статусу, выполняется медленно:
Код: sql
1.
2.
3.
4.
SELECT * FROM `company`
INNER JOIN `company_category`
ON `company_category`.`company_id` = `company`.`id`
WHERE `company_category`.`category_id` = 15  AND `company`.`location_id`= 686 AND `company`.`status` = 1


может выполняться от 1 до 10 секунд, что, конечно же, для меня неприемлемо.

EXPLAIN вроде бы в норме:
Код: sql
1.
2.
3.
4.
5.
6.
+----+-------------+------------------+--------+----------------------------------+-------------+---------+----------------------------------+-------+-------------+
| id | select_type | table            | type   | possible_keys                    | key         | key_len | ref                              | rows  | Extra       |
+----+-------------+------------------+--------+----------------------------------+-------------+---------+----------------------------------+-------+-------------+
|  1 | SIMPLE      | company_category | ref    | PRIMARY,category_id              | category_id | 2       | const                            | 10151 |             |
|  1 | SIMPLE      | company          | eq_ref | id,location_id,status            | id          | 8       | db.company_category.company_id   |     1 | Using where |
+----+-------------+------------------+--------+----------------------------------+-------------+---------+----------------------------------+-------+-------------+



Обычно запросы занимали у меня тысячные или сотые секунды, правда без джоинов и по индексам.
Хочу выяснить: это все-таки уже ограничения оборудования (пробовал и на сервере с 2GB RAM, и на локале с 4GB) или
что-то настроено не так?
...
Рейтинг: 0 / 0
18.01.2017, 11:51
| Ответить | Цитировать | Написать  
Подскажите по медленному JOIN: проблема оптимизации или железа?
    #39386719
Dimec
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimec
Гость
Дополню, что 99.9% согласно профилированию занимает Sending Data.
...
Рейтинг: 0 / 0
18.01.2017, 11:58
| Ответить | Цитировать | Написать  
Подскажите по медленному JOIN: проблема оптимизации или железа?
    #39386739
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
Dimec,

Сколько записей выдает этот запрос?

Какие числа выдадут следующие запрорсы:
Код: sql
1.
SELECT COUNT(*) FROM `company` WHERE `company`.`location_id`= 686 AND `company`.`status` = 1

Код: sql
1.
SELECT COUNT(*) FROM `company_category` WHERE `company_category`.`category_id` = 15

?
...
Рейтинг: 0 / 0
18.01.2017, 12:17
| Ответить | Цитировать | Написать  
Подскажите по медленному JOIN: проблема оптимизации или железа?
    #39386757
Melkij
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Melkij
Участник
Добавьте ещё DDL таблиц, видеть какие индексы уже есть.

Попробуйте с индексами company по status & location_id, company_category по category_id & company_id
...
Рейтинг: 0 / 0
18.01.2017, 12:27
| Ответить | Цитировать | Написать  
Подскажите по медленному JOIN: проблема оптимизации или железа?
    #39386787
Dimec
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimec
Гость
miksoft,

Запрос из моего начального поста находит 895 строк.
Первый из вашего: 403329
Второй из вашего: 10391

Melkij,

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

Причем если отдельно выбирать, например, компании по location_id и status, и отдельно company_category по category_id, то все происходит если не моментально, то с адекватным временем.
...
Рейтинг: 0 / 0
18.01.2017, 13:04
| Ответить | Цитировать | Написать  
Подскажите по медленному JOIN: проблема оптимизации или железа?
    #39386805
Melkij
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Melkij
Участник
Dimec,

А зачем вам все возможные индексы? Нужны вполне определённые. Эти два индекса, которые я предлагаю, должны эффективно фильтровать ваш запрос.
...
Рейтинг: 0 / 0
18.01.2017, 13:22
| Ответить | Цитировать | Написать  
Подскажите по медленному JOIN: проблема оптимизации или железа?
    #39386838
Dimec
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimec
Гость
Melkij,

Имел в виду возможные из необходимых.
Еще раз проверил с вашими индексами - ничего не изменилось.

Все-таки кто-нибудь может сказать по своему опыту: с учетом всех цифр ситуация с таким временем запроса все-таки норма или нет?
...
Рейтинг: 0 / 0
18.01.2017, 13:47
| Ответить | Цитировать | Написать  
Подскажите по медленному JOIN: проблема оптимизации или железа?
    #39386928
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
DimecЕще раз проверил с вашими индексами - ничего не изменилось.Показывайте полностью DDL таблиц, включая свежесозданные индексы, и новый план запроса.

Кстати, после создания индексов не забывайте делать ANALYZE TABLE для используемых таблиц.
...
Рейтинг: 0 / 0
18.01.2017, 15:15
| Ответить | Цитировать | Написать  
Подскажите по медленному JOIN: проблема оптимизации или железа?
    #39387075
Dimec
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimec
Гость
miksoft,

Да, спасибо, немножечко изменил индексы, раньше на время разработки в company id был не первичным, а unique.
Но существенно ничего не поменялось, выполняется долго.
Вот, смотрите пожалуйста:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
CREATE TABLE `company` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `location_id` int(11) NOT NULL,
 `name` text NOT NULL,
 `status` tinyint(4) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `status_location_id` (`status`, `location_id`),
) ENGINE=MyISAM AUTO_INCREMENT=4000001 DEFAULT CHARSET=utf8

CREATE TABLE `company_category` (
 `company_id` bigint(20) unsigned NOT NULL,
 `category_id` smallint(5) unsigned NOT NULL,
 PRIMARY KEY (`category_id`,`company_id`),
 KEY `company_id` (`company_id`)
) ENGINE=MyISAM AUTO_INCREMENT=6500001 DEFAULT CHARSET=utf8



Для запроса:
Код: sql
1.
2.
3.
4.
5.
SELECT * FROM `company`
INNER JOIN `company_category`
ON `company`.`id` = `company_category`.`company_id`
WHERE `company_category`.`category_id`=44 AND `company`.`location_id`=157 AND `company`.`status`=1
LIMIT 10



Explain такой:
Код: sql
1.
2.
3.
4.
5.
6.
+----+-------------+------------------+--------+---------------------------------------------+---------+---------+----------------------------------+------+-------------+
| id | select_type | table            | type   | possible_keys                               | key     | key_len | ref                              | rows | Extra       |
+----+-------------+------------------+--------+---------------------------------------------+---------+---------+----------------------------------+------+-------------+
|  1 | SIMPLE      | company_category | ref    | PRIMARY                                     | PRIMARY | 2       | const                            | 1260 | Using index |
|  1 | SIMPLE      | company          | eq_ref | PRIMARY,status_location_id                  | PRIMARY | 8       | db.company_category.company_id   |    1 | Using where |
+----+-------------+------------------+--------+---------------------------------------------+---------+---------+----------------------------------+------+-------------+
...
Рейтинг: 0 / 0
18.01.2017, 18:06
| Ответить | Цитировать | Написать  
Подскажите по медленному JOIN: проблема оптимизации или железа?
    #39387114
netwind
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
netwind
Участник
Dimec на сервере с 2GB RAM
зачем в 2017 году люди называют это сервером? Все такие сервера давно выведены из эксплуатации. У вас VPS с той или иной формой разделения ресурсов ввода-вывода. Характеристики этого разделения могут быть самые разнообразные

Но это не значит, что нет смысла критически подходить к структуре и планам выполнения.
...
Рейтинг: 0 / 0
18.01.2017, 18:49
| Ответить | Цитировать | Написать  
Подскажите по медленному JOIN: проблема оптимизации или железа?
    #39387234
Фотография MasterZiv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MasterZiv
Участник
Dimec.
Хочу выяснить: это все-таки уже ограничения оборудования (пробовал и на сервере с 2GB RAM, и на локале с 4GB) или
что-то настроено не так?


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

У тебя есть SARG из трех полей, допустим, все вместе они дают хорошую селективность, т. е. отфильтровывают много записей, и возвращают мало. Это хорошо. Но одно поле у тебя в одной таблице, а два других - в другой, индекс не построить на все три поля. А два оставшихся в одной таблице уже могут не давать такую хорошую селективность, индекс будет неэффективен.
...
Рейтинг: 0 / 0
18.01.2017, 22:57
| Ответить | Цитировать | Написать  
Подскажите по медленному JOIN: проблема оптимизации или железа?
    #39387235
Фотография MasterZiv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MasterZiv
Участник
Dimecmiksoft,

Запрос из моего начального поста находит 895 строк.
Первый из вашего: 403329
Второй из вашего: 10391

.

во, налицо она, описанная мной проблема.
в общем она не решается, тем более, что у тебя там many-to-many, а не 1 к N.

решается она вводом дополнительных критериев поиска на application уровне.
...
Рейтинг: 0 / 0
18.01.2017, 23:03
| Ответить | Цитировать | Написать  
Подскажите по медленному JOIN: проблема оптимизации или железа?
    #39387237
Фотография MasterZiv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MasterZiv
Участник
Dimec.

Причем если отдельно выбирать, например, компании по location_id и status, и отдельно company_category по category_id, то все происходит если не моментально, то с адекватным временем.


это ты ошибаешься, тебе так кажется, потому что ты не выполняешь весь запрос, а только выбираешь первые несколько сот записей.
...
Рейтинг: 0 / 0
18.01.2017, 23:06
| Ответить | Цитировать | Написать  
Подскажите по медленному JOIN: проблема оптимизации или железа?
    #39387241
Фотография MasterZiv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MasterZiv
Участник
MelkijПопробуйте с индексами company по status & location_id, company_category по category_id & company_id


оба эти индекса одновременно никогда работать не будут, либо один, либо другой, в этом и вся проблема.

нужно либо делать функциональный индекс в таблице категорий компаний на все три поля, но mySQL это не умеет, либо денормализовать данные, протащить status и location_id из company в company_category, и там по всем тем строить индекс, и соответственно переписать запрос.
...
Рейтинг: 0 / 0
18.01.2017, 23:15
| Ответить | Цитировать | Написать  
Подскажите по медленному JOIN: проблема оптимизации или железа?
    #39387243
Dimec
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimec
Гость
MasterZivтут у тебя, как видно сходу, будет проблема, как я ее называю, распределенной селективности. (термин мой)

У тебя есть SARG из трех полей, допустим, все вместе они дают хорошую селективность, т. е. отфильтровывают много записей, и возвращают мало. Это хорошо. Но одно поле у тебя в одной таблице, а два других - в другой, индекс не построить на все три поля. А два оставшихся в одной таблице уже могут не давать такую хорошую селективность, индекс будет неэффективен.
Да, в этом и проблема.

MasterZivнужно либо делать функциональный индекс в таблице категорий компаний на все три поля, но mySQL это не умеет, либо денормализовать данные, протащить status и location_id из company в company_category, и там по всем тем строить индекс, и соответственно переписать запрос.

Собственно собирался так делать, но потом подумал что получается какая-то ерунда с избыточностью данных, а не каноничная many-to-many, и решил спросить на форуме. Но раз не мне одному пришла такая мысль, видимо стоит подумать в этом направлении.

Большое спасибо всем за помощь!
...
Рейтинг: 0 / 0
18.01.2017, 23:35
| Ответить | Цитировать | Написать  
Подскажите по медленному JOIN: проблема оптимизации или железа?
    #39387252
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
Dimec,

0) key_buffer_size у вас сколько?

1) Создайте индекс `company`(`id`, `status`, `location_id`). В порядке полей тут я не уверен, но, по идее, существенной разницы он дать не должен, если key_buffer_size достаточен.

2) Сделайте ANALYZE TABLE для обеих таблиц.

3) Попробуйте несколько раз выполнить такой запрос (именно такой, без изменений):
Код: sql
1.
2.
3.
4.
5.
SELECT `company`.`id`
FROM `company`
INNER JOIN `company_category`
ON `company`.`id` = `company_category`.`company_id`
WHERE `company_category`.`category_id`=44 AND `company`.`location_id`=157 AND `company`.`status`=1

Какое будет время выполнения и план запроса?
...
Рейтинг: 0 / 0
18.01.2017, 23:52
| Ответить | Цитировать | Написать  
Подскажите по медленному JOIN: проблема оптимизации или железа?
    #39387466
Фотография MasterZiv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MasterZiv
Участник
Dimec[quot

Собственно собирался так делать, но потом подумал что получается какая-то ерунда с избыточностью данных, а не каноничная many-to-many, и решил спросить на форуме. Но раз не мне одному пришла такая мысль, видимо стоит подумать в этом направлении.

Большое спасибо всем за помощь!

ну, Денормализация - это всегда избыточность данных. главное чтобы были непротиворечивы.

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

еще вариант - выделить одну главную категорию компании и пронести ее в таблицу компании, и искать только по ней. но это меняет постановку.
...
Рейтинг: 0 / 0
19.01.2017, 11:09
| Ответить | Цитировать | Написать  
17 сообщений из 17, страница 1 из 1
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Подскажите по медленному JOIN: проблема оптимизации или железа?
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]