PolarDB有一个SQL.语句。没有group by 的时候很快0.4S。请问还有什么优化方案吗？

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

展开

收起

真的很搞笑 2024-02-15 09:08:01 68 0

3 条回答

写回答

取消提交回答

1941623231718325
该查询涉及大量的表连接和条件过滤，当添加GROUP BY子句时，MySQL可能需要创建临时表来处理分组操作，这通常会消耗更多的内存资源并显著增加执行时间。

针对你目前的情况，以下是一些可能的优化方案：
1. 索引优化：
  
  确保所有JOIN操作关联字段都已建立索引，例如 a.profileid, a.billid, d.billdetailid 等字段。同时，考虑对WHERE子句中用到的字段建立索引，比如 a.billtype, a.status, a.ifred, a.profileid, bc.cid 和日期字段。
2. 减少JOIN操作：
  
  分析是否所有的JOIN都是必要的，特别是那些LEFT JOIN且没有在WHERE或GROUP BY中被引用的字段，尝试简化或去除不必要的JOIN以减少计算量。
3. 预过滤数据：
  
  如果表很大，先用WHERE子句中的条件尽可能预先筛选出较小的数据集，再进行JOIN操作，这样可以降低后续处理的数据量。
4. 避免全表扫描：
  
  虽然你已经在某些表上指定了FORCE INDEX，但请检查其他未指定的表是否有合适的索引以避免全表扫描。
5. GROUP BY优化：
  
  分析GROUP BY的具体字段，尽量确保这些字段都有对应的索引，或者它们是连接键的一部分。
6. 查询重写：
  
  可能需要重新审视查询逻辑，看是否可以通过分解复杂查询为几个简单查询，然后在应用层进行合并处理，以提高效率。
7. LIMIT与OFFSET：
  
  如果你只需要返回部分结果，可以考虑加上LIMIT子句，减少输出的数据量。
8. 资源调优：
  
  对于MySQL实例本身，除了tmp_table_size外，还有sort_buffer_size, read_rnd_buffer_size等相关参数可以适当调整，但这需要根据实际的硬件资源和数据分布情况来定。
最后，请结合实际业务场景和数据库数据分布情况，逐步排查和试验上述优化方案，可能需要多次调整和测试才能达到理想的效果。如果数据量实在庞大，可能还需要考虑数据库架构层面的优化，例如分区表、物化视图等高级技术手段。
2024-02-18 15:04:20

赞同展开评论打赏
小周sir

面对过去，不要迷离；面对未来，不必彷徨；活在今天，你只要把自己完全展示给别人看。
根据您提供的信息，当使用GROUP BY时查询变得非常慢，而没有GROUP BY时查询速度很快。这可能是由于在执行GROUP BY操作时，数据库需要对数据进行排序和分组，这会导致性能下降。

以下是一些可能的优化方案：
1. 索引优化：确保您的查询中使用到的列都有适当的索引。您可以使用EXPLAIN命令来分析查询计划，查看是否使用了正确的索引。如果发现某些列没有索引，可以考虑添加索引以提高查询性能。
2. 分区表：如果您的表非常大，可以考虑将表分区。通过将表分成多个较小的分区，可以加快查询速度并提高性能。您可以根据业务需求选择适当的分区键。
3. 调整查询逻辑：检查您的查询逻辑，看是否可以进行优化。例如，可以尝试减少JOIN操作的数量或者使用子查询来替代部分JOIN操作。还可以尝试使用更高效的聚合函数或条件来优化查询。
4. 硬件优化：如果您的数据库服务器硬件资源有限，可能会导致查询变慢。您可以考虑升级硬件，如增加内存、使用更快的磁盘等，以提高查询性能。
5. 数据库配置优化：检查您的数据库配置，确保参数设置合理。例如，可以调整缓冲池大小、连接数等参数，以适应您的查询需求。
2024-02-16 10:08:02

赞同展开评论打赏
芯在这

而不加GROUP BY检索到500条数据就结束了，两个肯本不在一个量级上，你先包一层先LIMIT在去group by那也很快就会结束了，如果您希望提高group by..limit...的性能，可以尝试在group by的表上建立对应索引。这样可以使用流式计算，有limit的时候可以提前结束。，此回答整理自钉群“PolarDB 专家面对面 - 慢SQL索引选择优化器新特性”

2024-02-15 12:57:02

赞同展开评论打赏