PHP.SU

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

Страниц (22): [1] 2 3 4 5 6 7 8 9 ... » В конец

> Найдено сообщений: 320
nordghost Отправлено: 14 Октября, 2017 - 12:47:41 • Тема: Оптимизация SQL запросов • Форум: Работа с СУБД

Ответов: 17
Просмотров: 2172
Мелкий пишет:
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 такого подзапроса?


После всех оптимизаций данный запрос занимает 0.5 секунды, это прогресс по сравнению с 3.2 изначально!!! Спасибо
(Добавление)
LIME пишет:
nordghost срочно нужен explain на второй запрос после индекса captured_datetime Радость
(Добавление)
CODE (SQL):
скопировать код в буфер обмена
  1. EXPLAIN FORMAT=JSON SELECT ...

(Добавление)
он вербознее


Вот експлкйн

CODE (javascript):
скопировать код в буфер обмена
  1.  
  2. {
  3.   "query_block": {
  4.     "select_id": 1,
  5.     "cost_info": {
  6.       "query_cost": "3410169.83"
  7.     },
  8.     "ordering_operation": {
  9.       "using_temporary_table": true,
  10.       "using_filesort": true,
  11.       "cost_info": {
  12.         "sort_cost": "1548168.53"
  13.       },
  14.       "nested_loop": [
  15.         {
  16.           "table": {
  17.             "table_name": "device_node_mapping",
  18.             "access_type": "ALL",
  19.             "rows_examined_per_scan": 524,
  20.             "rows_produced_per_join": 52,
  21.             "filtered": "10.00",
  22.             "cost_info": {
  23.               "read_cost": "100.37",
  24.               "eval_cost": "10.48",
  25.               "prefix_cost": "110.85",
  26.               "data_read_per_join": "7K"
  27.             },
  28.             "used_columns": [
  29.               "id",
  30.               "device_id",
  31.               "node_id",
  32.               "node_name"
  33.             ],
  34.             "attached_condition": "(`clouddashboard`.`device_node_mapping`.`device_id` = 6145686)"
  35.           }
  36.         },
  37.         {
  38.           "table": {
  39.             "table_name": "node",
  40.             "access_type": "ALL",
  41.             "possible_keys": [
  42.               "PRIMARY"
  43.             ],
  44.             "rows_examined_per_scan": 390,
  45.             "rows_produced_per_join": 2043,
  46.             "filtered": "10.00",
  47.             "using_join_buffer": "Block Nested Loop",
  48.             "cost_info": {
  49.               "read_cost": "1.03",
  50.               "eval_cost": "408.72",
  51.               "prefix_cost": "4199.07",
  52.               "data_read_per_join": "239K"
  53.             },
  54.             "used_columns": [
  55.               "id",
  56.               "node_id",
  57.               "node_type"
  58.             ],
  59.             "attached_condition": "(`clouddashboard`.`device_node_mapping`.`node_id` = `clouddashboard`.`node`.`node_id`)"
  60.           }
  61.         },
  62.         {
  63.           "table": {
  64.             "table_name": "last_captured_node",
  65.             "access_type": "ref",
  66.             "possible_keys": [
  67.               "<auto_key0>"
  68.             ],
  69.             "key": "<auto_key0>",
  70.             "used_key_parts": [
  71.               "node_FK"
  72.             ],
  73.             "key_length": "4",
  74.             "ref": [
  75.               "clouddashboard.node.id"
  76.             ],
  77.             "rows_examined_per_scan": 757,
  78.             "rows_produced_per_join": 1548168,
  79.             "filtered": "100.00",
  80.             "cost_info": {
  81.               "read_cost": "1548168.53",
  82.               "eval_cost": "309633.71",
  83.               "prefix_cost": "1862001.30",
  84.               "data_read_per_join": "23M"
  85.             },
  86.             "used_columns": [
  87.               "node_FK",
  88.               "captured_datetime"
  89.             ],
  90.             "materialized_from_subquery": {
  91.               "using_temporary_table": true,
  92.               "dependent": false,
  93.               "cacheable": true,
  94.               "query_block": {
  95.                 "select_id": 2,
  96.                 "cost_info": {
  97.                   "query_cost": "76295.40"
  98.                 },
  99.                 "grouping_operation": {
  100.                   "using_temporary_table": true,
  101.                   "using_filesort": true,
  102.                   "table": {
  103.                     "table_name": "node_data",
  104.                     "access_type": "ref",
  105.                     "possible_keys": [
  106.                       "device_FK",
  107.                       "node_FK"
  108.                     ],
  109.                     "key": "device_FK",
  110.                     "used_key_parts": [
  111.                       "device_FK"
  112.                     ],
  113.                     "key_length": "4",
  114.                     "ref": [
  115.                       "const"
  116.                     ],
  117.                     "rows_examined_per_scan": 295452,
  118.                     "rows_produced_per_join": 295452,
  119.                     "filtered": "100.00",
  120.                     "index_condition": "(`clouddashboard`.`node_data`.`device_FK` <=> 199)",
  121.                     "cost_info": {
  122.                       "read_cost": "17205.00",
  123.                       "eval_cost": "59090.40",
  124.                       "prefix_cost": "76295.40",
  125.                       "data_read_per_join": "6M"
  126.                     },
  127.                     "used_columns": [
  128.                       "id",
  129.                       "device_FK",
  130.                       "node_FK",
  131.                       "captured_datetime"
  132.                     ]
  133.                   }
  134.                 }
  135.               }
  136.             }
  137.           }
  138.         }
  139.       ]
  140.     }
  141.   }
  142. }
  143.  

(Добавление)
Дело ещё в том что node_data имеет очень много колонок и JOIN с node_data - это перебор кучи данных? Возможно ли задать какой либо лимит для JOIN-а?
nordghost Отправлено: 10 Октября, 2017 - 11:16:16 • Тема: Оптимизация SQL запросов • Форум: Работа с СУБД

Ответов: 17
Просмотров: 2172
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 итак уже лежат все эти поля и он является покрывающим по определению - удалить однозначно


Спасибо большое!
nordghost Отправлено: 10 Октября, 2017 - 11:10:39 • Тема: Оптимизация SQL запросов • Форум: Работа с СУБД

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


Понятия не имею, не я это делал, мне всунули и приказали оптимизировать Радость Я так понял ключ этот лишний
nordghost Отправлено: 10 Октября, 2017 - 11:04:31 • Тема: Оптимизация SQL запросов • Форум: Работа с СУБД

Ответов: 17
Просмотров: 2172
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 и индекс. Спасибо. Буду пробывать ковырять в этом направлении.
nordghost Отправлено: 10 Октября, 2017 - 10:59:42 • Тема: Оптимизация SQL запросов • Форум: Работа с СУБД

Ответов: 17
Просмотров: 2172
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.  
nordghost Отправлено: 10 Октября, 2017 - 10:41:30 • Тема: Оптимизация SQL запросов • Форум: Работа с СУБД

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


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

Explain-ы
http://prntscr[dot]com/gvi77c
http://prntscr[dot]com/gvi7js
nordghost Отправлено: 10 Октября, 2017 - 09:54:07 • Тема: Оптимизация SQL запросов • Форум: Работа с СУБД

Ответов: 17
Просмотров: 2172
Приветствую, нужна помощь с оптимизацией 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.  


Заранее благодарю за любую помощь и подсказку!
nordghost Отправлено: 10 Ноября, 2016 - 02:57:20 • Тема: Twig • Форум: Вопросы новичков

Ответов: 3
Просмотров: 225
any ideas?
nordghost Отправлено: 09 Ноября, 2016 - 09:52:07 • Тема: Twig • Форум: Вопросы новичков

Ответов: 3
Просмотров: 225
Здравствуйте, пилю платформу для инет магазинов, что то вроде shopify, каждый юзер может создать свой инет магазин и свой шаблон, как шаблонизатор планирую использовать twig, и дать пользователям самим клепать шаблоны как угодно, используя заранее определённые в контроллере переменные. Насколько безопасно будет давать юзерам доступ к созданию шаблонов через twig?
nordghost Отправлено: 03 Марта, 2016 - 17:19:15 • Тема: Яндекс Рейсы • Форум: Вопросы новичков

Ответов: 0
Просмотров: 88
Здравствуйте, есть необходимость парсить рейсы с Яндекса - Поезда самолёты и т.д. Когда Вылетает, Посмотрел API - это не проблема, но насколько я понял цену вытащить невозможно через их API а она мне нужна! Придёться без API ручками писать парсер другого сайта. Кто сталкивался с подобным? Заранее благодарю за помощь.
nordghost Отправлено: 13 Ноября, 2015 - 18:20:52 • Тема: вирус "реклама" • Форум: Прочее

Ответов: 8
Просмотров: 122
LIME пишет:
kuller открой для себя Linux и PhpStorm
поначалу будет непривычно но потом....проблемы закончатся
если ктото из старичков форума скажет что я не прав я готов .... простоять под холодным душем минут 10
только просьба не троллить))
(Добавление)
kuller хотя ты такой же известный персонаж как и я
за сим....пох


Или Netbeans)
nordghost Отправлено: 13 Ноября, 2015 - 18:09:18 • Тема: Нагрузка на БД • Форум: Вопросы новичков

Ответов: 12
Просмотров: 546
LIME пишет:
Только обязательно отпишись
Интересно как получилось


У меня не с адресами возня, у меня задача сделать простой генератор рабочего процесса с помощью визуального программирования на PHP+jQuery, Там есть условия и есть неслабая вложенность процессов и т.д.
nordghost Отправлено: 13 Ноября, 2015 - 12:06:26 • Тема: Нагрузка на БД • Форум: Вопросы новичков

Ответов: 12
Просмотров: 546
Мелкий пишет:
nordghost пишет:
в каждом городе район, в каждом районе улица, и в каждой улице дом, а в каждом доме - квартира

Ох до чего же все эти гипотезы далеки от действительности.
Небось, ещё предполагаете, что номер дома - это цифра?

nordghost пишет:
все их показать пользователю

Нафига? Нафига юзеру все эти мегабайты информации?
Обычно дают выбрать страну. Затем аяксом подтягивают регионы. А лучше автокомплитом. Но не выгружают весь массив юзеру.


Задача стоит не такая, в данном случае был упрощённый пример. Сама задача немного посложнее.
(Добавление)
LIME пишет:
Свести все в единую таблицу с полем "тип сущности"
Тоесть тип улица или страна или тд и тп
Реализовать в виде nested sets ...погуглить
Тогда можно будет тащить всю иерархию одним запросом
Или ветвь дерева
Второй вариант юзать кладр
Для кладр есть готовые решения
Зависит от движка
Тоже в гугл
Както так
(Добавление)
Nested sets это способ отображения иерархии в реляционной бд
Вернее одна из оптимизаций оного
Это так....для общего понимания


Спасибо будем пробовать.
nordghost Отправлено: 12 Ноября, 2015 - 22:46:23 • Тема: Чат • Форум: Программирование на PHP

Ответов: 18
Просмотров: 1704
max939393 пишет:
Добрый вечер всем))))
Есть такой вопрос, логично ли строить чат для сайта на файлах, т.е. хранить переписку в файлах на сервере. Или подскажите как лучше организовать чат, обмен сообщениями между 2 пользователями.


Самым логичным на сегодняшний день для создания является использование NodeJs, в сторону PHP+AJAX даже не смотрите. А самая популярная книга по Node на русском за первые 200 страниц расскажет как это сделать, а скринкаст который Вы можете загуглить расскажет как это сделать правильно!
nordghost Отправлено: 12 Ноября, 2015 - 22:36:25 • Тема: Нагрузка на БД • Форум: Вопросы новичков

Ответов: 12
Просмотров: 546
Вопрос больше связан с логикой чем с языком программирования. Допустим у нас есть страна, в каждой стране есть область, в каждой области город, в каждом городе район, в каждом районе улица, и в каждой улице дом, а в каждом доме - квартира, и каждая из этих сущностей разделена по отдельным таблицам, и связаны с помощью ID. В один прекрасный момент нам необходимо все их показать пользователю (прям вот так как есть соблюдая вложенность), и первое что приходит в голову это использовать циклы. Сначала выбрать все страны, потом в каждой стране выбрать города, потом в каждом городе район, и в каждом районе улицу и т.д. Что мы имеем в итоге? Тучу запросов к MySQL и это может закончиться очень печально. Как можно оптимизировать данное действие что бы снизить нагрузку на MySQL?

Страниц (22): [1] 2 3 4 5 6 7 8 9 ... » В конец
Powered by PHP  Powered By MySQL  Powered by Nginx  Valid CSS  RSS

 
Powered by ExBB FM 1.0 RC1. InvisionExBB