关联双表SQL查询耗时过长求助：主表90k行副表200k+行

这种主副表关联查询卡慢的情况我之前做项目时碰到过好几次，尤其是数据量破十万之后，分步查询很容易因为循环访问或者索引缺失拖垮整体速度，给你几个实际用过的优化思路：

• 用JOIN合并查询，替换分步操作
  你现在是先捞主表的id再去副表补数据，如果是循环着每个id查一次副表，那200k+的数据量肯定慢到离谱。不如直接把两个表用JOIN合并成单条SQL，让数据库的优化器来处理关联逻辑，效率会提升很多。举个例子：

  SELECT m.id, s.needed_column1, s.needed_column2
  FROM Main m
  LEFT JOIN SubTable s ON m.id = s.main_id  -- 这里替换成你的实际关联字段
  WHERE m.contains_tags IS NOT NULL 
    AND m.contains_mediums IS NOT NULL 
    AND m.contains_techniques IS NOT NULL
  

  注意选合适的JOIN类型：如果副表必须有对应主表的记录，用INNER JOIN；如果允许副表无对应数据，用LEFT JOIN。另外别用SELECT *，只查你需要的字段，能减少数据传输和内存占用。

• 给关键字段加针对性索引
  慢查询的核心九成是索引问题，你得检查这几个关键点：

  • 主表的过滤字段：contains_tags、contains_mediums、contains_techniques这三个是你的过滤条件，可以建个复合索引：

    CREATE INDEX idx_main_filter ON Main(contains_tags, contains_mediums, contains_techniques);
    

  • 副表的关联字段（比如和主表id对应的main_id）：必须建索引，JOIN的时候数据库能快速定位对应记录：

    CREATE INDEX idx_sub_main_id ON SubTable(main_id);
    

  • 如果副表还有其他要查询的字段，可以把关联字段和这些字段一起建覆盖索引，这样查询时直接从索引拿数据，不用回表查原数据：

    CREATE INDEX idx_sub_main_needed ON SubTable(main_id, needed_column1, needed_column2);
    

• 用执行计划定位瓶颈
  不管怎么优化，先看执行计划才是最靠谱的。在你的查询语句前加EXPLAIN，比如：

  EXPLAIN SELECT m.id, s.needed_column1 FROM Main m LEFT JOIN SubTable s ON m.id = s.main_id WHERE ...;
  

  重点看这几个列：

  • type：最好是range、ref、eq_ref，如果是ALL说明是全表扫描，肯定慢；
  • key：这里显示的是查询用到的索引，如果是空的，说明索引没生效；
  • rows：预估扫描的行数，如果和实际数据量差太多，可能需要更新统计信息。

• 分批次处理大结果集
  如果最终要返回的结果集特别大（比如几万条以上），一次性查出来不仅慢，还可能占满内存。可以按主表的id分段查询，比如：

  -- 第一次查id小于10000的记录
  SELECT m.id, s.needed_column1 FROM Main m LEFT JOIN SubTable s ON m.id = s.main_id WHERE m.id < 10000 AND ...;
  -- 第二次查id在10000到20000之间的，以此类推
  

  每次处理小批量数据，整体耗时反而可能降低，也能避免一次性占用过多数据库资源。

• 临时表/CTE优化复杂过滤场景
  如果主表的过滤逻辑特别复杂，第一步查出来的id集合不大，可以先把主表的结果放到临时表或者CTE（公共表表达式）里，再和副表关联：
  用CTE的例子：

  WITH FilteredMain AS (
    SELECT id FROM Main WHERE contains_tags IS NOT NULL AND contains_mediums IS NOT NULL AND contains_techniques IS NOT NULL
  )
  SELECT fm.id, s.needed_column1 FROM FilteredMain fm LEFT JOIN SubTable s ON fm.id = s.main_id;
  

  如果用临时表，还可以给临时表加索引，进一步提速：

  CREATE TEMPORARY TABLE temp_main AS SELECT id FROM Main WHERE ...;
  CREATE INDEX idx_temp_main_id ON temp_main(id);
  SELECT tm.id, s.needed_column1 FROM temp_main tm LEFT JOIN SubTable s ON tm.id = s.main_id;
  

  这种方式在主表过滤后数据量很小的时候，效果比直接JOIN好很多。

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