Slide 1
Slide 1 text
Google Cloud Spannerにおける
ページネーションと
インデックスの考え方
久米 祐貴 / くめーる
Twitter：@kume_ru
Github：@Khmer495
Slide 2
Slide 2 text
自己紹介
久米 祐貴（くめ ゆうき）
株式会社サイバーエージェント
2021年度 新卒入社
AI事業本部 小売DX領域
~2022/4 広告配信システムのバックエンド
2022/5~ アプリ運用センター
小売アプリのバックエンドリーダー
Go, Spanner
Clean ArchitectureやDDD等アーキテクチャや設計手法にも興味あり
Slide 3
Slide 3 text
目次
• Spannerについて
• ページネーションの種類
• ページネーションにおけるクエリの書き方
• （ページネーションにおける）インデックスの使い方
• まとめ
Slide 4
Slide 4 text
Spannerについて
Slide 5
Slide 5 text
Spannerとは
公式サイト*からの引用
> 無制限のスケーリング、強整合性、最大 99.999% の可用性を備えたフルマネージド リレーショナル データベース
• NewSQL
• 一般的なRDBの機能が存在。Google標準SQL、PostgreSQL (完全互換ではない) が使用可能
• スプリットにより分散トランザクションが可能
• スプリット / split：物理的に分割された一つ一つの保存領域。シャード / shardとも呼ばれる
• 自動データベースシャーディング
• ２種類のリレーション (外部キー、インターリーブ)
• インターリーブ / interleave：
親要素と子要素を「物理的に」同じ場所に保存することで、子要素の取得速度が向上する
• フルマネージド
• 設定項目はコンピューティングリソースの量を決める「処理ユニット」もしくは「ノード」のみ
*https://cloud.google.com/spanner?hl=ja
Slide 6
Slide 6 text
インデックスとホットスポット
前提となる仕様
• 保存されるスプリットはインデックスのキーで決定
• 「物理的に」キーの昇順または降順で保存
• ソート順が近いキーは同じスプリットに保存されやすい
• 複合キーの場合は先頭から順にソートされる（キーの順番が重要）
ホットスポット
• 先頭のキーが連続値である（単調増加・減少する）場合、
同一のスプリットにアクセスが集中すること。
パフォーマンス低下の恐れあり
Slide 7
Slide 7 text
インデックスとホットスポット
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
↓
負荷が分散せず
パフォーマンス低下
前提となる仕様
• 保存されるスプリットはインデックスのキーで決定
• 「物理的に」キーの昇順または降順で保存
Slide 8
Slide 8 text
ホットスポットの解決方法*
先頭のキーが分散していることが必須
• 連続値から生成したハッシュ値の剰余
• ページネーションで主に使用
• ページネーションの際、先頭のキーの範囲が既知であることが重要
• UUIDを使用
• 連続値のbit順を逆転
*https://cloud.google.com/spanner/docs/schema-design?hl=ja#primary-key-prevent-hotspots
Slide 9
Slide 9 text
ハッシュ値の剰余によるホットスポットの解決
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が分散
↓
アクセスされる
スプリットも分散
前提となる仕様
• 保存されるスプリットはインデックスのキーで決定
• 「物理的に」キーの昇順または降順で保存
Slide 10
Slide 10 text
ページネーション
について
Slide 11
Slide 11 text
ページネーションとは
一覧画面等で複数のデータを一定個数ごとにデータを分割し、取得・表示等の
操作を行うこと
ページングとも呼ばれる
要件に応じてソートや絞り込みが同時に行われる
→ データベースにおいてはインデックスが重要となる
例：1~20のデータがあり、昇順で偶数を5個ずつ取得する場合
・1ページ目： 2, 4, 6, 8, 10
・2ページ目：12, 14, 16, 18, 20
ソート 絞り込み
Slide 12
Slide 12 text
クエリの手法
オフセット法 シーク法
概要 OFFSETを用いる
1~20のうち6~10が必要な場合、1~10を
取得後、1~5を捨てる
前のページの最後のデータから、
次のページを取得する
1ページあたり5個で1ページ目が1~「5」の場合、
2ページ目は「5の次」から5個取得する
ページの境界の
計算方法
簡単
ページ数と1ページあたりの個数から、
任意のページの開始位置を計算可能
複雑
複雑なクエリで計算可能だが、必要なページ以前を
全てスキャンする必要がある
パフォーマンス 後ろのページに行くほど
悪くなる
必要なページ以前を全てスキャンする
必要がある
常に早い
必要なページのデータのみスキャンする
Slide 13
Slide 13 text
一般的な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
Slide 14
Slide 14 text
一般的な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
Slide 15
Slide 15 text
ページネーションにおける
クエリの書き方
Slide 16
Slide 16 text
ゴール
Slide 17
Slide 17 text
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;
Slide 18
Slide 18 text
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;
Slide 19
Slide 19 text
ShardIdがある
オフセット法を読み解く
Slide 20
Slide 20 text
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;
Slide 21
Slide 21 text
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要素ずつ参照できる
Slide 22
Slide 22 text
GENERATE_ARRAY, UNNEST
Goで雰囲気を表現してみると、
for _, OneShardId := range []int{0, 1, 2, …, @shardCount} {
後続の処理
}
UNNEST(GENERATE_ARRAY(0, @shardCount)) AS OneShardId,
Slide 23
Slide 23 text
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;
Slide 24
Slide 24 text
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オブジェクトもカラム・値に展開
Slide 25
Slide 25 text
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…)
}
Slide 26
Slide 26 text
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
Slide 27
Slide 27 text
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
Slide 28
Slide 28 text
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
Slide 29
Slide 29 text
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;
読めるようになりましたか？
Slide 30
Slide 30 text
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
Slide 31
Slide 31 text
（ページネーションにおける）
インデックスの使い方
Slide 32
Slide 32 text
連続値のインデックス
Slide 33
Slide 33 text
キーの順番が変わった場合
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のみ
インデックスはキーの順にのみ適用
↓
インデックスが使用不可
↓
フルスキャン
Slide 34
Slide 34 text
インデックス用の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を作成
キーの先頭に
Slide 35
Slide 35 text
インデックスのキーと
STORING句
Slide 36
Slide 36 text
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句：キー以外でインデックステーブルに保存したいカラムに適用
Slide 37
Slide 37 text
最適なインデックスはどれ？①
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
に対して
という条件をかけるとき、
・それぞれのインデックスを使用した場合の挙動はどうなる？
・最適なインデックスはどれ？
Slide 38
Slide 38 text
最適なインデックスはどれ？①
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
Slide 39
Slide 39 text
最適なインデックスはどれ？②
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
に対して
という条件をかけるとき、
・それぞれのインデックスを使用した場合の挙動はどうなる？
・最適なインデックスはどれ？
Slide 40
Slide 40 text
最適なインデックスはどれ？②
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
に対して
という条件をかけるとき、
・それぞれのインデックスを使用した場合の挙動はどうなる？
・最適なインデックスはどれ？
Slide 41
Slide 41 text
最適なインデックスは何？
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
に対して
という条件で
・オフセット法によるページネーションを行う場合、
・シーク法によるページネーションを行う場合、
最適なインデックスは何？
Slide 42
Slide 42 text
最適なインデックスは何？
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句のカラムと同じである
Slide 43
Slide 43 text
インデックスの決め方
キー
• ORDER句に含むもの
• 順序
• WHERE句に含むものを前に
• 分散性の高いものを前に
STORING句
• ORDER句に含まないが、WHERE句に含むもの
• (case by case) SELECT句に含むもの
Slide 44
Slide 44 text
まとめ
• キーの先頭は分散値
連続値から生成するとき、ページネーションの場合はShardIdが好ましい
• ページネーションにおいて各ShardIdに対するlimit, offsetは
• オフセット法
• shardLimit = limit + offset
• shardOffset = 0
• シーク法
• shardLimit = limit
• shardOffset = 0
• インデックスのキーはORDER句のカラム
STORINGはORDER句にないWHERE句のカラム
Slide 45
Slide 45 text
参考資料
● 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