Блог

Главная - Вопросы по программированию - Как я могу ускорить свой запрос. подзапрос выполняется слишком медленно

Как я могу ускорить свой запрос. подзапрос выполняется слишком медленно

#mysql

#mysql

Вопрос:

У меня есть запрос к таблице инвентаризации. Что делает соединение с подзапросом, так это получает общее количество заказов на выполнение, имеющихся для каждого инвентарного актива. Если я запускаю базовый запрос с основными объединениями для типа оборудования, поставщика, местоположения и комнаты, он выполняется просто отлично. Меньше секунды, чтобы вернуть результат. используя его с присоединением к подзапросу, возврат результата занимает от 15 до 20 секунд.

Вот полный запрос:

 SELECT `inventory`.inventory_id AS 'inventory_id', 
       `inventory`.media_tag AS 'media_tag', 
       `inventory`.asset_tag AS 'asset_tag', 
       `inventory`.idea_tag AS 'idea_tag', 
       `equipTypes`.equipment_type AS 'equipment_type',  
       `inventory`.equip_make AS 'equip_make', 
       `inventory`.equip_model AS 'equip_model', 
       `inventory`.equip_serial AS 'equip_serial', 
       `inventory`.sales_order AS 'sales_order', 
       `vendors`.vendor_name AS 'vendor_name', 
       `inventory`.purchase_order AS 'purchase_order', 
       `status`.status AS 'status', 
       `locations`.location_name AS 'location_name', 
       `rooms`.room_number AS 'room_number', 
       `inventory`.notes AS 'notes', 
       `inventory`.send_to AS 'send_to', 
       `inventory`.one_to_one AS 'one_to_one', 
       `enteredBy`.user_name AS 'user_name', 
       from_unixtime(`inventory`.enter_date, '%m/%d/%Y') AS 'enter_date', 
       from_unixtime(`inventory`.modified_date, '%m/%d/%Y') AS 'modified_date', 
       COALESCE(at.assets,0) AS assets 
FROM mod_inventory_data AS `inventory` 
LEFT JOIN mod_inventory_equip_types AS `equipTypes` 
       ON `equipTypes`.equip_type_id = `inventory`.equip_type_id 
LEFT JOIN mod_vendors_main AS `vendors`  
       ON `vendors`.vendor_id = `inventory`.vendor_id 
LEFT JOIN mod_inventory_status AS `status`  
       ON `status`.status_id = `inventory`.status_id 
LEFT JOIN mod_locations_data AS `locations`  
       ON `locations`.location_id = `inventory`.location_id 
LEFT JOIN mod_locations_rooms AS `rooms`  
       ON `rooms`.room_id = `inventory`.room_id 
LEFT JOIN mod_users_data AS `enteredBy`  
       ON `enteredBy`.user_id = `inventory`.entered_by
LEFT JOIN  
       ( SELECT asset_tag, count(*) AS assets 
         FROM mod_workorder_data 
         WHERE asset_tag IS NOT NULL 
         GROUP BY asset_tag ) AS at  
       ON at.asset_tag = inventory.asset_tag 
ORDER BY inventory_id ASC LIMIT 0,20

Данные MySQL EXPLAIN для этого здесь

  ---- ------------- -------------------- -------- --------------- ----------- --------- ------------------------------------- ------- --------------------------------- 
| id | select_type | table              | type   | possible_keys | key       | key_len | ref                                 | rows  | Extra                           |
 ---- ------------- -------------------- -------- --------------- ----------- --------- ------------------------------------- ------- --------------------------------- 
|  1 | PRIMARY     | inventory          | ALL    | NULL          | NULL      | NULL    | NULL                                | 12612 | Using temporary; Using filesort |
|  1 | PRIMARY     | equipTypes         | eq_ref | PRIMARY       | PRIMARY   | 4       | spsd_woidbs.inventory.equip_type_id |     1 |                                 |
|  1 | PRIMARY     | vendors            | eq_ref | PRIMARY       | PRIMARY   | 4       | spsd_woidbs.inventory.vendor_id     |     1 |                                 |
|  1 | PRIMARY     | status             | eq_ref | PRIMARY       | PRIMARY   | 4       | spsd_woidbs.inventory.status_id     |     1 |                                 |
|  1 | PRIMARY     | locations          | eq_ref | PRIMARY       | PRIMARY   | 4       | spsd_woidbs.inventory.location_id   |     1 |                                 |
|  1 | PRIMARY     | rooms              | eq_ref | PRIMARY       | PRIMARY   | 4       | spsd_woidbs.inventory.room_id       |     1 |                                 |
|  1 | PRIMARY     | enteredBy          | eq_ref | PRIMARY       | PRIMARY   | 4       | spsd_woidbs.inventory.entered_by    |     1 |                                 |
|  1 | PRIMARY     | <derived2>         | ALL    | NULL          | NULL      | NULL    | NULL                                |  4480 |                                 |
|  2 | DERIVED     | mod_workorder_data | range  | asset_tag     | asset_tag | 13      | NULL                                | 15897 | Using where; Using index        |
 ---- ------------- -------------------- -------- --------------- ----------- --------- ------------------------------------- ------- ---------------------------------

Используя профилирование запросов MySQL, я получаю это:

  -------------------------------- ------------ 
| Status                         | Time       |
 -------------------------------- ------------ 
| starting                       |  0.000020  | 
| checking query cache for query |  0.000263  |
| Opening tables                 |  0.000034  |
| System lock                    |  0.000013  |
| Table lock                     |  0.000079  |
| optimizing                     |  0.000011  |
| statistics                     |  0.000138  |
| preparing                      |  0.000019  |
| executing                      |  0.000010  |
| Sorting result                 |  0.000004  |
| Sending data                   |  0.015103  |
| init                           |  0.000094  |
| optimizing                     |  0.000009  |
| statistics                     |  0.000049  |
| preparing                      |  0.000022  |
| Creating tmp table             |  0.000104  |
| executing                      |  0.000009  |
| Copying to tmp table           | 15.410168  |
| Sorting result                 |  0.009488  |
| Sending data                   |  0.000215  |
| end                            |  0.000006  |
| removing tmp table             |  0.001997  |
| end                            |  0.000018  |
| query end                      |  0.000005  |
| freeing items                  |  0.000112  |
| storing result in query cache  |  0.000011  |
| removing tmp table             |  0.000022  |
| closing tables                 |  0.000036  |
| logging slow query             |  0.000005  |
| logging slow query             |  0.000005  |
| cleaning up                    |  0.000013  |
 -------------------------------- ------------

который показывает мне, что горлышко бутылки копируется во временную таблицу, но я не уверен, как это ускорить. Есть ли настройки на стороне сервера, которые я могу настроить, чтобы ускорить это? Могу ли я внести изменения в существующий запрос, которые приведут к тем же результатам, которые были бы быстрее?

Мне кажется, что подзапрос LEFT JOIN каждый раз выдавал бы одну и ту же результирующую матрицу данных, поэтому, если он должен выполнять этот запрос для каждой строки в инвентарном списке, я могу понять, почему это будет медленно. Или MySQL кэширует подзапрос при его выполнении? Мне показалось, я где-то читал, что MySQL не кэширует подзапросы, это правда?

Приветствуется любая помощь.

Комментарии:

1. Сколько данных в этих таблицах, которые вы запрашиваете? И вы убедились, что все столбцы, которые вы используете для своих предикатов объединения, проиндексированы?

2. Таблица данных инвентаризации содержит 12 612 записей, а таблица данных workorder в подзапросе содержит 19 159 записей. У меня есть индексы для всех полей ID и поля asset_tag, используемых в объединениях. Я хотел указать это в исходном сообщении, но забыл. Должен ли я содержать их все в одном именованном индексе? В настоящее время у меня есть отдельные индексы. Кстати, спасибо за переформатирование сообщения.

3. Хм. Это не похоже на огромный объем данных, и я не думаю, что объединение индексов даст такой большой прирост. Для устранения неполадок в работе. проблема На самом деле я бы начал с демонтажа запроса. Удаляйте объединение за раз и оценивайте производительность. Это может выявить проблему где-то. Возможно, начните с удаления подзапроса и посмотрите, как это работает. Если это само по себе является причиной, то вариантом может быть денормализация данных и создание таблицы, которая содержит эти подсчеты.

4. Я упоминал в исходном сообщении, что если я выполняю базовый запрос со всеми соединениями, кроме соединения подзапроса, он выполняется нормально. Я только что запустил его, и запрос занял 0,0215 секунды. Я уже знал, что узким местом был подзапрос. Если я запускаю подзапрос сам по себе, на это уходит 0,0016 секунды, если я умножу это число на количество записей (12 612) в инвентарной таблице, я получу 20,1792 секунды, что составляет всего 5 секунд разницы между этим и полным запросом / подзапросом.

5. Извините, это было несколько часов назад, я расставил. Я думаю, что создание денормализованной таблицы с предварительно заполненными графами вашего поля asset_tag и выполнение регулярного соединения с ней будет вашим лучшим шансом на повышение производительности.

Ответ №1:

Вот что я сделал, и, похоже, это работает хорошо. Я создал таблицу с именем mod_workorder_counts. В таблице есть два поля: тег ресурса, который уникален, и wo_count, который является полем и INT (3). Я заполняю эту таблицу этим запросом:

 INSERT INTO mod_workorder_counts ( asset_tag, wo_count ) 
select s.asset_tag, ct 
FROM
  ( SELECT t.asset_tag, count(*) as ct
    FROM mod_workorder_data t
    WHERE t.asset_tag IS NOT NULL
    GROUP BY t.asset_tag
  ) as s
ON DUPLICATE KEY UPDATE mod_workorder_counts.wo_count = ct

который выполняется за 0,1580 секунды, что можно считать немного медленным, но неплохим.

Теперь, когда я запускаю эту модификацию моего исходного запроса:

 SELECT `inventory`.inventory_id AS 'inventory_id', 
       `inventory`.media_tag AS 'media_tag', 
       `inventory`.asset_tag AS 'asset_tag', 
       `inventory`.idea_tag AS 'idea_tag', 
       `equipTypes`.equipment_type AS 'equipment_type',  
       `inventory`.equip_make AS 'equip_make', 
       `inventory`.equip_model AS 'equip_model', 
       `inventory`.equip_serial AS 'equip_serial', 
       `inventory`.sales_order AS 'sales_order', 
       `vendors`.vendor_name AS 'vendor_name', 
       `inventory`.purchase_order AS 'purchase_order', 
       `status`.status AS 'status', 
       `locations`.location_name AS 'location_name', 
       `rooms`.room_number AS 'room_number', 
       `inventory`.notes AS 'notes', 
       `inventory`.send_to AS 'send_to', 
       `inventory`.one_to_one AS 'one_to_one', 
       `enteredBy`.user_name AS 'user_name', 
       from_unixtime(`inventory`.enter_date, '%m/%d/%Y') AS 'enter_date', 
       from_unixtime(`inventory`.modified_date, '%m/%d/%Y') AS 'modified_date', 
       COALESCE(at.wo_count, 0) AS workorders 
FROM mod_inventory_data AS `inventory` 
LEFT JOIN mod_inventory_equip_types AS `equipTypes` 
       ON `equipTypes`.equip_type_id = `inventory`.equip_type_id 
LEFT JOIN mod_vendors_main AS `vendors`  
       ON `vendors`.vendor_id = `inventory`.vendor_id 
LEFT JOIN mod_inventory_status AS `status`  
       ON `status`.status_id = `inventory`.status_id 
LEFT JOIN mod_locations_data AS `locations`  
       ON `locations`.location_id = `inventory`.location_id 
LEFT JOIN mod_locations_rooms AS `rooms`  
       ON `rooms`.room_id = `inventory`.room_id 
LEFT JOIN mod_users_data AS `enteredBy`  
       ON `enteredBy`.user_id = `inventory`.entered_by
LEFT JOIN mod_workorder_counts AS at  
       ON at.asset_tag = inventory.asset_tag 
ORDER BY inventory_id ASC LIMIT 0,20

Он выполняется за 0,0051 секунды. Это приводит к тому, что общее время между двумя запросами составляет 0,1631 секунды, что составляет около 1/10 секунды по сравнению с 15 секундами с исходным подзапросом.

Если я просто включил поле «wo_count» без использования COALESCE, я получил нулевые значения для любых тегов активов, которые не были перечислены в таблице «mod_workorder_counts». Таким образом, объединение дало бы мне 0 для любого нулевого значения, чего я и хочу.

Теперь я настрою его так, чтобы при вводе рабочего задания для тега ресурса у меня был запрос INSERT / UPDATE для обновления таблицы подсчетов в это время, чтобы он не выполнялся без необходимости.