Cloud SpannerのNull Filtered INDEXの用途を考察する 〜サブタイプ実装〜

この記事はUniposアドベントカレンダー2022の5日目の記事です！

はじめに

Uniposのサーバサイドエンジニアの周東です。
以前投稿したSpannerのパフォーマンスチューニングの記事を書いていてNull Filtered INDEXについて理解が浅かったなーと思ったので、いい機会なので深ぼって考えてみようと思います。

Null Filtered INDEXの基本的な知識についてはさらっとしか触れないので、詳しくは前回の記事を先に見ていただければと思います。

NullFilteredINDEX

NullFilteredはキー値のいずれかにNULLを含むレコードをINDEXの作成対象から除外するという性質を持ったCloud Spannerが提供するINDEXの仕組みです。

公式docが言うには、STORINGと組み合わせることで実質マテリアライズドビューを実現できるとのこと。

STORINGのもう1つの便利な応用は、NULL_FILTERED インデックスの一部として使用することです。 これにより、効率的にスキャンできるテーブルの疎なサブセットの実質的な実体化されたビューを定義できます。 たとえば、メールボックスの is_unread 列にこのようなインデックスを作成すると、1 回のテーブル スキャンで未読メッセージのビューを処理でき、すべてのメールボックスの完全なコピーに費用を費やす必要がありません。

マテリアライズドビューは実体を持つ導出表で、その導出には関係演算（選択・射影・結合）を用います。NULL_FILTEREDはキーがNULLでないタプルを抽出するという選択を行い， STORINGで必要なカラムを追加で格納するという射影を行うため、実質的にINDEXで実体を持つマテリアライズドビューであるということです。

使い道の考察

サブタイプ

公式docが言うように、Mailに対してIsUnreadなどのNullableカラムを追加して、特定の属性を持つサブセットに対して高速にスキャンを行うことが出来ます。ここで思ったのが、「あ、フラグや区分のようなサブタイプ識別子で表現されるようなサブタイプの表現に適していそうだなー」という点です。

サブタイプとは、あるエンティティタイプの概念を特化させたものです。例えば、商品は新品と中古品に分けられますがこれは特化の例です。逆に、サブタイプを汎化させたものがスーパタイプです。

例として、データベーススペシャリストH30午後Ⅱ問2のような製造業の品目管理システムで次のような概念設計をして、関係スキーマを作成したとします。

• 受注品目: 顧客から受注して販売する
• 投入品目: 製造に必要な品目で、直接販売されることはない
• 製造品目: 自社で製造する
• 発注品目: 仕入先に発注する

// #は主キー、_は外部キーとする

品目（#品目コード,品目名,製造フラグ,発注フラグ,受注投入品目区分）
  受注品目（#品目コード,標準販売単価）
  投入品目（#品目コード,投入方法）
  製造品目（#品目コード,製造ロット数量）
  発注品目（#品目コード,標準仕入単価,納入リードタイム,発注ロット数量,_仕入取引先コード）

スーパタイプはサブタイプに対する共通属性を持ち、サブタイプは固有属性を持っています。実際に利用する場合には、受注品目を取るときは受注品目と品目を結合して利用するみたいなイメージです。区分によって識別されるサブタイプを排他的サブタイプ、フラグによって識別されるサブタイプを共存的サブタイプといいます。受注と投入は同時には満たせないという制約があり、それ以外は同時に満たせるので、パターンとしては受注製造・受注発注・投入製造・投入発注があります（ややこしいので受注製造発注・投入製造発注は除外して考えます）。

サブタイプの実装

サブタイプのテーブルへの実装方法は、設計通りに全てテーブルを分ける、全て一つのテーブルにまとめる、部分的にテーブルをまとめるなど色々ありますが、今回はCloud SpannerのNull Filtered INDEXを活かして実装することを考えてみます。

NullFilteredINDEXが実質マテビューなら、スーパタイプだけのテーブルにまとめてしまって、サブタイプは全てINDEXで表現するというのも全然利用する上で問題ないんじゃないかなと思いました。

-- スーパタイプには全ての属性を持たせておく
CREATE TABLE 品目 (
  品目コード STRING(256) NOT NULL,
  品目名 STRING(256) NOT NULL,
  標準販売単価 INT64, -- Nullable
  投入方法 STRING(256), -- Nullable
  製造ロット数量 IN64T, -- Nullable
  標準仕入単価 IN64T, -- Nullable
  納入リードタイム INT64, -- Nullable
  発注ロット数量 INT64, -- Nullable
  仕入取引先コード STRING(256), -- Nullable
  FOREIGN KEY (仕入取引先コード) REFERENCES 仕入れ先 (仕入取引先コード)
) PRIMARY KEY(品目コード);

-- サブタイプには固有属性をキーとして持たせたNULL_FILTERED INDEXを利用
CREATE NULL_FILTERED INDEX 受注品目 ON 品目（標準販売単価）;
CREATE NULL_FILTERED INDEX 投入品目 ON 品目（投入方法）;
CREATE NULL_FILTERED INDEX 製造品目 ON 品目（製造ロット数量）;
CREATE NULL_FILTERED INDEX 発注品目 ON 品目（標準仕入単価, 納入リードタイム, 発注ロット数量, 仕入取引先コード）;

-- 共存的サブタイプの組み合わせ系
CREATE NULL_FILTERED INDEX 受注製造品目 ON 品目（標準販売単価, 製造ロット数量）;
CREATE NULL_FILTERED INDEX 受注発注品目 ON 品目（標準販売単価, 標準仕入単価, 納入リードタイム, 発注ロット数量, 仕入取引先コード）;
CREATE NULL_FILTERED INDEX 投入製造品目 ON 品目（投入方法,製造ロット数量）;
CREATE NULL_FILTERED INDEX 投入発注品目 ON 品目（投入方法, 標準仕入単価, 納入リードタイム, 発注ロット数量, 仕入取引先コード）;

こんな感じで、STORINGにスーパタイプの属性をもたせておけば、スーパタイプとのJOINも不要になります。

CREATE NULL_FILTERED INDEX 受注品目 ON 品目（標準販売単価） STORING (品目名);

これで、サブタイプごとに該当するデータだけをINDEXに追加してくれるし、標準販売単価の範囲検索や投入方法による検索も高速にできるので結構ありなんじゃないかなーと思いました。

SELECT 品目コード, 品目名, 標準販売単価
FROM 品目@{FORCE_INDEX=受注品目};

素直な実装

愚直にテーブルを分けるとこんな感じ。

-- スーパタイプ
CREATE TABLE 品目 (
  品目コード STRING(256) NOT NULL,
  品目名 STRING(256) NOT NULL,
  製造フラグ BOOL NOT NULL,
  発注フラグ BOOL NOT NULL,
  受注投入品目区分 STRING(256),
) PRIMARY KEY(品目コード);

-- サブタイプ
CREATE TABLE 受注品目 (
  品目コード STRING(256) NOT NULL,
  標準販売単価 INT64 NOT NULL,
  FOREIGN KEY (品目コード) REFERENCES 品目 (品目コード)
) PRIMARY KEY(品目コード);

STORINGでスーパタイプの固有属性へのアクセスができないので、JOINによるオーバヘッドが避けられないことになります。これはNullFilteredINDEXでは回避できたことなので、明確な差別化点だと思います。

SELECT 品目.品目コード, 品目.品目名, 受注品目.標準販売単価
FROM 受注品目 INNER JOIN 品目 ON 受注品目.品目コード = 品目.品目コード;

さいごに

今回のテーブルはかなり極端に全部INDEXで表現しようとしてますが、スーパタイプ・サブタイプがIDを共有せずに別のテーブルとして存在していてもいいですし、サブタイプを部分的にまとめた上でNULL_FILTEREDを活用するのもいいかもしれません。

いずれにせよ、正規化理論違反や無駄なカラムの追加が起こってしまうので綺麗な設計ではないかもしれません。しかし、パフォーマンス向上のための非正規化なども時には必要なので、手段の一つとして考えてみてもいいかもしれないなと思いました。

NullFilteredINDEXにはかなり可能性を感じるものがあったので、引き続き夢想して思ったこととか書きます。あわよくばプロダクトに生かしたい。

こんな感じで役に立つんだか立たないんだかわからないような、空想話を今後も投稿していこうと思います。