SQL Query Guidelines

原文：https://docs.gitlab.com/ee/development/sql.html

SQL Query Guidelines

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

Using LIKE Statements

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

  1. SELECT *
  2. FROM issues
  3. WHERE title LIKE 'WIP:%';

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

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

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

您可以这样写：

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

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

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

  1. table = Issue.arel_table
  3. Issue.where(table[:title].matches('WIP:%').or(table[:foo].matches('WIP:%')))

在 PostgreSQL 上，这将产生：

  1. SELECT *
  2. FROM issues
  3. WHERE (title ILIKE 'WIP:%' OR foo ILIKE 'WIP:%')

LIKE & Indexes

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

  1. SELECT *
  2. FROM issues
  3. WHERE title ILIKE '%WIP:%';

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

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

  1. CREATE INDEX [CONCURRENTLY] index_name_here
  2. ON table_name
  3. USING GIN(column_name gin_trgm_ops);

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

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

  1. 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:

  1. class MigrationName < ActiveRecord::Migration[4.2]
  2.   disable_ddl_transaction!
  3. end

例如：

  1. class AddUsersLowerUsernameEmailIndexes < ActiveRecord::Migration[4.2]
  2.   disable_ddl_transaction!
  4.   def up
  5.     execute 'CREATE INDEX CONCURRENTLY index_on_users_lower_username ON users (LOWER(username));'
  6.     execute 'CREATE INDEX CONCURRENTLY index_on_users_lower_email ON users (LOWER(email));'
  7.   end
  9.   def down
  10.     remove_index :users, :index_on_users_lower_username
  11.     remove_index :users, :index_on_users_lower_email
  12.   end
  13. end

Reliably referencing database columns

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

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

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

  • projects表的path
  • merge_requests表中的user_id

查询：

  1. # bad, avoid
  2. 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)

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

使用 Arel（ arel_table ）的示例：

  1. Project.select(:path, MergeRequest.arel_table[:user_id]).joins(:merge_requests)
  3. # SELECT "projects"."path", "merge_requests"."user_id" FROM "projects" ...

编写原始 SQL 查询时：

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

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

  1. include ActiveRecord::ConnectionAdapters::Quoting
  3. """
  4. SELECT #{quote_table_name('projects')}.#{quote_column_name('path')}, #{quote_table_name('merge_requests')}.#{quote_column_name('user_id')} FROM ...
  5. """

Bad (avoid)

  1. Project.select('id, path, user_id').joins(:merge_requests).to_sql
  3. # SELECT id, path, user_id FROM "projects" ...
  1. Project.select("path", "user_id").joins(:merge_requests)
  2. # SELECT "projects"."path", "user_id" FROM "projects" ...
  4. # or
  6. Project.select(:path, :user_id).joins(:merge_requests)
  7. # SELECT "projects"."path", "user_id" FROM "projects" ...

给定列列表后，ActiveRecord 尝试将参数与projects表中定义的列进行匹配，并自动在表名前添加前缀. 在这种情况下， id列不会有问题，但是user_id列可能返回意外数据：

  1. Project.select(:id, :user_id).joins(:merge_requests)
  3. # Before deployment (user_id is taken from the merge_requests table):
  4. # SELECT "projects"."id", "user_id" FROM "projects" ...
  6. # After deployment (user_id is taken from the projects table):
  7. # SELECT "projects"."id", "projects"."user_id" FROM "projects" ...

Plucking IDs

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

  1. projects = Project.all.pluck(:id)
  3. MergeRequest.where(source_project_id: projects)

相反，您可以只使用性能更好的子查询：

  1. 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

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

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

Use UNIONs

UNION 在大多数 Rails 应用程序中并不是很常用，但是它们非常强大且有用. 在大多数应用程序中，查询倾向于使用大量 JOIN 来获取相关数据或基于特定条件的数据，但是 JOIN 性能会随着所涉及数据的增长而迅速恶化.

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

  1. SELECT *
  2. FROM projects
  3. JOIN namespaces ON namespaces.id = projects.namespace_id
  4. WHERE projects.name ILIKE '%gitlab%'
  5. OR namespaces.name ILIKE '%gitlab%';

使用大型数据库，此查询可能很容易花费大约 800 毫秒来运行. 使用 UNION，我们改为编写以下内容：

  1. SELECT projects.*
  2. FROM projects
  3. WHERE projects.name ILIKE '%gitlab%'
  5. UNION
  7. SELECT projects.*
  8. FROM projects
  9. JOIN namespaces ON namespaces.id = projects.namespace_id
  10. WHERE namespaces.name ILIKE '%gitlab%';

反过来，此查询只需要 15 毫秒即可完成，同时返回完全相同的记录.

这并不意味着您应该在所有地方开始使用 UNION，但是在查询中使用大量 JOIN 并根据联接的数据过滤掉记录时要牢记这一点.

GitLab 带有一个Gitlab::SQL::Union类，可用于构建多个ActiveRecord::Relation对象的 UNION. 您可以按如下方式使用此类：

  1. union = Gitlab::SQL::Union.new([projects, more_projects, ...])
  3. Project.from("(#{union.to_sql}) projects")

Ordering by Creation Date

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

Use WHERE EXISTS instead of WHERE IN

虽然可以使用WHERE IN和WHERE EXISTS来生成相同的数据，但建议尽可能使用WHERE EXISTS . 尽管在许多情况下 PostgreSQL 可以很好地优化WHERE IN但在许多情况下WHERE EXISTS会好得多.

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

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

然后，将按照以下内容生成查询：

  1. SELECT *
  2. FROM projects
  3. WHERE EXISTS (
  4.     SELECT 1
  5.     FROM users
  6.     WHERE projects.creator_id = users.id
  7.     AND users.foo = X
  8. )

.find_or_create_by is not atomic

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

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

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

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

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