Оптимизирует ли MySQL выбранные агрегаты в связанных таблицах, чтобы избежать N+1?

Question

Является ли этот запрос оптимальным в MySQL? Я имею в виду: есть ли постоянное количество выполняемых запросов?

ИЛИ это относится к проблеме N+1? В официальных документах MySQL по оптимизации ничего не нашел.

SELECT t.*, (SELECT COUNT(1) from related_table rt where rt.t_id = t.id)
FROM table t

Наивно, есть запрос и N запросов, так что это может привести к проблеме N+1.

MySQL 5.5+ автоматически + внутренне улучшает этот запрос, чтобы сделать постоянное количество запросов? возможно, преобразовав это внутренне в нечто вроде:

SELECT t.*, COUNT(rt.id)
FROM table t LEFT OUTER JOIN related_table rt
GROUP BY t.id

Я имею в виду: я знаю, как улучшить это вручную, но я спрашиваю об этом, потому что:

1. Возможно, внесение изменений в среду с (каким-то образом неполным ИМХО) ORM через библиотеку.
2. Любопытство. Нашел не так много документации в официальных документах MySQL.

Answer 1

Нет, коррелированный подзапрос в списке выбора не оптимизирован MySQL.

Вы можете подтвердить это с помощью EXPLAIN, чтобы получить отчет о плане оптимизации. Вот аналогичный запрос с использованием тестовой базы данных:

mysql> explain select *, (SELECT COUNT(*) FROM cast_info where cast_info.role_id = role_type.id) AS c 
    from role_type\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: role_type
   partitions: NULL
         type: index
possible_keys: NULL
          key: role
      key_len: 98
          ref: NULL
         rows: 12
     filtered: 100.00
        Extra: Using index
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: cast_info
   partitions: NULL
         type: ref
possible_keys: cr
          key: cr
      key_len: 4
          ref: imdb.role_type.id
         rows: 2534411
     filtered: 100.00
        Extra: Using index

Тип выбора DEPENDENT SUBQUERY означает, что подзапрос будет выполняться много раз, возможно, один раз для каждой строки внешнего запроса.

Сравните с EXPLAIN для оптимизированного вручную запроса:

mysql> explain select r.*, COUNT(c.id) AS c from role_type AS r  left outer join cast_info as c on r.id = c.role_id group by r.id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: r
   partitions: NULL
         type: index
possible_keys: PRIMARY,role
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 12
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: c
   partitions: NULL
         type: ref
possible_keys: cr
          key: cr
      key_len: 4
          ref: imdb.r.id
         rows: 2534411
     filtered: 100.00
        Extra: Using index

Это показывает, что доступ ко второй таблице является просто простым соединением по ссылке.

Вы также можете проверить с помощью профилировщика запросов MySQL. Вот второй запрос, который использует соединение:

+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000167 |
| checking permissions | 0.000015 |
| checking permissions | 0.000016 |
| Opening tables       | 0.000050 |
| init                 | 0.000059 |
| System lock          | 0.000044 |
| optimizing           | 0.000011 |
| statistics           | 0.000151 |
| preparing            | 0.000099 |
| Sorting result       | 0.000019 |
| executing            | 0.000010 |
| Sending data         | 9.700879 |
| end                  | 0.000024 |
| query end            | 0.000022 |
| closing tables       | 0.000017 |
| freeing items        | 0.000243 |
| cleaning up          | 0.000056 |
+----------------------+----------+

А вот тот, с зависимым подзапросом:

+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000152 |
| checking permissions | 0.000014 |
| checking permissions | 0.000013 |
| Opening tables       | 0.000050 |
| init                 | 0.000067 |
| System lock          | 0.000042 |
| optimizing           | 0.000010 |
| statistics           | 0.000367 |
| preparing            | 0.000033 |
| optimizing           | 0.000015 |
| statistics           | 0.000032 |
| preparing            | 0.000020 |
| executing            | 0.000010 |
| Sending data         | 0.000191 |
| executing            | 0.000010 |
| Sending data         | 4.103899 |
| executing            | 0.000018 |
| Sending data         | 2.413570 |
| executing            | 0.000018 |
| Sending data         | 0.043924 |
| executing            | 0.000022 |
| Sending data         | 0.037834 |
| executing            | 0.000020 |
| Sending data         | 0.014127 |
| executing            | 0.000021 |
| Sending data         | 0.089977 |
| executing            | 0.000023 |
| Sending data         | 0.045968 |
| executing            | 0.000024 |
| Sending data         | 0.000044 |
| executing            | 0.000005 |
| Sending data         | 0.190935 |
| executing            | 0.000034 |
| Sending data         | 1.046394 |
| executing            | 0.000018 |
| Sending data         | 0.017567 |
| executing            | 0.000021 |
| Sending data         | 0.882959 |
| end                  | 0.000046 |
| query end            | 0.000023 |
| closing tables       | 0.000018 |
| freeing items        | 0.000248 |
| cleaning up          | 0.000025 |
+----------------------+----------+

Вы можете видеть, что подзапрос вызывает многократное выполнение. В моем случае у меня было всего несколько строк в таблице role_type, но если у вас есть сотни или тысячи, количество выполнений подзапроса может быть настолько длинным, что профилировщик усекает этот отчет.