SQL Query Guidelines · Gitlab 中文文檔

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