У меня есть следующий запрос
select * from mapping_channel_fqdn_virtual_host where id in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10);
объяснение вышеуказанного запроса дает мне следующий результат:
explain select * from mapping_channel_fqdn_virtual_host where id in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10);
+----+-------------+-----------------------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | mapping_channel_fqdn_virtual_host | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 10 | 100.00 | Using where |
+----+-------------+-----------------------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0,00 sec)
id — первичный ключ таблицы. Похоже, что это запрос диапазона, и он использует первичный ключ в качестве индекса для этого.
Когда я пытаюсь объяснить следующий запрос, я получаю разные результаты:
explain select * from mapping_channel_fqdn_virtual_host where id in (select max(id) from mapping_channel_fqdn_virtual_host group by channel_id, fqdn_virtual_host_id);
+----+-------------+-----------------------------------+------------+-------+------------------+------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------------------------+------------+-------+------------------+------------------+---------+------+------+----------+-------------+
| 1 | PRIMARY | mapping_channel_fqdn_virtual_host | NULL | ALL | NULL | NULL | NULL | NULL | 4849 | 100.00 | Using where |
| 2 | SUBQUERY | mapping_channel_fqdn_virtual_host | NULL | index | idx_channel_fqdn | idx_channel_fqdn | 8 | NULL | 4849 | 100.00 | Using index |
+----+-------------+-----------------------------------+------------+-------+------------------+------------------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0,00 sec)
idx_channel_fqdn
— ключ составного индекса для пары столбцов, используемой в предложении groupby. Но при использовании подзапроса основной запрос перестает использовать индекс, как это было раньше. Можете ли вы объяснить, почему это происходит?
Попробовал запрос JOIN, предложенный Ануаром:
explain select * from mapping_channel_fqdn_virtual_host as x join (select max(id) as ids from mapping_channel_fqdn_virtual_host group by channel_id, fqdn_virtual_host_id) as y on x.id=y.ids;
+----+-------------+-----------------------------------+------------+--------+------------------+------------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------------------------+------------+--------+------------------+------------------+---------+-------+------+----------+-------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 4849 | 100.00 | Using where |
| 1 | PRIMARY | x | NULL | eq_ref | PRIMARY | PRIMARY | 4 | y.ids | 1 | 100.00 | NULL |
| 2 | DERIVED | mapping_channel_fqdn_virtual_host | NULL | index | idx_channel_fqdn | idx_channel_fqdn | 8 | NULL | 4849 | 100.00 | Using index |
+----+-------------+-----------------------------------+------------+--------+------------------+------------------+---------+-------+------+----------+-------------+
3 rows in set, 1 warning (0,00 sec)
Судя по типам index и eq_ref, лучше использовать JOIN, чем подзапрос? Можете ли вы немного подробнее объяснить результат выражения объяснения соединения g?
id in (100,101,...,110)
? Мое дикое предположение состоит в том, что оптимизатор видит, что вы запрашиваете первые десять строк, поэтому нет необходимости запрашивать индекс, чтобы найти начальную запись диапазона. И id использует индекс, чтобы найти максимальное значение во втором примере. - person hage   schedule 03.11.2017IN ( SELECT ... )
общеизвестно плохо влияет на производительность. Переключитесь наJOIN
(в данном случае) илиEXISTS
(в некоторых других случаях). - person Rick James   schedule 20.01.2018