💎一站式轻松地调用各大LLM模型接口，支持GPT4、智谱、星火、月之暗面及文生图 广告
# 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` .