Mysql. Игры разума.

InstantCMS 2.X

Ничего не понимаю в поведении мускула.

#1 12 июня 2019 в 00:07

Небольшая предыстория:
Давно хочу обновить свой сайт до актуальной версии. Но не просто обновить, а практически создать сайт заново и потом инсертить данные в таблицы не изменяя их структуры. При этом правильно создать новые поля в типах контента, избавиться от напрасных компонентов, которые давно удалены, но тут и там всплывают их следы, правильно сделать хуки с учетом накопленного опыта и т.д. То есть глобальная работа над ошибками.
Надеюсь таким образом получить правильно сделанный сайт с правильной структурой и предсказуемым поведением.
Все работы производятся на опенсервере, конфигурация mariadb дефолтная.
Таблицы не мигрирую целиком, а переношу данные инсертом, чтобы не трогать структуру новых таблиц.
То есть например:

Спойлер

INSERT IGNORE INTO новаябаза.cms_comments(`id`, `parent_id`, `level`, `ordering`, `user_id`, `date_pub`, `date_last_modified`, `target_controller`, `target_subject`, `target_id`, `target_url`, `target_title`, `author_name`, `author_email`, `author_url`, `content`, `content_html`, `is_deleted`, `is_private`, `rating`, `is_approved`) SELECT `id`, `parent_id`, `level`, `ordering`, `user_id`, `date_pub`, `date_last_modified`, `target_controller`, `target_subject`, `target_id`, `target_url`, `target_title`, `author_name`, `author_email`, `author_url`, `content`, `content_html`, `is_deleted`, `is_private`, `rating`, `is_approved` FROM стараябаза.cms_comments

И всё шло хорошо, пока дело не дошло до комментариев. А их миллион. И после инсерта комментариев из старой базы в новую новый сайт встал колом. Загрузка виджета комментариев 38 секунд.

Спойлер

SELECT i.*, r.score AS `is_rated`, u.nickname AS `user_nickname`, u.is_deleted AS `user_is_deleted`, u.groups AS `user_groups`, u.avatar AS `user_avatar`, IF(online.date_created IS NOT NULL AND TIMESTAMPDIFF(SECOND, online.date_created, NOW()) <= 180, 1, 0) AS `is_online`
FROM cms_comments i
LEFT JOIN cms_users AS u ON u.id = i.user_id
LEFT JOIN cms_sessions_online AS online ON online.user_id = u.id
LEFT JOIN cms_comments_rating AS r ON r.comment_id = i.id AND r.user_id='1'
WHERE (i.is_deleted IS NULL) AND (i.is_approved = '1')
ORDER BY i.date_pub DESC
LIMIT 0, 20
Время выполнения 37.64345 секунд

Стал удалять из запроса по таблице и выяснил, что если не делать запросов в cms_sessions_online, то есть просто установить

LEFT JOIN cms_sessions_online AS online ON online.user_id = 1

то всё летает. Запрос выполняется за 0.00000 секунд

Стал экспериментировать с индексами таблицы cms_sessions_online. И выяснил интересную вещь. Если добавить в индекс date_created столбец user_id, то запросы в комментариях начинают выполняться за ноль секунд. Всё опять летает!

Спойлер

SELECT i.*, r.score AS `is_rated`, u.nickname AS `user_nickname`, u.is_deleted AS `user_is_deleted`, u.groups AS `user_groups`, u.avatar AS `user_avatar`, IF(online.date_created IS NOT NULL AND TIMESTAMPDIFF(SECOND, online.date_created, NOW()) <= 180, 1, 0) AS `is_online`
FROM cms_comments i
LEFT JOIN cms_users AS u ON u.id = i.user_id
LEFT JOIN cms_sessions_online AS online ON online.user_id = u.id
LEFT JOIN cms_comments_rating AS r ON r.comment_id = i.id AND r.user_id='1'
WHERE (i.is_deleted IS NULL) AND (i.is_approved = '1')
ORDER BY i.date_pub DESC
LIMIT 0, 20
Время выполнения 0.00085 секунд

Кто-нибудь из понимающих сможет мне объяснить в чем дело?

#2 12 июня 2019 в 10:14

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

То есть у вас есть миллион комментов.Один коммент должен получить инфу о пользователе и узнать этот пользователь онлайн или оффлайн.

В изначальном запросе один коммент определял сначала всех пользователей кто онлайн и потом в них искал своего автора. И все это помноженное на миллион раз.

После правки у вас появилась одна таблица индексов с информацией о конкретных пользователях из которой сразу берется инфа о конкретном пользователе и вычисляется его статус онлайн.

Кстати, для эксперимента, если поменять местами — сделать сначала user_id, а потом date_created — то сколько времени займет это в мариидб?

#3 12 июня 2019 в 16:33

Стал экспериментировать с индексами таблицы cms_sessions_online. И выяснил интересную вещь. Если добавить в индекс date_created столбец user_id, то запросы в комментариях начинают выполняться за ноль секунд. Всё опять летает!
Ris

Проверил, так и есть. Даже при небольшом количестве зарегистрированных пользователей, скорость авторизации на сайте выросла!
Так же таблица cms_sessions_online часто влияет на любой серфинг по сайту. После добавления в индекс date_created столбец user_id, выросла и скорость серфинга.

По рефке 10% скидка на мощный и гибкий хостинг на NVMе дисках! Дешевые домены здесь! Чат-боты для любых соцсетей

HTTP ERROR 500

Массовое добавление тега к зап

Вы не можете отвечать в этой теме.
Войдите или зарегистрируйтесь, чтобы писать на форуме.

Mysql. Игры разума.

Похожие темы

Игры для пользователей на сайт

Запрос mysql вывод дублей на странице. Сохранения страницы html как таблицу XSL

Игры на сайте

[ЕСТЬ РЕШЕНИЕ] Создаётся большая нагрузка на сервере и сайтом дублируются запоосы вида. mysql> show processlist; Как решить???

Как оптимизировать запросы к БД, чтобы они не выполнялись на протяжении такого количества времени.

MySQL выборка по нескольким условиям

InstantCMS Team

О проекте

Поддержка

Дополнения