如何优化如下SQL查询？大表数据下查询耗时过长求建议

Hey，针对你这个all_products超7万条后查询耗时2~7秒的问题，我先贴出你的原查询方便分析：

SELECT a.id,b.size,c.item_no,d.size_id 
FROM inv a 
LEFT JOIN product b ON a.id=b.id 
LEFT JOIN all_products c ON a.id=c.id and a.size=c.size 
LEFT JOIN (SELECT qty, code, code2,status FROM prod_stock where status='1') AS d ON c.web_code=d.code 
LEFT JOIN prod_size e ON a.size_id=e.prod_size_id 
WHERE a.id='123456' 
ORDER BY a.id,e.prod_size_id;

从语句结构和你的场景来看，慢查询的核心大概率是关联字段索引缺失和部分逻辑冗余，下面是具体的优化建议：

• 给核心关联/过滤字段补全索引（最关键！）

  • 对于all_products这个大表，你的关联条件是a.id=c.id AND a.size=c.size，后续还要用c.web_code关联prod_stock，直接建个复合覆盖索引：CREATE INDEX idx_all_products_id_size_webcode ON all_products(id, size, web_code); 这个索引能让数据库直接从索引里拿到需要的关联字段，不用扫全表，这应该能大幅降低耗时。
  • 检查inv表的id是不是主键——如果不是，赶紧把它设为主键或者加唯一索引，毕竟你的WHERE条件是a.id='123456'，主键索引的查找速度是最快的；另外关联prod_size用到了a.size_id=e.prod_size_id，给prod_size.prod_size_id加个普通索引（如果还没加的话）。
  • prod_stock的子查询过滤了status='1'，还要关联c.web_code=d.code，建索引：CREATE INDEX idx_prod_stock_code_status ON prod_stock(code, status); 这样子查询能快速定位符合条件的数据，避免全表扫描。
  • product表的关联条件是a.id=b.id，如果product.id不是主键，也给它加个索引。

• 把子查询改成直接关联，减少冗余
  你现在用子查询先筛选prod_stock的status='1'再关联，其实可以直接把过滤条件放到JOIN里，让数据库优化器生成更高效的执行计划：

  LEFT JOIN prod_stock d ON c.web_code=d.code AND d.status='1'
  

  这样不用先生成临时子查询结果集再关联，能省不少内存和CPU开销。

• 清理冗余的关联和字段
  我注意到你的SELECT字段里有b.size，但product是LEFT JOIN，如果你不需要这个字段，或者product的size和其他表的重复，完全可以去掉这个关联；另外还有个小问题——你的子查询只选了qty, code, code2,status，但SELECT里写了d.size_id，这会直接报错的，先确认这个字段是不是写错了，避免无效查询。

• 用EXPLAIN定位具体瓶颈
  执行下面的命令看执行计划，能精准找到慢的根源：

  EXPLAIN SELECT a.id,b.size,c.item_no,d.size_id FROM inv a LEFT JOIN product b ON a.id=b.id LEFT JOIN all_products c ON a.id=c.id and a.size=c.size LEFT JOIN (SELECT qty, code, code2,status FROM prod_stock where status='1') AS d ON c.web_code=d.code LEFT JOIN prod_size e ON a.size_id=e.prod_size_id WHERE a.id='123456' ORDER BY a.id,e.prod_size_id;
  

  重点看这几个列：

  • type：如果是ALL就是全表扫描，要优先解决；最好是ref/eq_ref这种索引查找。
  • rows：预估扫描的行数，如果all_products的行数接近7万，说明索引没生效。
  • Extra：如果出现Using filesort/Using temporary，说明排序或者临时表开销大，需要调整；出现Using index就是用到了覆盖索引，是好现象。

• 简化排序逻辑
  你的ORDER BY写了a.id,e.prod_size_id，但WHERE条件已经把a.id固定为'123456'了，所以排序只需要e.prod_size_id就行，去掉a.id能减少排序的开销，尤其是结果集比较大的时候。

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