Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Анализ запроса DISTINCT, GROUP BY / 3 сообщений из 3, страница 1 из 1
05.12.2015, 14:06:00
    #39121319
devKP
devKP
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Анализ запроса DISTINCT, GROUP BY
Версия MySQL: 5.6.27

Задача: вывести список уникальных пар id с одинаковыми title.

Есть таблица(упрощенная версия):
Код: sql
1.
2.
3.
4.
5.
CREATE TABLE `items` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=201109 DEFAULT CHARSET=utf8;



Реализовал два решения и пытаюсь разобраться почему одно работает быстрее другого. Если есть другие решения так же бы на них посмотрел. Дополнительные индексы добавлять не надо.

1ый запрос:
Код: sql
1.
2.
3.
4.
5.
SELECT g.id, gs.id 
    FROM item i 
    JOIN item im ON i.title = im.title AND i.id != im.id 
    GROUP BY LEAST(i.id, im.id), GREATEST(i.id, im.id) 
    ORDER BY NULL;



EXPLAIN:
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| 1 | SIMPLE | i | ALL | NULL | NULL | NULL | NULL | 106 | Using temporary |
| 1 | SIMPLE | im | ALL | NULL | NULL | NULL | NULL | 106 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+

EXPLAIN FORMAT=JSON:
Код: javascript
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
{
  "query_block": {
    "select_id": 1,
    "ordering_operation": {
      "using_filesort": false,
      "grouping_operation": {
        "using_temporary_table": true,
        "using_filesort": false,
        "nested_loop": [
          {
            "table": {
              "table_name": "i",
              "access_type": "ALL",
              "rows": 106,
              "filtered": 100
            }
          },
          {
            "table": {
              "table_name": "im",
              "access_type": "ALL",
              "rows": 106,
              "filtered": 100,
              "using_join_buffer": "Block Nested Loop",
              "attached_condition": "((`tmp_login`.`im`.`name` = `tmp_login`.`i`.`name`) and (`tmp_login`.`i`.`id` <> `tmp_login`.`im`.`id`))"
            }
          }
        ]
      }
    }
  }
}



2ой запрос:
Код: sql
1.
2.
3.
4.
SELECT DISTINCT LEAST(i.id, im.id) , GREATEST(i.id, im.id) 
    FROM  `items` i
    JOIN  `items` im ON i.title = im.title
    AND i.id != im.id



EXPLAIN:
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| 1 | SIMPLE | i | ALL | NULL | NULL | NULL | NULL | 106 | Using temporary |
| 1 | SIMPLE | im | ALL | NULL | NULL | NULL | NULL | 106 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+

EXPLAIN FORMAT=JSON:
Код: javascript
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
{
  "query_block": {
    "select_id": 1,
    "duplicates_removal": {
      "using_temporary_table": true,
      "using_filesort": false,
      "nested_loop": [
        {
          "table": {
            "table_name": "i",
            "access_type": "ALL",
            "rows": 106,
            "filtered": 100
          }
        },
        {
          "table": {
            "table_name": "im",
            "access_type": "ALL",
            "rows": 106,
            "filtered": 100,
            "using_join_buffer": "Block Nested Loop",
            "attached_condition": "((`tmp_login`.`im`.`name` = `tmp_login`.`i`.`name`) and (`tmp_login`.`i`.`id` <> `tmp_login`.`im`.`id`))"
          }
        }
      ]
    }
  }
}



Теперь непосредственно к вопросу:
Как можно обьяснить, что 2ой запрос работает быстрее? Где можно почитать про расшифровку explain'a в формате json? Возможно есть более оптимальный путь решения этой задачи?

Если посмотреть на простой explain, то они одинаковые и сделать вывод почему один быстрее другого сделать сложно(во всяком случае мне). А как этот вывод сделать из формата json я пока не совсем понимаю. Интересно было бы понять, что происходит под капотом. Возможно ответ находится в описание distinct, group by, temporary table, количестве данных в этой таблице? Не понятно для меня так же почему в 2ом запросе с DISTINCT используется временная таблица( https://docs.oracle.com/cd/E17952_01/refman-5.6-en/internal-temporary-tables.html ).

Буду рад любым ответам, спасибо!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.12.2015, 17:38:48
    #39121398
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Анализ запроса DISTINCT, GROUP BY
devKP
Код: sql
1.
AND i.id != im.id

Можно использовать строгое "меньше" (или "больше"). Заодно избавишься и от LEAST/GREATEST, и от DISTINCT...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.12.2015, 18:59:06
    #39121441
devKP
devKP
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Анализ запроса DISTINCT, GROUP BY
Akina, Спасибо! Хороший вариант.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Анализ запроса DISTINCT, GROUP BY / 3 сообщений из 3, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 279ms
Закрыть
start [/forum/topic.php?fid=47&mobile=1&tid=1832409]:
 0ms
get settings:
 5ms
get forum list:
 9ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 28ms
get topic data:
 6ms
get forum data:
 1ms
get page messages:
 21ms
get tp. blocked users:
 1ms
others: 204ms
total:  279ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]