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

 PHP.SU

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


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

> Описание: Медленная работа
Поиск в теме | Версия для печати
Мелкий Супермодератор
Отправлено: 10 Октября, 2017 - 12:44:02
Post Id



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


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


Помог: 618 раз(а)
LIME пишет:
вспомнить что в мскл JOIN это nested loop и поле участвует в фильтре, то становится ожидаемо что сначала произойдет выборка по индексу в нужном направлении и лишь потом произойдет внутренние циклы JOINов

Это если не вспоминать про дурацкий планировщик. Который увидит сортировку и пойдёт отсортированное множество сортировать ещё раз.
Так-то да, никаких проблем читать индекс и nested loop'ом джойнить попутно данные и сразу их выдавать клиенту минуя сортировку.
Так умеет планировщик postgresql, а вот mysql мне так помнится не умеет. Зато mysql умеет loose index scan Радость


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


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


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


Помог: 322 раз(а)
nordghost срочно нужен explain на второй запрос после индекса captured_datetime Радость
(Добавление)
CODE (SQL):
скопировать код в буфер обмена
  1. EXPLAIN FORMAT=JSON SELECT ...

(Добавление)
он вербознее
 
 Top
nordghost
Отправлено: 14 Октября, 2017 - 12:47:41
Post Id



Посетитель


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


Помог: 3 раз(а)
Мелкий пишет:
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-а?


-----
<a href="http://netbeans[dot]org/">Моя любимая IDE</a> :3
 
My status
 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