mysql distinct 查询疑问

Question

需求 想知道最近N条记录中某一字段取值有哪几种 {代码...} 为什么第一种写法不行？ 似乎是先将所有a的取值都查出来再截取4个，但此时没有id啊，只有a啊。Mysql又是怎样处理order by id的呢？

PHP中文网 · Answer

这是由于sql的执行顺序来决定的.
写的顺序：select ... from... where.... group by... having... order by.. limit [offset,] (rows)
执行顺序：from... where...group by... having.... select ... order by... limit
可以出来,limit是最后一个被执行的.
看你的sql,其实是先找出所有的distinct(a),然后再limit 4(4个distinct a) .

大家讲道理 · Answer

首先Explain一下

mysql> explain select *  from t order by id limit 4;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
|  1 | SIMPLE      | t     | index | NULL          | PRIMARY | 4       | NULL |    4 | NULL  |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+

然后加入distinct

mysql> explain select distinct a  from t order by id limit 4;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra           |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------+
|  1 | SIMPLE      | t     | index | NULL          | PRIMARY | 4       | NULL |    4 | Using temporary |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------+
1 row in set (0.00 sec)

区别是Extra ： Using temporary，即distinct用临时表保存中间结果。

所以可以这样理解，执行结果是把select distinct a from t放到了临时表，然后再从临时表取出数据，做了where、 order by操作。