Форумы портала PHP.SU :: Версия для печати :: Оптимизировать MySQL запрос?

Warning: Cannot use a scalar value as an array in /home/admin/public_html/forum/include/fm.class.php on line 757

Warning: Invalid argument supplied for foreach() in /home/admin/public_html/forum/include/fm.class.php on line 770
Форумы портала PHP.SU :: Версия для печати :: Оптимизировать MySQL запрос?

Форумы портала PHP.SU » PHP » SQL и Архитектура БД » Оптимизировать MySQL запрос?

Страниц (1): [1]

1. ArtmirArt - 22 Апреля, 2019 - 04:33:32 - перейти к сообщению

Здравствуйте. Как можно оптимизировать такой MySQL-запрос? Растерялся

CODE (SQL):
скопировать код в буфер обмена

SELECT U.id,U.name,U.birthday,U.sex,U.city,U.STATUS,U.regdate
FROM users U
LEFT JOIN shipping SA ON SA.user_id=U.id
LEFT JOIN activity A ON A.user_id=U.id
LEFT JOIN blocked B ON B.user_id=U.id
LEFT JOIN deleted D ON D.user_id=U.id
WHERE SA.id AND A.last_activity_at > 1553260577 AND B.id IS NULL AND D.id IS NULL AND
U.id NOT IN (
( SELECT who_id FROM blacklist WHERE user_id=17 )
UNION
( SELECT user_id FROM blacklist WHERE who_id=17 )
UNION
( SELECT 17 )
)
GROUP BY U.id ORDER BY U.id DESC LIMIT 1 OFFSET 3

2. andrewkard - 23 Апреля, 2019 - 21:26:06 - перейти к сообщению

ArtmirArt пишет:

LEFT JOIN blocked B ON B.user_id=U.id
LEFT JOIN deleted D ON D.user_id=U.id

Это статусы? По моему лучше добавить соотв. колонки в таблицу users

3. LIME - 24 Апреля, 2019 - 18:55:45 - перейти к сообщению

а activity наоборот вынести в какое-нибудь быстрое хранилище типа redis, чтобы MVCC сума не сошел
если надо типа активные за последние сутки, то лучше их кэшировать отдельно
по ситуации конечно
общего решения нет
(Добавление)
Мелкий как считаешь?

4. Мелкий - 24 Апреля, 2019 - 19:07:36 - перейти к сообщению

LIME пишет:

Мелкий как считаешь?

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

5. LIME - 24 Апреля, 2019 - 19:09:50 - перейти к сообщению

Мелкий я не столько о самом запросе сколько о

LIME пишет:

а activity наоборот вынести в какое-нибудь быстрое хранилище типа redis, чтобы MVCC сума не сошел

ты как дба что думаешь?

6. Мелкий - 24 Апреля, 2019 - 19:31:57 - перейти к сообщению

Вот как дба у меня и нет желания думать в этой теме.

Ок, activity выкинули куда-то. Дальше что? where U.id in (100 тысяч чисел на несколько мегабайт размером запрос)? Хрень это будет, а не оптимизация. Изобретать inner join на приложении и сделать 100 тысяч походов в редис чтобы отсечь некоторое количество из них? Опять хрень получилась.
А какое реальное распределение данных? Что такое activity? append-only лог или постоянный update одних и тех же счётчиков?

7. LIME - 24 Апреля, 2019 - 20:24:30 - перейти к сообщению

100 тыс не понадобится в выборке
Такое пагинируют
А если надо то это что-то монстрячное и мегабайты вполне норм
Я вот думаю как себя будет таблица юзеров чувствовать если на каждый запрос новая версия mvcc?
Вакуум не устанет?
(Добавление)

LIME пишет:

100 тыс не понадобится в выборке

я к тому что не надо таких бизнес задач
Смысла в них не бывает
(Добавление)
добрался до кампуктера
попробую пояснить

LIME пишет:

я к тому что не надо таких бизнес задач

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

8. Мелкий - 24 Апреля, 2019 - 23:27:37 - перейти к сообщению

LIME пишет:

100 тыс не понадобится в выборке
Такое пагинируют

То-то и оно, что пагинируют как? Навскидку условие "пользователь проявлял активность недавно" как раз весьма селективно относительно множества всех пользователей.
Обрати внимание: к множеству активных пользователей есть ещё фильтры. В частности (неверно написанный) inner join к shipping. И по этому условию возможно будет обрезано неопределённое число пользователей. Как это пагинировать?

Ситуацию, впрочем, чуть улучшает явная сортировка по независимому критерию. Так что вторую страницу можно сделать нормально передав последний id с прошлой страницы. Но всё равно: мы не можем затребовать topN записей и приклеить к ним недостающие данные со второй базы. Потому что там есть свои фильтры и вместо N рискуем получить вплоть до 0 в результате.

Кстати, проблему никак не облегчает и то, когда это разные таблицы в одной базе. Всё тот же напрасный перебор в join filter. Накопали пользователей на страницу быстро - повезло. А можно и миллионы строк перебрать в поисках тех 10 нужных. Пересечение больших множеств так просто не считается.

LIME пишет:

Вакуум не устанет?

Найдёшь его в mysql? Хинт: нет его.

LIME пишет:

Я вот думаю как себя будет таблица юзеров чувствовать если на каждый запрос новая версия mvcc?

Ну а касательно postgresql - на каждый запрос всегда прокручиваются колёса mvcc машинерии. Даже index only scan - лишь возможность пропустить проверку видимости, если visibility map сказал что можно. А в пределе это может быть неотличимо от index scan. Проверяем, видна ли эа эта версия строки текущей транзакции. А перебираем строки те же самые, из прошлого, будущего (относительно этой транзакции), редкие из настоящего. Потому и вопрос - какова сущность данных в activity? append only лог и постоянный update неких счётчиков будут вести себя весьма различно.
Для innodb логика mvcc тоже прокручивается, но со своими тараканами. В листьях primary key всегда самая последняя версия строки, даже если не закоммичена ещё. Читатели в поисках видимой им версии лезут в журнал записи и достают оттуда предыдущие версии строки если им надо.

LIME пишет:

я только одно могу придумать
сортировать таблицу юзеров по последней активности

Не сортировать, а фильтровать. Нормальная бизнесхотелка вывести каких-то пользователей, исключая неактивных.
Реалтайм для этого не нужен, да. Но это вопрос именно разработки подойти и спросить у бизнеса: "какая задержка будет не важной для попадания пользователя в этот список? 15 минут ок?"
Или ещё как поизвращаться. Но чтобы извращаться - надо знать свои данные.
А для бизнеса задача как раз вполне осмысленно выглядит.

9. LIME - 25 Апреля, 2019 - 10:51:36 - перейти к сообщению

Мелкий пишет:

Навскидку условие "пользователь проявлял активность недавно" как раз весьма селективно относительно множества всех пользователей.

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

Мелкий пишет:

Найдёшь его в mysql?

причем тут это? я для примера того что это надо как-то еще чистить, а не только записать

LIME пишет:

если на каждый запрос новая версия mvcc

я имел ввиду на каждую активность, http запрос или wc или что там считается

Мелкий пишет:

на каждый запрос всегда прокручиваются колёса mvcc машинерии.

вот именно
а мы ее еще так не хило нагружаем данными не связанными с бизнес событием
активность не должна быть в состоянии имхо

Мелкий пишет:

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

это решение, не задача

Мелкий пишет:

Но это вопрос именно разработки подойти и спросить у бизнеса

именно
например за сутки агрегировать данные в отдельное хранилище(не mvcc) и ночью обрабатывать наполняя отчеты или что там надо
имхо лучше поизвращаться чем устраивать для субд гей-порно експириэнс)
(Добавление)
а если вспомнить про шардирование то от извращений никуда не деться
придется задачи иначе решать хошнехош