order by我们都知道是用于排序的
今天看到一些很奇妙的写法
order by --id
order by null
以前没见过这东西,老稀奇了。兴致来了,就做个实验研究一下
mysql> explain select * from test1 ;
+----+-------------+-------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | test1 | ALL | NULL | NULL | NULL | NULL | 5160 | NULL |
+----+-------------+-------------+------+---------------+------+---------+------+------+-------+
1 row in set
裸奔的执行计划,作为对比
mysql> explain select * from test1 order by null;
+----+-------------+-------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | test1 | ALL | NULL | NULL | NULL | NULL | 5160 | NULL |
+----+-------------+-------------+------+---------------+------+---------+------+------+-------+
1 row in set
order by null的执行计划,没有什么变化
mysql> explain select * from test1 order by --test1ID;
->
-> ;
+----+-------------+-------------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+---------------+------+---------+------+------+----------------+
| 1 | SIMPLE | test1 | ALL | NULL | NULL | NULL | NULL | 5160 |Using filesort |
+----+-------------+-------------+------+---------------+------+---------+------+------+----------------+
1 row in set
在使用--的时候竟然走了磁盘排序,从这里受到了启发。
实验发现order by -id是按id倒序排序,--就是两次倒序的正序排序,但是经过两次是要走磁盘排序的。
-------------------------------------------------------------------------------------------------
那order by null是什么意思呢,经高人指点,实际就是不排序的意思。应用的场合就是使用group by的场合
我们知道默认的group by是要进行排序的,这个可能会有很高的性能消耗,如果order by null可以规避掉,这自然是极好的。
mysql> explain select count(*) from test_profile group by kind;
+----+-------------+----------------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+------+---------------+------+---------+------+------+---------------------------------+
| 1 | SIMPLE | test_profile | ALL | NULL | NULL | NULL | NULL | 165 |Using temporary; Using filesort |
+----+-------------+----------------+------+---------------+------+---------+------+------+---------------------------------+
1 row in set
没有使用order by null的场合,可见使用了临时空间,也用了磁盘进行排序。
mysql> explain select count(*) from test_profile group by kind order by null;
+----+-------------+----------------+------+---------------+------+---------+------+------+-----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+------+---------------+------+---------+------+------+-----------------+
| 1 | SIMPLE | test_profile | ALL | NULL | NULL | NULL | NULL | 165 | Using temporary |
+----+-------------+----------------+------+---------------+------+---------+------+------+-----------------+
1 row in set
使用order by null的场合,还是进行了排序!但是不再使用磁盘排序了!可见使用order by null可以规避掉部分的排序。
经过对比,order by null的数据的是无序,这两条sql并不等价,但是在不需要排序的场合,完全可以用order by null规避掉group by中的排序!