添加GROUP BY cmb.Centris_No后SQL查询执行极慢的优化方案咨询

你的查询在添加GROUP BY cmb.Centris_No后性能骤降，结合已做的优化和表结构，我整理了几个针对性的优化方向：

1. 修正SELECT与GROUP BY的字段一致性问题 #

你当前SELECT语句包含t1.broker_name、t1.agency_name等未在GROUP BY中声明的字段，虽然MySQL关闭ONLY_FULL_GROUP_BY时允许运行，但这会迫使数据库生成临时表处理非聚合字段逻辑，是性能瓶颈的核心原因之一。

可以根据业务逻辑调整：

• 若每个Centris_No对应的经纪/机构信息唯一，将所有非聚合字段加入GROUP BY：

  GROUP BY cmb.Centris_No, t1.broker_name, t1.agency_name, t2.type, cmb.Price, cmb.Rent_Price
  

• 若无需保留所有匹配的经纪信息，用聚合函数（如MAX()/MIN()）包裹非GROUP BY字段，明确聚合规则：

  SELECT MAX(t1.broker_name), MAX(t1.agency_name), MAX(t2.type), cmb.Centris_No, MAX(cmb.Price) AS sell_price, MAX(cmb.Rent_Price) AS rent_price
  ...
  GROUP BY cmb.Centris_No
  

2. 用UNION ALL替代UNION减少开销 #

子查询中的UNION会对三个表的结果集做去重和排序，耗时极高。如果三个all_mls_#表的Centris_No无重复数据，换成UNION ALL跳过去重步骤，能大幅降低子查询执行时间：

INNER JOIN (
  SELECT * FROM all_mls_1_i 
  UNION ALL SELECT * FROM all_mls_2_i 
  UNION ALL SELECT * FROM all_mls_3_i
) cmb ON t2.mls_id = cmb.Centris_No 

3. 调整查询顺序：先过滤再JOIN #

当前查询先JOIN所有表再过滤分组，可先筛选符合条件的Centris_No，再关联其他表，减少JOIN的数据量：

SELECT t1.broker_name, t1.agency_name, t2.type, filtered_cmb.Centris_No, filtered_cmb.Price AS sell_price, filtered_cmb.Rent_Price AS rent_price
FROM (
  SELECT Centris_No, Price, Rent_Price 
  FROM all_mls_1_i WHERE target_date > 20210101
  UNION ALL
  SELECT Centris_No, Price, Rent_Price 
  FROM all_mls_2_i WHERE target_date > 20210101
  UNION ALL
  SELECT Centris_No, Price, Rent_Price 
  FROM all_mls_3_i WHERE target_date > 20210101
  GROUP BY Centris_No, Price, Rent_Price
) filtered_cmb
INNER JOIN brokers_to_listings t2 ON filtered_cmb.Centris_No = t2.mls_id
INNER JOIN brokers_global2 t1 ON t2.broker_id = t1.broker_id
WHERE t1.agency_name LIKE '%String%'
LIMIT 0, 50000

4. 检查JOIN字段的类型一致性 #

确保brokers_to_listings.mls_id和cmb.Centris_No的字段类型完全一致（均为varchar(25)）。类型不同会触发MySQL隐式转换，导致Centris_No的索引失效，JOIN操作退化为全表扫描，严重拖慢速度。

5. 优化agency_name的过滤逻辑 #

WHERE t1.agency_name LIKE '%String%'是前缀模糊匹配，完全无法使用索引，会导致brokers_global2全表扫描。可尝试：

• 改为后缀匹配（LIKE 'String%'）或全匹配（=），利用索引提速；
• 给agency_name创建全文索引，用MATCH() AGAINST()替代LIKE；
• 提前缓存符合agency_name LIKE '%String%'的经纪人ID到临时表，再关联查询。

6. 用DISTINCT替代GROUP BY（针对唯一键场景） #

因为Centris_No是唯一键，GROUP BY cmb.Centris_No本质是去重，换成SELECT DISTINCT可能生成更高效的执行计划：

SELECT DISTINCT t1.broker_name, t1.agency_name, t2.type, cmb.Centris_No, cmb.Price AS sell_price, cmb.Rent_Price AS rent_price 
FROM brokers_global2 t1 
INNER JOIN brokers_to_listings t2 ON t1.broker_id = t2.broker_id 
INNER JOIN (
  SELECT * FROM all_mls_1_i 
  UNION ALL SELECT * FROM all_mls_2_i 
  UNION ALL SELECT * FROM all_mls_3_i
) cmb ON t2.mls_id = cmb.Centris_No 
WHERE t1.agency_name LIKE '%String%' AND cmb.target_date > 20210101 
LIMIT 0 , 50000

7. 调整MySQL配置参数 #

若查询需生成临时表，默认大小可能不足以在内存中处理，导致写入磁盘变慢。可调整以下参数：

tmp_table_size = 64M
max_heap_table_size = 64M

让临时表优先在内存中处理，提升分组和排序效率。

8. 合并all_mls系列表 #

如果业务允许，将三个all_mls_#表合并为一个，避免每次查询的UNION操作，从根源减少数据处理开销。合并后保留Centris_No的唯一索引和target_date的索引即可。

内容的提问来源于stack exchange，提问作者Robert Sinclair