MySQL会短路OR​​DER BY子句吗? [英] Does MySQL short-circuit the ORDER BY clause?

问题描述

给出此SQL:

SELECT * FROM mytable ORDER BY mycolumn, RAND()

假设mycolumn恰好只包含唯一值(因此，包含足够的信息来执行ORDER BY)，MySQL是否会短路操作并跳过对其余值的评估?

Assuming that mycolumn happens to only contain unique values (and hence, contains enough information to perform the ORDER BY), does MySQL short-circuit the operation and skip evaluating the rest?

推荐答案

我认为这就是答案. Mysql使用不同的计划，并且无法执行延迟评估(麻烦").

I think this is the answer. Mysql uses different plans and can't perform lazy evaluation (o "hort-circuit").

mysql> explain select * from avatar  order by id;
+----+-------------+--------+-------+---------------+---------+---------+------+-------+-------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows  | Extra |
+----+-------------+--------+-------+---------------+---------+---------+------+-------+-------+
|  1 | SIMPLE      | avatar | index | NULL          | PRIMARY | 8       | NULL | 28777 |       |
+----+-------------+--------+-------+---------------+---------+---------+------+-------+-------+
1 row in set (0.00 sec)

mysql> explain select * from avatar  order by id, name;
+----+-------------+--------+------+---------------+------+---------+------+-------+----------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows  | Extra          |
+----+-------------+--------+------+---------------+------+---------+------+-------+----------------+
|  1 | SIMPLE      | avatar | ALL  | NULL          | NULL | NULL    | NULL | 28777 | Using filesort |
+----+-------------+--------+------+---------------+------+---------+------+-------+----------------+
1 row in set (0.00 sec)
mysql> explain select * from avatar  order by id, RAND();
+----+-------------+--------+------+---------------+------+---------+------+-------+---------------------------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows  | Extra                           |
+----+-------------+--------+------+---------------+------+---------+------+-------+---------------------------------+
|  1 | SIMPLE      | avatar | ALL  | NULL          | NULL | NULL    | NULL | 28782 | Using temporary; Using filesort |
+----+-------------+--------+------+---------------+------+---------+------+-------+---------------------------------+
1 row in set (0.00 sec)

这篇关于MySQL会短路OR​​DER BY子句吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！