mysql distinct 查詢疑問

問題描述

需求

想知道最近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的呢？

問題解答

這是由于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) .

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