💡

Cloud Spannerで条件を絞った上でソートするクエリのパフォーマンスチューニング Part1

2021/12/10に公開

Google Cloud

Cloud Spanner

tech

はじめに

この記事はUnipos Advent Calender 2021、10日目の記事です。

今回はCloud Spannerでのクエリパフォーマンスチューニングについて書きます。
Cloud Spannerの基本的な知識についてはSpanner 沼への誘いを見ると良いかなと思います。

実験について

想定しているユースケース

ユーザ一覧ページのように、ユーザを特定の条件(例えばユーザタイプとか)でフィルタリングしながら作成日降順で取得するようなユースケースを想定しています。

スキーマ

CREATE TABLE User10m (
    ID STRING(256) NOT NULL,
    EmailAddress STRING(256) NOT NULL,
    Name STRING(256) NOT NULL,
    UserType INT64,
    CreatedAt TIMESTAMP NOT NULL,
) PRIMARY KEY(ID);

CREATE INDEX User10mByUserTypeCreatedAt ON User10m(UserType, CreatedAt DESC);

CREATE INDEX User10mByCreatedAtUserType ON User10m(CreatedAt DESC, UserType);

データ

レコード数: 1000万件
内容
- ID: UUID
- EmailAddress: ${UUID}@example.com
- Name: ランダム
- UserType: [NULL, 0~9]の中から同じ比率になるようにランダム抽出
- CreatedAt: [2017-10-31 ~ 2021-12-08]の中から同じ比率になるようにランダム抽出
- Goでこんな感じに書いて生成しました

実験パターン

UserTypeを1つ指定してCreatedAtで降順にソートし20件取得する
UserTypeを2つ指定してCreatedAtで降順にソートし20件取得する
UserTypeを5つ指定してCreatedAtで降順にソートし20件取得する
存在しないUserTypeを1つ指定してCreatedAtで降順にソートし20件取得する
存在しないUserTypeを1つ含んで、UserTypeを2つ指定してCreatedAtで降順にソートし20件取得する

使用するクエリ

INDEXを使わないクエリ
例: 「UserTypeを1つ指定してCreatedAtで降順にソートし20件取得する」の場合
```
SELECT * FROM User10m WHERE UserType = 0 ORDER BY CreatedAt DESC LIMIT 20
```
UserByUserTypeCreatedAtを使うクエリ
例: 「UserTypeを2つ指定してCreatedAtで降順にソートし20件取得する」の場合
```
SELECT * FROM User10m@{FORCE_INDEX=User10mByUserTypeCreatedAt} WHERE UserType IN (0,9) ORDER BY CreatedAt DESC LIMIT 20
```
UserByCreatedAtUserTypeを使うクエリ
例: 「存在しないUserTypeを1つ含んで、UserTypeを2つ指定してCreatedAtで降順にソートし20件取得する」の場合
```
SELECT * FROM User10m@{FORCE_INDEX=User10mByCreatedAtUserType} WHERE UserType IN (0,10) ORDER BY CreatedAt DESC LIMIT 20
```

評価方法

Cloud SpannerインスタンスのProcessing unitsは1000の状態で、それぞれのクエリをCloud Spannerコンソール上から実行し、3回のCPU時間の平均を取りました。

実験結果

上記のような環境で実験した結果を実験パターンごとに表にしてまとめました。INDEX未使用はパフォーマンスが悪いことが明白だったので、2つのパターンでの結果しか載せていません。

UserTypeを1つ指定してCreatedAtで降順にソートし20件取得する

クエリ種別	3回の平均CPU時間[ms]	使用したクエリ
INDEX未使用	5433.33	`SELECT * FROM User10m WHERE UserType = 0 ORDER BY CreatedAt DESC LIMIT 20`
UserByUserTypeCreatedAt	10.13	`SELECT * FROM User10m@{FORCE_INDEX=User10mByUserTypeCreatedAt} WHERE UserType = 0 ORDER BY CreatedAt DESC LIMIT 20`
UserByCreatedAtUserType	9.1	`SELECT * FROM User10m@{FORCE_INDEX=User10mByCreatedAtUserType} WHERE UserType = 0 ORDER BY CreatedAt DESC LIMIT 20`

UserTypeを2つ指定してCreatedAtで降順にソートし20件取得する

クエリ種別	3回の平均CPU時間[ms]	使用したクエリ
UserByUserTypeCreatedAt	1236.67	`SELECT * FROM User10m@{FORCE_INDEX=User10mByUserTypeCreatedAt} WHERE UserType IN (0,9) ORDER BY CreatedAt DESC LIMIT 20`
UserByCreatedAtUserType	8.89	`SELECT * FROM User10m@{FORCE_INDEX=User10mByCreatedAtUserType} WHERE UserType IN (0,9) ORDER BY CreatedAt DESC LIMIT 20`

UserTypeを5つ指定してCreatedAtで降順にソートし20件取得する

クエリ種別	3回の平均CPU時間[ms]	使用したクエリ
UserByUserTypeCreatedAt	3063.33	`SELECT * FROM User10m@{FORCE_INDEX=User10mByUserTypeCreatedAt} WHERE UserType IN (0,2,4,6,8) ORDER BY CreatedAt DESC LIMIT 20`
UserByCreatedAtUserType	9.78	`SELECT * FROM User10m@{FORCE_INDEX=Use10mrByCreatedAtUserType} WHERE UserType IN (0,2,4,6,8) ORDER BY CreatedAt DESC LIMIT 20`

存在しないUserTypeを1つ指定してCreatedAtで降順にソートし20件取得する

クエリ種別	3回の平均CPU時間[ms]	使用したクエリ
INDEX未使用	4470	`SELECT * FROM User10m WHERE UserType = 10 ORDER BY CreatedAt DESC LIMIT 20`
UserByUserTypeCreatedAt	5.62	`SELECT * FROM User10m@{FORCE_INDEX=User10mByUserTypeCreatedAt} WHERE UserType = 10 ORDER BY CreatedAt DESC LIMIT 20`
UserByCreatedAtUserType	2943.33	`SELECT * FROM User10m@{FORCE_INDEX=User10mByCreatedAtUserType} WHERE UserType = 10 ORDER BY CreatedAt DESC LIMIT 20`

存在しないUserTypeを1つ含んで、UserTypeを2つ指定してCreatedAtで降順にソートし20件取得する

クエリ種別	3回の平均CPU時間[ms]	使用したクエリ
UserByUserTypeCreatedAt	629.62	`SELECT * FROM User10m@{FORCE_INDEX=User10mByUserTypeCreatedAt} WHERE UserType IN (0,10) ORDER BY CreatedAt DESC LIMIT 20`
UserByCreatedAtUserType	13.23	`SELECT * FROM User10m@{FORCE_INDEX=Use10mrByCreatedAtUserType} WHERE UserType IN (0,10) ORDER BY CreatedAt DESC LIMIT 20`

結果と考察

考察1: ORDER BYで指定するカラムが第一ソートであるINDEXから取得する方がパフォーマンスが良い

結果: UserTypeの指定数に関わらずUser10mByUserTypeCreatedAtとUser10mByCreatedAtUserTypeを比較すると、User10mByCreatedAtUserTypeの方がパフォーマンスが良い。

第一ソート条件がCreatedAtのINDEXから取得する場合、既にソートされているのでスキャン数が減る
- User10mByCreatedAtUserTypeの実行計画の一部
第一ソート条件がUserTypeのINDEXから取得する場合、WHEREの条件に当てはまるレコードをすべて取得してからソートしてLIMITしている
- User10mByUserTypeCreatedAtの実行計画の一部

考察2: WHEREで指定する値が存在しない(LIMITまで取得できない)場合はWHEREで指定するカラムが第一ソートであるINDEXから取得する方がパフォーマンスが良い

結果: 存在しないUserTypeのみを指定した場合、User10mByUserTypeCreatedAtとUser10mByCreatedAtUserTypeを比較すると、User10mByCreatedAtUserTypeの方がパフォーマンスが悪い。

第一ソート条件がUserTypeでない場合、指定されたUserTypeが存在するか判断できないのでパフォーマンスが悪くなる
- User10mByUserTypeCreatedAtの実行計画の一部
- User10mByCreatedAtUserTypeの実行計画の一部

今回の実験では、UserTypeは全部の値にほぼ同じぐらいの数が入っていたが、極端に偏ったデータ (UserTypeの99%が0、0.9%が1、0.1%が2のようなデータ)の場合でも同じようにWHEREで指定するカラムが第一ソートであるINDEXから取得する方がパフォーマンスが良さそう。

おわりに

今回の実験環境の場合は、ORDER BYで指定するカラムを第一ソートとした方がパフォーマンスが良いということがわかりました。しかし、考察2にあるようにWHEREで指定する値が存在しない場合やデータの散らばりによっても最適なクエリは違うので、実際のアプリケーションに応じて、実行計画を見ながらクエリのパフォーマンスチューニングをすることが大事かなって思いました。

この記事で紹介したクエリの改良版をPart2に書いてます。

参考

Spanner 沼への誘い