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 запросов

 PHP.SU

Программирование на PHP, MySQL и другие веб-технологии
PHP.SU Портал     На главную страницу форума Главная     Помощь Помощь     Поиск Поиск     Поиск Яндекс Поиск Яндекс     Вакансии  Пользователи Пользователи


 Страниц (2): [1] 2 »   

> Описание: Медленная работа
nordghost
Отправлено: 10 Октября, 2017 - 09:54:07
Post Id



Посетитель


Покинул форум
Сообщений всего: 319
Дата рег-ции: Окт. 2011  


Помог: 3 раз(а)




Приветствую, нужна помощь с оптимизацией SQL запросов, проблема в том что данные SQL запросы, по времени занимают от 2-х до 14 секунд. Скажу сразу таблица node_data имеет более 4-х миллионов записей
CODE (SQL):
скопировать код в буфер обмена
  1.  
  2. SELECT `node`.`id`, `node`.`node_id`, `device_node_mapping`.`id` AS `nodeNameId`, `node`.`node_type`, MAX(node_data.captured_datetime) AS captured_datetime, `device_node_mapping`.`node_name` AS `nodeName` FROM `node_data` JOIN `node` ON node_data.node_FK = node.id JOIN `device_node_mapping` ON device_node_mapping.node_id = node.node_id AND device_node_mapping.device_id='6145686' WHERE `node_data`.`device_FK`='199' GROUP BY `node`.`node_id` ORDER BY `captured_datetime`
  3.  


CODE (SQL):
скопировать код в буфер обмена
  1.  
  2. SELECT `node_FK` AS `node_id`, `device_node_mapping`.`node_name`, `sensor_data`.`sensor_type`, `node_data`.`captured_datetime`, `sensor_data`.`value` FROM `node_data` JOIN `sensor_data` ON sensor_data.node_data_FK = node_data.id LEFT JOIN `device_node_mapping` ON device_node_mapping.node_id = node_FK WHERE ((`device_FK`='199') AND (`node_FK`='514') AND (`sensor_type`='HUM')) AND (`node_data`.`captured_datetime` >= '2015-10-09 17:26:18 00:00:00') AND (`node_data`.`captured_datetime` < '2016-12-17 06:40:23 23:59:59') ORDER BY `captured_datetime`
  3.  


Заранее благодарю за любую помощь и подсказку!


-----
<a href="http://netbeans[dot]org/">Моя любимая IDE</a> :3
 
My status
 Top
Мелкий Супермодератор
Отправлено: 10 Октября, 2017 - 10:09:49
Post Id



Активный участник


Покинул форум
Сообщений всего: 11926
Дата рег-ции: Июль 2009  
Откуда: Россия, Санкт-Петербург


Помог: 618 раз(а)




Сколько строк попадает в аггрегацию?
Покажите DDL и explain.


-----
PostgreSQL DBA
 
 Top
nordghost
Отправлено: 10 Октября, 2017 - 10:41:30
Post Id



Посетитель


Покинул форум
Сообщений всего: 319
Дата рег-ции: Окт. 2011  


Помог: 3 раз(а)




Мелкий пишет:
Сколько строк попадает в аггрегацию?
Покажите DDL и explain.


Первый запрос - 3 строки
Второй запрос - 944 строки - иногда доходит до 5000-10000 тысяч

Explain-ы
http://prntscr[dot]com/gvi77c
http://prntscr.com/gvi7js

(Отредактировано автором: 10 Октября, 2017 - 10:43:10)



-----
<a href="http://netbeans[dot]org/">Моя любимая IDE</a> :3
 
My status
 Top
LIME
Отправлено: 10 Октября, 2017 - 10:51:52
Post Id


Активный участник


Покинул форум
Сообщений всего: 10732
Дата рег-ции: Нояб. 2010  


Помог: 322 раз(а)




nordghost DDL это CREATE TABLE
просили показать структуру
это оба запроса подряд 2-14 сек или каждый по отдельности?
(Добавление)
навскидку не хватает индекса node_data.captured_datetime
а вот индекс sensor_type возможно лишний, какая у него селективность? если пара-тройка вариантов то нет смысла в нем

(Отредактировано автором: 10 Октября, 2017 - 10:55:08)

 
 Top
nordghost
Отправлено: 10 Октября, 2017 - 10:59:42
Post Id



Посетитель


Покинул форум
Сообщений всего: 319
Дата рег-ции: Окт. 2011  


Помог: 3 раз(а)




LIME пишет:
nordghost DDL это CREATE TABLE
просили показать структуру
это оба запроса подряд 2-14 сек или каждый по отдельности?
(Добавление)
навскидку не хватает индекса node_data.captured_datetime
а вот индекс sensor_type возможно лишний, какая у него селективность? если пара-тройка вариантов то нет смысла в нем


Каждый по отдельности:
CODE (SQL):
скопировать код в буфер обмена
  1.  
  2. -- Дамп структуры для таблица dev_clouddashboard.device_node_mapping
  3. CREATE TABLE IF NOT EXISTS `device_node_mapping` (
  4.   `id` int(11) NOT NULL AUTO_INCREMENT,
  5.   `device_id` varchar(64) NOT NULL,
  6.   `node_id` int(11) NOT NULL,
  7.   `node_name` varchar(64) NOT NULL,
  8.   PRIMARY KEY (`id`),
  9.   KEY `node_id` (`node_id`),
  10.   KEY `device_id` (`device_id`)
  11. ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
  12.  
  13. -- Экспортируемые данные не выделены.
  14.  
  15.  
  16. -- Дамп структуры для таблица dev_clouddashboard.node
  17. CREATE TABLE IF NOT EXISTS `node` (
  18.   `id` int(11) NOT NULL AUTO_INCREMENT,
  19.   `node_id` varchar(32) NOT NULL,
  20.   `node_type` int(11) NOT NULL,
  21.   `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  22.   `interval_minutes` int(11) NOT NULL DEFAULT '60',
  23.   PRIMARY KEY (`id`),
  24.   KEY `node_id` (`node_id`)
  25. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  26.  
  27. -- Экспортируемые данные не выделены.
  28.  
  29.  
  30. -- Дамп структуры для таблица dev_clouddashboard.node_data
  31. CREATE TABLE IF NOT EXISTS `node_data` (
  32.   `id` int(11) NOT NULL AUTO_INCREMENT,
  33.   `device_FK` int(11) NOT NULL,
  34.   `node_FK` int(11) NOT NULL,
  35.   `captured_datetime` timestamp NULL DEFAULT NULL,
  36.   `received_datetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  37.   PRIMARY KEY (`id`),
  38.   KEY `device_FK` (`device_FK`),
  39.   KEY `node_FK` (`node_FK`),
  40.   KEY `received_datetime` (`received_datetime`),
  41.   CONSTRAINT `node_data_ibfk_1` FOREIGN KEY (`device_FK`) REFERENCES `device` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  42.   CONSTRAINT `node_data_ibfk_2` FOREIGN KEY (`node_FK`) REFERENCES `node` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
  43. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  44.  
  45. -- Экспортируемые данные не выделены.
  46.  
  47.  
  48. -- Дамп структуры для таблица dev_clouddashboard.sensor_data
  49. CREATE TABLE IF NOT EXISTS `sensor_data` (
  50.   `id` int(11) NOT NULL AUTO_INCREMENT,
  51.   `node_data_FK` int(11) NOT NULL,
  52.   `sensor_type` varchar(6) NOT NULL,
  53.   `value` varchar(64) NOT NULL,
  54.   PRIMARY KEY (`id`),
  55.   KEY `node_data_FK` (`node_data_FK`),
  56.   KEY `sensor_type` (`sensor_type`),
  57.   KEY `node_data_FK_2` (`node_data_FK`),
  58.   KEY `id` (`id`,`node_data_FK`,`sensor_type`,`value`)
  59. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  60.  
  61.  

(Отредактировано автором: 10 Октября, 2017 - 11:03:43)



-----
<a href="http://netbeans[dot]org/">Моя любимая IDE</a> :3
 
My status
 Top
LIME
Отправлено: 10 Октября, 2017 - 11:01:07
Post Id


Активный участник


Покинул форум
Сообщений всего: 10732
Дата рег-ции: Нояб. 2010  


Помог: 322 раз(а)




nordghost пишет:
(`node_data`.`captured_datetime` >= '2015-10-09 17:26:18 00:00:00') AND (`node_data`.`captured_datetime` < '2016-12-17 06:40:23 23:59:59')
лучше сделать через BETWEEN
побыстрее будет
(Добавление)
но только в случае наличия индекса
 
 Top
nordghost
Отправлено: 10 Октября, 2017 - 11:04:31
Post Id



Посетитель


Покинул форум
Сообщений всего: 319
Дата рег-ции: Окт. 2011  


Помог: 3 раз(а)




LIME пишет:
nordghost пишет:
(`node_data`.`captured_datetime` >= '2015-10-09 17:26:18 00:00:00') AND (`node_data`.`captured_datetime` < '2016-12-17 06:40:23 23:59:59')
лучше сделать через BETWEEN
побыстрее будет
(Добавление)
но только в случае наличия индекса


Ок, добавлю BETWEEN и индекс. Спасибо. Буду пробывать ковырять в этом направлении.

(Отредактировано автором: 10 Октября, 2017 - 11:05:07)



-----
<a href="http://netbeans[dot]org/">Моя любимая IDE</a> :3
 
My status
 Top
LIME
Отправлено: 10 Октября, 2017 - 11:09:20
Post Id


Активный участник


Покинул форум
Сообщений всего: 10732
Дата рег-ции: Нояб. 2010  


Помог: 322 раз(а)




nordghost пишет:
KEY `id` (`id`,`node_data_FK`,`sensor_type`,`value`)
а это что за трэш? для каких запросов?
 
 Top
nordghost
Отправлено: 10 Октября, 2017 - 11:10:39
Post Id



Посетитель


Покинул форум
Сообщений всего: 319
Дата рег-ции: Окт. 2011  


Помог: 3 раз(а)




LIME пишет:
nordghost пишет:
KEY `id` (`id`,`node_data_FK`,`sensor_type`,`value`)
а это что за трэш? для каких запросов?


Понятия не имею, не я это делал, мне всунули и приказали оптимизировать Радость Я так понял ключ этот лишний


-----
<a href="http://netbeans[dot]org/">Моя любимая IDE</a> :3
 
My status
 Top
LIME
Отправлено: 10 Октября, 2017 - 11:14:33
Post Id


Активный участник


Покинул форум
Сообщений всего: 10732
Дата рег-ции: Нояб. 2010  


Помог: 322 раз(а)




nordghost пишет:
  KEY `node_data_FK` (`node_data_FK`),
  KEY `sensor_type` (`sensor_type`),
  KEY `node_data_FK_2` (`node_data_FK`),
  KEY `id` (`id`,`node_data_FK`,`sensor_type`,`value`)
ничего здесь не смущает?))
node_data_FK и node_data_FK_2 дублируются
убери один
иначе тормозит DML и занимает место в кэше тормозя выборку вымыванием данных
по по воду sensor_type писал выше, надо подумать
(`id`,`node_data_FK`,`sensor_type`,`value`) - то есть кладем в индекс все поля?
кхе кхе ... в InnoDB первичный ключ является кластерным
то есть в id итак уже лежат все эти поля и он является покрывающим по определению - удалить однозначно
 
 Top
nordghost
Отправлено: 10 Октября, 2017 - 11:16:16
Post Id



Посетитель


Покинул форум
Сообщений всего: 319
Дата рег-ции: Окт. 2011  


Помог: 3 раз(а)




LIME пишет:
nordghost пишет:
  KEY `node_data_FK` (`node_data_FK`),
  KEY `sensor_type` (`sensor_type`),
  KEY `node_data_FK_2` (`node_data_FK`),
  KEY `id` (`id`,`node_data_FK`,`sensor_type`,`value`)
ничего здесь не смущает?))
node_data_FK и node_data_FK_2 дублируются
убери один
иначе тормозит DML и занимает место в кэше тормозя выборку вымыванием данных
по по воду sensor_type писал выше, надо подумать
(`id`,`node_data_FK`,`sensor_type`,`value`) - то есть кладем в индекс все поля?
кхе кхе ... в InnoDB первичный ключ является кластерным
то есть в id итак уже лежат все эти поля и он является покрывающим по определению - удалить однозначно


Спасибо большое!


-----
<a href="http://netbeans[dot]org/">Моя любимая IDE</a> :3
 
My status
 Top
Мелкий Супермодератор
Отправлено: 10 Октября, 2017 - 11:43:48
Post 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 для второго запроса должно зашевелиться довольно внятно вот так:
CODE (SQL):
скопировать код в буфер обмена
  1. SELECT `node`.`id`, `node`.`node_id`, `device_node_mapping`.`id` AS `nodeNameId`, `node`.`node_type`, last_captured_node.captured_datetime, `device_node_mapping`.`node_name` AS `nodeName`
  2. FROM (
  3.     SELECT node_FK, max(node_data.captured_datetime) AS captured_datetime
  4.     FROM `node_data`
  5.     WHERE `device_FK`=199
  6.     GROUP BY node_FK
  7. ) last_captured_node
  8. JOIN `node` ON last_captured_node.node_FK = node.id
  9. JOIN `device_node_mapping` ON device_node_mapping.node_id = node.node_id AND device_node_mapping.device_id=6145686
  10. ORDER BY `captured_datetime`

Интересно, mysql сумеет сделать loose index only scan такого подзапроса?


-----
PostgreSQL DBA
 
 Top
LIME
Отправлено: 10 Октября, 2017 - 11:52:54
Post Id


Активный участник


Покинул форум
Сообщений всего: 10732
Дата рег-ции: Нояб. 2010  


Помог: 322 раз(а)




Мелкий изза подзапроса потеряем сортировку по индексу captured_datetime
не?
 
 Top
Мелкий Супермодератор
Отправлено: 10 Октября, 2017 - 12:23:49
Post Id



Активный участник


Покинул форум
Сообщений всего: 11926
Дата рег-ции: Июль 2009  
Откуда: Россия, Санкт-Петербург


Помог: 618 раз(а)




3 строки отсортировать ничего не стоит. А при живых джойнах и агрегации - смысла в index sort как-то не много. Даже если в таком окружении mysql вообще умеет брать сортировку с индекса - а то у меня смутное ощущение, что любой не входящий в индекс фильтр (например джойн) отрубает оптимизацию чтения порядка из индекса. И группировка опять же. Фактически сортировка по вычисляемому полю. Сомнительно, в общем.
Лишь бы loose index scan заработал.


-----
PostgreSQL DBA
 
 Top
LIME
Отправлено: 10 Октября, 2017 - 12:30:27
Post Id


Активный участник


Покинул форум
Сообщений всего: 10732
Дата рег-ции: Нояб. 2010  


Помог: 322 раз(а)




у тебя указано что это второй запрос а на самом деле первый
бегло глянул и запутался
да
(Добавление)
Мелкий пишет:
любой не входящий в индекс фильтр (например джойн) отрубает оптимизацию чтения порядка из индекса
не думаю
если сортировать по полю внешней таблицы(которая FROM) и вспомнить что в мскл JOIN это nested loop и поле участвует в фильтре, то становится ожидаемо что сначала произойдет выборка по индексу в нужном направлении и лишь потом произойдет внутренние циклы JOINов
 
 Top
Страниц (2): [1] 2 »
Сейчас эту тему просматривают: 0 (гостей: 0, зарегистрированных: 0)
« Работа с СУБД »


Все гости форума могут просматривать этот раздел.
Только зарегистрированные пользователи могут создавать новые темы в этом разделе.
Только зарегистрированные пользователи могут отвечать на сообщения в этом разделе.
 



Powered by PHP  Powered By MySQL  Powered by Nginx  Valid CSS  RSS

 
Powered by ExBB FM 1.0 RC1. InvisionExBB