版本区别

MySQL 5.7

默认情况下GROUP BY隐式排序(即，缺少GROUP BY列的ASC或DESC指示符)。但是，不推荐依赖于隐式GROUP BY排序(即，在没有ASC或DESC指示符的情况下排序)或GROUP BY的显式排序(即，通过对GROUP BY列使用显式ASC或DESC指示符)。要生成给定的排序 ORDER，请提供ORDER BY子句。

MySQL 8.0

在MySQL 8.0中，不再发生这种情况，因此不再需要在末尾指定ORDER BY NULL来抑制隐式排序 数据测试

数据测试

# MySQL版本：5.7
-- 隐式排序
SELECT pid,appName from T group by appName;    
= SELECT pid,appName from T group by appName asc;
= SELECT pid,appName from T group by appName order by appName asc;

-- 显式排序
SELECT pid,appName from T group by appName order by appName desc;

# MySQL版本：8.0
SELECT pid,appName from T group by appName;
# 在MySQL 8.0中，GROUP BY隐式排序不支持了，上面测试例子是无序的。GROUP BY显示排序则直接报错。
SELECT pid,appName from T group by appName DESC;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DESC' at line 1

group by索引

-- 有索引：appName_idx
mysql> EXPLAIN SELECT appName from 0122_csj_demo GROUP BY appName \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: 0122_csj_demo
   partitions: NULL
         type: index
possible_keys: appName_idx
          key: appName_idx
      key_len: 515
          ref: NULL
         rows: 28
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

-- 没有索引
mysql> EXPLAIN SELECT appName from 0122_csj_demo GROUP BY appName \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: 0122_csj_demo
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 28
     filtered: 100.00
        Extra: Using temporary; Using filesort
1 row in set, 1 warning (0.00 sec)

-- 用户可以显式指定ORDER BY NULL就能让MySQL知道GROUP BY不需要排序。
mysql> EXPLAIN SELECT appName from 0122_csj_demo GROUP BY appName ORDER BY null 
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: 0122_csj_demo
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 28
     filtered: 100.00
        Extra: Using temporary
1 row in set, 1 warning (0.00 sec)