!!!![Важно]Индексы

Разработка дополнительных модулей, подключение различных приложений.

Модератор: immortal

Ответить
stellhawk
Сообщения: 264
Зарегистрирован: Чт ноя 08, 2018 5:51 am
Благодарил (а): 10 раз
Поблагодарили: 83 раза

!!!![Важно]Индексы

Сообщение stellhawk » Вс дек 22, 2019 8:28 am

Сергей, в тебе личное обращение обрати внимание на ядро, там во многих местах не хватает индексов, как минимум вот это надо сделать :ALTER TABLE `phistory` ADD INDEX(VALUE_ID,ID);

Просьба ко всем разработчикам модулей поработать над индексами в таблицах.
я включил логирование запросов без индексов и за две минут отловил вот столько:
СпойлерПоказать
ALTER TABLE `shouts` ADD INDEX(`MEMBER_ID`);
ALTER TABLE `gpsdevices` ADD INDEX(`DEVICEID`);
ALTER TABLE `operations_queue` ADD INDEX(`EXPIRE`);
ALTER TABLE `elm_states` ADD INDEX(`ELEMENT_ID`);
ALTER TABLE `onvif_commands` ADD INDEX(`LINKED_OBJECT`);
ALTER TABLE `onvif_commands` ADD INDEX(`LINKED_PROPERTY`);
ALTER TABLE `devices_linked` ADD INDEX(`DEVICE1_ID`);
ALTER TABLE `devices_linked` ADD INDEX(`DEVICE2_ID`);
ALTER TABLE `mqtt` ADD INDEX(`LINKED_OBJECT`);
ALTER TABLE `mqtt` ADD INDEX(`LINKED_PROPERTY`);
ALTER TABLE `mqtt` ADD INDEX(`READONLY`);
ALTER TABLE `operations_queue` ADD INDEX(`TOPIC`);
ALTER TABLE `operations_queue` ADD INDEX(`DATANAME`);
ALTER TABLE `miio_commands` ADD INDEX(`LINKED_OBJECT`);
ALTER TABLE `miio_commands` ADD INDEX(`LINKED_PROPERTY`);
ALTER TABLE `shouts` ADD INDEX(`ADDED`);
ALTER TABLE `scenes` ADD INDEX(`HIDDEN`);
ALTER TABLE `elements` ADD INDEX(`SCENE_ID`);
ALTER TABLE `zmrn0808_relays` ADD INDEX(`LINKED_OBJECT`);
ALTER TABLE `zmrn0808_relays` ADD INDEX(`LINKED_PROPERTY`);
ALTER TABLE `mqtt` ADD INDEX(`PATH`);
ALTER TABLE `zigbee2mqtt` ADD INDEX(`PATH`);
ALTER TABLE `zigbee2mqtt` ADD INDEX(`METRIKA`);
ALTER TABLE `zigbee2mqtt_devices` ADD INDEX(`IEEEADDR`);
ALTER TABLE `zigbee2mqtt_devices` ADD INDEX(`TITLE`);
ALTER TABLE `miio_commands` ADD INDEX(`DEVICE_ID`);
ALTER TABLE `miio_commands` ADD INDEX(`TITLE`);
ALTER TABLE `phistory` ADD INDEX(VALUE_ID,ID);
В целом общие правила для создания индексов:
1. Если вы делаете поиск по строке: используете строку в выражении where - она должна быть индексом.
2. Поля LINKED_OBJECT и LINKED_PROPERTY должны быть индексами. Вы их сами не используете для поиска, но ядро мажордомо это делает!!!
3. Внешние ключи должны быть индексами
4. К полям по которым происходит сортировка надо отнестись ответственно. как минимум они должны быть индексными.
5. Составные ключи должны быть именно в той последовательности в какой Вы их используете. лучше не менять последовательность в разных местах, оптимизатор mysql не всегда правильно отрабатывает.

Пример:
Система постоянно делает запросы в базу вот такого содержания:
SELECT * FROM phistory WHERE VALUE_ID='1514' ORDER BY ID DESC LIMIT 2;
данные запросы при достижении таблицы 500 тыс записей стали выполняться 4-6 секунд
эти запросы делает сама система в цикле оптимизации истории значений.

# Query_time: 6.645977 Lock_time: 0.000148 Rows_sent: 2 Rows_examined: 382416
SELECT * FROM phistory WHERE VALUE_ID='1514' ORDER BY ID DESC LIMIT 2;

не смотря на то что по value_id есть индекс, затронутых полей при запросе 382 тыс.
это все потому что для сортировки нужен составной ключ:
ALTER TABLE `phistory` ADD INDEX(VALUE_ID,ID);
Если сделать ключ в обратной последовательности (ID,VALUE_ID) то это не помогает.

как проверить запрос:
идем в phpmyadmin и добавляем к запросу в начало слово EXPLAIN
EXPLAIN SELECT * FROM phistory WHERE VALUE_ID='1514' ORDER BY ID DESC LIMIT 2
в результате запроса мы видим какие индексы использовались(key) Сколько строк попало в результат(rows) и есть поле Extra, в нём описывается какие манипуляции пришлось делать серверу.
какие есть варианты ответов можно посмотреть тут: http://www.mysql.ru/docs/man/EXPLAIN.html
например запись:
Using filesort - плохая запись -системе пришлось сохранить результат, выгрузить его и отсортировать а потом только выбрать два поля, что очень долго
Using temporary - тоже очень плохая ситуация.
За это сообщение автора stellhawk поблагодарили (всего 10):
xor (Вс дек 22, 2019 12:07 pm) • GIV_59 (Вс дек 22, 2019 12:44 pm) • Samir77 (Вс дек 22, 2019 6:48 pm) • Logrus (Вс дек 22, 2019 10:09 pm) • directman66 (Пн дек 23, 2019 6:32 am) • TrDA (Ср дек 25, 2019 12:33 pm) • nightwind (Пт дек 27, 2019 7:08 pm) • Nail (Сб дек 28, 2019 2:51 pm) • cplusmax (Вт янв 14, 2020 5:25 pm) • znachok88 (Пн мар 02, 2020 10:26 pm)
Рейтинг: 11.63%
stellhawk
Сообщения: 264
Зарегистрирован: Чт ноя 08, 2018 5:51 am
Благодарил (а): 10 раз
Поблагодарили: 83 раза

Re: !!!![Важно]Индексы

Сообщение stellhawk » Вс дек 22, 2019 8:29 am

Учитывая что большинство пользователей используют маломощные системы. я считаю, что работа над оптимизацией работы с базой должна быть постоянной.
За это сообщение автора stellhawk поблагодарили (всего 5):
xor (Вс дек 22, 2019 12:22 pm) • GIV_59 (Вс дек 22, 2019 12:44 pm) • Samir77 (Вс дек 22, 2019 6:49 pm) • nightwind (Вс дек 22, 2019 9:26 pm) • TrDA (Ср дек 25, 2019 12:33 pm)
Рейтинг: 5.81%
stellhawk
Сообщения: 264
Зарегистрирован: Чт ноя 08, 2018 5:51 am
Благодарил (а): 10 раз
Поблагодарили: 83 раза

Re: !!!![Важно]Индексы

Сообщение stellhawk » Вс дек 22, 2019 8:32 am

как добавить индексы в модуль?
идем в функцию dbInstall своего модуля
и добавляем строки
AliIPRelay: INDEX LINKED_OBJECT (LINKED_OBJECT)
пример тут:
https://github.com/stell-hawk/Majordomo ... s.php#L426
За это сообщение автора stellhawk поблагодарили (всего 2):
xor (Вс дек 22, 2019 12:22 pm) • cplusmax (Вт янв 14, 2020 5:12 pm)
Рейтинг: 2.33%
Аватара пользователя
xor
Сообщения: 2038
Зарегистрирован: Сб ноя 22, 2014 8:45 pm
Благодарил (а): 284 раза
Поблагодарили: 629 раз

Re: !!!![Важно]Индексы

Сообщение xor » Вс дек 22, 2019 12:27 pm

Очень важная тема, вот вам 3 спасибо! У меня это не так заметно, но курочка по зернышку клюет(
За это сообщение автора xor поблагодарил:
Samir77 (Вс дек 22, 2019 6:49 pm)
Рейтинг: 1.16%
stellhawk
Сообщения: 264
Зарегистрирован: Чт ноя 08, 2018 5:51 am
Благодарил (а): 10 раз
Поблагодарили: 83 раза

Re: !!!![Важно]Индексы

Сообщение stellhawk » Вс дек 22, 2019 5:51 pm

xor писал(а):
Вс дек 22, 2019 12:27 pm
Очень важная тема, вот вам 3 спасибо! У меня это не так заметно, но курочка по зернышку клюет(
спасибо. у меня тоже тормозов не замечается. процессы в основном из фона такие работают. но на распбери уже были проблемы.
За это сообщение автора stellhawk поблагодарили (всего 2):
Samir77 (Вс дек 22, 2019 6:49 pm) • directman66 (Пн дек 23, 2019 6:32 am)
Рейтинг: 2.33%
Аватара пользователя
nightwind
Сообщения: 333
Зарегистрирован: Вс июн 26, 2016 4:42 pm
Откуда: Барановичи
Благодарил (а): 28 раз
Поблагодарили: 52 раза
Контактная информация:

Re: !!!![Важно]Индексы

Сообщение nightwind » Вс дек 22, 2019 9:27 pm

Давно у себя это все проделал, LA упал в 3 раза.
но там есть еще один косяк - по 300 запросов в секунду, и много апдэйт и делит среди них.
Logrus
Сообщения: 2079
Зарегистрирован: Пт апр 07, 2017 12:20 pm
Благодарил (а): 313 раз
Поблагодарили: 457 раз

Re: !!!![Важно]Индексы

Сообщение Logrus » Вс дек 22, 2019 10:08 pm

nightwind писал(а):
Вс дек 22, 2019 9:27 pm
Давно у себя это все проделал, LA упал в 3 раза.
но там есть еще один косяк - по 300 запросов в секунду, и много апдэйт и делит среди них.
это что ты с ней сделал что б 300 было, у меня 90 в сек
Telegram | Блог
Raspberry Pi3, с образа от Сергея 3.31, PHP 7, флешка 16 Гб работает с 10.09.2017
Почти всё время уходит на исправление ошибок, оставшееся - на их повторение. (с) ))) Спасибо
Logrus
Сообщения: 2079
Зарегистрирован: Пт апр 07, 2017 12:20 pm
Благодарил (а): 313 раз
Поблагодарили: 457 раз

Re: !!!![Важно]Индексы

Сообщение Logrus » Вт дек 24, 2019 9:13 am

stellhawk писал(а):
Вс дек 22, 2019 8:28 am
ALTER TABLE `phistory` ADD INDEX(VALUE_ID,ID);

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

В целом общие правила для создания индексов:
1. Если вы делаете поиск по строке: используете строку в выражении where - она должна быть индексом.
2. Поля LINKED_OBJECT и LINKED_PROPERTY должны быть индексами. Вы их сами не используете для поиска, но ядро мажордомо это делает!!!
3. Внешние ключи должны быть индексами
4. К полям по которым происходит сортировка надо отнестись ответственно. как минимум они должны быть индексными.
5. Составные ключи должны быть именно в той последовательности в какой Вы их используете. лучше не менять последовательность в разных местах, оптимизатор mysql не всегда правильно отрабатывает.
как-то все промолчали, добавлю пять копеек, индексы это не только добро, но и зло

1. напомню в config.php есть

Код: Выделить всё

//Define('SEPARATE_HISTORY_STORAGE',1); //STORE HISTORY DATA IN DIFFERENT TABLES
2. в модулях в commands не помешает и не повредит, и эффект будет не заметен

п.с. а вот блокировки таблиц - напрягают
Telegram | Блог
Raspberry Pi3, с образа от Сергея 3.31, PHP 7, флешка 16 Гб работает с 10.09.2017
Почти всё время уходит на исправление ошибок, оставшееся - на их повторение. (с) ))) Спасибо
Ответить