Warning: Cannot use a scalar value as an array in /home/admin/public_html/forum/include/fm.class.php on line 757
Warning: Invalid argument supplied for foreach() in /home/admin/public_html/forum/include/fm.class.php on line 770
Warning: Invalid argument supplied for foreach() in /home/admin/public_html/forum/topic.php on line 737 Форумы портала PHP.SU :: Оптимизация SQL запросов
Покинул форум
Сообщений всего: 319
Дата рег-ции: Окт. 2011
Помог: 3 раз(а)
Приветствую, нужна помощь с оптимизацией SQL запросов, проблема в том что данные SQL запросы, по времени занимают от 2-х до 14 секунд. Скажу сразу таблица node_data имеет более 4-х миллионов записей
Покинул форум
Сообщений всего: 10732
Дата рег-ции: Нояб. 2010
Помог: 322 раз(а)
nordghost DDL это CREATE TABLE
просили показать структуру
это оба запроса подряд 2-14 сек или каждый по отдельности? (Добавление)
навскидку не хватает индекса node_data.captured_datetime
а вот индекс sensor_type возможно лишний, какая у него селективность? если пара-тройка вариантов то нет смысла в нем
Покинул форум
Сообщений всего: 319
Дата рег-ции: Окт. 2011
Помог: 3 раз(а)
LIME пишет:
nordghost DDL это CREATE TABLE
просили показать структуру
это оба запроса подряд 2-14 сек или каждый по отдельности? (Добавление)
навскидку не хватает индекса node_data.captured_datetime
а вот индекс sensor_type возможно лишний, какая у него селективность? если пара-тройка вариантов то нет смысла в нем
ничего здесь не смущает?))
node_data_FK и node_data_FK_2 дублируются
убери один
иначе тормозит DML и занимает место в кэше тормозя выборку вымыванием данных
по по воду sensor_type писал выше, надо подумать
(`id`,`node_data_FK`,`sensor_type`,`value`) - то есть кладем в индекс все поля?
кхе кхе ... в InnoDB первичный ключ является кластерным
то есть в id итак уже лежат все эти поля и он является покрывающим по определению - удалить однозначно
nordghost
Отправлено: 10 Октября, 2017 - 11:16:16
Посетитель
Покинул форум
Сообщений всего: 319
Дата рег-ции: Окт. 2011
ничего здесь не смущает?))
node_data_FK и node_data_FK_2 дублируются
убери один
иначе тормозит DML и занимает место в кэше тормозя выборку вымыванием данных
по по воду sensor_type писал выше, надо подумать
(`id`,`node_data_FK`,`sensor_type`,`value`) - то есть кладем в индекс все поля?
кхе кхе ... в InnoDB первичный ключ является кластерным
то есть в id итак уже лежат все эти поля и он является покрывающим по определению - удалить однозначно
Покинул форум
Сообщений всего: 11926
Дата рег-ции: Июль 2009 Откуда: Россия, Санкт-Петербург
Помог: 618 раз(а)
nordghost пишет:
Первый запрос - 3 строки
До агрегации?
Сколько после остаётся не очень интересно. Всё равно полезет сортировать временную табличку
Индекс по device_FK, node_FK и captured_datetime по node_data добавьте и ещё раз explain второго запроса. Лучше нормальным текстом, а не наскальной живописью.
Первый запрос. Хм, 3 строки после агрегации. Хм.
Индекс по device_node_mapping device_id & node_id и с учётом индекса по node_data для второго запроса должно зашевелиться довольно внятно вот так:
JOIN`device_node_mapping`ON device_node_mapping.node_id = node.node_id AND device_node_mapping.device_id=6145686
ORDERBY`captured_datetime`
Интересно, mysql сумеет сделать loose index only scan такого подзапроса?
----- PostgreSQL DBA
LIME
Отправлено: 10 Октября, 2017 - 11:52:54
Активный участник
Покинул форум
Сообщений всего: 10732
Дата рег-ции: Нояб. 2010
Помог: 322 раз(а)
Мелкий изза подзапроса потеряем сортировку по индексу captured_datetime
не?
Мелкий
Отправлено: 10 Октября, 2017 - 12:23:49
Активный участник
Покинул форум
Сообщений всего: 11926
Дата рег-ции: Июль 2009 Откуда: Россия, Санкт-Петербург
Помог: 618 раз(а)
3 строки отсортировать ничего не стоит. А при живых джойнах и агрегации - смысла в index sort как-то не много. Даже если в таком окружении mysql вообще умеет брать сортировку с индекса - а то у меня смутное ощущение, что любой не входящий в индекс фильтр (например джойн) отрубает оптимизацию чтения порядка из индекса. И группировка опять же. Фактически сортировка по вычисляемому полю. Сомнительно, в общем.
Лишь бы loose index scan заработал.
----- PostgreSQL DBA
LIME
Отправлено: 10 Октября, 2017 - 12:30:27
Активный участник
Покинул форум
Сообщений всего: 10732
Дата рег-ции: Нояб. 2010
Помог: 322 раз(а)
у тебя указано что это второй запрос а на самом деле первый
бегло глянул и запутался
да (Добавление)
Мелкий пишет:
любой не входящий в индекс фильтр (например джойн) отрубает оптимизацию чтения порядка из индекса
не думаю
если сортировать по полю внешней таблицы(которая FROM) и вспомнить что в мскл JOIN это nested loop и поле участвует в фильтре, то становится ожидаемо что сначала произойдет выборка по индексу в нужном направлении и лишь потом произойдет внутренние циклы JOINов
Все гости форума могут просматривать этот раздел. Только зарегистрированные пользователи могут создавать новые темы в этом разделе. Только зарегистрированные пользователи могут отвечать на сообщения в этом разделе.