部分インデックスの活用場面を紹介する

ちょっと前からHanamiに対してRodauthを適用しようと試行錯誤しているんですが、Rodauthの方のREADMEにこんな記述がありました。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


    create_table(:accounts) do
      primary_key :id, :type=>:Bignum
      foreign_key :status_id, :account_statuses, :null=>false, :default=>1
      if db.database_type == :postgres
        citext :email, :null=>false
        constraint :valid_email, :email=>/^[^,;@ \r\n]+@[^,@; \r\n]+\.[^,@; \r\n]+$/
        index :email, :unique=>true, :where=>{:status_id=>[1, 2]} # ←
      else
        String :email, :null=>false
        index :email, :unique=>true
      end
    end

注目して欲しいのは

1

index :email, :unique=>true, :where=>{:status_id=>[1,2]}

という記述です。indexに対してwhereってなんだろうと思って調べたところ部分インデックスというものを初めて知りました。

部分インデックス

部分インデックスとは、テーブルの部分集合に構築されるインデックスです。部分集合は、（部分インデックスの述語と呼ばれる）条件式で定義されます。部分インデックスには、その述語を満たすテーブル行のみに対するエントリが含まれます。

11.8. 部分インデックス

書いてある通りです。
引用元のページにも使用例が書いてありますが、具体的な使用例をあげてみようと思います。

使用例

一部値の出現率が低いカラムへのインデックス

まずは選択率というものを簡単に説明します。
選択率とはある絞り込みによって、レコードがどの程度絞り込めるかを表す値です。
例えば1000件あるレコードを10件まで絞り込むクエリがあった場合、そのクエリの選択率は1%ということになります。

この選択率というものはインデックスのパフォーマンスに大きく関わってきていて、SQL実践入門にはこのように書かれています。

そしてもう1つの基準が、選択率が低いこと、すなわち少ない行にしぼり込めることです。具体的な閾値はDBMSやストレージ性能などの条件によっても異なるのですが、最近のDBMSでは、だいたい5~10%前後というのが目安です。つまり、5%未満に絞り込める条件ならば、その列集合に対してはインデックスを作る価値がある(かもしれない)、ということになります。選択率がそれより高いと、テーブルフルスキャンのほうが速い可能性が高くなってきます。

SQL実践入門

つまり5%以内に絞り込めないカラムにインデックスを追加しても逆効果になってしまう可能性があるということです。
では以下のようなテーブルを考えてみます。

1
2
3
4
5
6
7


CREATE TYPE account_status AS ENUM ('Unverified', 'Verified', 'Closed')
CREATE TABLE accounts (
  id serial PRIMARY KEY,
  username text UNIQUE NOT NULL,
  email text UNIQUE NOT NULL,
  status account_status NOT NULL
);

status の割合は

Unverified: 3%
Verified: 95%
Closed: 2%

だとします。
このテーブルに対して WHERE status = 'Verified' のようなクエリを実行したとしたら選択率は95%となり、インデックススキャンよりもフルスキャンの方が効率がよくなります。
次に WHERE status = 'Closed' というクエリを実行すると選択率は3%になります。こちらの場合ではインデックススキャンの方が効率的です。このようにあるカラムにおいて、特定の値にのみインデックスを作成したい場面がよくあります。そう言った時に部分インデックスが使用できます。

1

CREATE INDEX status_idx ON accounts (status) WHERE status <> 'Verified';

こうすることで statusの値が Verified 以外の場合にのみインデックスを使用できるようになります。

特定条件下でのユニーク制約

一番わかりやすい例が論理削除です。
以下のようなテーブルがあったとします。

1
2
3
4
5
6


CREATE TABLE accounts (
  id serial PRIMARY KEY,
  name text NOT NULL,
  email text UNIQUE NOT NULL,
  deleted_at datetime
);

accountを追加し、その後論理削除、そして同じメールアドレスでaccountを追加してみます。

1
2
3
4
5


INSERT INTO accounts (username, email)
		VALUES('アカウント1', 'account@example.com');
UPDATE accounts SET deleted_at = NOW() WHERE email = 'account@example.com';
INSERT INTO accounts (username, email)
		VALUES('アカウント2', 'account@example.com');

当然ですが2つ目のINSERTでUNIQUE制約エラーになります。その対策として email カラムからUNIQUE制約を外すという手段をとる場合があると思います。しかしそれが有効なアカウントにおいて一意なのであればユニーク制約をつけておきたいですし、そうあるべきだと思います。

こういった場合にも部分インデックスが使用できます。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


CREATE TABLE accounts (
  id serial PRIMARY KEY,
  name text NOT NULL,
  email text NOT NULL,
  deleted_at datetime
);
CREATE UNIQUE INDEX email_idx ON accounts (email) WHERE deleted_at IS NULL;

INSERT INTO accounts (username, email)
		VALUES('アカウント1', 'account@example.com');
UPDATE accounts SET deleted_at = NOW() WHERE email = 'account@example.com';
INSERT INTO accounts (username, email)
		VALUES('アカウント2', 'account@example.com');

この一連の操作はエラーなく完了します。このように論理削除されていないレコードに対してのみ、ユニーク制約を付与することも可能になります。

注）部分インデックスがあるからといって、論理削除を簡単に使用して良いというわけではないのです。論理削除はなるべく避けるようにしましょう。

まとめ

知っている人からしたら当然かもしれませんが、自分にとってはなかなか衝撃的でした。非常に有用な機能だと思うので適切と判断できる場面では使用していきたいです。