该 sql 语句在生产环境执行需要 50s,如何改进它

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 表结构:

id cs_merchant_id goods_name status audit_status sq_type created_at updated_at
1 10000 测试商品1 1 2 2 2019-08-01 19:35:06 2019-08-03 09:54:32
2 10000 测试商品2 1 2 2 2019-08-01 19:35:06 2019-08-03 09:54:32

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

《L05 电商实战》
从零开发一个电商项目,功能包括电商后台、商品 & SKU 管理、购物车、订单管理、支付宝支付、微信支付、订单退款流程、优惠券等
《L02 从零构建论坛系统》
以构建论坛项目 LaraBBS 为线索,展开对 Laravel 框架的全面学习。应用程序架构思路贴近 Laravel 框架的设计哲学。
最佳答案

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

可以考虑降低数据基数,取最新的 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;

执行结果图:

file

4年前 评论
讨论数量: 13
wanghan

explain就能看出哪里慢了

4年前 评论
wanghan

把explain结果贴出来看看

4年前 评论
4年前 评论
_杭城浪子 4年前
wanghan

才这么点数据,50秒不至于吧,想办法让a表用到索引就完美解决了

4年前 评论
一个人的江湖 (楼主) 4年前
一个人的江湖 (楼主) 4年前

能说一下需求么,我感觉你这个查的东西是不用这样写的,像是简单的需求,SQL写的很复杂

4年前 评论
好人二狗 (作者) 4年前
一个人的江湖 (楼主) 4年前
一个人的江湖 (楼主) 4年前
66

表A 全表了

`status` = 1 and `audit_status` = 2 and `sq_type` <> 3 

做下组合索引
先看看效果 ,还慢就优化 子查询

4年前 评论
Sparkfly
SELECT
    `a`.* 
FROM
    `cs_goods` AS `a` 
LEFT JOIN `cs_goods` AS `b` 
ON 
    `a`.cs_merchant_id = `b`.cs_merchant_id
    AND 
    `b`.`created_at` > `a`.`created_at`
GROUP BY
    `a`.id
    `a`.cs_merchant_id
    `a`.created_at
HAVING
    count(`b`.id) <= 3
ORDER BY
    `a`.`created_at` DESC LIMIT 100;
4年前 评论
一个人的江湖 (楼主) 4年前
Sparkfly (作者) 4年前
ChenRuihua 4年前
一个人的江湖 (楼主) 4年前
aodaobi

file

4年前 评论
aodaobi

不建议使用 SELECT * 类型查询
Item: COL.001

Severity: L1

Content: 当表结构变更时,使用 * 通配符选择所有列将导致查询的含义和行为会发生更改,可能导致查询返回更多的数据。

避免在 WHERE 条件中使用函数或其他运算符
Item: FUN.001

Severity: L2

Content: 虽然在 SQL 中使用函数可以简化很多复杂的查询,但使用了函数的查询无法利用表中已经建立的索引,该查询将会是全表扫描,性能较差。通常建议将列名写在比较运算符左侧,将查询过滤条件放在比较运算符右侧。也不建议在查询比较条件两侧书写多余的括号,这会对阅读产生比较大的困扰。

同一张表被连接两次
Item: JOI.002

Severity: L4

Content: 相同的表在 FROM 子句中至少出现两次,可以简化为对该表的单次访问。

MySQL 对子查询的优化效果不佳
Item: SUB.001

Severity: L4

Content: MySQL 将外部查询中的每一行作为依赖子查询执行子查询。 这是导致严重性能问题的常见原因。这可能会在 MySQL 5.6 版本中得到改善, 但对于5.1及更早版本, 建议将该类查询分别重写为 JOIN 或 LEFT OUTER JOIN。

不建议在子查询中使用函数
Item: SUB.006

Severity: L2

Content: MySQL将外部查询中的每一行作为依赖子查询执行子查询,如果在子查询中使用函数,即使是semi-join也很难进行高效的查询。可以将子查询重写为OUTER JOIN语句并用连接条件对数据进行过滤。

4年前 评论
lchola 4年前
aodaobi (作者) 4年前

select tmp., count(tmp1.cs_merchant_id) num from (select from cs_goods where status = 1 and audit_status = 2 and sq_type <> 3 order by created_at desc) tmp join cs_goods tmp1 on tmp.id = tmp1.id group by tmp.cs_merchant_id having count(tmp1.cs_merchant_id) <= 3 limit 100

4年前 评论

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

可以考虑降低数据基数,取最新的 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;

执行结果图:

file

4年前 评论
颠倒的玉石

谁和我一样,有答案都看不懂 :sob: :sob: :sob: :sob:

4年前 评论
JohnZhang

其实我觉得另一个比较好的操作是去缓存结果,假如结果无法继续优化了可以尝试redis缓存。比如我们NOJ的热门小组,其实就是一分钟缓存一次的。这样有、高并发情况下并不会出现多个人运算多次的问题,可以减轻服务器负担。

4年前 评论
一个人的江湖 (楼主) 4年前

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!