该 sql 语句在生产环境执行需要 50s，如何改进它，有没有更好的解决方案 ?

Question

SELECT `a`.* FROM `cs_goods` AS `a` WHERE
( SELECT COUNT(`b`.`id`)  FROM `cs_goods` AS b 
WHERE `b`.`cs_merchant_id` = `a`.`cs_merchant_id` 
AND `b`.`created_at` > `a`.`created_at` 
AND `status` = 1 and `audit_status` = 2 and `sq_type` <> 3 
) < 3 AND `status` = 1 and `audit_status` = 2 and `sq_type` <> 3 
ORDER BY `a`.`created_at` DESC LIMIT 100;

cs_goods 表结构：

需求：拿到 cs_goods 表中最新的 100 个商品（按 created_at 倒序），且每个超市的商品不能超过 3
个（cs_merchant_id 是超市） 也就是一共需要拿到 100 个商品，但是每个超市不能超过 3 个。

Answer 1

在无法改变原有需求的前提下，又要考虑性能问题，

可以考虑降低数据基数，取最新的 1000 条数据来进行处理，

这样就比操作整张表的效率要高很多很多，压力也比较小

以下是正确的 sql，执行时间 0.111s

select a.id,a.cs_merchant_id,a.created_at,a.rank,a.status,a.audit_status,a.sq_type from 
( select b.*,@rownum:=@rownum+1,if(@id=b.cs_merchant_id,@rank:=@rank+1,@rank:=1) as rank, @id:=b.cs_merchant_id 
   from  (select g.* from cs_goods g where g.status = 1 and g.audit_status = 2 and g.sq_type <> 3 order by g.created_at desc limit 1000) b, 
         (select @rownum:=0 , @id:=null ,@rank:=0) c 
      order by b.cs_merchant_id,b.id desc 
) a having a.rank <4 order by a.created_at desc limit 100;

执行结果图：

Answer 2

可以用 left join 来链接自身：

SELECT `a`.* FROM `cs_goods` AS a 
left join `cs_goods` as b on b.`cs_merchant_id` = a.`cs_merchant_id` AND b.`status` = 1 AND b.`audit_status` = 2 AND b.`sq_type` <> 3 AND b.`created_at` < a.`created_at`
WHERE AND a.`status` = 1 AND a.`audit_status` = 2 AND a.`sq_type` <> 3
group by a.id
having count(distinct b.id) < 3;

另外，最好再给 cs_merchant_id 建一个索引。
试试呗，希望可以帮助到你。

Answer 3

要满足这种需求，还是再新建一个表比较好，表里边就存每个超市最新3个商品的id和上架时间。
需要的时候直接按 created_at 倒序取出100条数据即可