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;
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;
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;
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;
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オブジェクトもカラム・値に展開
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
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; 読めるようになりましたか?
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
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のみ インデックスはキーの順にのみ適用 ↓ インデックスが使用不可 ↓ フルスキャン
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を作成 キーの先頭に
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 に対して という条件をかけるとき、 ・それぞれのインデックスを使用した場合の挙動はどうなる? ・最適なインデックスはどれ?
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
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 に対して という条件をかけるとき、 ・それぞれのインデックスを使用した場合の挙動はどうなる? ・最適なインデックスはどれ?
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 に対して という条件をかけるとき、 ・それぞれのインデックスを使用した場合の挙動はどうなる? ・最適なインデックスはどれ?
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 に対して という条件で ・オフセット法によるページネーションを行う場合、 ・シーク法によるページネーションを行う場合、 最適なインデックスは何?
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句のカラムと同じである