623.md

# SQL Query Guidelines

> 原文：[https://docs.gitlab.com/ee/development/sql.html](https://docs.gitlab.com/ee/development/sql.html)

*   [Using LIKE Statements](#using-like-statements)
*   [LIKE & Indexes](#like--indexes)
*   [Reliably referencing database columns](#reliably-referencing-database-columns)
    *   [Good (prefer)](#good-prefer)
    *   [Bad (avoid)](#bad-avoid)
*   [Plucking IDs](#plucking-ids)
*   [Inherit from ApplicationRecord](#inherit-from-applicationrecord)
*   [Use UNIONs](#use-unions)
*   [Ordering by Creation Date](#ordering-by-creation-date)
*   [Use WHERE EXISTS instead of WHERE IN](#use-where-exists-instead-of-where-in)
*   [`.find_or_create_by` is not atomic](#find_or_create_by-is-not-atomic)

# SQL Query Guidelines[](#sql-query-guidelines "Permalink")

本文档介绍了使用 ActiveRecord / Arel 或原始 SQL 查询编写 SQL 查询时要遵循的各种准则.

## Using LIKE Statements[](#using-like-statements "Permalink")

搜索数据的最常见方法是使用`LIKE`语句. 例如，要获取标题以" WIP："开头的所有问题，您可以编写以下查询：

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

在 PostgreSQL 上， `LIKE`语句区分大小写. 要执行不区分大小写的`LIKE` ，必须改为使用`ILIKE` .

要自动处理此问题，您应该使用 Arel 而不是原始 SQL 片段使用`LIKE`查询，因为 Arel 在 PostgreSQL 上自动使用`ILIKE` .

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

您可以这样写：

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

根据所使用的数据库，此处的`matches`生成正确的`LIKE` / `ILIKE`语句.

如果您需要链接多个`OR`条件，也可以使用 Arel 进行此操作：

```
table = Issue.arel_table

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

在 PostgreSQL 上，这将产生：

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

## LIKE & Indexes[](#like--indexes "Permalink")

在一开始使用带有通配符的`LIKE` / `ILIKE`时，PostgreSQL 将不使用任何索引. 例如，这将不使用任何索引：

```
SELECT *
FROM issues
WHERE title ILIKE '%WIP:%'; 
```

因为`ILIKE`的值以通配符开头，所以数据库无法使用索引，因为它不知道从何处开始扫描索引.

幸运的是，PostgreSQL *确实*提供了一种解决方案：trigram GIN 索引. 可以如下创建这些索引：

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

这里的关键是`GIN(column_name gin_trgm_ops)`部分. 这将创建一个[GIN 索引](https://s0www0postgresql0org.icopy.site/docs/current/gin.html) ，并将操作符类设置为`gin_trgm_ops` . 这些索引*可*通过使用`ILIKE` / `LIKE` ，并可能导致大大改进的性能. 这些索引的缺点之一是它们很容易变大（取决于索引的数据量）.

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

```
index_TABLE_on_COLUMN_trigram 
```

例如，一个`issues.title`的 GIN / `issues.title`索引将称为`index_issues_on_title_trigram` .

Due to these indexes taking quite some time to be built they should be built concurrently. This can be done by using `CREATE INDEX CONCURRENTLY` instead of just `CREATE INDEX`. Concurrent indexes can *not* be created inside a transaction. Transactions for migrations can be disabled using the following pattern:

```
class MigrationName < ActiveRecord::Migration[4.2]
  disable_ddl_transaction!
end 
```

例如：

```
class AddUsersLowerUsernameEmailIndexes < ActiveRecord::Migration[4.2]
  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 
```

## Reliably referencing database columns[](#reliably-referencing-database-columns "Permalink")

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

*   仅指定几列以减少从数据库返回的数据量.
*   包括`JOIN`关系中的列.
*   执行计算（ `SUM` ， `COUNT` ）.

在此示例中，我们指定列，但不指定其表：

*   `projects`表的`path`
*   `merge_requests`表中的`user_id`

查询：

```
# bad, avoid
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`语句时，最好**使用表名明确指定列** .

### Good (prefer)[](#good-prefer "Permalink")

```
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"... 
```

When the raw SQL query is parameterized (needs escaping):

```
include ActiveRecord::ConnectionAdapters::Quoting

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

### Bad (avoid)[](#bad-avoid "Permalink")

```
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" ...

# or

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)

# Before deployment (user_id is taken from the merge_requests table):
# SELECT "projects"."id", "user_id" FROM "projects" ...

# After deployment (user_id is taken from the projects table):
# SELECT "projects"."id", "projects"."user_id" FROM "projects" ... 
```

## Plucking IDs[](#plucking-ids "Permalink")

这还不够强调： **永远不要**使用 ActiveRecord 的`pluck`将一组值插入内存中，而只是将它们用作另一个查询的参数. 例如，这将使数据库**非常**悲伤：

```
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`帮助器方法. 在后者中，您应该在相关模型中添加一个小的辅助方法.

## Inherit from ApplicationRecord[](#inherit-from-applicationrecord "Permalink")

GitLab 代码库中的大多数模型应继承自`ApplicationRecord` ，而不是`ActiveRecord::Base` . 这样可以轻松添加辅助方法.

在数据库迁移中创建的模型存在此规则的例外. 由于这些应与应用程序代码隔离，因此它们应继续从`ActiveRecord::Base`继承子类.

## Use UNIONs[](#use-unions "Permalink")

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") 
```

## Ordering by Creation Date[](#ordering-by-creation-date "Permalink")

根据记录的创建时间对记录进行排序时，只需按`id`列进行排序即可，而不必按`created_at`进行排序. 因为 ID 始终是唯一的，并且按照创建行的顺序递增，所以这将产生完全相同的结果. 这也意味着，由于默认情况下已经对`id`进行了索引，因此无需在`created_at`上添加索引以确保一致的性能.

## Use WHERE EXISTS instead of WHERE IN[](#use-where-exists-instead-of-where-in "Permalink")

虽然可以使用`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
) 
```

## `.find_or_create_by` is not atomic[](#find_or_create_by-is-not-atomic "Permalink")

`.find_or_create_by`和`.first_or_create`等方法的固有模式是它们不是原子的. 这意味着，它首先运行`SELECT` ，如果没有结果，则执行`INSERT` . 考虑到并发过程，因此存在竞争条件，这可能导致尝试插入两个相似的记录. 例如，这可能是不希望的，或者可能由于约束冲突而导致查询之一失败.

使用事务不能解决此问题.

为了解决这个问题，我们添加了`ApplicationRecord.safe_find_or_create_by` .

可以像平常的`find_or_create_by`一样使用此方法，但是它将调用包装在*新*事务中，如果由于`ActiveRecord::RecordNotUnique`错误而失败，则将重试.

为了能够使用此方法，请确保要在其上使用的模型继承自`ApplicationRecord` .