Задать вопрос

Почему этот запрос выводит из строя mysql?

Версия mysql = 8
Не то, чтобы я опытный в mysql, но для меня полтергейст.

Итак занимаюсь, чем умею на практике долблю в одиночку mysql, таблица довольно большая на 10 миллионов строк.
Делаю такой запрос:
SELECT id, time, ROW_NUMBER() OVER (PARTITION BY phone ORDER BY time DESC) AS n FROM sms WHERE
time > NOW() - INTERVAL 6 MONTH AND time < NOW() - INTERVAL 6 HOUR;

Срабатывает норм, в 5-10 секунд укладывается.

Далее добавляю это:
SELECT * FROM (SELECT id, time, ROW_NUMBER() OVER (PARTITION BY phone ORDER BY time DESC) AS n FROM sms WHERE
time > NOW() - INTERVAL 6 MONTH AND time < NOW() - INTERVAL 6 HOUR) t WHERE n <=4;

Тоже нормально.

А теперь просто трындец, пишу так и mysql виснет (не помогает systemctl restart mysql)
И не помогает reboot серверу (только с физической кнопки)
Пробовал на двух разных серверах.
SELECT * FROM (SELECT id, time, ROW_NUMBER() OVER (PARTITION BY phone ORDER BY time DESC) AS n FROM sms WHERE
time > NOW() - INTERVAL 6 MONTH AND time < NOW() - INTERVAL 6 HOUR) t WHERE n=4;


Видимо я что-то делаю не так и не понимаю, но логически мне кажется ничего особенного.
  • Вопрос задан
  • 984 просмотра
Подписаться 4 Простой 47 комментариев
Решения вопроса 1
ipatiev
@ipatiev
Потомок старинного рода Ипатьевых-Колотитьевых
Первый уровень оптимизации запросов - это, разумеется, индексы.

Но иногда даже если все нужные индексы есть, база тупит неимоверно. В этом случае надо сразу смотреть значение innodb_buffer_pool_size. По умолчанию значение этого параметра всего 128 мегабайт. А это, грубо говоря, максимальное значение памяти, доступное MySQL для работы.

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

Разумеется, у mysql есть просто миллион разных настроек, которые тоже влияют на производительность. У Перконы, кажется, даже есть калькулятор, в который ты заливаешь информацию о своей базе, а он тебе выдаёт значения параметров конфигурации. Но это уже такая тонкая настройка, третий уровень. Ничего из этого не будет работать, если innodb_buffer_pool_size недостаточного размера. И его размер рекомендуется выставлять на максимально допустимое значение. Если это выделенный mysql сервер, то 80-90% от физической памяти. Если не выделенный - то столько, сколько не жалко, чтобы не мешать остальным приложениям.
Ответ написан
Пригласить эксперта
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Похожие вопросы