Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

Khmer495
November 22, 2022

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

Khmer495

November 22, 2022
Tweet

Other Decks in Programming

Transcript

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

    Github:@Khmer495
  2. 自己紹介 久米 祐貴(くめ ゆうき) 株式会社サイバーエージェント 2021年度 新卒入社 AI事業本部 小売DX領域 ~2022/4

    広告配信システムのバックエンド 2022/5~ アプリ運用センター 小売アプリのバックエンドリーダー Go, Spanner Clean ArchitectureやDDD等アーキテクチャや設計手法にも興味あり
  3. 目次 • Spannerについて • ページネーションの種類 • ページネーションにおけるクエリの書き方 • (ページネーションにおける)インデックスの使い方 •

    まとめ
  4. Spannerについて

  5. Spannerとは 公式サイト*からの引用 > 無制限のスケーリング、強整合性、最大 99.999% の可用性を備えたフルマネージド リレーショナル データベース • NewSQL

    • 一般的なRDBの機能が存在。Google標準SQL、PostgreSQL (完全互換ではない) が使用可能 • スプリットにより分散トランザクションが可能 • スプリット / split:物理的に分割された一つ一つの保存領域。シャード / shardとも呼ばれる • 自動データベースシャーディング • 2種類のリレーション (外部キー、インターリーブ) • インターリーブ / interleave: 親要素と子要素を「物理的に」同じ場所に保存することで、子要素の取得速度が向上する • フルマネージド • 設定項目はコンピューティングリソースの量を決める「処理ユニット」もしくは「ノード」のみ *https://cloud.google.com/spanner?hl=ja
  6. インデックスとホットスポット 前提となる仕様 • 保存されるスプリットはインデックスのキーで決定 • 「物理的に」キーの昇順または降順で保存 • ソート順が近いキーは同じスプリットに保存されやすい • 複合キーの場合は先頭から順にソートされる(キーの順番が重要)

    ホットスポット • 先頭のキーが連続値である(単調増加・減少する)場合、 同一のスプリットにアクセスが集中すること。 パフォーマンス低下の恐れあり
  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 ↓ 負荷が分散せず パフォーマンス低下 前提となる仕様 • 保存されるスプリットはインデックスのキーで決定 • 「物理的に」キーの昇順または降順で保存
  8. ホットスポットの解決方法* 先頭のキーが分散していることが必須 • 連続値から生成したハッシュ値の剰余 • ページネーションで主に使用 • ページネーションの際、先頭のキーの範囲が既知であることが重要 • UUIDを使用

    • 連続値のbit順を逆転 *https://cloud.google.com/spanner/docs/schema-design?hl=ja#primary-key-prevent-hotspots
  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が分散 ↓ アクセスされる スプリットも分散 前提となる仕様 • 保存されるスプリットはインデックスのキーで決定 • 「物理的に」キーの昇順または降順で保存
  10. ページネーション について

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

    4, 6, 8, 10 ・2ページ目:12, 14, 16, 18, 20 ソート 絞り込み
  12. クエリの手法 オフセット法 シーク法 概要 OFFSETを用いる 1~20のうち6~10が必要な場合、1~10を 取得後、1~5を捨てる 前のページの最後のデータから、 次のページを取得する 1ページあたり5個で1ページ目が1~「5」の場合、

    2ページ目は「5の次」から5個取得する ページの境界の 計算方法 簡単 ページ数と1ページあたりの個数から、 任意のページの開始位置を計算可能 複雑 複雑なクエリで計算可能だが、必要なページ以前を 全てスキャンする必要がある パフォーマンス 後ろのページに行くほど 悪くなる 必要なページ以前を全てスキャンする 必要がある 常に早い 必要なページのデータのみスキャンする
  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
  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
  15. ページネーションにおける クエリの書き方

  16. ゴール

  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;
  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;
  19. ShardIdがある オフセット法を読み解く

  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;
  21. GENERATE_ARRAY, UNNEST UNNEST(GENERATE_ARRAY(0, @shardCount)) AS OneShardId, GENERATE_ARRAY(0, @shardCount) • [email protected]の配列を作成

    = [0, 1, 2, …, @shardCount] • spannerにはARRAY型が存在する • @shardCount = N - 1 (ShardId = 何らかのハッシュ関数 (LastAccess and UserId) % N(Nは任意の整数)) UNNEST(...) AS OneShardId • ARRAYを展開、1要素を1レコードに • OneShardIdを使用すると、後続で1要素ずつ参照できる
  22. GENERATE_ARRAY, UNNEST Goで雰囲気を表現してみると、 for _, OneShardId := range []int{0, 1,

    2, …, @shardCount} { 後続の処理 } UNNEST(GENERATE_ARRAY(0, @shardCount)) AS OneShardId,
  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;
  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オブジェクトもカラム・値に展開
  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…) }
  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
  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
  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
  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; 読めるようになりましたか?
  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
  31. (ページネーションにおける) インデックスの使い方

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

  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のみ インデックスはキーの順にのみ適用 ↓ インデックスが使用不可 ↓ フルスキャン
  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 [email protected]{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を作成 キーの先頭に
  35. インデックスのキーと STORING句

  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句:キー以外でインデックステーブルに保存したいカラムに適用
  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 に対して という条件をかけるとき、 ・それぞれのインデックスを使用した場合の挙動はどうなる? ・最適なインデックスはどれ?
  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
  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 に対して という条件をかけるとき、 ・それぞれのインデックスを使用した場合の挙動はどうなる? ・最適なインデックスはどれ?
  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 に対して という条件をかけるとき、 ・それぞれのインデックスを使用した場合の挙動はどうなる? ・最適なインデックスはどれ?
  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 に対して という条件で ・オフセット法によるページネーションを行う場合、 ・シーク法によるページネーションを行う場合、 最適なインデックスは何?
  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句のカラムと同じである
  43. インデックスの決め方 キー • ORDER句に含むもの • 順序 • WHERE句に含むものを前に • 分散性の高いものを前に

    STORING句 • ORDER句に含まないが、WHERE句に含むもの • (case by case) SELECT句に含むもの
  44. まとめ • キーの先頭は分散値 連続値から生成するとき、ページネーションの場合はShardIdが好ましい • ページネーションにおいて各ShardIdに対するlimit, offsetは • オフセット法 •

    shardLimit = limit + offset • shardOffset = 0 • シーク法 • shardLimit = limit • shardOffset = 0 • インデックスのキーはORDER句のカラム STORINGはORDER句にないWHERE句のカラム
  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