mysql distinct 查询疑问

Question

需求

想知道最近N条记录中某一字段取值有哪几种

select * from t;
+----+------+
| id | a    |
+----+------+
|  1 | aaa  |
|  2 | aaa  |
|  3 | bbb  |
|  4 | bbb  |
|  5 | ccc  |
|  6 | ddd  |
|  7 | ddd  |
|  8 | foo  |
|  9 | bar  |
+----+------+
# 想知道最早4条记录中 a取值有哪几种 期望是aaa bbb 但实际不满足期望
select distinct a  from t order by id limit 4;
+------+
| a    |
+------+
| aaa  |
| bbb  |
| ccc  |
| ddd  |
+------+
#必须使用这种写法
select distinct a  from (select a from t order by id limit 4) a;
+------+
| a    |
+------+
| aaa  |
| bbb  |
+------+

为什么第一种写法不行？ 似乎是先将所有a的取值都查出来再截取4个，但此时没有id啊，只有a啊。Mysql又是怎样处理order by id的呢？

Answer 1

這是由於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 2

首先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操作。