MySQL是否会使ORDER BY子句短路？(Does MySQL short

MySQL是否会使ORDER BY子句短路？(Does MySQL short-circuit the ORDER BY clause?)

鉴于此SQL：

SELECT * FROM mytable ORDER BY mycolumn, RAND()

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

Given this SQL:

SELECT * FROM mytable ORDER BY mycolumn, RAND()

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使用不同的计划，不能执行延迟评估（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)

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)