erlang

Ecto 高级查询与数据库工程：关联、多态与性能优化

Ecto 高级查询与数据库工程：查询构建器（from/join/where/order/group/having/窗口函数）、关联预加载（preload/join_preload）、多态关联（表继承/单表继承/多态外键）、数据库事务与隔离级别（Repo.transaction/Ecto.Multi）、变更集与数据验证（cast/validate/unique_constraint）、子查询与 CTE、Ecto 适配器与连接池（DBConnection/连接池大小/超时）、与 PostgreSQL 高级特性集成（JSONB/数组/范围/全文搜索）、性能分析与 N+1 治理。

Leeting Yan 7 分钟阅读 3072 字

引言

Ecto 是 Elixir 的数据库访问层——不是 ORM，而是「数据映射和查询构建器」的组合。它保留了 SQL 的表达能力（可写子查询、CTE、窗口函数），同时提供类型安全的变更集和关联预加载。在 Phoenix 应用中，Ecto 是默认选择；在纯 Elixir 应用中，它也是与 PostgreSQL/MySQL 交互的最优雅方式。本文从高级查询构建到多态关联、从事务隔离到性能优化——给 Ecto 用户的进阶手册。

前置：/elixir-otp-supervision-tasks/（OTP 基础）、/erlang-logging-telemetry-observability/（可观测性）。

目录

1. Ecto 查询构建器核心 API

1.1 基础查询

import Ecto.Query

# 链式查询
query = from u in User,
  where: u.age >= 18,
  order_by: [desc: u.inserted_at],
  limit: 10

Repo.all(query)

1.2 查询关键字

from p in Post,
  join: c in Comment, on: c.post_id == p.id,  # 内连接
  left_join: u in assoc(p, :author),           # 左连接 + 关联语法
  where: p.published == true,
  where: ilike(p.title, "%elixir%"),           # 不区分大小写
  group_by: p.id,
  having: count(c.id) > 5,
  order_by: [asc: p.title],
  select: %{title: p.title, comment_count: count(c.id)},
  preload: [:author]                            # 预加载关联

1.3 动态查询

# 条件追加
dynamic_query = User
|> filter_by_age(params["age"])
|> filter_by_name(params["name"])
|> Repo.all()

def filter_by_age(query, nil), do: query
def filter_by_age(query, age) do
  from u in query, where: u.age >= ^age
end

记忆 Ecto 查询 = from + 链式关键字（where/join/group/order/select）；动态查询用管道 + 条件函数（nil 透传）；assoc 简写关联、ilike 不区分大小写。

2. 关联预加载与 N+1 治理

2.1 三种加载方式

# 1) preload：额外查询（推荐）
Repo.all(from p in Post, preload: [:author, :comments])

# 2) join + preload：单查询（JOIN 膨胀时慎用）
Repo.all(from p in Post, join: a in assoc(p, :author), preload: [author: a])

# 3) 后加载（手动）
posts = Repo.all(Post)
posts = Repo.preload(posts, [:author, :comments])

2.2 N+1 问题

N+1：查 1 次主表 + N 次每个主记录的关联表
# 坏：
posts = Repo.all(Post)
for p <- posts do
  author = Repo.get!(User, p.author_id)  # N 次查询！
end

# 好：
posts = Repo.all(from p in Post, preload: :author)
# 2 次查询（posts + authors）

2.3 复杂预加载

# 嵌套预加载 + 条件过滤
Repo.all(
  from p in Post,
  preload: [
    author: a,
    comments: ^from(c in Comment, where: c.approved == true, order_by: [desc: c.inserted_at])
  ]
)

记忆 三种加载——preload 额外查询（推荐）、join_preload 单查询（JOIN 大时慎用）、Repo.preload 后加载；N+1 解决方案就是预加载；嵌套预加载可带过滤条件。

3. 多态关联设计模式

3.1 多态外键（单表引用）

# 评论表可评论 Post 或 Product
defmodule Comment do
  schema "comments" do
    field :content, :string
    field :commentable_id, :integer
    field :commentable_type, :string  # "Post" 或 "Product"
    timestamps()
  end
end

# 查询某文章的评论
Repo.all(from c in Comment,
  where: c.commentable_type == "Post" and c.commentable_id == ^post_id)

3.2 表继承（PostgreSQL）

# 继承表（原生支持）
defmodule Content do
  schema "contents" do
    field :title, :string
    field :body, :string
    field :type, :string  # "post" or "page"
    timestamps()
  end
end

3.3 选择策略

多态外键：简单但无外键约束
表继承：PostgreSQL 原生，但 Ecto 支持有限
单表继承：用 type 字段区分，适合字段相似度高的场景
# 推荐：PostgreSQL 用 type 字段 + 复合索引，MySQL 用多态外键

记忆 多态关联三种策略——多态外键（commentable_id+type，简单无约束）、表继承（PostgreSQL 原生）、单表继承（type 字段区分）；推荐 PostgreSQL 用 type 字段 + 复合索引。

4. 数据库事务与 Ecto.Multi

4.1 基础事务

Repo.transaction(fn ->
  user = Repo.insert!(%User{name: "Alice"})
  Repo.insert!(%Profile{user_id: user.id, bio: "Hello"})
end)
# 任一步失败全部回滚

4.2 Ecto.Multi（推荐）

multi = Ecto.Multi.new()
|> Ecto.Multi.insert(:user, User.changeset(%User{}, %{name: "Alice"}))
|> Ecto.Multi.insert(:profile, fn %{user: user} ->
  Profile.changeset(%Profile{}, %{user_id: user.id, bio: "Hello"})
end)
|> Ecto.Multi.update(:account, fn %{user: user} ->
  Account.changeset(user.account, %{status: "active"})
end)

Repo.transaction(multi)

4.3 隔离级别

Repo.transaction(fn ->
  # ...
end, isolation: :serializable)

# 级别：read_uncommitted / read_committed / repeatable_read / serializable
# 默认：数据库默认（PostgreSQL = read_committed）

记忆 基础事务用 Repo.transaction 匿名函数；Ecto.Multi 推荐——命名步骤、结果传递（fn %{user: u} ->）、部分失败回滚全部；隔离级别默认 read_committed，需严格一致性用 serializable。

5. 变更集：数据验证与约束

5.1 基础变更集

def changeset(user, attrs) do
  user
  |> cast(attrs, [:name, :email, :age])
  |> validate_required([:name, :email])
  |> validate_format(:email, ~r/@/)
  |> validate_number(:age, greater_than_or_equal_to: 0)
  |> unique_constraint(:email)
end

5.2 自定义验证

 defp validate_email_domain(changeset) do
  email = get_change(changeset, :email)
  if email && not String.ends_with?(email, "@company.com") do
    add_error(changeset, :email, "must be company domain")
  else
    changeset
  end
end

5.3 数据库约束 vs 变更集验证

变更集验证：格式/范围/唯一性（在应用层检查）
数据库约束：外键/非空/唯一索引（数据库强制执行）
# 冲突 handling：
#   on_conflict: :nothing    忽略冲突
#   on_conflict: :replace_all 替换全部
#   on_conflict: {:replace, [:updated_at]} 指定字段

记忆 变更集 = cast（白名单字段）+ validate_required/format/number/length + unique_constraint；自定义验证用 get_change + add_error；数据库约束（外键/唯一索引）变更集无法完全替代。

6. 子查询、CTE 与窗口函数

6.1 子查询

# 查询评论数大于 5 的文章
comments_count =
  from c in Comment,
  group_by: c.post_id,
  having: count(c.id) > 5,
  select: c.post_id

Repo.all(from p in Post, where: p.id in subquery(comments_count))

6.2 CTE

# PostgreSQL CTE
"popular_posts"
|> with_cte(as: from(p in Post, where: p.views > 1000, select: p.id))
|> join(:inner, [p], pp in "popular_posts", on: p.id == pp.id)
|> select([p], p)
|> Repo.all()

6.3 窗口函数

# 每类文章按浏览量排名
from p in Post,
  select: %{
    title: p.title,
    category: p.category,
    views: p.views,
    rank: over(row_number(), partition_by: p.category, order_by: [desc: p.views])
  }

记忆 子查询用 subquery() 嵌套；CTE 用 with_cte 定义临时表；窗口函数 over(row_number(), partition_by, order_by) 做排名/累计——Ecto 保留了 SQL 的高级表达能力。

7. 连接池与适配器配置

7.1 连接池配置

# config/runtime.exs
config :my_app, MyApp.Repo,
  username: System.get_env("DB_USER"),
  password: System.get_env("DB_PASS"),
  hostname: System.get_env("DB_HOST"),
  database: System.get_env("DB_NAME"),
  pool_size: 20,
  queue_target: 50,       # 目标等待时间 50ms
  queue_interval: 1000,   # 检查间隔 1s
  timeout: 15_000,        # 查询超时 15s
  ownership_timeout: 30_000  # 测试事务超时

7.2 适配器选择

适配器数据库特性
Ecto.Adapters.PostgresPostgreSQL最完整，推荐
Ecto.Adapters.MyXQLMySQL 8+现代驱动
Ecto.Adapters.TdsMSSQL企业环境
Ecto.Adapters.SQLite3SQLite嵌入式/测试

记忆 连接池——pool_size（并发数）、queue_target（等待目标）、timeout（查询超时）；生产至少 10-20 连接、queue_target 50ms 防雪崩；PostgreSQL 适配器最完整。

8. PostgreSQL 高级特性集成

8.1 JSONB

# 定义
field :metadata, :map  # PostgreSQL jsonb

# 查询
from u in User,
  where: fragment("?->>? = ?", u.metadata, "role", "admin")

# 索引
# CREATE INDEX idx_metadata ON users USING GIN (metadata);

8.2 数组与范围

field :tags, {:array, :string}
field :active_period, :daterange

# 查询包含
from p in Post, where: "elixir" in p.tags
from e in Event, where: fragment("? @> ?", e.active_period, ^Date.utc_today())

8.3 全文搜索

# PostgreSQL tsvector
from p in Post,
  where: fragment("to_tsvector('english', ?) @@ plainto_tsquery('english', ?)", p.body, ^search_term)

记忆 PostgreSQL 特性——JSONB（:map + -» 查询 + GIN 索引）、数组（{:array, type}）、范围（:daterange/numrange）、全文搜索（tsvector + @@）；用 fragment 写原生 SQL 表达式。

9. 性能分析与查询优化

9.1 查询日志

# 开发环境开启日志
config :my_app, MyApp.Repo, log: :debug

# Telemetry 监控查询耗时
:telemetry.attach("ecto-query", [:my_app, :repo, :query], fn event, measurements, _meta, _ ->
  IO.inspect(measurements.total_time, label: "Query time (μs)")
end, nil)

9.2 EXPLAIN

# 查看执行计划
Ecto.Adapters.SQL.explain(Repo, :all, query)

# 关注：
# - Seq Scan（全表扫描→加索引）
# - Index Scan / Index Only Scan（理想）
# - Nested Loop Join（小数据集）/ Hash Join（大数据集）
# - Sort / Merge（内存不足时用磁盘）

9.3 优化清单

1) N+1 → preload
2) 慢查询 → 加索引（EXPLAIN 确认）
3) 大结果集 → 分页/流式（Repo.stream）
4) 复杂聚合 → 数据库做（别拉全部应用层算）
5) 连接池满 → 增大 pool_size 或减少事务时间
6) 写冲突 → 用 on_conflict 或乐观锁

记忆 性能分析——Telemetry attach 监控查询耗时 + EXPLAIN 看执行计划（Seq Scan=需索引、Index Scan=理想）；优化清单：N+1→preload、慢查询→索引、大结果→分页、聚合→DB做、连接池满→扩池。

10. 速查表与一句话记忆

概念一句话
from查询起点
where过滤条件
preload关联预加载
assoc关联简写
Ecto.Multi多步骤事务
cast白名单字段
validate_required必填验证
unique_constraint唯一约束
subquery子查询
with_cteCTE 临时表
over窗口函数
fragment原生 SQL
:mapPostgreSQL JSONB

一句话记忆：Ecto 不是 ORM 是查询构建器+数据映射——查询用 from + 链式关键字（where/join/group/order/select），动态查询用管道+条件函数；关联加载 3 种——preload（推荐）、join_preload（单查询）、后加载；N+1 用 preload 解决；多态关联推荐 type 字段+复合索引；事务用 Ecto.Multi（命名步骤+结果传递+自动回滚）；变更集 = cast + validate + constraint；子查询/CTE/窗口函数保留 SQL 高级能力；PostgreSQL 特性 JSONB(:map)/数组/范围/全文搜索用 fragment；性能靠 Telemetry 监控 + EXPLAIN 分析（Seq Scan→加索引）——「Ecto 的优雅在于不掩盖 SQL，同时也不让你手写 SQL」。

延伸阅读

继续阅读

探索更多技术文章

浏览归档，发现更多关于系统设计、工具链和工程实践的内容。

全部文章 返回首页

「erlang」更多文章

  1. OTP 应用设计模式：监督树结构、release 打包与热升级
  2. Erlang/Elixir 安全加固：加密、认证与分布式信任
  3. Erlang/Elixir 可观测性：日志、Telemetry 指标与分布式追踪