Форумы портала PHP.SU

PHP.SU

Программирование на PHP, MySQL и другие веб-технологии

PHP.SU Портал

Главная

Помощь

Поиск

Поиск Яндекс Вакансии

Пользователи

Здравствуйте, Гость

( Вход · Регистрация · Правила форума )

Забыли пароль?

Форумы портала PHP.SU » Список сообщений, автором которых является nordghost

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

Найдено сообщений: 320

nordghost

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

Ответов: 17
Просмотров: 2044

Мелкий пишет:

nordghost пишет:

Первый запрос - 3 строки

До агрегации?
Сколько после остаётся не очень интересно. Всё равно полезет сортировать временную табличку

Индекс по device_FK, node_FK и captured_datetime по node_data добавьте и ещё раз explain второго запроса. Лучше нормальным текстом, а не наскальной живописью.

Первый запрос. Хм, 3 строки после агрегации. Хм.
Индекс по device_node_mapping device_id & node_id и с учётом индекса по node_data для второго запроса должно зашевелиться довольно внятно вот так:

CODE (SQL):
скопировать код в буфер обмена

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` 
FROM (
    SELECT node_FK, max(node_data.captured_datetime) AS captured_datetime 
    FROM `node_data` 
    WHERE `device_FK`=199 
    GROUP BY node_FK
) last_captured_node 
JOIN `node` ON last_captured_node.node_FK = node.id 
JOIN `device_node_mapping` ON device_node_mapping.node_id = node.node_id AND device_node_mapping.device_id=6145686 
ORDER BY `captured_datetime`

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

После всех оптимизаций данный запрос занимает 0.5 секунды, это прогресс по сравнению с 3.2 изначально!!! Спасибо
(Добавление)

LIME пишет:

nordghost срочно нужен explain на второй запрос после индекса captured_datetime Радость

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

CODE (SQL):
скопировать код в буфер обмена

EXPLAIN FORMAT=JSON SELECT ...

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

Вот експлкйн

CODE (javascript):
скопировать код в буфер обмена

 
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "3410169.83"
    },
    "ordering_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "1548168.53"
      },
      "nested_loop": [
        {
          "table": {
            "table_name": "device_node_mapping",
            "access_type": "ALL",
            "rows_examined_per_scan": 524,
            "rows_produced_per_join": 52,
            "filtered": "10.00",
            "cost_info": {
              "read_cost": "100.37",
              "eval_cost": "10.48",
              "prefix_cost": "110.85",
              "data_read_per_join": "7K"
            },
            "used_columns": [
              "id",
              "device_id",
              "node_id",
              "node_name"
            ],
            "attached_condition": "(`clouddashboard`.`device_node_mapping`.`device_id` = 6145686)"
          }
        },
        {
          "table": {
            "table_name": "node",
            "access_type": "ALL",
            "possible_keys": [
              "PRIMARY"
            ],
            "rows_examined_per_scan": 390,
            "rows_produced_per_join": 2043,
            "filtered": "10.00",
            "using_join_buffer": "Block Nested Loop",
            "cost_info": {
              "read_cost": "1.03",
              "eval_cost": "408.72",
              "prefix_cost": "4199.07",
              "data_read_per_join": "239K"
            },
            "used_columns": [
              "id",
              "node_id",
              "node_type"
            ],
            "attached_condition": "(`clouddashboard`.`device_node_mapping`.`node_id` = `clouddashboard`.`node`.`node_id`)"
          }
        },
        {
          "table": {
            "table_name": "last_captured_node",
            "access_type": "ref",
            "possible_keys": [
              "<auto_key0>"
            ],
            "key": "<auto_key0>",
            "used_key_parts": [
              "node_FK"
            ],
            "key_length": "4",
            "ref": [
              "clouddashboard.node.id"
            ],
            "rows_examined_per_scan": 757,
            "rows_produced_per_join": 1548168,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "1548168.53",
              "eval_cost": "309633.71",
              "prefix_cost": "1862001.30",
              "data_read_per_join": "23M"
            },
            "used_columns": [
              "node_FK",
              "captured_datetime"
            ],
            "materialized_from_subquery": {
              "using_temporary_table": true,
              "dependent": false,
              "cacheable": true,
              "query_block": {
                "select_id": 2,
                "cost_info": {
                  "query_cost": "76295.40"
                },
                "grouping_operation": {
                  "using_temporary_table": true,
                  "using_filesort": true,
                  "table": {
                    "table_name": "node_data",
                    "access_type": "ref",
                    "possible_keys": [
                      "device_FK",
                      "node_FK"
                    ],
                    "key": "device_FK",
                    "used_key_parts": [
                      "device_FK"
                    ],
                    "key_length": "4",
                    "ref": [
                      "const"
                    ],
                    "rows_examined_per_scan": 295452,
                    "rows_produced_per_join": 295452,
                    "filtered": "100.00",
                    "index_condition": "(`clouddashboard`.`node_data`.`device_FK` <=> 199)",
                    "cost_info": {
                      "read_cost": "17205.00",
                      "eval_cost": "59090.40",
                      "prefix_cost": "76295.40",
                      "data_read_per_join": "6M"
                    },
                    "used_columns": [
                      "id",
                      "device_FK",
                      "node_FK",
                      "captured_datetime"
                    ]
                  }
                }
              }
            }
          }
        }
      ]
    }
  }
}
 

(Добавление)
Дело ещё в том что node_data имеет очень много колонок и JOIN с node_data - это перебор кучи данных? Возможно ли задать какой либо лимит для JOIN-а?

nordghost

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

Ответов: 17
Просмотров: 2044

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 Просмотров: 2044	LIME пишет: nordghost пишет: KEY `id` (`id`,`node_data_FK`,`sensor_type`,`value`) а это что за трэш? для каких запросов? Понятия не имею, не я это делал, мне всунули и приказали оптимизировать Я так понял ключ этот лишний

nordghost

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

Ответов: 17
Просмотров: 2044

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
Просмотров: 2044

LIME пишет:

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

Каждый по отдельности:

CODE (SQL):
скопировать код в буфер обмена

 
-- Дамп структуры для таблица dev_clouddashboard.device_node_mapping
CREATE TABLE IF NOT EXISTS `device_node_mapping` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `device_id` varchar(64) NOT NULL,
  `node_id` int(11) NOT NULL,
  `node_name` varchar(64) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `node_id` (`node_id`),
  KEY `device_id` (`device_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
-- Экспортируемые данные не выделены.
 
 
-- Дамп структуры для таблица dev_clouddashboard.node
CREATE TABLE IF NOT EXISTS `node` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `node_id` varchar(32) NOT NULL,
  `node_type` int(11) NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `interval_minutes` int(11) NOT NULL DEFAULT '60',
  PRIMARY KEY (`id`),
  KEY `node_id` (`node_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
-- Экспортируемые данные не выделены.
 
 
-- Дамп структуры для таблица dev_clouddashboard.node_data
CREATE TABLE IF NOT EXISTS `node_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `device_FK` int(11) NOT NULL,
  `node_FK` int(11) NOT NULL,
  `captured_datetime` timestamp NULL DEFAULT NULL,
  `received_datetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `device_FK` (`device_FK`),
  KEY `node_FK` (`node_FK`),
  KEY `received_datetime` (`received_datetime`),
  CONSTRAINT `node_data_ibfk_1` FOREIGN KEY (`device_FK`) REFERENCES `device` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `node_data_ibfk_2` FOREIGN KEY (`node_FK`) REFERENCES `node` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
-- Экспортируемые данные не выделены.
 
 
-- Дамп структуры для таблица dev_clouddashboard.sensor_data
CREATE TABLE IF NOT EXISTS `sensor_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `node_data_FK` int(11) NOT NULL,
  `sensor_type` varchar(6) NOT NULL,
  `value` varchar(64) NOT NULL,
  PRIMARY KEY (`id`),
  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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 

nordghost	Отправлено: 10 Октября, 2017 - 10:41:30 • Тема: Оптимизация SQL запросов • Форум: Работа с СУБД
Ответов: 17 Просмотров: 2044	Мелкий пишет: Сколько строк попадает в аггрегацию? Покажите 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
Просмотров: 2044

Приветствую, нужна помощь с оптимизацией SQL запросов, проблема в том что данные SQL запросы, по времени занимают от 2-х до 14 секунд. Скажу сразу таблица node_data имеет более 4-х миллионов записей

CODE (SQL):
скопировать код в буфер обмена

 
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`
 

CODE (SQL):
скопировать код в буфер обмена

 
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`
 

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

nordghost	Отправлено: 10 Ноября, 2016 - 02:57:20 • Тема: Twig • Форум: Вопросы новичков
Ответов: 3 Просмотров: 198	any ideas?

nordghost

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

Ответов: 3
Просмотров: 198

Здравствуйте, пилю платформу для инет магазинов, что то вроде shopify, каждый юзер может создать свой инет магазин и свой шаблон, как шаблонизатор планирую использовать twig, и дать пользователям самим клепать шаблоны как угодно, используя заранее определённые в контроллере переменные. Насколько безопасно будет давать юзерам доступ к созданию шаблонов через twig?

nordghost

Отправлено: 03 Марта, 2016 - 17:19:15 • Тема: Яндекс Рейсы • Форум: Вопросы новичков

Ответов: 0
Просмотров: 73

Здравствуйте, есть необходимость парсить рейсы с Яндекса - Поезда самолёты и т.д. Когда Вылетает, Посмотрел API - это не проблема, но насколько я понял цену вытащить невозможно через их API а она мне нужна! Придёться без API ручками писать парсер другого сайта. Кто сталкивался с подобным? Заранее благодарю за помощь.

nordghost

Отправлено: 13 Ноября, 2015 - 18:20:52 • Тема: вирус "реклама" • Форум: Прочее

Ответов: 8
Просмотров: 82

LIME пишет:

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

Или Netbeans)

nordghost

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

Ответов: 12
Просмотров: 455

LIME пишет:

Только обязательно отпишись
Интересно как получилось

У меня не с адресами возня, у меня задача сделать простой генератор рабочего процесса с помощью визуального программирования на PHP+jQuery, Там есть условия и есть неслабая вложенность процессов и т.д.

nordghost

Отправлено: 13 Ноября, 2015 - 12:06:26 • Тема: Нагрузка на БД • Форум: Вопросы новичков

Ответов: 12
Просмотров: 455

Мелкий пишет:

nordghost пишет:

в каждом городе район, в каждом районе улица, и в каждой улице дом, а в каждом доме - квартира

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

nordghost пишет:

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

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

Задача стоит не такая, в данном случае был упрощённый пример. Сама задача немного посложнее.
(Добавление)

LIME пишет:

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

Спасибо будем пробовать.

nordghost

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

Ответов: 18
Просмотров: 1568

max939393 пишет:

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

Самым логичным на сегодняшний день для создания является использование NodeJs, в сторону PHP+AJAX даже не смотрите. А самая популярная книга по Node на русском за первые 200 страниц расскажет как это сделать, а скринкаст который Вы можете загуглить расскажет как это сделать правильно!

nordghost

Отправлено: 12 Ноября, 2015 - 22:36:25 • Тема: Нагрузка на БД • Форум: Вопросы новичков

Ответов: 12
Просмотров: 455

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

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