Три запроса быстрее, чем один - что не так с моими соединениями?


У меня есть настроенные отношения JPA ManyToMany, которые дают мне три важных таблицы: мою таблицу билетов, мою таблицу соединений и мою таблицу инвентаря. Это таблицы InnoDB на MySQL 5.1. Соответствующие биты:

Ticket:
+--------+----------+------+-----+---------+----------------+
| Field  | Type     | Null | Key | Default | Extra          |
+--------+----------+------+-----+---------+----------------+
| ID     | int(11)  | NO   | PRI | NULL    | auto_increment |
| Status | longtext | YES  |     | NULL    |                |
+--------+----------+------+-----+---------+----------------+

JoinTable:
+-------------+---------+------+-----+---------+-------+
| Field       | Type    | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| InventoryID | int(11) | NO   | PRI | NULL    |       | Foreign Key - Inventory
| TicketID    | int(11) | NO   | PRI | NULL    |       | Foreign Key - Ticket
+-------------+---------+------+-----+---------+-------+

Inventory:
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| ID           | int(11)      | NO   | PRI | NULL    | auto_increment |
| TStampString | varchar(32)  | NO   | MUL | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+

TStampStrings имеют вид "yyyy. mm.dd HH:MM:SS Z" (например, '2010.03.19 22:27:57 GMT'). Прямо сейчас все созданные билеты напрямую соответствуют некоторой конкретной часовой TStampString, так что SELECT COUNT(*) FROM Ticket; совпадает с SELECT COUNT(DISTINCT(SUBSTRING(TStampString, 1, 13))) FROM Inventory;

Что я хотел бы сделать, так это перегруппироваться. некоторые билеты, основанные на мельчайшей детализации строки Tstamp: (SUBSTRING(TStampString, 1, 16)). Поэтому я профилирую и тестирую выбор вставки в ... Оператор SELECT:

EXPLAIN SELECT SUBSTRING(i.TStampString, 1, 16) FROM Ticket t JOIN JoinTable j
ON t.ID = j.TicketID JOIN Inventory i ON j.InventoryID = i.ID WHERE t.Status
= 'Regroup' GROUP BY SUBSTRING(i.TStampString, 1, 16);

+--+------+---+--------+-------------+-----+-----+----------+-------+-----------+
|id| type |tbl| type   | psbl_keys   | key | len | ref      | rows  | Extra     |
+--+------+---+--------+-------------+-----+-----+----------+-------+-----------+
|1 | SMPL | t | ALL    | PRI         | NULL| NULL| NULL     | 35569 | where     |
|  |      |   |        |             |     |     |          |       | +temporary|
|  |      |   |        |             |     |     |          |       | +filesort |
|1 | SMPL | j | ref    | PRI,FK1,FK2 | FK2 | 4   | t.ID     |   378 | index     |
|1 | SMPL | i | eq_ref | PRI         | PRI | 4   | j.Invent |     1 |           |
|  |      |   |        |             |     |     |    oryID |       |           |
+--+------+---+--------+-------------+-----+-----+----------+-------+-----------+

Что это означает для меня, так это то, что для каждой строки в Ticket MySQL сначала выполняет соединения, а затем решает, что строка недопустима из-за предложения WHERE. Конечно, время выполнения отвратительно (я сдался через 30 минут). Обратите внимание, что он не идет быстрее с T. Status = 'Regroup' перемещается к первому соединению положение и без предложения where.

Но что интересно, если я выполняю этот запрос вручную в три шага, делая то, что я думал, что оптимизатор будет делать, каждый шаг возвращает почти сразу:
--Step 1: Select relevant Tickets (results dumped to file)
SELECT ID FROM Ticket WHERE Status = 'Regroup';

--Step 2: Get relevant Inventory entries
SELECT InventoryID FROM JoinTable WHERE TicketID IN (step 1s file);

--Step 3: Select what I wanted all along
SELECT SUBSTRING(TStampString, 1, 16) FROM Inventory WHERE ID IN (step 2s file)
GROUP BY SUBSTRING(TStampString, 1, 16);

В моих конкретных таблицах первый запрос дает 154 результата, второй создает 206 598 строк, а третий запрос возвращает 9198 строк. Все они вместе взятые занимают ~2 минуты для выполнения, причем последний запрос имеет единственное значительное время выполнения.

Сброс промежуточных результатов в файл громоздок, и что более важно, я хотел бы знать, как написать мой исходный запрос таким образом, чтобы он работал разумно. Итак, как мне структурировать это соединение трех таблиц таким образом, чтобы оно выполнялось так быстро, как я знаю, возможно?

UPDATE : я добавил префиксный индекс состояния (16), который изменяет мои строки профиля объяснения на 153, 378 и 1 соответственно (так как первая строка имеет ключ для использования). Версия соединения моего запроса теперь занимает ~6 минут, что терпимо, но все же значительно медленнее чем ручная версия. Я все еще хотел бы знать, почему соединение выполняет очень неоптимально, но может быть, что нельзя создать независимые подзапросы в багги MySQL 5.1. Если пройдет достаточно времени, я приму Add Index как решение моей проблемы, хотя это не совсем ответ на мой вопрос.

В конце концов я вручную воссоздал каждый шаг соединения на диске. Десятки тысяч файлов каждый с тысячью запросов все еще были значительно быстрее, чем все, что я мог получить мою версию MySQL, чтобы сделать. Но поскольку этот процесс был бы ужасно специфичным и бесполезным для непрофессионала, я принимаю ответ ypercube о добавлении (частичных) индексов.

2   2   2012-07-31 03:30:07

2 ответа:

Что можно сделать для ускорения запроса:

  • Добавьте индекс на Status. Даже если вы не измените тип на VARCHAR, Вы все равно можете добавить частичный индекс:

    ALTER TABLE Ticket
      ADD INDEX status_idx
        Status(16) ;
    
  • Я предполагаю, что первичным ключом таблицы Join является (InventoryID, TicketID). Вы также можете добавить еще один индекс на (TicketID, InventoryID). Это может не принести пользы данному конкретному запросу, но будет полезно в других запросах, которые у вас будут.

Ответ на вопрос, почему это происходит, заключается в том, что оптимизатор не всегда выбирает хороший план. Вы можете попробовать этот вариант запроса и посмотреть, чем отличается план EXPLAIN и есть ли какой-либо прирост эффективности:

SELECT SUBSTRING(i.TStampString, 1, 16) 
FROM 
    ( SELECT (DISTINCT) j.InventoryID 
      FROM Ticket t 
        JOIN JoinTable j
          ON t.ID = j.TicketID 
      WHERE t.Status = 'Regroup' 
    ) AS tmp
  JOIN Inventory i 
    ON tmp.InventoryID = i.ID
GROUP BY SUBSTRING(i.TStampString, 1, 16) ;

Попробуйте дать первому предложению-подстроке псевдоним и использовать его в group-by.

SELECT SUBSTRING(i.TStampString, 1, 16) as blaa FROM Ticket t JOIN JoinTable j
ON t.ID = j.TicketID JOIN Inventory i ON j.InventoryID = i.ID WHERE t.Status
= 'Regroup' GROUP BY blaa;

Также избегайте соединения вообще, так как оно вам не нужно..

SELECT distinct(SUBSTRING(i.TStampString, 1,16)) from inventory i where i.ID in 
 ( select id from JoinTable j where j.TicketID in 
    (select id from Ticket t where t.Status = 'Regroup'));

Сработает ли это?

Кстати. у вас есть индекс в поле Статус ?