Help us learn about your current experience with the documentation. Take the survey. 

---
stage: Data Access
group: Database Frameworks
info: 任何至少拥有 Maintainer 角色的用户都可以合并对此内容的更新。详情请参见 https://docs.gitlab.com/development/development_processes/#development-guidelines-review。
title: SQL 查询指南
---

本文档描述了编写 SQL 查询时应遵循的各种指南，无论是使用 ActiveRecord/Arel 还是原生 SQL 查询。

## 使用 `LIKE` 语句

搜索数据最常见的方式是使用 `LIKE` 语句。例如，要获取所有标题以 "Draft:" 开头的 issues，可以编写以下查询：

```sql
SELECT *
FROM issues
WHERE title LIKE 'Draft:%';

在 PostgreSQL 中，LIKE 语句是区分大小写的。要进行不区分大小写的 LIKE 查询，必须改用 ILIKE。

为自动处理此问题，应使用 Arel 而非原生 SQL 片段来编写 LIKE 查询，因为 Arel 会自动在 PostgreSQL 上使用 ILIKE。

Issue.where('title LIKE ?', 'Draft:%')

应改为以下写法：

Issue.where(Issue.arel_table[:title].matches('Draft:%'))

这里 matches 会根据所使用的数据库生成正确的 LIKE / ILIKE 语句。

如果需要链式多个 OR 条件，也可以使用 Arel 实现：

table = Issue.arel_table

Issue.where(table[:title].matches('Draft:%').or(table[:foo].matches('Draft:%')))

在 PostgreSQL 上，这会生成：

SELECT *
FROM issues
WHERE (title ILIKE 'Draft:%' OR foo ILIKE 'Draft:%')

LIKE 与索引

PostgreSQL 在使用通配符开头的 LIKE / ILIKE 时不会使用任何索引。例如，以下查询不会使用任何索引：

SELECT *
FROM issues
WHERE title ILIKE '%Draft:%';

由于 ILIKE 的值以通配符开头，数据库无法使用索引，因为它不知道从哪里开始扫描索引。

幸运的是，PostgreSQL 提供了解决方案：三元组广义倒排索引（trigram Generalized Inverted Index, GIN）。这些索引可以按以下方式创建：

CREATE INDEX [CONCURRENTLY] index_name_here
ON table_name
USING GIN(column_name gin_trgm_ops);

关键在于 GIN(column_name gin_trgm_ops) 部分。这会创建一个GIN 索引，其操作符类设置为 gin_trgm_ops。这些索引可以被 ILIKE / LIKE 使用，并能显著提升性能。这些索引的一个缺点是它们很容易变得非常大（取决于索引的数据量）。

为保持这些索引命名的一致性，请使用以下命名模式：

index_TABLE_on_COLUMN_trigram

例如，issues.title 的 GIN/三元组索引应命名为 index_issues_on_title_trigram。

由于构建这些索引需要较长时间，应使用并发构建。可以通过使用 CREATE INDEX CONCURRENTLY 而非 CREATE INDEX 来实现。并发索引不能在事务内创建。可以使用以下模式禁用迁移的事务：

class MigrationName < Gitlab::Database::Migration[2.1]
  disable_ddl_transaction!
end

例如：

class AddUsersLowerUsernameEmailIndexes < Gitlab::Database::Migration[2.1]
  disable_ddl_transaction!

  def up
    execute 'CREATE INDEX CONCURRENTLY index_on_users_lower_username ON users (LOWER(username));'
    execute 'CREATE INDEX CONCURRENTLY index_on_users_lower_email ON users (LOWER(email));'
  end

  def down
    remove_index :users, :index_on_users_lower_username
    remove_index :users, :index_on_users_lower_email
  end
end

可靠地引用数据库列

ActiveRecord 默认返回查询数据库表中的所有列。在某些情况下，返回的行可能需要自定义，例如：

  • 指定少数几列以减少从数据库返回的数据量。
  • 包含来自 JOIN 关系的列。
  • 执行计算（SUM、COUNT）。

在此示例中，我们指定了列，但未指定其所属的表：

  • 来自 projects 表的 path
  • 来自 merge_requests 表的 user_id

查询如下：

# 不推荐，避免使用
Project.select("path, user_id").joins(:merge_requests) # SELECT path, user_id FROM "projects" ...

稍后，新功能为 projects 表添加了一个额外列：user_id。在部署过程中，可能存在一个短暂的时间窗口：数据库迁移已执行，但应用程序的新版本尚未部署。当上述查询在此期间执行时，查询会因以下错误消息而失败：PG::AmbiguousColumn: ERROR: column reference "user_id" is ambiguous

问题源于从数据库选择属性的方式。user_id 列同时存在于 users 和 merge_requests 表中。查询规划器在查找 user_id 列时无法决定使用哪个表。

编写自定义的 SELECT 语句时，最好显式指定带表名的列。

推荐写法

Project.select(:path, 'merge_requests.user_id').joins(:merge_requests)

# SELECT "projects"."path", merge_requests.user_id as user_id FROM "projects" ...
Project.select(:path, :'merge_requests.user_id').joins(:merge_requests)

# SELECT "projects"."path", "merge_requests"."id" as user_id FROM "projects" ...

使用 Arel (arel_table) 的示例：

Project.select(:path, MergeRequest.arel_table[:user_id]).joins(:merge_requests)

# SELECT "projects"."path", "merge_requests"."user_id" FROM "projects" ...

编写原生 SQL 查询时：

SELECT projects.path, merge_requests.user_id FROM "projects"...

当原生 SQL 查询需要参数化（需要转义）时：

include ActiveRecord::ConnectionAdapters::Quoting

"""
SELECT
  #{quote_table_name('projects')}.#{quote_column_name('path')},
  #{quote_table_name('merge_requests')}.#{quote_column_name('user_id')}
FROM ...
"""

不推荐写法（避免使用）

Project.select('id, path, user_id').joins(:merge_requests).to_sql

# SELECT id, path, user_id FROM "projects" ...
Project.select("path", "user_id").joins(:merge_requests)
# SELECT "projects"."path", "user_id" FROM "projects" ...

# 或

Project.select(:path, :user_id).joins(:merge_requests)
# SELECT "projects"."path", "user_id" FROM "projects" ...

当提供列列表时，ActiveRecord 会尝试将参数与 projects 表中定义的列进行匹配，并自动添加表名。在这种情况下，id 列没有问题，但 user_id 列可能返回意外数据：

Project.select(:id, :user_id).joins(:merge_requests)

# 部署前（user_id 取自 merge_requests 表）：
# SELECT "projects"."id", "user_id" FROM "projects" ...

# 部署后（user_id 取自 projects 表）：
# SELECT "projects"."id", "projects"."user_id" FROM "projects" ...

提取 IDs (Plucking IDs)

使用 ActiveRecord 的 pluck 将一组值加载到内存中，仅将其用作另一个查询的参数时，务必非常谨慎。通常，将查询逻辑移出 PostgreSQL 并放入 Ruby 是有害的，因为 PostgreSQL 的查询优化器在拥有更多关于所需操作的上下文时表现更好。

如果出于某种原因需要 pluck 并在单个查询中使用结果，那么很可能物化 CTE（Common Table Expression）会是更好的选择：

WITH ids AS MATERIALIZED (
  SELECT id FROM table...
)
SELECT * FROM projects
WHERE id IN (SELECT id FROM ids);

这将使 PostgreSQL 将值提取到内部数组中。

应避免的一些与 pluck 相关的错误：

  • 向查询中传入过多整数。虽然没有明确限制，但 PostgreSQL 对 ID 数量有实际限制（几千个）。我们不希望触及此限制。
  • 生成巨大的查询文本，可能对我们的日志基础设施造成问题。
  • 意外扫描整个表。例如，这会执行一个额外的不必要的数据库查询并将大量不必要的数据加载到内存中：
projects = Project.all.pluck(:id)

MergeRequest.where(source_project_id: projects)

可以改用性能好得多的子查询：

MergeRequest.where(source_project_id: Project.all.select(:id))

选择 pluck 的几个特定原因：

  • 你确实需要在 Ruby 中对值进行操作。例如，将其写入文件。
  • 值会被缓存或记忆化，以便在多个相关查询中重用。

遵循我们的 CodeReuse/ActiveRecord 规则，你只能在模型代码中使用类似 pluck(:id) 或 pluck(:user_id) 的形式。在前一种情况下，可以使用 ApplicationRecord 提供的 .pluck_primary_key 辅助方法。在后一种情况下，应在相关模型中添加一个小的辅助方法。

如果你有充分理由使用 pluck，限制提取的记录数量可能是合理的。在 ApplicationRecord 中，MAX_PLUCK 默认为 1_000。在所有情况下，你仍应考虑使用子查询，并确保使用 pluck 是一个更可靠的选择。

继承自 ApplicationRecord

GitLab 代码库中的大多数模型应继承自 ApplicationRecord 或 Ci::ApplicationRecord，而非 ActiveRecord::Base。这可以轻松添加辅助方法。

此规则的一个例外是在数据库迁移中创建的模型。由于这些模型应与应用代码隔离，它们应继续继承仅在迁移上下文中可用的 MigrationRecord。

使用 UNION

UNION 在大多数 Rails 应用中不常用，但它们非常强大且有用。查询往往使用大量 JOIN 来获取相关数据或基于特定条件的数据，但随着涉及数据量的增长，JOIN 性能会迅速下降。

例如，如果你想获取名称包含某个值或命名空间名称包含某个值的项目列表，大多数人会编写以下查询：

SELECT *
FROM projects
JOIN namespaces ON namespaces.id = projects.namespace_id
WHERE projects.name ILIKE '%gitlab%'
OR namespaces.name ILIKE '%gitlab%';

在大型数据库上，此查询执行时间很容易达到 800 毫秒左右。使用 UNION，我们可以编写如下查询：

SELECT projects.*
FROM projects
WHERE projects.name ILIKE '%gitlab%'

UNION

SELECT projects.*
FROM projects
JOIN namespaces ON namespaces.id = projects.namespace_id
WHERE namespaces.name ILIKE '%gitlab%';

此查询执行时间仅需约 15 毫秒，同时返回完全相同的记录。

这并不意味着你应该开始到处使用 UNION，但在查询中使用大量 JOIN 并基于连接的数据过滤记录时，这是需要注意的一点。

GitLab 提供了一个 Gitlab::SQL::Union 类，可用于构建多个 ActiveRecord::Relation 对象的 UNION。你可以按如下方式使用此类：

union = Gitlab::SQL::Union.new([projects, more_projects, ...])

Project.from("(#{union.to_sql}) projects")

FromUnion 模型关注点提供了更便捷的方法来产生与上述相同的结果：

class Project
  include FromUnion
  ...
end

Project.from_union(projects, more_projects, ...)

UNION 在代码库中很常见，但也可以使用 EXCEPT 和 INTERSECT 的其他 SQL 集合操作符：

class Project
  include FromIntersect
  include FromExcept
  ...
end

intersected = Project.from_intersect(all_projects, project_set_1, project_set_2)
excepted = Project.from_except(all_projects, project_set_1, project_set_2)

UNION 子查询中的列数不匹配

当 UNION 查询的 SELECT 子句中列数不均时，数据库会返回错误。考虑以下 UNION 查询：

SELECT id FROM users WHERE id = 1
UNION
SELECT id, name FROM users WHERE id = 2
end

查询结果会产生以下错误消息：

each UNION query must have the same number of columns

这个问题很明显，在开发过程中很容易修复。一个边缘情况是当 UNION 查询与显式列列表结合使用时，该列表来自 ActiveRecord 模式缓存。

示例（不推荐，避免使用）：

scope1 = User.select(User.column_names).where(id: [1, 2, 3]) # 显式选择列
scope2 = User.where(id: [10, 11, 12]) # 使用 SELECT users.*

User.connection.execute(Gitlab::SQL::Union.new([scope1, scope2]).to_sql)

当代码部署时，它不会立即引起问题。当另一个开发人员向 users 表添加新的数据库列时，此查询在生产环境中会中断并可能导致停机。第二个查询 (SELECT users.*) 包含新添加的列；然而，第一个查询不包含。column_names 方法返回过时的值（缺少新列），因为这些值缓存在 ActiveRecord 模式缓存中。这些值通常在应用程序启动时填充。

此时，唯一的修复方法是完全重启应用程序以更新模式缓存。由于 GitLab 16.1，模式缓存将自动重置，以便后续查询能够成功。可以通过禁用 ops 功能标志 reset_column_information_on_statement_invalid 来禁用此重置。

如果我们始终使用 SELECT users.* 或始终显式定义列，则可以避免此问题。

使用 SELECT users.*：

# 不推荐，避免使用
scope1 = User.select(User.column_names).where(id: [1, 2, 3])
scope2 = User.where(id: [10, 11, 12])

# 推荐，两个查询都生成 SELECT users.*
scope1 = User.where(id: [1, 2, 3])
scope2 = User.where(id: [10, 11, 12])

User.connection.execute(Gitlab::SQL::Union.new([scope1, scope2]).to_sql)

显式列列表定义：

# 推荐，SELECT 列一致
columns = User.cached_column_list # 辅助方法返回完全限定（表.列）的列名（Arel）
scope1 = User.select(*columns).where(id: [1, 2, 3]) # 显式选择列
scope2 = User.select(*columns).where(id: [10, 11, 12]) # 使用 SELECT users.*

User.connection.execute(Gitlab::SQL::Union.new([scope1, scope2]).to_sql)

按 created_at 排序

简而言之，除非确定它会给你的功能带来问题，否则应优先使用 ORDER BY id 而非 ORDER BY created_at。

用户普遍希望按 created_at 提供排序后的数据。在分页表格视图和分页 API 中，通常希望首先看到最新的（或最早的）。这通常导致我们想在查询中添加类似 ORDER BY created_at DESC LIMIT 20 的内容。添加此查询意味着我们需要在 created_at 上添加索引（或根据其他过滤要求的复合索引）。添加索引会带来成本。此外，由于 created_at 通常不是唯一列，对其进行排序和分页将不稳定，我们仍需要为排序添加一个决胜列（例如 ORDER BY created_at, id）并为其创建适当的索引。

但是，对于大多数功能，用户发现 ORDER BY id 是他们需求的足够好的代理。技术上按 id 排序并不总是完全等同于按 created_at 排序，但足够接近。考虑到 created_at 几乎从不直接由用户控制（即它是内部实现细节），用户真正关心这两列之间差异的情况很少见。

因此，按 id 排序至少有 3 个优势：

  1. 作为主键，它已被索引，对于没有其他过滤或排序参数的简单查询可能足够。
  2. 如果需要复合索引，诸如 btree (namespace_id, id) 的索引比 btree (namespace_id, created_at, id) 更小。
  3. 它是唯一的，因此对排序和分页是稳定的。

使用 WHERE EXISTS 替代 WHERE IN

虽然 WHERE IN 和 WHERE EXISTS 可以用于生成相同的数据，但只要可能，建议使用 WHERE EXISTS。尽管在许多情况下 PostgreSQL 可以很好地优化 WHERE IN，但在许多情况下 WHERE EXISTS 的性能（要好得多）。

在 Rails 中，你必须通过创建 SQL 片段来使用它：

Project.where('EXISTS (?)', User.select(1).where('projects.creator_id = users.id AND users.foo = X'))

这将生成类似以下的查询：

SELECT *
FROM projects
WHERE EXISTS (
    SELECT 1
    FROM users
    WHERE projects.creator_id = users.id
    AND users.foo = X
)

.exists? 查询的查询计划翻转问题

在 Rails 中，对 ActiveRecord 作用域调用 .exists? 可能导致查询计划翻转问题，这可能导致数据库语句超时。在准备查询计划进行审查时，建议检查 ActiveRecord 作用域的所有底层查询形式。

示例：检查组及其子组中是否存在任何 epics。

# 类似查询，但它们的行为可能不同（不同的查询执行计划）

Epic.where(group_id: group.first.self_and_descendant_ids).order(:id).limit(20) # 用于分页
Epic.where(group_id: group.first.self_and_descendant_ids).count # 用于提供总数
Epic.where(group_id: group.first.self_and_descendant_ids).exists? # 用于检查是否存在至少一个 epic

当调用 .exists? 方法时，Rails 会修改 ActiveRecord 作用域：

  • 将选择列替换为 SELECT 1。
  • 向查询添加 LIMIT 1。

当调用时，复杂的 ActiveRecord 作用域（如带有 IN 查询的作用域）可能会对数据库查询规划行为产生负面影响。

执行计划：

Epic.where(group_id: group.first.self_and_descendant_ids).exists?
Limit  (cost=126.86..591.11 rows=1 width=4)
  ->  Nested Loop Semi Join  (cost=126.86..3255965.65 rows=7013 width=4)
        Join Filter: (epics.group_id = namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)])
        ->  Index Only Scan using index_epics_on_group_id_and_iid on epics  (cost=0.42..8846.02 rows=426445 width=4)
        ->  Materialize  (cost=126.43..808.15 rows=435 width=28)
              ->  Bitmap Heap Scan on namespaces  (cost=126.43..805.98 rows=435 width=28)
                    Recheck Cond: ((traversal_ids @> '{9970}'::integer[]) AND ((type)::text = 'Group'::text))
                    ->  Bitmap Index Scan on index_namespaces_on_traversal_ids_for_groups  (cost=0.00..126.32 rows=435 width=0)
                          Index Cond: (traversal_ids @> '{9970}'::integer[])

注意 index_epics_on_group_id_and_iid 索引上的 Index Only Scan，规划器估计读取超过 400,000 行。

如果我们执行不带 exists? 的查询，会得到不同的执行计划：

Epic.where(group_id: Group.first.self_and_descendant_ids).to_a

执行计划：

Nested Loop  (cost=807.49..11198.57 rows=7013 width=1287)
  ->  HashAggregate  (cost=807.06..811.41 rows=435 width=28)
        Group Key: namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)]
        ->  Bitmap Heap Scan on namespaces  (cost=126.43..805.98 rows=435 width=28)
              Recheck Cond: ((traversal_ids @> '{9970}'::integer[]) AND ((type)::text = 'Group'::text))
              ->  Bitmap Index Scan on index_namespaces_on_traversal_ids_for_groups  (cost=0.00..126.32 rows=435 width=0)
                    Index Cond: (traversal_ids @> '{9970}'::integer[])
  ->  Index Scan using index_epics_on_group_id_and_iid on epics  (cost=0.42..23.72 rows=16 width=1287)
        Index Cond: (group_id = (namespaces.traversal_ids)[array_length(namespaces.traversal_ids, 1)])

此查询计划不包含 MATERIALIZE 节点，并通过首先加载组层次结构使用更高效的访问方法。

查询计划翻转甚至可能由最小的查询更改意外引入。回顾 .exists? 查询，其中选择组 ID 数据库列的方式不同：

Epic.where(group_id: group.first.select(:id)).exists?
Limit  (cost=126.86..672.26 rows=1 width=4)
  ->  Nested Loop  (cost=126.86..1763.07 rows=3 width=4)
        ->  Bitmap Heap Scan on namespaces  (cost=126.43..805.98 rows=435 width=4)
              Recheck Cond: ((traversal_ids @> '{9970}'::integer[]) AND ((type)::text = 'Group'::text))
              ->  Bitmap Index Scan on index_namespaces_on_traversal_ids_for_groups  (cost=0.00..126.32 rows=435 width=0)
                    Index Cond: (traversal_ids @> '{9970}'::integer[])
        ->  Index Only Scan using index_epics_on_group_id_and_iid on epics  (cost=0.42..2.04 rows=16 width=4)
              Index Cond: (group_id = namespaces.id)

在这里我们再次看到更好的执行计划。如果我们对查询做一个小改动，它又会翻转：

Epic.where(group_id: group.first.self_and_descendants.select('id + 0')).exists?
Limit  (cost=126.86..591.11 rows=1 width=4)
  ->  Nested Loop Semi Join  (cost=126.86..3255965.65 rows=7013 width=4)
        Join Filter: (epics.group_id = (namespaces.id + 0))
        ->  Index Only Scan using index_epics_on_group_id_and_iid on epics  (cost=0.42..8846.02 rows=426445 width=4)
        ->  Materialize  (cost=126.43..808.15 rows=435 width=4)
              ->  Bitmap Heap Scan on namespaces  (cost=126.43..805.98 rows=435 width=4)
                    Recheck Cond: ((traversal_ids @> '{9970}'::integer[]) AND ((type)::text = 'Group'::text))
                    ->  Bitmap Index Scan on index_namespaces_on_traversal_ids_for_groups  (cost=0.00..126.32 rows=435 width=0)
                          Index Cond: (traversal_ids @> '{9970}'::integer[])

如果将 IN 子查询移动到 CTE，可以强制执行计划：

cte = Gitlab::SQL::CTE.new(:group_ids, Group.first.self_and_descendant_ids)
Epic.where('epics.id IN (SELECT id FROM group_ids)').with(cte.to_arel).exists?
Limit  (cost=817.27..818.12 rows=1 width=4)
  CTE group_ids
    ->  Bitmap Heap Scan on namespaces  (cost=126.43..807.06 rows=435 width=4)
          Recheck Cond: ((traversal_ids @> '{9970}'::integer[]) AND ((type)::text = 'Group'::text))
          ->  Bitmap Index Scan on index_namespaces_on_traversal_ids_for_groups  (cost=0.00..126.32 rows=435 width=0)
                Index Cond: (traversal_ids @> '{9970}'::integer[])
  ->  Nested Loop  (cost=10.21..380.29 rows=435 width=4)
        ->  HashAggregate  (cost=9.79..11.79 rows=200 width=4)
              Group Key: group_ids.id
              ->  CTE Scan on group_ids  (cost=0.00..8.70 rows=435 width=4)
        ->  Index Only Scan using epics_pkey on epics  (cost=0.42..1.84 rows=1 width=4)
              Index Cond: (id = group_ids.id)

由于 CTE 的复杂性，应将其作为最后手段使用。仅当更简单的查询更改无法产生有利的执行计划时才使用 CTE。

.find_or_create_by 不是原子的

.find_or_create_by 和 .first_or_create 等方法的固有模式是它们不是原子的。这意味着，它首先运行一个 SELECT，如果没有结果则执行一个 INSERT。考虑到并发进程，存在一个竞态条件，可能导致尝试插入两个相似的记录。这可能不是期望的，或者可能导致其中一个查询因违反约束而失败。

使用事务并不能解决这个问题。

为此，我们添加了 ApplicationRecord.safe_find_or_create_by。

此方法可以像 find_or_create_by 一样使用，但它将调用包装在一个新事务（或子事务）中，并在因 ActiveRecord::RecordNotUnique 错误失败时重试。

要使用此方法，请确保你想要使用的模型继承自 ApplicationRecord。

在 Rails 6 及更高版本中，有一个.create_or_find_by方法。此方法与我们的 .safe_find_or_create_by 方法不同，因为它执行 INSERT，并且仅在 INSERT 失败时才执行 SELECT 命令。

如果 INSERT 失败，它会留下一个死元组并增加主键序列（如果有的话），以及其他缺点。

如果我们常见的路径是首先创建单个记录，然后在首次创建后重用它，我们更倾向于 .safe_find_or_create_by。但是，如果更常见的路径是创建新记录，而我们只想避免在边缘情况（例如任务重试）中插入重复记录，那么 .create_or_find_by 可以为我们节省一次 SELECT。

如果现有事务上下文中执行，两种方法内部都使用子事务。如果单个事务内使用超过 64 个活动子事务，这会显著影响整体性能。

我可以使用 .safe_find_or_create_by 吗？

如果你的代码通常是隔离的（例如仅在 worker 中执行）并且没有用另一个事务包装，那么你可以使用 .safe_find_or_create_by。但是，没有工具可以捕获其他人在你事务内调用你的代码的情况。使用 .safe_find_or_create_by 肯定会带来一些目前无法完全消除的风险。

此外，我们有一个 RuboCop 规则 Performance/ActiveRecordSubtransactionMethods，它会阻止使用 .safe_find_or_create_by。可以通过 # rubocop:disable Performance/ActiveRecordSubtransactionMethods 在特定情况下禁用此规则。

.find_or_create_by 的替代方案

替代方案 1：UPSERT

.upsert 方法可以在表由唯一索引支持时作为替代解决方案。

.upsert 方法的简单用法：

BuildTrace.upsert(
  {
    build_id: build_id,
    title: title
  },
  unique_by: :build_id
)

需要注意的几点：

  • 即使记录仅被更新，主键序列也会递增。
  • 不会返回创建的记录。returning 选项仅在发生 INSERT（新记录）时返回数据。
  • 不执行 ActiveRecord 验证。

带有验证和记录加载的 .upsert 方法示例：

params = {
  build_id: build_id,
  title: title
}

build_trace = BuildTrace.new(params)

unless build_trace.valid?
  raise 'notify the user here'
end

BuildTrace.upsert(params, unique_by: :build_id)

build_trace = BuildTrace.find_by!(build_id: build_id)

# do something with build_trace here

如果 build_id 列上有模型级别的唯一性验证，上面的代码片段将无法正常工作，因为我们在调用 .upsert 之前执行了验证。

为了解决这个问题，我们有两个选择：

  • 从 ActiveRecord 模型中移除唯一性验证。
  • 使用 on 关键字 并实现特定上下文的验证。

替代方案 2：检查存在性并捕获异常

当并发创建相同记录的可能性非常低时，我们可以使用更简单的方法：

def my_create_method
  params = {
    build_id: build_id,
    title: title
  }

  build_trace = BuildTrace
    .where(build_id: params[:build_id])
    .first

  build_trace = BuildTrace.new(params) if build_trace.blank?

  build_trace.update!(params)

rescue ActiveRecord::RecordInvalid => invalid
  retry if invalid.record&.errors&.of_kind?(:build_id, :taken)
end

该方法执行以下操作：

  1. 通过唯一列查找模型。
  2. 如果未找到记录，则构建一个新记录。
  3. 持久化记录。

在查找查询和持久化查询之间存在一个短暂的竞态条件，另一个进程可能插入该记录并导致 ActiveRecord::RecordInvalid 异常。

代码捕获此特定异常并重试操作。对于第二次运行，记录将被成功找到。例如，请参阅 PreventApprovalByAuthorService 中的这段代码。

在生产环境中监控 SQL 查询

GitLab 团队成员可以使用 PostgreSQL 日志监控 GitLab.com 上的慢查询或已取消查询，这些日志被索引在 Elasticsearch 中，并可通过 Kibana 搜索。

有关更多详细信息，请参阅运行手册。

何时使用公共表表达式 (CTE)

你可以使用公共表表达式（CTE）在更复杂的查询中创建临时结果集。你也可以使用递归 CTE 在查询本身中引用 CTE 的结果集。以下示例查询了一组 personal access tokens，它们在 previous_personal_access_token_id 列中相互引用。

WITH RECURSIVE "personal_access_tokens_cte" AS (
(
    SELECT
      "personal_access_tokens".*
    FROM
      "personal_access_tokens"
    WHERE
      "personal_access_tokens"."previous_personal_access_token_id" = 15)
  UNION (
    SELECT
      "personal_access_tokens".*
    FROM
      "personal_access_tokens",
      "personal_access_tokens_cte"
    WHERE
      "personal_access_tokens"."previous_personal_access_token_id" = "personal_access_tokens_cte"."id"))
SELECT
  "personal_access_tokens".*
FROM
  "personal_access_tokens_cte" AS "personal_access_tokens"

 id | previous_personal_access_token_id
----+-----------------------------------
 16 |                                15
 17 |                                16
 18 |                                17
 19 |                                18
 20 |                                19
 21 |                                20
(6 rows)

由于 CTE 是临时结果集，你可以在另一个 SELECT 语句中使用它们。将 CTE 与 UPDATE 或 DELETE 一起使用可能导致意外行为：

考虑以下方法：

def personal_access_token_chain(token)
  cte = Gitlab::SQL::RecursiveCTE.new(:personal_access_tokens_cte)
  personal_access_token_table = Arel::Table.new(:personal_access_tokens)

  cte << PersonalAccessToken
           .where(personal_access_token_table[:previous_personal_access_token_id].eq(token.id))
  cte << PersonalAccessToken
           .from([personal_access_token_table, cte.table])
           .where(personal_access_token_table[:previous_personal_access_token_id].eq(cte.table[:id]))
  PersonalAccessToken.with.recursive(cte.to_arel).from(cte.alias_to(personal_access_token_table))
end

当用于查询数据时，它按预期工作：

> personal_access_token_chain(token)

WITH RECURSIVE "personal_access_tokens_cte" AS (
(
    SELECT
      "personal_access_tokens".*
    FROM
      "personal_access_tokens"
    WHERE
      "personal_access_tokens"."previous_personal_access_token_id" = 11)
  UNION (
    SELECT
      "personal_access_tokens".*
    FROM
      "personal_access_tokens",
      "personal_access_tokens_cte"
    WHERE
      "personal_access_tokens"."previous_personal_access_token_id" = "personal_access_tokens_cte"."id"))
SELECT
    "personal_access_tokens".*
FROM
    "personal_access_tokens_cte" AS "personal_access_tokens"

但是，当与 #update_all 一起使用时，CTE 会被丢弃。因此，该方法会更新整个表：

> personal_access_token_chain(token).update_all(revoked: true)

UPDATE
    "personal_access_tokens"
SET
    "revoked" = TRUE

要解决此行为：

  1. 查询记录的 ids：

    > token_ids = personal_access_token_chain(token).pluck_primary_key
=> [16, 17, 18, 19, 20, 21]

  2. 使用此数组限定 PersonalAccessTokens：

    PersonalAccessToken.where(id: token_ids).update_all(revoked: true)

或者将这两个步骤结合起来：

PersonalAccessToken
  .where(id: personal_access_token_chain(token).pluck_primary_key)
  .update_all(revoked: true)

避免更新大量无界数据。如果数据上没有应用限制，或者你不确定数据量，你应该批量更新数据。