SQLで大量レコード更新時のパフォーマンス改善について
こんにちは。Aerial Partnersの野上です。
弊社ではGtaxという、仮想通貨取引の際に生じた所得を計算するWebサービスを、仮想通貨投資家の方々に提供させていただいています。
Gtaxの主な使い方は、仮想通貨取引所での取引履歴を取引所からCSVでダウンロードし、それをGtaxに取り込んでから計算するという流れになっています。
その中で、ユーザーによってはボット取引などを活用して大量の取引履歴を計算しないといけない方も多くいらっしゃいます。（多い方だと50万回ほどの取引をしていらっしゃる方もいます （！））
所得計算の際には、全件の取引履歴を参照かつ更新することが求められ、愚直なままで対応するとかなりの待ち時間が発生してしまいます。そこで、計算の高速化に当たって工夫した点を以下にご紹介します。
1. バルクアップデート
まずは実際更新を行う箇所である、UPDATE文の工夫について見ていきましょう。バルクインサートはよく使われると思いますが、バルクアップデートも更新時の高速化として非常に効果的です。
GtaxではMySqlを利用していますが、バルクアップデートの書き方は2パターンあります。
一つはCASE WHEN構文を使ったやり方です。可読性が高く取り扱いやすいため、私はこちらを選択しました。
もう一つはELT()とFIELD()を使う方法です。若干テクニカルな方法ですが、CASE WHENよりも文字数を抑えられるのが利点です。
ELT(), FIELD()の使い方はこちらの記事が参考になりました。
2. SELECT LIMIT OFFSETの改善
次に、更新するレコードを取得するSELECT文の改善についてです。
更新するレコードを処理するため、SELECTでアプリケーションロジックにもってこなければならないのですが、一度に全てもってくるとアプリのメモリ容量をオーバーするので、分割してレコードを取得することになるます。
GtaxはLaravelというPHPのフレームワークを使っており、最初はchunk()というフレームワークのメソッドで処理していました。以下のような使い方です。
$query->chunk(10000, function ($record) {
// 1万件ずつ取得し、ここで$recordに対してupdateの処理を書く
}Sale（売上）というテーブルを例に簡単な例を挙げると
$sales = Sale::orderBy('sale_date');
$sale->chunk(10000, function ($trade) {
->save();
});これが、SQLではこのように展開されます。
SELECT * FROM `sales` order by `sale_date` limit 10000 offset 0
SELECT * FROM `sales` order by `sale_date` limit 10000 offset 10000
SELECT * FROM `sales` order by `sale_date` limit 10000 offset 20000...以下offsetが10000ずつ増える
一見良さそうなクエリに見えますが、offsetが増えるごとにクエリ速度がどんどん遅くなっていきます。これは、offsetを指定しても、結局常にoffset0から行を走査していってしまうからです。
ちゃんとoffsetを指定したところから走査を開始するためには、whereで開始を指定してあげることが必要です。10000件selectが終わったタイミングのsale_dateを保存しておき、以下のようにクエリを走らせるようにすれば良いはずです。
SELECT * FROM `sales` order by `sale_date` limit 10000;
SELECT * FROM `sales` order by `sale_date` where sale_date < '2018-01-01 00:00:00' limit 10000;
SELECT * FROM `sales` order by `sale_date` where sale_date < '2017-01-01 00:00:00' limit 10000;# '2018-01-01 00:00:00 '2017-01-01 00:00:00'は例なので、実際は取得できた値をいれる
これでおおよそはOKなのですが、sale_dateが重複するレコードが存在する場合はそこで順序がとれなくなるため、以下のようにやや複雑なクエリで対応する必要があります
SELECT *
FROM ( SELECT *
FROM sales
WHERE sale_date <= ?
AND NOT (sale_date = ? AND sale_id >= ?)
ORDER BY sale_date DESC, sale_id DESC
)chunk()ではこのような対応をすることはできないので、chunkに似たメソッドを自作することで対応しました。
詳しく解説すると長くなってしまうのでこれぐらいで留めておきますが、もっと知りたい方はこちらのサイトが参考になります。
https://use-the-index-luke.com/ja/sql/partial-results/fetch-next-page
（余談ですが、上のサイトにある行値式を使ったクエリは、MySql5.7で試したところ、結果はちゃんとでるのですが、インデックスが効かないため使用を断念しました。）
3. ORMを利用しない
LaravelのEloquentのモデルは非常に便利ですが、パフォーマンスとのトレードオフとなっているため、今回の速度が求められる計算の場面では利用せず自作のクラスを使いました。
今回対象となる取引履歴のTradeクラスを例にとると、早くしたい場合用にLightTradeクラスを作成し、Tradeクラスと似たように使えるよう、テーブルのカラムをプロパティに設定し、共通で使えるメソッドはTradableというトレイトにまとめました。
Laravelのお作法に反しているので基本的にはあまりやるべきでないと思いますが、どうしても速度が必要になる場合は上記のように工夫して使うのはアリだと思います。
最後に
ブロックチェーン業界では、イーサリアムをはじめとしたブロックチェーン自体のコードを書くことだけではなく、こうした周辺サービスの開発が重要です。
むしろ、こうした周辺領域の開発を通して、どれだけユーザにとって使いやすい技術にできるかという点が、ブロックチェーン技術を社会実装する上で鍵になることは間違いありません。
Aerialのメンバーと一緒に話してみたい、ブロックチェーン技術の社会実装に興味がある、という方はぜひ一度私たちとお話しにオフィスまで遊びに来てください！
