$30 off During Our Annual Pro Sale. View Details »

Google Cloud Spannerにおけるページネーションとインデックスの考え方

Khmer495
November 22, 2022

Google Cloud Spannerにおけるページネーションとインデックスの考え方

Khmer495

November 22, 2022
Tweet

More Decks by Khmer495

Other Decks in Programming

Transcript

  1. Google Cloud Spannerにおける
    ページネーションと
    インデックスの考え方
    久米 祐貴 / くめーる
    Twitter:@kume_ru
    Github:@Khmer495

    View Slide

  2. 自己紹介
    久米 祐貴(くめ ゆうき)
    株式会社サイバーエージェント
    2021年度 新卒入社
    AI事業本部 小売DX領域
    ~2022/4 広告配信システムのバックエンド
    2022/5~ アプリ運用センター
    小売アプリのバックエンドリーダー
    Go, Spanner
    Clean ArchitectureやDDD等アーキテクチャや設計手法にも興味あり

    View Slide

  3. 目次
    • Spannerについて
    • ページネーションの種類
    • ページネーションにおけるクエリの書き方
    • (ページネーションにおける)インデックスの使い方
    • まとめ

    View Slide

  4. Spannerについて

    View Slide

  5. Spannerとは
    公式サイト*からの引用
    > 無制限のスケーリング、強整合性、最大 99.999% の可用性を備えたフルマネージド リレーショナル データベース
    • NewSQL
    • 一般的なRDBの機能が存在。Google標準SQL、PostgreSQL (完全互換ではない) が使用可能
    • スプリットにより分散トランザクションが可能
    • スプリット / split:物理的に分割された一つ一つの保存領域。シャード / shardとも呼ばれる
    • 自動データベースシャーディング
    • 2種類のリレーション (外部キー、インターリーブ)
    • インターリーブ / interleave:
    親要素と子要素を「物理的に」同じ場所に保存することで、子要素の取得速度が向上する
    • フルマネージド
    • 設定項目はコンピューティングリソースの量を決める「処理ユニット」もしくは「ノード」のみ
    *https://cloud.google.com/spanner?hl=ja

    View Slide

  6. インデックスとホットスポット
    前提となる仕様
    • 保存されるスプリットはインデックスのキーで決定
    • 「物理的に」キーの昇順または降順で保存
    • ソート順が近いキーは同じスプリットに保存されやすい
    • 複合キーの場合は先頭から順にソートされる(キーの順番が重要)
    ホットスポット
    • 先頭のキーが連続値である(単調増加・減少する)場合、
    同一のスプリットにアクセスが集中すること。
    パフォーマンス低下の恐れあり

    View Slide

  7. インデックスとホットスポット
    https://cloud.google.com/spanner/docs/schema-design?hl=ja#primary-key-prevent-hotspots
    CREATE TABLE UserAccessLog (
    LastAccess TIMESTAMP NOT NULL,
    UserId STRING(8) NOT NULL,
    ...
    ) PRIMARY KEY (LastAccess, UserId);
    連続値が
    キーの先頭
    スプリット
    同一スプリットに
    連続でinsert

    負荷が分散せず
    パフォーマンス低下
    前提となる仕様
    • 保存されるスプリットはインデックスのキーで決定
    • 「物理的に」キーの昇順または降順で保存

    View Slide

  8. ホットスポットの解決方法*
    先頭のキーが分散していることが必須
    • 連続値から生成したハッシュ値の剰余
    • ページネーションで主に使用
    • ページネーションの際、先頭のキーの範囲が既知であることが重要
    • UUIDを使用
    • 連続値のbit順を逆転
    *https://cloud.google.com/spanner/docs/schema-design?hl=ja#primary-key-prevent-hotspots

    View Slide

  9. ハッシュ値の剰余によるホットスポットの解決
    https://cloud.google.com/spanner/docs/schema-design?hl=ja#fix_hash_the_key
    CREATE TABLE UserAccessLog (
    ShardId INT64 NOT NULL,
    LastAccess TIMESTAMP NOT NULL,
    UserId STRING(8) NOT NULL,
    ...
    ) PRIMARY KEY (ShardId, LastAccess, UserId);
    ShardId = 何らかのハッシュ関数(LastAccess and UserId) % N(Nは任意の整数)
    ShardIdは0~N-1で分散
    スプリット
    ShardIdが分散

    アクセスされる
    スプリットも分散
    前提となる仕様
    • 保存されるスプリットはインデックスのキーで決定
    • 「物理的に」キーの昇順または降順で保存

    View Slide

  10. ページネーション
    について

    View Slide

  11. ページネーションとは
    一覧画面等で複数のデータを一定個数ごとにデータを分割し、取得・表示等の
    操作を行うこと
    ページングとも呼ばれる
    要件に応じてソートや絞り込みが同時に行われる
    → データベースにおいてはインデックスが重要となる
    例:1~20のデータがあり、昇順で偶数を5個ずつ取得する場合
    ・1ページ目: 2, 4, 6, 8, 10
    ・2ページ目:12, 14, 16, 18, 20
    ソート 絞り込み

    View Slide

  12. クエリの手法
    オフセット法 シーク法
    概要 OFFSETを用いる
    1~20のうち6~10が必要な場合、1~10を
    取得後、1~5を捨てる
    前のページの最後のデータから、
    次のページを取得する
    1ページあたり5個で1ページ目が1~「5」の場合、
    2ページ目は「5の次」から5個取得する
    ページの境界の
    計算方法
    簡単
    ページ数と1ページあたりの個数から、
    任意のページの開始位置を計算可能
    複雑
    複雑なクエリで計算可能だが、必要なページ以前を
    全てスキャンする必要がある
    パフォーマンス 後ろのページに行くほど
    悪くなる
    必要なページ以前を全てスキャンする
    必要がある
    常に早い
    必要なページのデータのみスキャンする

    View Slide

  13. 一般的なRDBにおけるオフセット法の例
    CREATE TABLE UserAccessLog (
    LastAccess Date NOT NULL,
    UserId STRING(8) NOT NULL,
    ...
    ) PRIMARY KEY (LastAccess, UserId);
    SELECT LastAccess, UserId
    FROM UserAccessLog
    WHERE LastAccess BETWEEN '2022-11-01' AND '2022-11-07'
    ORDER BY LastAccess DESC, UserId ASC
    LIMIT @limit
    OFFSET @offset;
    LastAccess UserId
    2022-11-01 4efcc208
    2022-11-02 0b891155
    2022-11-02 4efcc208
    2022-11-03 3d04e5a0
    2022-11-04 6da1762c
    2022-11-05 6da1762c
    2022-11-06 3d04e5a0
    @limit page @offset = limit * (page - 1) LastAccess, UserId
    2 0 0 2022-11-06, 3d04e5a0
    2022-11-05, 6da1762c
    2 1 2 2022-11-04, 6da1762c
    2022-11-03, 3d04e5a0
    2 2 4 2022-11-02, 4efcc208
    2022-11-02, 0b891155

    View Slide

  14. 一般的なRDBにおけるシーク法の例
    CREATE TABLE UserAccessLog (
    LastAccess Date NOT NULL,
    UserId STRING(8) NOT NULL,
    ...
    ) PRIMARY KEY (LastAccess, UserId);
    SELECT LastAccess, UserId
    FROM UserAccessLog
    WHERE LastAccess BETWEEN '2022-11-01' AND '2022-11-07'
    AND LastAccess < @lastAccess
    OR (
    LastAccess = @lastAccess
    AND UserId < @userId
    )
    ORDER BY LastAccess DESC, UserId ASC
    LIMIT @limit;
    @limit page @lastAccess @userId LastAccess, UserId
    2 1 9999-12-31 ffffffff 2022-11-06, 3d04e5a0
    2022-11-05, 6da1762c
    2 2 2022-11-05 6da1762c 2022-11-04, 6da1762c
    2022-11-03, 3d04e5a0
    2 3 2022-11-03 3d04e5a0 2022-11-02, 4efcc208
    2022-11-02, 0b891155
    LastAccess UserId
    2022-11-01 4efcc208
    2022-11-02 0b891155
    2022-11-02 4efcc208
    2022-11-03 3d04e5a0
    2022-11-04 6da1762c
    2022-11-05 6da1762c
    2022-11-06 3d04e5a0

    View Slide

  15. ページネーションにおける
    クエリの書き方

    View Slide

  16. ゴール

    View Slide

  17. ShardIdがあるオフセット法
    CREATE TABLE UserAccessLog (
    ShardId INT64 NOT NULL,
    LastAccess TIMESTAMP NOT NULL,
    UserId STRING(8) NOT NULL,
    ...
    ) PRIMARY KEY (ShardId, LastAccess, UserId);
    SELECT
    LimitedByShard.*
    FROM
    UNNEST(GENERATE_ARRAY(0, @shardCount)) AS OneShardId,
    UNNEST(ARRAY(
    SELECT AS STRUCT LastAccess, UserId
    FROM UserAccessLog
    WHERE ShardId = OneShardId
    AND LastAccess BETWEEN '2022-11-01' AND '2022-11-07'
    ORDER BY LastAccess DESC, UserId ASC
    LIMIT @shardLimit
    )) AS s
    ORDER BY
    LimitedByShard.LastAccess DESC,
    LimitedByShard.UserId ASC
    LIMIT @limit
    OFFSET @offset;
    参考:ShardIdがない場合
    SELECT LastAccess, UserId
    FROM UserAccessLog
    WHERE LastAccess BETWEEN '2022-11-01'
    AND '2022-11-07'
    ORDER BY LastAccess DESC, UserId ASC
    LIMIT @limit
    OFFSET @offset;

    View Slide

  18. ShardIdがあるシーク法
    CREATE TABLE UserAccessLog (
    ShardId INT64 NOT NULL,
    LastAccess TIMESTAMP NOT NULL,
    UserId STRING(8) NOT NULL,
    ...
    ) PRIMARY KEY (ShardId, LastAccess, UserId);
    SELECT
    LimitedByShard.*
    FROM
    UNNEST(GENERATE_ARRAY(0, @shardCount)) AS OneShardId,
    UNNEST(ARRAY(
    SELECT AS STRUCT LastAccess, UserId
    FROM UserAccessLog
    WHERE ShardId = OneShardId
    AND LastAccess BETWEEN '2022-11-01' AND '2022-11-07'
    AND LastAccess < @lastAccess
    OR (
    LastAccess = @lastAccess
    AND UserId < @userId
    )
    ORDER BY LastAccess DESC, UserId ASC
    LIMIT @shardLimit
    )) AS LimitedByShard
    ORDER BY
    LimitedByShard.LastAccess DESC,
    LimitedByShard.UserId ASC
    LIMIT @limit;
    参考:ShardIdがない場合
    SELECT LastAccess, UserId
    FROM UserAccessLog
    WHERE LastAccess BETWEEN '2022-11-01'
    AND '2022-11-07'
    AND LastAccess < @lastAccess
    OR (
    LastAccess = @lastAccess
    AND UserId < @userId
    )
    ORDER BY LastAccess DESC, UserId ASC
    LIMIT @limit;

    View Slide

  19. ShardIdがある
    オフセット法を読み解く

    View Slide

  20. GENERATE_ARRAY, UNNEST
    CREATE TABLE UserAccessLog (
    ShardId INT64 NOT NULL,
    LastAccess TIMESTAMP NOT NULL,
    UserId STRING(8) NOT NULL,
    ...
    ) PRIMARY KEY (ShardId, LastAccess, UserId);
    SELECT
    LimitedByShard.*
    FROM
    UNNEST(GENERATE_ARRAY(0, @shardCount)) AS OneShardId,
    UNNEST(ARRAY(
    SELECT AS STRUCT LastAccess, UserId
    FROM UserAccessLog
    WHERE ShardId = OneShardId
    AND LastAccess BETWEEN '2022-11-01' AND '2022-11-07'
    ORDER BY LastAccess DESC, UserId ASC
    LIMIT @shardLimit
    )) AS LimitedByShard
    ORDER BY
    LimitedByShard.LastAccess DESC,
    LimitedByShard.UserId ASC
    LIMIT @limit
    OFFSET @offset;
    参考:ShardIdがない場合
    SELECT LastAccess, UserId
    FROM UserAccessLog
    WHERE LastAccess BETWEEN '2022-11-01'
    AND '2022-11-07'
    ORDER BY LastAccess DESC, UserId ASC
    LIMIT @limit
    OFFSET @offset;

    View Slide

  21. GENERATE_ARRAY, UNNEST
    UNNEST(GENERATE_ARRAY(0, @shardCount)) AS OneShardId,
    GENERATE_ARRAY(0, @shardCount)
    • 0~@shardCountの配列を作成 = [0, 1, 2, …, @shardCount]
    • spannerにはARRAY型が存在する
    • @shardCount = N - 1 (ShardId = 何らかのハッシュ関数
    (LastAccess and UserId) % N(Nは任意の整数))
    UNNEST(...) AS OneShardId
    • ARRAYを展開、1要素を1レコードに
    • OneShardIdを使用すると、後続で1要素ずつ参照できる

    View Slide

  22. GENERATE_ARRAY, UNNEST
    Goで雰囲気を表現してみると、
    for _, OneShardId := range []int{0, 1, 2, …, @shardCount} {
    後続の処理
    }
    UNNEST(GENERATE_ARRAY(0, @shardCount)) AS OneShardId,

    View Slide

  23. UNNEST, ARRAY, SELECT AS STRUCT
    CREATE TABLE UserAccessLog (
    ShardId INT64 NOT NULL,
    LastAccess TIMESTAMP NOT NULL,
    UserId STRING(8) NOT NULL,
    ...
    ) PRIMARY KEY (ShardId, LastAccess, UserId);
    SELECT
    LimitedByShard.*
    FROM
    UNNEST(GENERATE_ARRAY(0, @shardCount)) AS OneShardId,
    UNNEST(ARRAY(
    SELECT AS STRUCT LastAccess, UserId
    FROM UserAccessLog
    WHERE ShardId = OneShardId
    AND LastAccess BETWEEN '2022-11-01' AND '2022-11-07'
    ORDER BY LastAccess DESC, UserId ASC
    LIMIT @shardLimit
    )) AS LimitedByShard
    ORDER BY
    LimitedByShard.LastAccess DESC,
    LimitedByShard.UserId ASC
    LIMIT @limit
    OFFSET @offset;
    参考:ShardIdがない場合
    SELECT LastAccess, UserId
    FROM UserAccessLog
    WHERE LastAccess BETWEEN '2022-11-01'
    AND '2022-11-07'
    ORDER BY LastAccess DESC, UserId ASC
    LIMIT @limit
    OFFSET @offset;

    View Slide

  24. UNNEST, ARRAY, SELECT AS STRUCT
    UNNEST(ARRAY(
    SELECT AS STRUCT LastAccess, UserId
    FROM UserAccessLog
    WHERE ShardId = OneShardId
    AND LastAccess BETWEEN '2022-11-01' AND '2022-11-07'
    ORDER BY LastAccess DESC, UserId ASC
    LIMIT @shardLimit
    )) AS PagedByShard
    SELECT AS STRUCT
    • カラム名: 値のkey-valueオブジェクトを作成
    ARRAY(SELECT AS STRUCT …)
    • 同一クエリで生成された全てのkey-valueオブジェクトの配列を作成
    UNNEST(ARRAY(SELECT AS STRUCT …))
    • 配列を展開。key-valueオブジェクトもカラム・値に展開

    View Slide

  25. UNNEST, ARRAY, SELECT AS STRUCT
    UNNEST(ARRAY(
    SELECT AS STRUCT LastAccess, UserId
    FROM UserAccessLog
    WHERE ShardId = OneShardId
    AND LastAccess BETWEEN '2022-11-01' AND '2022-11-07'
    ORDER BY LastAccess DESC, UserId ASC
    LIMIT @shardLimit
    )) AS PagedByShard
    SELECT AS STRUCT(OneShardIdを1個ずつ実行)
    OneShardId = 0 → {LastAccess: 2022-11-06, UserId: 3d04e5a0},{LastAccess: 2022-11-04, UserId: 6da1762c}
    OneShardId = 1 → {LastAccess: 2022-11-05, UserId: 6da1762c}
    OneShardId = 2 → …

    ARRAY(全OneShardIdの結果をUNION後に配列化)
    [{LastAccess: 2022-11-06, UserId: 3d04e5a0},{LastAccess: 2022-11-04, UserId: 6da1762c},{LastAccess: 2022-11-05, UserId: 6da1762c},…]

    UNNEST
    LastAccess, UserId
    2022-11-06, 3d04e5a0
    2022-11-04, 6da1762c
    2022-11-05, 6da1762c

    Goで雰囲気を表現してみると
    LimitedByShard := make(
    []struct{LastAccess time.Time; UserId int64},
    0,
    @shardCount+1,
    )
    for _, OneShardId := range []int{0, 1, 2, …, @shardCount} {
    limited := someQuery(OneShardId)
    LimitedByShard = append(LimitedByShard, limited…)
    }

    View Slide

  26. shardLimitとLimit, Offset
    CREATE TABLE UserAccessLog (
    ShardId INT64 NOT NULL,
    LastAccess TIMESTAMP NOT NULL,
    UserId STRING(8) NOT NULL,
    ...
    ) PRIMARY KEY (ShardId, LastAccess, UserId);
    SELECT
    LimitedByShard.*
    FROM
    UNNEST(GENERATE_ARRAY(0, @shardCount)) AS OneShardId,
    UNNEST(ARRAY(
    SELECT AS STRUCT LastAccess, UserId
    FROM UserAccessLog
    WHERE ShardId = OneShardId
    AND LastAccess BETWEEN '2022-11-01' AND '2022-11-07'
    ORDER BY LastAccess DESC, UserId ASC
    LIMIT @shardLimit
    )) AS LimitedByShard
    ORDER BY
    LimitedByShard.LastAccess DESC,
    LimitedByShard.UserId ASC
    LIMIT @limit
    OFFSET @offset;
    参考:ShardIdがない場合
    SELECT LastAccess, UserId
    FROM UserAccessLog
    WHERE LastAccess BETWEEN '2022-11-01'
    AND '2022-11-07'
    ORDER BY LastAccess DESC, UserId ASC
    LIMIT @limit
    OFFSET @offset;
    shard単位のクエリではoffsetを用いない
    shard単位のクエリではlimitの値が異なる
    @shardLimit = @limit + @offset

    View Slide

  27. shardLimitとLimit, Offset
    失敗例
    ● shard単位のクエリでoffsetを用いる
    ● shard単位のクエリでlimitの値が同じ
    shardId (N=2) LastAccess UserId
    1 2022-11-01 4efcc208
    0 2022-11-02 0b891155
    1 2022-11-02 4efcc208
    0 2022-11-03 3d04e5a0
    0 2022-11-04 6da1762c
    1 2022-11-05 6da1762c
    1 2022-11-06 3d04e5a0
    @limit page @offset shard 0
    LastAccess, UserId
    shard 1
    LastAccess, UserId
    LastAccess, UserId Expected
    2 1 0 2022-11-04, 6da1762c
    2022-11-03, 3d04e5a0
    2022-11-06, 3d04e5a0
    2022-11-05, 6da1762c
    2022-11-06, 3d04e5a0
    2022-11-05, 6da1762c
    2022-11-06, 3d04e5a0
    2022-11-05, 6da1762c
    2 2 2 2022-11-02, 0b891155 2022-11-02, 4efcc208
    2022-11-01, 4efcc208
    2022-11-02, 0b891155
    2022-11-02, 4efcc208
    2022-11-04, 6da1762c
    2022-11-03, 3d04e5a0
    2 3 4 2022-11-02, 0b891155
    2022-11-02, 4efcc208

    View Slide

  28. shardLimitとLimit, Offset
    @shardLimit = @limit + @offset
    (@shardOffset = 0)
    shardId (N=2) LastAccess UserId
    1 2022-11-01 4efcc208
    0 2022-11-02 0b891155
    1 2022-11-02 4efcc208
    0 2022-11-03 3d04e5a0
    0 2022-11-04 6da1762c
    1 2022-11-05 6da1762c
    1 2022-11-06 3d04e5a0
    @limit page @offset @shardLimit shard 0
    LastAccess, UserId
    shard 1
    LastAccess, UserId
    LastAccess, UserId
    2 1 0 2 2022-11-04, 6da1762c
    2022-11-03, 3d04e5a0
    2022-11-06, 3d04e5a0
    2022-11-05, 6da1762c
    2022-11-06, 3d04e5a0
    2022-11-05, 6da1762c
    2 2 2 4 2022-11-04, 6da1762c
    2022-11-03, 3d04e5a0
    2022-11-02, 0b891155
    2022-11-06, 3d04e5a0
    2022-11-05, 6da1762c
    2022-11-02, 4efcc208
    2022-11-01, 4efcc208
    2022-11-04, 6da1762c
    2022-11-03, 3d04e5a0
    2 3 4 6 2022-11-04, 6da1762c
    2022-11-03, 3d04e5a0
    2022-11-02, 0b891155
    2022-11-06, 3d04e5a0
    2022-11-05, 6da1762c
    2022-11-02, 4efcc208
    2022-11-01, 4efcc208
    2022-11-02, 0b891155
    2022-11-02, 4efcc208

    View Slide

  29. ShardIdがあるオフセット法
    CREATE TABLE UserAccessLog (
    ShardId INT64 NOT NULL,
    LastAccess TIMESTAMP NOT NULL,
    UserId STRING(8) NOT NULL,
    ...
    ) PRIMARY KEY (ShardId, LastAccess, UserId);
    SELECT
    LimitedByShard.*
    FROM
    UNNEST(GENERATE_ARRAY(0, @shardCount)) AS OneShardId,
    UNNEST(ARRAY(
    SELECT AS STRUCT LastAccess, UserId
    FROM UserAccessLog
    WHERE ShardId = OneShardId
    AND LastAccess BETWEEN '2022-11-01' AND '2022-11-07'
    ORDER BY LastAccess DESC, UserId ASC
    LIMIT @shardLimit
    )) AS s
    ORDER BY
    LimitedByShard.LastAccess DESC,
    LimitedByShard.UserId ASC
    LIMIT @limit
    OFFSET @offset;
    参考:ShardIdがない場合
    SELECT LastAccess, UserId
    FROM UserAccessLog
    WHERE LastAccess BETWEEN '2022-11-01'
    AND '2022-11-07'
    ORDER BY LastAccess DESC, UserId ASC
    LIMIT @limit
    OFFSET @offset;
    読めるようになりましたか?

    View Slide

  30. ShardIdがあるシーク法
    CREATE TABLE UserAccessLog (
    ShardId INT64 NOT NULL,
    LastAccess TIMESTAMP NOT NULL,
    UserId STRING(8) NOT NULL,
    ...
    ) PRIMARY KEY (ShardId, LastAccess, UserId);
    SELECT
    LimitedByShard.*
    FROM
    UNNEST(GENERATE_ARRAY(0, @shardCount)) AS OneShardId,
    UNNEST(ARRAY(
    SELECT AS STRUCT LastAccess, UserId
    FROM UserAccessLog
    WHERE ShardId = OneShardId
    AND LastAccess BETWEEN '2022-11-01' AND '2022-11-07'
    AND LastAccess < @lastAccess
    OR (
    LastAccess = @lastAccess
    AND UserId < @userId
    )
    ORDER BY LastAccess DESC, UserId ASC
    LIMIT @limit
    )) AS LimitedByShard
    ORDER BY
    LimitedByShard.LastAccess DESC,
    LimitedByShard.UserId ASC
    LIMIT @limit;
    参考:ShardIdがない場合
    SELECT LastAccess, UserId
    FROM UserAccessLog
    WHERE LastAccess BETWEEN '2022-11-01'
    AND '2022-11-07'
    AND LastAccess < @lastAccess
    OR (
    LastAccess = @lastAccess
    AND UserId < @userId
    )
    ORDER BY LastAccess DESC, UserId ASC
    LIMIT @limit;
    シーク法も同じ
    ただし@shardLimit = @Limit

    View Slide

  31. (ページネーションにおける)
    インデックスの使い方

    View Slide

  32. 連続値のインデックス

    View Slide

  33. キーの順番が変わった場合
    CREATE TABLE UserAccessLog (
    UserId STRING(8) NOT NULL,
    LastAccess Date NOT NULL,
    ...
    ) PRIMARY KEY (UserId, LastAccess);
    SELECT UserId, LastAccess
    FROM UserAccessLog
    WHERE LastAccess BETWEEN '2022-11-01' AND '2022-11-07'
    ORDER BY LastAccess DESC, UserId ASC
    LIMIT @limit
    OFFSET @offset;
    UserIdは乱数
    キーの先頭に来てもShardIdは不要
    WHEREの対象がLastAccessのみ
    インデックスはキーの順にのみ適用

    インデックスが使用不可

    フルスキャン

    View Slide

  34. インデックス用のShardId
    CREATE TABLE UserAccessLog (
    UserId STRING(8) NOT NULL,
    LastAccess Date NOT NULL,
    LastAccessShardId INT64 NOT NULL,
    ...
    ) PRIMARY KEY (UserId, LastAccess);
    CREATE INDEX UserAccessLog_LastAccessDesc
    ON UserAccessLog (LastAccessShardId, LastAccess DESC, UserId);
    SELECT
    LimitedByShard.*
    FROM
    UNNEST(GENERATE_ARRAY(0, @shardCount)) AS OneShardId,
    UNNEST(ARRAY(
    SELECT AS STRUCT LastAccess, UserId
    FROM UserAccessLog@{FORCE_INDEX=UserAccessLog_LastAccessDesc}
    WHERE LastAccessShardId = OneShardId
    AND LastAccess BETWEEN '2022-11-01' AND '2022-11-07'
    ORDER BY LastAccess DESC, UserId ASC
    LIMIT @shardLimit
    )) AS LimitedByShard
    ORDER BY
    LimitedByShard.LastAccess DESC,
    LimitedByShard.UserId ASC
    LIMIT @limit
    OFFSET @offset;
    インデックスはキーを
    プライマリーキーとして
    別テーブルを作成

    インデックスの先頭のキーが
    連続値の場合
    ホットスポット発生の恐れ

    インデックス用のShardIdを作成
    キーの先頭に

    View Slide

  35. インデックスのキーと
    STORING句

    View Slide

  36. STORING句
    CREATE TABLE UserAccessLog (
    UserId STRING(8) NOT NULL,
    LastAccess Date NOT NULL,
    LastAccessShardId INT64 NOT NULL,

    DeletedAt TIMESTAMP,
    ) PRIMARY KEY (UserId, LastAccess);
    CREATE INDEX UserAccessLog_LastAccessDesc
    ON UserAccessLog (LastAccessShardId, LastAccess DESC, UserId) STORING (DeletedAt);
    ↓≒
    CREATE TABLE UserAccessLog_LastAccessDesc (
    LastAccessShardId INT64 NOT NULL,
    LastAccess Date NOT NULL,
    UserId STRING(8) NOT NULL,
    DeletedAt TIMESTAMP,
    ) PRIMARY KEY (LastAccessShardId, LastAccess DESC, UserId);
    STORING句:キー以外でインデックステーブルに保存したいカラムに適用

    View Slide

  37. 最適なインデックスはどれ?①
    CREATE TABLE UserAccessLog (
    UserId STRING(8) NOT NULL,
    LastAccess Date NOT NULL,
    LastAccessShardId INT64 NOT NULL,

    DeletedAt TIMESTAMP,
    ) PRIMARY KEY (UserId, LastAccess);
    CREATE INDEX UserAccessLog_LastAccessDesc
    ① ON UserAccessLog (LastAccessShardId, LastAccess DESC);
    ② ON UserAccessLog (LastAccessShardId, LastAccess DESC, DeletedAt);
    ③ ON UserAccessLog (LastAccessShardId, LastAccess DESC) STORING (DeletedAt);
    WHERE LastAccess BETWEEN '2022-11-01' AND '2022-11-07'
    AND DeletedAt IS NULL
    ORDER BY LastAccess DESC
    に対して
    という条件をかけるとき、
    ・それぞれのインデックスを使用した場合の挙動はどうなる?
    ・最適なインデックスはどれ?

    View Slide

  38. 最適なインデックスはどれ?①
    CREATE TABLE UserAccessLog (
    UserId STRING(8) NOT NULL,
    LastAccess Date NOT NULL,
    LastAccessShardId INT64 NOT NULL,

    DeletedAt TIMESTAMP,
    ) PRIMARY KEY (UserId, LastAccess);
    CREATE INDEX UserAccessLog_LastAccessDesc
    ① ON UserAccessLog (LastAccessShardId, LastAccess DESC);
    → ✕:DeletedAtがインデックステーブルにないため、インデックス使用不可
    ② ON UserAccessLog (LastAccessShardId, LastAccess DESC, DeletedAt);
    → ○/△:DeletedAtがインデックステーブルに含まれるが、IS NULLに対するインデックスの効果は薄い
    ③ ON UserAccessLog (LastAccessShardId, LastAccess DESC) STORING (DeletedAt);
    → ○:DeletedAtがインデックステーブルに含まれる
    に対して
    という条件をかけるとき、
    ・それぞれのインデックスを使用した場合の挙動はどうなる?
    ・最適なインデックスはどれ?
    WHERE LastAccess BETWEEN '2022-11-01' AND '2022-11-07'
    AND DeletedAt IS NULL
    ORDER BY LastAccess DESC

    View Slide

  39. 最適なインデックスはどれ?②
    CREATE TABLE UserAccessLog (
    UserId STRING(8) NOT NULL,
    LastAccess Date NOT NULL,
    LastAccessShardId INT64 NOT NULL,

    DeletedAt TIMESTAMP,
    ) PRIMARY KEY (UserId, LastAccess);
    CREATE INDEX UserAccessLog_LastAccessDesc
    ① インデックスが不要
    ② ON UserAccessLog (LastAccessShardId, LastAccess DESC, UserId ASC);
    ③ ON UserAccessLog (LastAccessShardId, LastAccess DESC) STORING (UserId);
    WHERE LastAccess BETWEEN '2022-11-01' AND '2022-11-07'
    ORDER BY UserId ASC
    に対して
    という条件をかけるとき、
    ・それぞれのインデックスを使用した場合の挙動はどうなる?
    ・最適なインデックスはどれ?

    View Slide

  40. 最適なインデックスはどれ?②
    CREATE TABLE UserAccessLog (
    UserId STRING(8) NOT NULL,
    LastAccess Date NOT NULL,
    LastAccessShardId INT64 NOT NULL,

    DeletedAt TIMESTAMP,
    ) PRIMARY KEY (UserId, LastAccess);
    CREATE INDEX UserAccessLog_LastAccessDesc
    ① インデックスが不要
    → ✕:フルスキャン。プライマリーキーのインデックスを使用するにはまずUserIdの指定が必要
    ② ON UserAccessLog (LastAccessShardId, LastAccess DESC, UserId ASC);
    → ○:LastAccessで絞り込み後、UserIdの昇順に既になっている
    ③ ON UserAccessLog (LastAccessShardId, LastAccess DESC) STORING (UserId);
    → △:LastAccessで絞り込み後、UserIdに対するソート処理が必要
    WHERE LastAccess BETWEEN '2022-11-01' AND '2022-11-07'
    ORDER BY UserId ASC
    に対して
    という条件をかけるとき、
    ・それぞれのインデックスを使用した場合の挙動はどうなる?
    ・最適なインデックスはどれ?

    View Slide

  41. 最適なインデックスは何?
    CREATE TABLE UserAccessLog (
    UserId STRING(8) NOT NULL,
    LastAccess Date NOT NULL,
    LastAccessShardId INT64 NOT NULL,

    DeletedAt TIMESTAMP,
    ) PRIMARY KEY (UserId, LastAccess);
    WHERE LastAccess BETWEEN '2022-11-01' AND '2022-11-07'
    AND DeletedAt IS NULL
    ORDER BY LastAccess DESC, UserId ASC
    に対して
    という条件で
    ・オフセット法によるページネーションを行う場合、
    ・シーク法によるページネーションを行う場合、
    最適なインデックスは何?

    View Slide

  42. 最適なインデックスは何?
    CREATE TABLE UserAccessLog (
    UserId STRING(8) NOT NULL,
    LastAccess Date NOT NULL,
    LastAccessShardId INT64 NOT NULL,

    DeletedAt TIMESTAMP,
    ) PRIMARY KEY (UserId, LastAccess);
    CREATE INDEX UserAccessLog_LastAccessDesc
    ON UserAccessLog (LastAccessShardId, LastAccess DESC, UserId ASC) STORING (DeletedAt);
    WHERE LastAccess BETWEEN '2022-11-01' AND '2022-11-07'
    AND DeletedAt IS NULL
    ORDER BY LastAccess DESC, UserId ASC
    に対して
    という条件で
    ・オフセット法によるページネーションを行う場合、
    ・シーク法によるページネーションを行う場合、
    最適なインデックスは何?
    オフセット法・シーク法ともに
    シーク法で追加で必要となる WHERE句の対象のカラムは、 ORDER句のカラムと同じである

    View Slide

  43. インデックスの決め方
    キー
    • ORDER句に含むもの
    • 順序
    • WHERE句に含むものを前に
    • 分散性の高いものを前に
    STORING句
    • ORDER句に含まないが、WHERE句に含むもの
    • (case by case) SELECT句に含むもの

    View Slide

  44. まとめ
    • キーの先頭は分散値
    連続値から生成するとき、ページネーションの場合はShardIdが好ましい
    • ページネーションにおいて各ShardIdに対するlimit, offsetは
    • オフセット法
    • shardLimit = limit + offset
    • shardOffset = 0
    • シーク法
    • shardLimit = limit
    • shardOffset = 0
    • インデックスのキーはORDER句のカラム
    STORINGはORDER句にないWHERE句のカラム

    View Slide

  45. 参考資料
    ● offsetでページネーションは遅い。これからはシーク法だ!https://qiita.com/madilloar/items/b4e786a932ef9d4551b9
    ● で、オフセット法に比べてシーク法のページネーションはどれだけ早いの?RDB毎に。
    https://qiita.com/madilloar/items/5625e61cf3e348d08ef8
    ● ホットスポットを防ぐ主キーの選択方法
    https://cloud.google.com/spanner/docs/schema-design?hl=ja#primary-key-prevent-hotspots
    ● Shard
    https://github.com/gcpug/nouhau/blob/spanner/shard/spanner/note/shard/README.md

    View Slide