mysql order by group by limit 查询缓慢

末蓝、 2022-11-19 12:55 259阅读 0赞

需求：分组排序取一段数据  
问题：在正常的查询情况下查询效率低下

总表有10w,下面的查询语句查询时间达到了2.648s，时间表有80W查询时间达到了18s，显然很不符合要求

SELECT * FROM `publish_task_walmart_new` group by parent_sku,account_id order by id desc limit 0,50

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY3NDExMw_size_16_color_FFFFFF_t_70]

*  这里已经添加上了索引但是还是异常的缓慢  
    ![在这里插入图片描述][20210112135316856.png]
 *  定位问题基本上是 `group by`分组耗时时间比较长，重点优化方向，进行语句优化

SELECT * FROM (SELECT id  FROM `publish_task_walmart_new` group by parent_sku,account_id order by id desc limit 0,50
    )as maix LEFT JOIN publish_task_walmart_new as b on maix.id=b.id

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY3NDExMw_size_16_color_FFFFFF_t_70 1]

*  这里通过先分组找出id，在进行联合查询的方式，查询结果的时间变为0.171s,效率大大的提升

\-这里我们用到了联合查询的方式，在我们获取到了id过后，其实我们可以使用where in的方式进行查询。

SELECT * from publish_task_walmart_new where id in( select t.id FROM(select id FROM publish_task_walmart_new GROUP BY parent_sku,account_id order by id desc limit 0,50) as t)

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY3NDExMw_size_16_color_FFFFFF_t_70 2]  
得到的结果与联合查询相差不大

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY3NDExMw_size_16_color_FFFFFF_t_70]: /images/20221119/fa76cc79ee524debb12536ee60c7bbb8.png
[20210112135316856.png]: https://img-blog.csdnimg.cn/20210112135316856.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY3NDExMw_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20210112135509523.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY3NDExMw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY3NDExMw_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20210112140030534.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY3NDExMw==,size_16,color_FFFFFF,t_70