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

[stellhawk]

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

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

SPOILERSPOILER_SHOW

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]

Учитывая что большинство пользователей используют маломощные системы. я считаю, что работа над оптимизацией работы с базой должна быть постоянной.

[stellhawk]

как добавить индексы в модуль?
идем в функцию dbInstall своего модуля
и добавляем строки
AliIPRelay: INDEX LINKED_OBJECT (LINKED_OBJECT)
пример тут:
https://github.com/stell-hawk/Majordomo ... s.php#L426

[xor]

Очень важная тема, вот вам 3 спасибо! У меня это не так заметно, но курочка по зернышку клюет(

[stellhawk]

Очень важная тема, вот вам 3 спасибо! У меня это не так заметно, но курочка по зернышку клюет(

спасибо. у меня тоже тормозов не замечается. процессы в основном из фона такие работают. но на распбери уже были проблемы.

[nightwind]

Давно у себя это все проделал, LA упал в 3 раза.
но там есть еще один косяк - по 300 запросов в секунду, и много апдэйт и делит среди них.

[Logrus]

Давно у себя это все проделал, LA упал в 3 раза.
но там есть еще один косяк - по 300 запросов в секунду, и много апдэйт и делит среди них.

это что ты с ней сделал что б 300 было, у меня 90 в сек

[Logrus]

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 не помешает и не повредит, и эффект будет не заметен

п.с. а вот блокировки таблиц - напрягают