SQLクエリのガイドライン

このドキュメントでは、ActiveRecord/Arelまたは生のSQLクエリを使用してSQLクエリを記述する際の様々なガイドラインについて説明します。

LIKE文の使用

データを検索する最も一般的な方法は、LIKE ステートメントを使用することです。例えば、”WIP: “で始まるタイトルを持つすべてのイシューを取得するには、次のクエリを記述します： 

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

PostgreSQLでは、LIKE 文は大文字小文字を区別します。 大文字小文字を区別しないLIKE を実行するには、代わりにILIKE を使用しなければなりません。

Arelは自動的にPostgreSQL上でILIKE 。これを自動的に処理するためには、生のSQLフラグメントの代わりにArelを使用したLIKE クエリを使用する必要があります。 

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＆インデックス

PostgreSQL は、LIKE /ILIKE の先頭にワイルドカードを使用した場合、インデックスを使用しません。 例えば、これはインデックスを使用しません： 

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_trgm_opsに設定したGIN インデックスを作成します。 これらのインデックスは、ILIKE /LIKE で使用する_ことが_でき、パフォーマンスを大幅に向上させることができます。 これらのインデックスの欠点は、（インデックス化されるデータ量にもよりますが）非常に大きくなりやすいことです。

これらのインデックスの命名に一貫性を持たせるために、以下の命名パターンを使用してください： 

index_TABLE_on_COLUMN_trigram

例えば、issues.title の GIN/trigram インデックスはindex_issues_on_title_trigramと呼ばれます。

これらのインデックスの構築にはかなりの時間がかかるため、同時に構築する必要があります。 これは、CREATE INDEXだけでなく、CREATE INDEX CONCURRENTLY を使用することで可能です。 同時インデックスは、トランザクション内部では作成_できません_。 マイグレーション用のトランザクションは、以下のパターンを使用して無効にすることができます： 

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

データベースのカラムを確実に参照

ActiveRecordはデフォルトで、クエリされたデータベーステーブルのすべてのカラムを返します。 場合によっては、返される行をカスタマイズする必要があります：

  • データベースから返されるデータ量を減らすために、いくつかのカラムだけを指定します。
  • JOIN リレーションからの列を含みます。
  • 計算の実行 (SUM,COUNT).

この例では、カラムは指定しますが、テーブルは指定しません：

  • path projects テーブルより
  • user_id merge_requests テーブルより

クエリです： 

# 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 ステートメントを記述する場合は、テーブル名でカラムを明示的に指定する方がよいでしょう。

良い（好む） 

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

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

プラッキングID

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-provided.pluck_primary_key ヘルパーメソッドを代わりに使用することができます。 後者の場合、関連するモデルに小さなヘルパーメソッドを追加する必要があります。

ApplicationRecord から継承

GitLabコードベースのほとんどのモデルは、ActiveRecord::Baseから継承するのではなく、ApplicationRecordから継承します。 これにより、ヘルパーメソッドを簡単に追加することができます。

このルールの例外は、データベースマイグレーションで作成されたモデルに存在します。 これらのモデルはアプリケーションコードから内部的に分離されている必要があるため、ActiveRecord::Baseのサブクラス化を継続する必要があります。

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ミリ秒で完了します。

しかし、クエリ内で多くのJOINを使用し、結合されたデータに基づいてレコードをフィルタリングする場合は、この点に注意する必要があります。

GitLab にはGitlab::SQL::Union クラスが用意されています。このクラスを使って複数のActiveRecord::Relation オブジェクトの UNION を作ることができます。このクラスは次のように使います： 

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

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

作成日順

id created_atID は常に一意であり、行が作成された順にインクリメントされるため、まったく同じ結果が得られます。 またcreated_at id、デフォルトですでにインデックスが設定されているため、id 一貫したパフォーマンスを確保するためにcreated_atインデックスを追加する必要はcreated_at idありません。

WHERE IN の代わりに WHERE EXISTS を使用します。

WHERE IN とandをWHERE EXISTS 使用して同じデータを生成することが WHERE EXISTS WHERE INできますが、WHERE IN 可能な限り WHERE INandをWHERE IN WHERE EXISTS 使用することを推奨 WHERE EXISTS WHERE INします。 PostgreSQLWHERE IN は多くの場合 WHERE IN非常にうまくWHERE IN 最適化することができますが 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 は原子ではありません

.find_or_create_by や.first_or_create などのメソッドに固有のパターンは、アトミックではないということです。 つまり、最初にSELECTを実行し、結果がなければINSERT が実行されます。並行処理を念頭に置くと、2つの類似したレコードを挿入しようとするレースコンディションが発生する可能性があります。 これは望ましくないかもしれませんし、例えば制約違反のためにクエリの1つが失敗する可能性もあります。

トランザクションを使っても、この問題は解決しません。

これを解決するために、ApplicationRecord.safe_find_or_create_byを追加しました。

このメソッドは通常のfind_or_create_by と同じように使用できますが、呼び出しを新しいトランザクションでラップし、ActiveRecord::RecordNotUnique エラーで失敗した場合に再試行します。

このメソッドを使えるようにするには、このメソッドを使いたいモデルがApplicationRecordを継承していることを確認してください。