查询最新的记录，换句话说，过滤相同的数据以得到最新的记录，同时探讨GROUP BY的分组规则,并优化按id筛选数据和优化MySQL派生表(derived)

小半 • 2023年2月12日上午10:31 • 技术随笔 • 阅读 225

今天项目有这样的需求：查询小票充值的订单记录，获取相同单号的最新一条记录，并根据状态【order_status】来处理其他逻辑，如图所示：

上图中的SQL语句为:

SELECT 
	id, createAt, out_trade_no, order_status, status_msg 
from 
	table_bill  
WHERE 
	out_trade_no ='2020042XXXXXXX3116' 
order BY id desc

我们应该拿到id= 97的这条记录，因而想到的方案，也是大多数网上的方案：按id降序的方式设计子表，主表以订单号分组，如SQL所示：

SELECT 
 * 
FROM (
        SELECT 
            id, createAt, out_trade_no, order_status, status_msg 
        from 
            table_bill  
        WHERE 
            operation = 1 and out_trade_no ='2020042XXXXXXX3116'
        order BY 
            createAt desc 
    ) a  
GROUP BY out_trade_no order BY createAt asc;

实际得到的结果是：

并没有得到预期的结果，也就是说，没有拿到最新的记录，于是，排查问题出在哪里，原来问题出现在GROUP BY。
我们都知道GROUP BY是分组关键字，它是按原生表默认排序【主键升序】下各自组别的第一条数据。什么是原生表默认排序？即打开数据库表展示的结果就是原生表默认排序，可以理解成每条记录的创建时间。
我们使用SQLSELECT id, createAt, out_trade_no, order_status, status_msg from table_bill WHERE out_trade_no ='2020042XXXXXXX3116' ;，查询该订单号的默认排序，结果如图所示：

果然第一条数据是准备支付的，网上这种查询方式行不通，于是，修改成如下SQL语句查询：

SELECT 
    t_out.id, t_out.createAt, t_out.out_trade_no, t_out.order_status, t_out.status_msg
FROM 
    table_bill t_out,
    (
        SELECT 
            max(`id`) id 
        FROM 
            table_bill 
        where 
            operation = 1 and out_trade_no ='2020042XXXXXXX3116'
        GROUP BY 
            `out_trade_no`
    ) t_inner
where 
    t_out.id = t_inner.id 
ORDER BY 
    t_out.id ASC

查询得到的结果，如图所示：

这就方式便达到我们的预期，因而，网上查询按id降序的子表，主表以out_trade_no分组的方式不一定行得通，使用时要慎重。

但后期在开发的过程中，发现这种方式也不是太好，上面的SQL语句使用explain就会发现，其出现了派生表，如图所示：

派生表的查询是非常低效的，因为它的Type =all，于是修改上面的代码

EXPLAIN SELECT DISTINCT
	  id, createAt, out_trade_no,order_status, status_msg
FROM
	table_bill   
WHERE
	id in ( 
		SELECT max( `id` ) id FROM table_bill WHERE operation = 1 AND out_trade_no ='2020042XXXXXXX3116' GROUP BY `out_trade_no` 
	) 
ORDER BY
	id ASC