Просьба ко всем разработчикам модулей поработать над индексами в таблицах.
я включил логирование запросов без индексов и за две минут отловил вот столько:
СпойлерПоказать
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);
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 - тоже очень плохая ситуация.