3M条数据下MySQL查询过慢及连接异常优化求助

问题场景 #

涉及6张业务表的关联查询，已为相关字段添加索引，但查询耗时30-40秒，偶发「MySQL Server has gone away」错误。查询语句如下：

SELECT * FROM 
   salesman_access sa 
 JOIN 
   client_master cm ON sa.client_id = cm.client_id 
 JOIN 
   pricing_detail_master pdm ON pdm.pricing_group = cm.pricing_group 
 JOIN
   prd_acc pa ON pa.client_id = cm.client_id 
 WHERE 
   sa.salesman_id = 202

优化建议 #

1. 精准查询字段，避免SELECT * #

SELECT *会拉取所有表的全部字段，尤其是prd_acc（300万条）这类大表，会产生海量冗余数据，大幅增加IO、内存消耗，不仅拖慢查询速度，还容易触发连接超时。只查询业务需要的字段，比如：

SELECT sa.salesman_id, cm.client_name, pdm.sku_code, pa.acc_amount
FROM ... -- 后续关联逻辑不变

2. 优化索引策略，消除冗余回表 #

• salesman_access表：建立联合索引(salesman_id, client_id)，WHERE条件过滤salesman_id后，直接通过索引拿到client_id，无需回表查询原数据；
• client_master表：建立联合索引(client_id, pricing_group)，关联时直接从索引获取pricing_group，减少回表开销；
• prd_acc表：确保client_id字段有独立索引，快速定位单客户的所有账户数据；
• pricing_detail_master表：确认pricing_group索引有效，保证按定价组快速过滤SKU数据。

3. 缩小中间结果集，调整关联顺序 #

先通过WHERE条件过滤出最小范围的数据，再逐步关联其他表，避免生成过大的中间结果。可以用子查询提前筛选目标客户：

SELECT 所需字段列表
FROM (
    SELECT cm.client_id, cm.pricing_group
    FROM salesman_access sa
    JOIN client_master cm ON sa.client_id = cm.client_id
    WHERE sa.salesman_id = 202
) AS filtered_clients
JOIN pricing_detail_master pdm ON filtered_clients.pricing_group = pdm.pricing_group
JOIN prd_acc pa ON filtered_clients.client_id = pa.client_id

4. 解决「MySQL Server has gone away」错误 #

• 调整超时参数：若查询耗时超过MySQL的wait_timeout或interactive_timeout阈值，会触发连接断开。可临时调大参数（如SET GLOBAL wait_timeout=300;），但核心还是优化查询减少耗时；
• 调大数据包限制：若结果集过大超出max_allowed_packet设置，也会导致连接中断，可适当调大该参数（如SET GLOBAL max_allowed_packet=67108864;）。

5. 长期优化：分表或数据归档 #

• prd_acc表：若数据带有时间维度，可按月份/季度分表，查询时仅访问目标时间段的分表，减少扫描数据量；
• salesman_access表：若单销售关联客户数较多，可按salesman_id做水平分表，缩小单表数据范围。

6. 用执行计划定位瓶颈 #

执行EXPLAIN分析查询语句，查看每一步的扫描行数、索引使用情况，针对性解决全表扫描或结果集过大的问题：

EXPLAIN SELECT * FROM salesman_access sa JOIN client_master cm ON sa.client_id = cm.client_id JOIN pricing_detail_master pdm ON pdm.pricing_group = cm.pricing_group JOIN prd_acc pa ON pa.client_id = cm.client_id WHERE sa.salesman_id = 202;

内容的提问来源于stack exchange，提问作者Vivek Makwana