如何优化ActiveRecord/SQL关联查询以缩短执行时间？

先从查询计划里的核心瓶颈说起：**case_statuses表的全表扫描（Seq Scan）**和最后的Sort操作是当前239ms执行时间的主要来源，加上关联过程中索引覆盖不足，导致了额外的性能消耗。下面是具体的优化方案：

1. 添加针对性复合索引 #

索引是提升查询效率最直接的手段，结合你的查询逻辑和表结构，建议创建以下几个索引：

针对case_statuses表 #

当前case_statuses只有loan_application_id的单字段索引，但查询中需要关联loan_case_id、过滤deleted_at IS NULL，还要关联loan_applications的ID。创建复合索引可以彻底避免全表扫描：

CREATE INDEX index_case_statuses_on_loan_case_id_deleted_at_loan_app_id 
ON case_statuses (loan_case_id, deleted_at, loan_application_id);

这个索引能让数据库快速定位到属于某个loan_case且未被删除的case_status记录，同时直接拿到关联loan_applications所需的ID，无需回表查询额外数据。

针对loan_applications表 #

查询中需要过滤deleted_at IS NULL和case_code IS NOT NULL，添加复合索引加速条件筛选：

CREATE INDEX index_loan_applications_on_deleted_at_case_code 
ON loan_applications (deleted_at, case_code);

配合主键索引覆盖的id字段，这个索引可以快速筛选出符合条件的申请记录。

针对loan_cases表 #

查询中需要过滤deleted_at IS NULL、按rank排序，分组还需要id和case_status字段。创建覆盖索引可以同时满足过滤、排序和分组需求，直接消除查询计划中的Sort操作：

CREATE INDEX index_loan_cases_on_deleted_at_rank_id_case_status 
ON loan_cases (deleted_at, rank, id, case_status);

这个索引让数据库直接从索引中获取所有需要的字段，无需访问表数据，而且rank字段的有序性可以避免额外的排序开销。

2. 调整查询关联顺序 #

当前查询从loan_applications出发关联其他表，考虑到loan_cases的数据量更小（且有deleted_at过滤），可以调整关联顺序，从loan_cases开始查询，减少初始数据集的大小：

LoanCase.joins(case_statuses: :loan_application)
        .where(loan_applications: { case_code: nil })
        .where(deleted_at: nil)
        .where(loan_applications: { deleted_at: nil })
        .group(:id, :case_status)
        .select(:case_status, 'count(loan_applications.id) as application_count')
        .order(rank: :asc)

这样数据库会先筛选出未被删除的loan_cases，再关联对应的case_statuses和符合条件的loan_applications，大幅减少中间结果集的体积。

3. 简化Group By逻辑 #

观察你的GROUP BY子句：loan_cases.id, loan_cases.case_status。如果业务上case_status是loan_cases的固有属性（即一个loan_case对应唯一的case_status），可以只按loan_cases.id分组——因为case_status会随id唯一确定，这样能减少分组的计算开销：

# 调整后的分组和字段选择
.group(:id)
.select(:case_status, 'count(loan_applications.id) as application_count')

（注：需确保业务上loan_cases.id与case_status是一一对应的，否则不能做此调整）

4. 验证优化效果 #

每次调整后，用.explain查看新的查询计划，确认：

• 不再出现Seq Scan（全表扫描）
• Sort操作被消除（借助索引的有序性）
• GroupAggregate的开销显著降低

原始查询相关信息 #

ActiveRecord查询

LoanApplication.joins(:case_status => :loan_case).where.not(case_code: nil).group("loan_cases.id, loan_cases.case_status").select("loan_cases.case_status, count(loan_applications.id)").order("loan_cases.rank ASC")

查询计划

QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Sort (cost=2924.84..2924.85 rows=1 width=532)
Sort Key: loan_cases.rank
-> GroupAggregate (cost=0.43..2924.83 rows=1 width=532)
Group Key: loan_cases.id
-> Nested Loop (cost=0.43..2918.67 rows=1230 width=528)
-> Nested Loop (cost=0.14..1898.81 rows=1765 width=528)
Join Filter: (case_statuses.loan_case_id = loan_cases.id)
-> Index Scan using loan_cases_pkey on loan_cases (cost=0.14..12.45 rows=1 width=524)
Filter: (deleted_at IS NULL)
-> Seq Scan on case_statuses (cost=0.00..1423.03 rows=37066 width=8)
Filter: (deleted_at IS NULL)
-> Index Scan using loan_applications_pkey on loan_applications (cost=0.29..0.58 rows=1 width=4)
Index Cond: (id = case_statuses.loan_application_id)
Filter: ((deleted_at IS NULL) AND (case_code IS NOT NULL))
(14 rows)

对应的SQL查询

SELECT loan_cases.case_status, count(loan_applications.id)
FROM "loan_applications"
INNER JOIN "case_statuses" ON "case_statuses"."loan_application_id" = "loan_applications"."id" AND "case_statuses"."deleted_at" IS NULL
INNER JOIN "loan_cases" ON "loan_cases"."id" = "case_statuses"."loan_case_id" AND "loan_cases"."deleted_at" IS NULL
WHERE "loan_applications"."deleted_at" IS NULL AND ("loan_applications"."case_code" IS NOT NULL)
GROUP BY loan_cases.id, loan_cases.case_status
ORDER BY loan_cases.rank ASC

表结构定义

create_table "loan_applications", force: :cascade do |t|
t.string "name"
t.string "email"
t.string "case_code"
end
create_table "loan_cases", force: :cascade do |t|
t.string "case_status", limit: 255
t.text "description"
t.text "can_flow_to"
t.datetime "created_at"
t.datetime "updated_at"
t.datetime "deleted_at"
t.integer "rank"
end
create_table "case_statuses", force: :cascade do |t|
t.integer "loan_application_id"
t.integer "loan_case_id"
end
add_index "case_statuses", ["loan_application_id"], name: "index_case_statuses_on_loan_application_id", using: :btree

Rails模型定义

class LoanApplication
has_one :case_status, inverse_of: :loan_application, :dependent => :destroy
has_one :loan_case, :through => :case_status, :source => :loan_case
end
class CaseStatus
belongs_to :loan_application, inverse_of: :case_status
belongs_to :loan_case
end
class LoanCase
has_many :case_statuses
end

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