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

This is determined by the execution order of sql.
Writing order: select... from... where.... group by... having... order by... limit [offset ,] (rows)
Execution order: from... where...group by... having.... select... order by... limit
can come out, limit is the last one to be Executed.
Looking at your sql, you actually find all the distinct(a) first, and then limit 4 (4 distinct a).

Answer 2

First 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  |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+

Then add 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)

The difference is Extra: Using temporary, that is, distinct uses a temporary table to save the intermediate results.

So it can be understood this way. The execution result is to put select distinct a from t into the temporary table, and then take out the data from the temporary table and perform where and order by operations.