如下:
mysql> select * from lry_name;
+----+------+------+
| id | name | addr |
+----+------+------+
| 1 | lry | NULL |
| 2 | lry | NULL |
| 5 | lry | NULL |
| 7 | aaa | NULL |
+----+------+------+
mysql> explain select sum(id) from lry_name group by name;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| 1 | SIMPLE | lry_name | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | Using temporary; Using filesort |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select sum(id) from lry_name group by name order by null;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| 1 | SIMPLE | lry_name | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | Using temporary |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-----------------+
1 row in set, 1 warning (0.00 sec)
可以看到:
1)从上面的经验来看,ordery by null的作用是强制对查询结果禁用排序
2)如果在适应的场合没有必要对结果进行排序输入,那么我们可以用order by 取消排序,哪怕可能性能几乎没有提高,但是有的时候却可能有很大作用