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. Игры разума.

Похожие темы

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

Что лучше MariaDB или MySQL ?

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

Игры на сайте

Изменение таблиц в MySQL

Миграции системы с Mysql на PostgreSQL

InstantCMS Team

О проекте

Поддержка

Дополнения