MySQL 億単位のデータをExcelドキュメントにエクスポートする

私たちの会社の SaaS システムでは、ユーザーにリストデータを Excel ドキュメントとしてエクスポートする機能を提供する必要があります。しかし、企業によっては財務明細などのリストデータがすでに数千万件、億単位に達しており、このようなデータのエクスポートには専用のソリューションが必要になります。

そうしないと、月末に数百社が同時に Excel をエクスポートした場合、MySQL の QPS が急上昇し、サーバーの CPU と RAM が一瞬でアラート閾値に達してしまいます。大量エクスポートによってサーバーが利用不能になり、最悪の場合ダウンする恐れもあります。(注意:財務などの機密データについては、ファイルの暗号化や一時ファイルの認可付きダウンロードといった対策が必要です)

具体的なソリューション

ソリューションの出発点は、「大量エクスポートが通常業務の稼働に影響を与えてはならない」ということです。

データベースのリードレプリカノードを別途デプロイしてクエリ負荷を分担し、さらに複数のサーバーグループを別途用意して Spring Boot プロジェクトを MQ のコンシューマークラスターとして稼働させます。すべてのエクスポートリクエストは MQ キューに投入され、Spring Boot はプル方式でキューから Excel エクスポートタスクのメッセージを能動的に取得して実行します。MQ のピークカット・平準化と非同期処理の能力を有効活用し、Spring Boot プロジェクト内ではスレッドプールのリソースを厳密に割り当てます。

実際のタスクコンシューマーは、億単位ページングの手法で MySQL のリストデータを分割クエリし、Alibaba の EasyExcel を使ってデータをハードディスクに書き出します。出力が完了したら OSS にアップロードし(OSS の内部ネットワーク経由でストリーム転送し、直接 OSS ファイルシステムに送ることも可能です)、その後ローカルディスクのファイルを削除します。

このような設計により、エクスポート機能はメイン業務から完全に切り離され、独立した機能モジュールとなり、デプロイも専用のサーバーノードで行われます。仮にエクスポートプロジェクトが突発的な大量エクスポートリクエストでダウンしたとしても(MQ がこの問題をうまく回避してくれるため、通常は起こりませんが)、メイン業務の正常な機能には影響しません。

リソース制限とページングクエリ

スレッドプールによるリソース制限に加えて、1 顧客あたりの同時エクスポートファイル数の上限を設け、JVM が一度にプルするタスク数を厳密にコントロールすることで、サービスの堅牢性を確保します。

データベースのクエリには億単位ページングの手法を採用しています。まず条件に基づいて対象データの id を取得し、limit の大きなオフセットを使う方法をやめて id カーソル方式に切り替えることで、毎回の SQL クエリの実行効率をミリ秒レベルに保ちます。同時にクエリするカラムを適切に選択し(インデックスは必須です)、MySQL から毎回流れ出るデータ量が一定のサイズ範囲(100KB〜500KB)に収まるようにします。

-- 大きなオフセットの limit の代わりに id カーソルを使い、毎回前バッチの最大 id を起点にする
select id from テーブル where 条件 and id > 前バッチの最大id order by id limit 5000;

さらに抽象ファクトリーとインターフェースをラップして、開発者が各種機能リストのエクスポートを実装できるように提供します。開発者は、具体的にどうページング・バッチ分割でデータを取得するか、どう Excel を生成するか、どうアップロードするかを気にする必要はなく、エクスポート用の SQL クエリを書くだけで、データの取得に専念できます。

実際の効果

その後のテストでは、7,550 万件のデータのエクスポートに約 20 数分かかりました(適切にインデックスを追加し、マルチスレッドでタスクを分解してクエリ・エクスポートすれば、実際にはもっと速くできます)。現在の業務シナリオの要件は十分に満たせています。

1 回のクエリの SQL 通信 + 実行時間を 100ms と見積もると、おおよその時間の計算式は次のとおりです:(75500000 / 5000 × 100ms) / 1000 / 60 ≈ 25min

1 顧客のエクスポートによるメモリ増加は 20MB 程度の振れ幅しかありません。dataList は一度に 5,000 件しか取得せず、データがディスクに出力された後は list オブジェクトのメモリがすでに回収されているため、メモリの増加はごくわずかで、ディスク I/O は毎回 3〜4MB です。複数顧客の場合は、スレッドリソースの制限と総エクスポートタスク数の制限を行い、JVM の OOM を防ぐ必要があります。

Excel の 1 つのワークシートには約 100 万行までしか書き込めないため、それを超える場合はワークブックを分割して処理する必要があります。ワークブックの分割はマシンのメモリを考慮して行います。合計 1 億件のレコードをエクスポートする場合、単一ファイルのサイズが大きくなりすぎるため、その後 Excel ファイルを分割保存して圧縮する必要があります。単一ファイルが大きすぎると、開く際のメモリ使用量が高くなりすぎて、顧客がダウンロード後にファイルを開けなくなってしまうからです。

データベースが毎回吐き出すデータもパフォーマンスを消費しすぎないようにし、7,550 万件のデータのエクスポートは複数バッチに分けて完了させる必要があります。ディスクに出力された Excel ファイルが完成したら zip 圧縮してそのまま OSS にアップロードし、最後に OSS のリソースパスをフロントエンドのアプリや Web ブラウザに提供して、クライアントにダウンロードさせます。

まとめ

たかがデータエクスポート機能とはいえ、パフォーマンスを極限まで追求しようとすると、注目すべきポイントは非常に多くあります。MQ による非同期ピークカット、リードレプリカでのクエリ負荷分散から、カーソルページング、メモリ制御とファイル分割、さらにデプロイレベルでの分離、そして将来の業務成長に向けた水平スケールアウトの考慮まで、あらゆる面をきちんと設計しておく必要があるのです。