Q:

MySQL 8.0 Group by Performance

To compare MySQL 5.7 and 8.0 I created a table using sysbench.
And I tried the test. The performance of the server is exactly the same
As a result, oltp_point_select showed almost similar performance.
However, when doing the group by tests below, MySQL 8.0 showed 10 times better performance.
But I do not know why it is fast.
I do not know if I can find the MySQL 8.0 Release Notes.
In 8.0, who will tell me why group by are faster?

0. table schema
CREATE TABLE `sbtest2` (

  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `pad` char(60) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_2` (`k`),
  KEY `idx1` (`k`,`pad`)
) ENGINE=InnoDB AUTO_INCREMENT=1099289 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

1. MySQL 5.7
Server version:         5.7.17-log Source distribution
5.7 RESULT
    testdb 17:24:38 > select count(0),pad from sbtest2 where k between 400000 and 600000 group by pad limit 1;
        +----------+-------------------------------------------------------------+
        | count(0) | pad                                                         |
        +----------+-------------------------------------------------------------+
        |        1 | 00000012813-73478546096-60491222567-55128816668-26346684571 |
        +----------+-------------------------------------------------------------+
        **1 row in set (21.88 sec)**

testdb 17:21:30 > explain select count(0),pad from sbtest2 where k between 400000 and 600000 group by pad limit 10\G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: sbtest2
       partitions: NULL
             type: range
    possible_keys: k_2,idx1
              key: idx1
          key_len: 4
              ref: NULL
             rows: 493200
         filtered: 100.00
            Extra: Using where; Using index; Using temporary; Using filesort
    1 row in set, 1 warning (0.00 sec)

    17:55:17 > show profile;
        +---------------------------+-----------+
        | Status                    | Duration  |
        +---------------------------+-----------+
        | starting                  |  0.000083 |
        | checking permissions      |  0.000008 |
        | Opening tables            |  0.000017 |
        | init                      |  0.000032 |
        | System lock               |  0.000013 |
        | optimizing                |  0.000010 |
        | statistics                |  0.076138 |
        | preparing                 |  0.000018 |
        | Creating tmp table        |  0.000033 |
        | Sorting result            |  0.000005 |
        | executing                 |  0.000003 |
        | Sending data              |  2.790353 |
        | converting HEAP to ondisk |  1.709809 |
        | Sending data              | 14.403732 |
        | Creating sort index       |  2.236432 |
        | end                       |  0.000014 |
        | query end                 |  0.000012 |
        | removing tmp table        |  0.017293 |
        | query end                 |  0.000007 |
        | closing tables            |  0.000011 |
        | freeing items             |  0.000252 |
        | logging slow query        |  0.000055 |
        | cleaning up               |  0.000014 |
        +---------------------------+-----------+

2. MySQL 8.0
Server version:         8.0.11 MySQL Community Server - GPL
RESULT
 testdb 17:24:24 > select count(0),pad from sbtest2 where k between 400000 and 600000 group by pad limit 1;
    +----------+-------------------------------------------------------------+
    | count(0) | pad                                                         |
    +----------+-------------------------------------------------------------+
    |        1 | 30506873183-39276415649-14634764539-17515472140-54546407224 |
    +----------+-------------------------------------------------------------+
    **1 row in set (2.32 sec)**

 testdb 17:23:29 > explain select count(0),pad from sbtest2 where k between 400000 and 600000 group by pad limit 10\G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: sbtest2
       partitions: NULL
             type: range
    possible_keys: k_2,idx1
              key: idx1
          key_len: 4
              ref: NULL
             rows: 493200
         filtered: 100.00
            Extra: Using where; Using index; Using temporary
    1 row in set, 1 warning (0.04 sec)

    testdb 17:55:00 > show profile;
    +----------------------+----------+
    | Status               | Duration |
    +----------------------+----------+
    | starting             | 0.000103 |
    | checking permissions | 0.000018 |
    | Opening tables       | 0.000051 |
    | init                 | 0.000008 |
    | System lock          | 0.000013 |
    | optimizing           | 0.000010 |
    | statistics           | 0.000144 |
    | preparing            | 0.000024 |
    | Creating tmp table   | 0.000042 |
    | executing            | 0.000004 |
    | Sending data         | 2.407031 |
    | end                  | 0.000018 |
    | query end            | 0.000019 |
    | removing tmp table   | 0.087750 |
    | query end            | 0.000020 |
    | closing tables       | 0.000018 |
    | freeing items        | 0.000193 |
    | logging slow query   | 0.000061 |
    | cleaning up          | 0.000015 |
    +----------------------+----------+

@Rick James
The sbtest2 table of 5.7 was dumped and applied to 8.0. 
Now the two tables are exactly the same.
5_7.testdb 18:25:13 > select @@version;
+------------+
| @@version  |
+------------+
| 5.7.17-log |
+------------+
1 row in set (0.00 sec)

5_7.testdb 18:25:16 > select count(*) from sbtest2;
+----------+
| count(*) |
+----------+
|  1099288 |
+----------+
1 row in set (0.17 sec)

5_7.testdb 18:25:22 > select count(0),pad from sbtest2 where k between 400000 and 500000 group by pad order by pad limit 1;
+----------+-------------------------------------------------------------+
| count(0) | pad                                                         |
+----------+-------------------------------------------------------------+
|        1 | 00000012813-73478546096-60491222567-55128816668-26346684571 |
+----------+-------------------------------------------------------------+
1 row in set (7.34 sec)

5_7.testdb 18:26:15 > explain select count(0),pad from sbtest2 where k between 400000 and 500000 group by pad order by pad limit 1;        
+----+-------------+---------+------------+-------+---------------+------+---------+------+--------+----------+-----------------------------------------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                                                     |
+----+-------------+---------+------------+-------+---------------+------+---------+------+--------+----------+-----------------------------------------------------------+
|  1 | SIMPLE      | sbtest2 | NULL       | range | k_2,idx1      | idx1 | 4       | NULL | 542160 |   100.00 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+---------+------------+-------+---------------+------+---------+------+--------+----------+-----------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

Note (Code 1003): /* select#1 */ select count(0) AS `count(0)`,`testdb`.`sbtest2`.`pad` AS `pad` from `testdb`.`sbtest2` where (`testdb`.`sbtest2`.`k` between 400000 and 500000) group by `testdb`.`sbtest2`.`pad` order by `testdb`.`sbtest2`.`pad` limit 1
5_7.testdb 18:26:27 > explain format=json select count(0),pad from sbtest2 where k between 400000 and 500000 group by pad order by pad limit 1;        
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "232810.86"
    },
    "ordering_operation": {
      "using_filesort": false,
      "grouping_operation": {
        "using_temporary_table": true,
        "using_filesort": true,
        "table": {
          "table_name": "sbtest2",
          "access_type": "range",
          "possible_keys": [
            "k_2",
            "idx1"
          ],
          "key": "idx1",
          "used_key_parts": [
            "k"
          ],
          "key_length": "4",
          "rows_examined_per_scan": 542160,
          "rows_produced_per_join": 542160,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "124378.86",
            "eval_cost": "108432.00",
            "prefix_cost": "232810.86",
            "data_read_per_join": "380M"
          },
          "used_columns": [
            "id",
            "k",
            "pad"
          ],
          "attached_condition": "(`testdb`.`sbtest2`.`k` between 400000 and 500000)"
        }
      }
    }
  }
} |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

8.0
8_0.testdb 18:25:00 >  select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.11    |
+-----------+
1 row in set (0.00 sec)

8_0.testdb 18:25:25 > select count(*) from sbtest2;
+----------+
| count(*) |
+----------+
|  1099288 |
+----------+
1 row in set (0.16 sec)

8_0.testdb 18:25:29 > select count(0),pad from sbtest2 where k between 400000 and 500000 group by pad order by pad limit 1;
+----------+-------------------------------------------------------------+
| count(0) | pad                                                         |
+----------+-------------------------------------------------------------+
|        1 | 00000012813-73478546096-60491222567-55128816668-26346684571 |
+----------+-------------------------------------------------------------+
1 row in set (1.27 sec)

8_0.testdb 18:26:04 > explain select count(0),pad from sbtest2 where k between 400000 and 500000 group by pad order by pad limit 1;        
+----+-------------+---------+------------+-------+---------------+------+---------+------+--------+----------+-----------------------------------------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                                                     |
+----+-------------+---------+------------+-------+---------------+------+---------+------+--------+----------+-----------------------------------------------------------+
|  1 | SIMPLE      | sbtest2 | NULL       | range | k_2,idx1      | idx1 | 4       | NULL | 503160 |   100.00 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+---------+------------+-------+---------------+------+---------+------+--------+----------+-----------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

Note (Code 1003): /* select#1 */ select count(0) AS `count(0)`,`testdb`.`sbtest2`.`pad` AS `pad` from `testdb`.`sbtest2` where (`testdb`.`sbtest2`.`k` between 400000 and 500000) group by `testdb`.`sbtest2`.`pad` order by `testdb`.`sbtest2`.`pad` limit 1
8_0.testdb 18:26:23 > explain format=json select count(0),pad from sbtest2 where k between 400000 and 500000 group by pad order by pad limit 1;        
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "104331.96"
    },
    "ordering_operation": {
      "using_filesort": false,
      "grouping_operation": {
        "using_temporary_table": true,
        "using_filesort": true,
        "table": {
          "table_name": "sbtest2",
          "access_type": "range",
          "possible_keys": [
            "k_2",
            "idx1"
          ],
          "key": "idx1",
          "used_key_parts": [
            "k"
          ],
          "key_length": "4",
          "rows_examined_per_scan": 503160,
          "rows_produced_per_join": 503160,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "54015.96",
            "eval_cost": "50316.00",
            "prefix_cost": "104331.96",
            "data_read_per_join": "353M"
          },
          "used_columns": [
            "id",
            "k",
            "pad"
          ],
          "attached_condition": "(`testdb`.`sbtest2`.`k` between 400000 and 500000)"
        }
      }
    }
  }
} |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

A:

MySQL 8.0 uses a new storage engine, TempTable, for internal temporary tables.  (See MySQL Manual for details.) This engine does not have a max memory limit per table, but a common memory pool for all internal tables.  It also has its own overflow to disk mechanism, and does not overflow to InnoDB or MyISAM as earlier versions.  
The profile for 5.7 contains "converting HEAP to ondisk".  This means that the table reached the max table size for the MEMORY engine (default 16 MB) and the data is transferred to InnoDB.  Most of the time after that is spent accessing the temporary table in InnoDB.  In MySQL 8.0, the default size of the memory pool for temporary tables is 1 GB, so there will probably not be any overflow to disk in that case.

