优化ScalikeJDBC查询耗时：8万行数据查询提速咨询

代码逻辑说明（中文翻译） #

这段代码的作用是根据国家代码/名称前缀，查询对应的机场和关联跑道数据，核心逻辑如下：

1. 关联机场表（Airports）与跑道表（Runway），用两者的ID字段匹配
2. 再关联国家表（Country），用机场表的Country字段匹配国家表的Code字段
3. 过滤条件：国家代码等于输入值，或者国家名称以输入值开头

优化步骤（按优先级排序） #

• 修正关联逻辑（最可能的核心问题）
  你写的innerJoin(Runway as r).on(r.ID, a.ID)大概率存在逻辑错误：跑道表的ID是自身主键，机场表的ID是机场主键，这样关联会产生大量无效匹配甚至笛卡尔积，直接拖垮查询速度。
  正确逻辑应该是用跑道表的「所属机场ID」（比如字段名是airport_id）关联机场表的ID，修改后的代码片段：

  innerJoin(Runway as r).on(r.airportId, a.ID)
  

• 给关键字段添加索引
  针对查询的过滤、关联字段创建索引，避免数据库全表扫描：

  -- 国家表：针对精确匹配的Code、前缀匹配的Name建索引
  CREATE INDEX idx_country_code ON Country(Code);
  CREATE INDEX idx_country_name ON Country(Name);
  -- 机场表：针对关联国家的字段建索引
  CREATE INDEX idx_airports_country ON Airports(Country);
  -- 跑道表：针对关联机场的字段（比如airport_id）建索引
  CREATE INDEX idx_runway_airport_id ON Runway(airport_id);
  

• 优化查询逻辑，避免OR导致索引失效
  OR条件容易让数据库放弃使用索引，可拆成两个查询用UNION合并，示例代码：

  def findAirportAndRunwayByCountry(code: String)(implicit session: DBSession = AutoSession): List[(Airports, Runway)] = {
    val (c, a, r) = (Country.syntax("c"), Airports.syntax("a"), Runway.syntax("r"))
    
    // 精确匹配国家Code的查询
    val queryByCode = select.from(Airports as a)
      .innerJoin(Runway as r).on(r.airportId, a.ID)
      .innerJoin(Country as c).on(c.Code, a.Country)
      .where.eq(c.Code, code)
    
    // 前缀匹配国家Name的查询
    val queryByName = select.from(Airports as a)
      .innerJoin(Runway as r).on(r.airportId, a.ID)
      .innerJoin(Country as c).on(c.Code, a.Country)
      .where.like(c.Name, code + "%")
    
    // 合并两个查询并去重
    withSQL(queryByCode.union(queryByName)).map(rs => (Airports(rs), Runway(rs))).list.apply()
  }
  

• 减少数据传输量
  不要用select.from查询所有字段，明确指定业务需要的字段，比如：

  select(a.id, a.name, r.length, r.surface).from(Airports as a)...
  

• 优化结果集处理
  如果查询结果条数较多，别一次性用list加载所有数据到内存，改用stream分批处理：

  withSQL(...).map(rs => (Airports(rs), Runway(rs))).stream.foreach { case (airport, runway) =>
    // 逐条处理数据
  }
  

• 分析执行计划定位瓶颈
  用数据库的EXPLAIN命令查看原查询的执行计划，比如MySQL中执行：

  EXPLAIN SELECT * FROM Airports a INNER JOIN Runway r ON r.ID = a.ID INNER JOIN Country c ON c.Code = a.Country WHERE c.Code = 'XXX' OR c.Name LIKE 'XXX%';
  

  根据执行计划判断是否存在全表扫描、笛卡尔积等问题，针对性调整。

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