探索云世界
PolarDB有一个SQL.语句。 没有group by 的时候很快 0.4S。有group by 就很慢很慢 60S。我调整 tmp_table_size 参数，几乎没有作用。 请问还有什么优化方案吗？explain
select a.,d. ,b.advanceid,b1.BUF10,b2.qty,b3.BUF10,c.attrpricetrace,bu.b2b_isputon,mu.b2b_isputon,bg.billdetailid,bs.Flag
from erp_bill_index a force index(idx_for_sale_analysis)
#
datascope_join_d
inner join erp_goods_Stock_inout_deal d force index(idx_for_sale_analysis)
ON d.profileid= a.profileid AND d.billid= a.billid
LEFT JOIN erp_bill_sale b force index(idx_for_sale_analysis) ON-- b.billid = a.billid AND a.profileid= b.profileid and
b.billid = a.billid and d.billdetailid= b.billdetailid -- and b.billtype in (601,607)
LEFT JOIN erp_bill_sale_exchange b1 ON b1.billid = a.billid AND a.profileid= b1.profileid and d.billdetailid= b1.billdetailid
LEFT JOIN erp_bill_real_inout b2 ON b2.frombillid = a.billid AND a.profileid= b2.profileid AND b2.frombilldetailid=d.billdetailid AND a.billtype=611
LEFT JOIN erp_bill_sale_return b3 ON b3.billid = a.billid AND a.profileid= b3.profileid and d.billdetailid= b3.billdetailid
LEFT JOIN erp_bill_detail_goods_attribute c ON c.billdetailid= d.billdetailid AND c.billid = d.billid AND c.profileid= d.profileid
left join erp_bill_index_ext e force index(idx_for_sale_analysis)
on e.billid=a.billid and e.profileid=a.profileid
LEFT JOIN bas_goods_unit bu ON d.gid=bu.gid AND d.profileid=bu.profileid AND bu.uid=d.muid
left join bas_goods_unit mu on mu.gid=d.gid and mu.profileid=b.profileid and mu.usetype&1>0
LEFT JOIN erp_contract_bill_goodscash bg ON bg.billid=d.billid AND bg.billdetailid=d.billdetailid AND bg.profileid=d.profileid
left join bas_store bs on d.sid = bs.sid
#
shownode inner join bas_goods g on g.profileid=d.profileid and g.gid=d.gid
#
dealer left join bas_dealer bd on bd.did=a.did and bd.profileid=a.profileid
LEFT JOIN bas_goods_unit big ON d.gid=big.gid AND d.profileid=big.profileid AND big.sort=3 and big.Flag = 0
LEFT JOIN bas_goods_unit middle ON d.gid=middle.gid AND d.profileid=middle.profileid AND middle.sort=2 and middle.flag = 0
left join bas_clerk bc on bc.cid=a.cid and bc.profileid=a.profileid
#
showbranch left join bas_branch br on br.profileid=a.profileid and br.bid=a.bid
where a.billtype in(601,607,603,715,602) AND a.status >=20 and a.ifred = 0 and a.profileid = 200015950
AND ( TRUE or bc.cid=638839)
#
ginfo
and true and a.billdate>='2023-01-01' and a.billdate<'2024-01-09'
这个是没有group by 的， show profile 查看。耗时都在sending_data
写回答
该查询涉及大量的表连接和条件过滤，当添加GROUP BY子句时，MySQL可能需要创建临时表来处理分组操作，这通常会消耗更多的内存资源并显著增加执行时间。
针对你目前的情况，以下是一些可能的优化方案：
索引优化：
a.profileid, a.billid, d.billdetailid 等字段。同时，考虑对WHERE子句中用到的字段建立索引，比如 a.billtype, a.status, a.ifred, a.profileid, bc.cid 和日期字段。减少JOIN操作：
预过滤数据：
避免全表扫描：
GROUP BY优化：
查询重写：
LIMIT与OFFSET：
资源调优：
最后，请结合实际业务场景和数据库数据分布情况，逐步排查和试验上述优化方案，可能需要多次调整和测试才能达到理想的效果。如果数据量实在庞大，可能还需要考虑数据库架构层面的优化，例如分区表、物化视图等高级技术手段。
根据您提供的信息，当使用GROUP BY时查询变得非常慢，而没有GROUP BY时查询速度很快。这可能是由于在执行GROUP BY操作时，数据库需要对数据进行排序和分组，这会导致性能下降。
以下是一些可能的优化方案：
索引优化：确保您的查询中使用到的列都有适当的索引。您可以使用EXPLAIN命令来分析查询计划，查看是否使用了正确的索引。如果发现某些列没有索引，可以考虑添加索引以提高查询性能。
分区表：如果您的表非常大，可以考虑将表分区。通过将表分成多个较小的分区，可以加快查询速度并提高性能。您可以根据业务需求选择适当的分区键。
调整查询逻辑：检查您的查询逻辑，看是否可以进行优化。例如，可以尝试减少JOIN操作的数量或者使用子查询来替代部分JOIN操作。还可以尝试使用更高效的聚合函数或条件来优化查询。
硬件优化：如果您的数据库服务器硬件资源有限，可能会导致查询变慢。您可以考虑升级硬件，如增加内存、使用更快的磁盘等，以提高查询性能。
数据库配置优化：检查您的数据库配置，确保参数设置合理。例如，可以调整缓冲池大小、连接数等参数，以适应您的查询需求。
而不加GROUP BY检索到500条数据就结束了，两个肯本不在一个量级上，你先包一层先LIMIT在去group by那也很快就会结束了，如果您希望提高group by..limit...的性能，可以尝试在group by的表上建立对应索引。这样可以使用流式计算，有limit的时候可以提前结束。 ，此回答整理自钉群“PolarDB 专家面对面 - 慢SQL索引选择优化器新特性”
版权声明：本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
