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

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とも呼ばれる
   • 自動データベースシャーディング
   • ２種類のリレーション (外部キー、インターリーブ)
   • インターリーブ / 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