はじめに
弊社では ecforce というシステムをSaaSという形でサービスを提供しています。
ecforceの導入を希望されるクライアント様には、大きく2パターンがあります。
-
新規でECを立ち上げたいというクライアント様 - 別のカートシステムを現在使っており、ecforceに乗り換えたいというクライアント様
後者につきまして、当然ながら顧客や注文データをリセットするわけにもいかないので、 既存データも引き継ぎたい という要望をいただくことが大多数です。
ただ他社のカートシステムとecforceではデータ構造が異なるので、データを抽出してそのままecforceのデータベースにインポートできるわけではありません。
この後者の要望を叶えるためにはデータを
- 抽出し
- 加工した上で
- ecforceのデータベースにインポートする
という手順を踏む必要があります。
これを社内では 移行業務 と呼んでおり、弊社にはこの移行業務を行う専門チームがあります。
移行業務は当初、注文件数が数10万件程度でしたので、特に何も仕組み化せずプレーンなRailsスクリプトのみで行っていました。
並列処理やSQLのチューニングもしていない状態です。
ただ注文件数が600万件レベルの当時最大規模の案件を対応するにあたり、SQLや仕組みの根本改善が必要となりました。
今回は SQLの高速化 という観点で話を進めていきます。
本題
行ったSQLの高速化について、具体的には以下の通りです。
- 複数のデータをまとめて1行のINSERT/UPDATEでDBへの取り込みを行う
- 最初にSELECTを行いメモリ上にキャッシュ、細かなSELECTを行わないようにする
- 外部キーチェックを無効化する
複数のデータをまとめてINSERT
1データに対して1つのINSERTで実行するよりも、1000データに対して1つのINSERTのようにまとめて実行するようにしました。
これによって通信の回数も減らせる上、DBの方がスペックが高ければより高速化できるという戦略です。
複数のデータをまとめてINSERTするにあたり、activerecord-importというGemを使用しています。
以下のように column_names にはDBのカラム名、 order_values にはカラムに対する値の配列を複数持った配列が入っており、これを実行すると1行のINSERT文で一気にデータが作成されます。
また validate: false オプションによってRails側のバリデーションをスキップでき、通常のINSERTより30倍ほど高速に取り込みを行えるようになりました。
EcForce::Order.import column_names, order_values, validate: false
以下のような書き方もできるものの、モデルオブジェクトを作成するより、配列化したほうが 30% ほど短縮されました。
EcForce::Order.import order_objects, validate: false
複数のデータをまとめてUPDATE
こちらもINSERTと同様、activerecord-importというGemを使用しています。
UPDATEといいつつ、厳密には UPSERT を行っています。
これは対象データが存在していればUPDATE、なければINSERTを行うデータ操作を指します。
EcForce::Order.import(
[:id, :times, :created_at, :updated_at],
order_values,
validate: false,
timestamps: false,
on_duplicate_key_update: [:times]
)
キャッシュの活用
移行業務において、INSERTやUPDATEだけでなくSELECTの効率化も重要です。
SELECTでN+1が発生すると、移行対象件数が多ければ多いほど影響を受けやすくなります。
SQLの発行量を抑えて高速化しつつ、効率良くメモリを活用するために、キャッシュを使用しています。
キャッシュ対象のサイズが小さい場合
キャッシュ対象のサイズが小さい場合は、並列化処理を実施する前に定数としてメモリ上に保持して、各並列処理内で利用しています。
商品や都道府県（名前からIDに変換）や配送業者などに利用されています。
例えばすべての商品をキャッシュして、商品コードから商品IDが引けるようなハッシュを作成するためのキャッシュは以下のとおりです。
PRODUCT_NUMBER = EcForce::Product.unscoped.with_deleted.pluck(:number, :id).to_h.freeze
キャッシュ対象のサイズが大きい場合
キャッシュ対象が大きい場合は、各並列処理内で対象すべてを1つのSELECTで一度メモリ上に読み込んでから処理を行っています。
例えばchunk内で商品をキャッシュして、商品コードから商品のモデルが引けるようなハッシュを作成するためのキャッシュは以下のとおりです。
variant_hash = {}
EcForce::Product.unscoped.with_deleted.where(
number: chunk.map{ |raw_row| @csv.col(raw_row, '商品コード') }
).each{ |v| variant_hash[v.sku] = v }
外部キーチェックの無効化
MySQLはデフォルトで、外部キー制約を保つために foreign_key_checks という変数が 1 になっています。
したがって通常では、外部キー制約があるカラムを持ったテーブルへのINSERT/UPDATEでは、外部キーチェックを1件ずつ行います。
ただ移行業務のように数百万件のデータのINSERTを行う上で、1件ずつ外部キーをチェックしていては時間がかかってしまいますので、以下のように一時的に外部キーチェックを無効化しています。
SET FOREIGN_KEY_CHECKS = 0;
まとめ
今回は移行業務を効率化するための SQLの高速化 についてでした。
次回は並列処理について触れていきます。
SUPER STUDIOの採用について
SUPER STUDIOでは、エンジニアを採用しています。
少しでも興味がありましたら、以下をご覧ください。
昨年12月に9期目を迎えたSUPER STUDIOのキックオフイベントで社内表彰されたエンジニア受賞インタビュー記事です。よりSUPER STUDIOのエンジニア組織を理解できる内容となっておりますので、ご一読ください。
Discussion