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

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とも呼ばれる • 自動データベースシャーディング • ２種類のリレーション (外部キー、インターリーブ) • インターリーブ / 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) • 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要素ずつ参照できる
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 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を作成 キーの先頭に
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