同一张表子查询和min优化过程

业务需求是获取用户的第一笔订单信息：(实际业务用具体字段替换*)

第一版sql：（id between 1 and 100000 是替换业务条件）

select * from list where id in (
    select min(id) from bankcard where id between 1 and 100000 group by user_id
)

由于子查询中有一种 where in 子查询 性能比较糟糕  而且 min()函数也比较耗时

改进第一版：（利用use index(PRIMARY) 排序和分组 代替min函数）

select * from list where id in (
    select * from list use index(PRIMARY) where id between 1 and 100000 group by user_id
)

改进第二版：（内联查询改进where in 条件子查询）

select * from list as a inner join list as b use index(PRIMARY) on a.id =b.id and b.id between 1 and 100000 group by b.user_id

按理说 两个耗时的地方改造好应该已经大功告成，但是查询结果好像不太一样

改进第四版：（改进子查询方式）

select a.* from list as a join (select user_id,min(id) as id from list  group by user_id) as b on a.user_id=b.user_id and a.id=b.id where a.id between 1 and 100000 

效率的确高了一些

后来我突然发现一种方式 绕了一大圈居然绕回来了

终版：

select * from list use index(PRIMARY) where id between 1 and 100000 group by user_id

直接用use index(PAIMARY) 并分组 各组返回的都是最小id

返回条数完全一样

最后用sql验证一下结果是否完全一样：

select a.id,b.id from  (select * from list where id in (
select min(id) from list where id between 1 and 100000 group by user_id
)) as a join (select * from list use index(PRIMARY) where id between 1 and 100000 group by user_id
) as b on a.id = b.id

完全一样。