MySQL, GROUP BY - невероятные тормоза

Открыли Америку. Т.е. по-вашему можно и не убирать? :)

Еще двое.

Знаем.

А еще знаем, что когда в руках молоток, то всё вокруг гвозди и очень хочется "специально для еще одних всех" поделиться инструкцией по использованию молотка... даже когда надо завернуть шуруп и у человека в руках уже отвертка:)

Объясним что мы в данном случае (во фразе про молоток и шурупы) имеем ввиду.

Сначала по поводу "шурупов".

ТС выбирает `browser`,`stream_id`,`source_id`,`service_id`,`user_id` , ему нужны представители строчки с уникальным browser и агрегатных функций он не использует.

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

distinct и group by часто дают одинаковый результат, но не всегда, поэтому когда работа с группами не нужна - нужен distinct, а не group by.

Теперь по поводу "молотка."

Дело в том, что distinct (в отличии от group by) сортировки по умолчанию не включает, поэтому order by null ему не нужен.

Обратите внимание что тот кусочек мануала что Вы привели - относится именно к group by, но никак не касается distinct, посмотрите мануал по distinct - убедитесь.

Лимиты в виде limit x,y тут не решат вопрос совсем.

Объясним. ТС очевидно нужные все данные.

В отсутствии сортировки по основному полю (browser) порядок выборки данных будет непредсказуем. А значит бить один большой запрос на лимиты нельзя - данные могут дублироваться или пропускаться.

В присутствии сортировки надо помнить о том как работает лимит и что для выполнения limit 10000,1000 мускул пролистает все 11000 записей, поэтому нет никакого смысла выбирать по тысяче предыдущие 10 тысяч, все они и так будут в последнем запросе.

join в данном случае так себе идея.

Лучше сначала выбрать browser_id, потом отдельным запросом browser по этим id.

Когда идет работа с большими данными, то даже при использовании innodb (а у автора может myisam) занимать две таблицы вместо одной на заметный срок нет смысла, по крайней мере в ситуации когда из второй таблицы просто подтягиваются данные по ключам.

А вообще еесли вопрос не решится заменой выносом user_agent в отдельную табличку со ссылкой на нее через browser_id, то с точки зрения производительности больше смысла будет в том, что бы сделать отдельную таблицу `browser`,`stream_id`,`source_id`,`service_id`,`user_id` сделать там browser уникальным и складывать в нее только значения с user_id<>0. Скорее всего она будет крошечная (юзеров обычно меньше анонимов), выборка как следствие будет реактивная (таблица меньше + дублирования строк с браузерами не будет), а накладные расходы на вставку в нее значений ничтожными (в меру ее размера хотя бы).

---------- Добавлено 08.08.2017 в 02:14 ----------

Несколько миллионов записей (допустим 5), varchar(50) + 4 поля инт - допустим 64 байта всего = 300 мегабайт база минимум. При этом индекс только на user_id, а группировка по текстовому полю нефиксированной длины. Тут никакого explain не надо запускать, что бы понять что "все вообще не так".

Да тут можно на размер базы не смотреть - запрос неверно составлен, группировка без агрегации - явная ошибка и ее нужно исправить.

По сортировке я бы еще добавил свои пять копеек. Практического значения в данном вопросе они не имеют, но раз уж тут начали тыкать носом в мануал, то давайте разберемся. А почему собственно группировка вызывает именно такую сортировку? Разработчики мускула такие тупые что заложили лишнюю нагрузку по умолчанию или.... а, ну да, конечно, это ведь группировка. Агрегатные функции и все такое. Работа с сгруппированными данными, а память не резиновая. Я не утверждаю что это лучший способ, но мне кажется что если немножко подумать, то любому станет очевидно, что такая сортировка это артефакт алгоритма группировки а не желание разработчиков замедлить ваши запросы. Да, безусловно, такие хаки могут в определенных случаях ускорять работу, и ускорять существенно. Однако это требует экспериментального подтверждения и вообще не уместно в контексте изначально ошибочно построенного запроса.

Коллеги, спасибо всем. Сейчас еще раз внимательно перечитаю Ваши сообщения. Там много полезного, того что действительно стоит учесть.

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

Не надо его туда писать. В отдельную таблицу надо его писать.

Если у вас в базе по 100500 раз дублируются какие-то строки, то это неправильная база...

Ну может ему дешевле такую денормализацию сделать чем JOIN-ить и т.п.

ТС ведь уперся в скорость а не в размер базы.

Подозреваю что если убрать группировку, странную сортировку и накинуть индекс, то ТС будет счастлив. А уже если это не поможет то да, нормализация, изучение планов запросов и т.п.

Не поймите меня неправильно, но если ТС строит запросы вручную а не через ORM и при этом плавает в этих самых запросах, то может не стоит усложнять структуру? Мы ведь не знаем сколько еще у него запросов к этой таблице. Сейчас нормализация, потом вьювы чтобы скрыть нормализацию от легаси, потом оптимизация вьювов... Может пока остановимся просто на исправлении запроса...

1) Можно обойтись без группировки, достаточно сортировки по полю browser. На поле browser обязательно повесить индекс.

2) Поле browser заменить на browser_id, а browser - вынести в отдельную таблицу. При работе с миллионами записей это обязательно.

3) Тормоза после выполнения п.п. 1 и 2 возможно сохраняться, т.к. выборка у вас получается огромная - с миллионами записей, что требует много времени на передачу результата клиенту, а также сколько то гигабайт оперативки, в результате чего возможен своп с замедлением в сотню раз. Если вы еще весь этот результат в массив ПХП загоняете, то проблемы неудивительны.

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

Я не раз сталкивался с подобными проблемами больших выборок (32 Гига оперативы мне не хватало), поэтому в итоге в выборку в ключал только id и обработку вел поэлементно: намного лучше сделать миллион быстрых (по индексу) простых запросов к БД, чем за раз вытянуть несколько гигабайт данных и захлебнуться в них.

И при вставке каждый раз селектом лазить за нужным ид, а если его там нету, то сначала туда впиливать а потом уже в таблицу лога ?

Скорость как правило функция от размера в том или ином виде.

Практика показывает, что делать можно по-разному, но чем больше очевидных нерешенных проблем,

тем больше будет вылезать проблем с производительностью и даже функциональностью.

Можно и запрос поправить, только вот шанс, что потом придется править базу и опять запрос довольно велик.

Хотя если делать чужим людям, за фиксированные бабки, и быть уверенным что они тебя потом не найдут...