Как оптимизировать и ускаорить данный запрос к базе данных MySQL Zabbix?

Question

[ArtiomK]

Я создаю веб приложение вокруг базы данных Zabbix (MySQL), я хотел бы оптимизировать и ускорить следующий запрос:

```
SELECT distinct(hosts.hostid), max(CONVERT(CONCAT(events.eventid, events.value, events.severity), UNSIGNED))
FROM hosts
INNER JOIN hosts_groups ON hosts.hostid = hosts_groups.hostid
INNER JOIN hstgrp ON hosts_groups.groupid = hstgrp.groupid

INNER JOIN items ON hosts.hostid = items.hostid
INNER JOIN functions ON items.itemid = functions.itemid
INNER JOIN events ON functions.triggerid = events.objectid
WHERE events.name = %s
AND hstgrp.groupid = %s
AND hosts.status != 3 # 3 - not templates
GROUP BY hosts.hostid;
```

Я делаю 7 таких запросов внутри одного запроса http GET к веб приложению на основе Flask. Общее время этих 7 запросов варьируется от 10 до 70 секунд, что я связываю с тем, что таблица events постоянно меняется. Таблица events содержит большое кол-во записей.

Результаты Explain:



Как можно ускорить данный запрос?

Сейчас я добавляю Redis для хранения результатов этих запросов в течение 5 минут, далее наверно добавлю Celery, чтобы убрать такие долгие по времени запросы из http сервера. Но это о ускорении приложения в целом.

Comments

[d'Ivan]

https://dev.mysql.com/doc/refman/8.0/en/using-expl...

[ArtiomK]

Роман Мирр, знаком с этой командой )))

[d'Ivan]

ArtiomK, так если знаком, то в чем трудности?
Мы ведь не знаем какой план запроса.

[ArtiomK]

Роман Мирр, интересуюсь как Заббикс настолько нагружает таблицу ивентов, что разница во времени выполнения одного и того же запроса в 10 раз. Я например могу попробовать ограничить временной промежуток ивента по верхней границе, могу отсечь по нижней границе, правда это будет не совсем верно - например упала точка 1 месяц назад и больше не восстановилась, если брать промежуток меньше месяца, то точка и не падала - нет ивента в этом промежутку времени.

[d'Ivan]

ArtiomK, а что можно советовать, даже не видя вывода EXPLAIN?

[ArtiomK]

Роман Мирр, проблема из-за поиска по этой таблице events - около 1 млн записей. Она еще часто обновляется новыми записями. Читал в одной статье, что Zabbix еще эту таблицу и чистит периодически.

[d'Ivan]

ArtiomK, я все понимаю, но результата вывода с EXPLAIN так и не вижу до сих пор.

[ArtiomK]

Роман Мирр, добавил результаты expain в описание вопроса, действительно только написал, что таблица ивентов быстро меняется не уточнив её размер, согласен оставляет неопределенность.

Answer 1

[d'Ivan]

2-я строка в EXPLAIN говорит, что СУБД должна пройтись по всем 958 тыс. записей для того, чтобы выбрать из них нужные по условию where. Насколько я понимаю, WHERE events.name = %s.
Предлагаю производить выборку не по полю name, а по тому, у которого есть индекс. Или создать индекс по name и другим полям, по которым производится поиск..

Также можно использовать партицирование таблицы events.

Если и этого не хватит, то из-за тяжелых запросов к СУБД имеет смысл создать дополнительную таблицу, которую обновлять планировщиком задач. Она будет представлять из себя материализованное представление, оптимизированное для обращений веб-приложения.

Answer 2

[ArtiomK]

Для того чтобы получить список текущих проблем и недавно завершеных проблем существует отдельная таблица problem