如何从存在多个关联的两个MySQL表中高效查询数据 兼容Rails ActiveRecord

针对大数据量场景，不推荐关联10次item表的查询方式，会导致结果集笛卡尔积膨胀、查询效率大幅下降，优先选择以下两种方案：

Rails ActiveRecord 方案（推荐，适配日常业务、分页查询场景） #

该方案仅执行2次SQL查询，完全避免N+1问题，性能远高于多表关联：

1. 首先给Ranking模型定义关联，用循环简化10个关联的编写：

class Ranking < ApplicationRecord
  # 自动生成first_item到tenth_item的10个关联
  1.upto(10) do |i|
    belongs_to :"#{i.ordinalize}_item", class_name: 'Item', foreign_key: "#{i.ordinalize}_item_id", optional: true
  end
end

2. 查询时用preload预加载所有关联的item数据，一次性拉取所有用到的item记录，再手动映射字段：

# 预加载所有关联item，仅执行2次SQL：一次查rankings，一次查所有关联的item
rankings = Ranking.limit(10).preload(1.upto(10).map { |i| :"#{i.ordinalize}_item" })

# 映射为预期返回格式
result = rankings.map do |r|
  {
    id: r.id,
    search_text: r.search_text,
    first: r.first_item&.item_code,
    second: r.second_item&.item_code,
    third: r.third_item&.item_code,
    forth: r.forth_item&.item_code,
    # 按同样规则补全剩余6个字段即可
    tenth: r.tenth_item&.item_code
  }
end

3. 如果是百万级以上的批量处理，用find_each分批加载避免内存溢出：

Ranking.find_each(batch_size: 1000).preload(1.upto(10).map { |i| :"#{i.ordinalize}_item" }) do |r|
  # 单条记录处理逻辑
end

纯SQL方案（适配全量导出、离线批处理场景） #

适合直接在数据库层执行的批处理场景，以MySQL 8.0+为例：

WITH item_mapping AS (
  -- 先拉取需要的item映射关系，走主键索引速度极快
  SELECT id, item_code FROM item
)
SELECT 
  r.id,
  r.search_text,
  (SELECT item_code FROM item_mapping WHERE id = r.first_item_id) AS first,
  (SELECT item_code FROM item_mapping WHERE id = r.second_item_id) AS second,
  (SELECT item_code FROM item_mapping WHERE id = r.third_item_id) AS third,
  (SELECT item_code FROM item_mapping WHERE id = r.forth_item_id) AS forth,
  -- 按同样规则补全剩余6个字段即可
  (SELECT item_code FROM item_mapping WHERE id = r.tenth_item_id) AS tenth
FROM rankings r
-- 可自行添加查询条件
LIMIT 10;

性能说明 #

两种方案均为两次查询逻辑：第一次拉取rankings数据，第二次拉取所有关联的item映射关系，相比10次JOIN的方式有以下优势：

• 避免多表关联导致的结果集膨胀，数据传输量、内存占用降低90%以上
• item表查询走主键索引，耗时可忽略
• 支持item数据缓存复用，高频查询场景下性能可进一步提升

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