Import CSV to Database: MySQL PostgreSQL Guide

すべてを変えた午前3時のデータベースインポート

午前3時に開発者の声にパニックが混ざったのを今でも覚えています。「クライアントのCSVインポートが6時間も実行されていて、まだ40%しか完了していません。彼らのビジネスは3時間後に始まり、この顧客データが必要です。」それは私のデータベースアーキテクトとしてのキャリアの初期、7年前のことでした。今日は、50,000行から5000万行までのデータを処理する企業のMySQLおよびPostgreSQLデータベースで、数百のCSVインポート操作を最適化した経験から、ほとんどの開発者が間違った方法を使っており、それが処理時間を数時間、サーバーコストを数千ドルも無駄にしていることをお伝えできます。

真実は、CSVファイルをデータベースにインポートすることは、一見単純に見えるタスクの一つであり、朝までに本番環境に投入する必要のある2GBのファイルを見つめるまでそれがどれほど複雑か理解していないことです。私は、適切なアプローチを用いれば12分で行えることを8時間かけてインポートするためにカスタムPythonスクリプトを書かざるを得ないチームを見てきました。私は、誤って構成されたインポートのメモリ負荷のためにサーバーがクラッシュするのを見てきました。そして、INSERT文からバルクロードメソッドに切り替えるだけで、企業が毎月のデータ処理コストを73%削減するのを手伝いました。

このガイドでは、2億行以上のCSVデータをMySQLとPostgreSQLデータベースにインポートする際に学んだすべてを共有します。生産環境で実際に機能する方法、知っておくべきパフォーマンスベンチマーク、そして午前3時のパニックコールから救ってくれるトラブルを避けるための注意点をカバーします。5MBの顧客リストをインポートする場合でも、50GBのトランザクションログをインポートする場合でも、どのアプローチを使用するべきか、そしてその理由を正確に理解して帰ることができるでしょう。

インポート方法の実際のパフォーマンスギャップを理解する

具体的な方法に入る前に、なぜインポート方法がそれほど重要なのかを理解する必要があります。昨年、私はクライアントのために包括的なベンチマークテストを実施しました。彼らは毎日約230万行の販売データを847MBのCSVファイルでインポートしていました。同じハードウェアで4つの異なるインポート方法をテストしました：4つのvCPUと16GBのRAMを搭載した標準のAWS RDS db.m5.xlargeインスタンスです。

「INSERT文とバルクローディングの違いは、速度だけではありません—それは6時間のインポートウィンドウと12分のそれとの違いです。本番環境では、そのギャップが成功と失敗の違いです。」

結果は驚くべきものでした。Pythonスクリプトを介して個々のINSERT文を使用した場合、4時間23分を要しました。同じインポートで、バッチ処理（1000行ごと）の準備されたステートメントを使用した場合は47分で完了しました。MySQLのLOAD DATA INFILEは8分12秒で終了しました。しかし、私が最も驚いたのは、PostgreSQLのCOPYコマンドを適切に構成して使用した場合、全体のインポートがわずか3分41秒で完了したことです。これは素朴なアプローチに対して71倍のパフォーマンス向上です。

その違いは速度だけではありません—リソースの利用状況にも関係しています。INSERT文のアプローチでは、CPU使用率が89%まで上昇し、それが全期間持続しました。各行がデータベースへの往復を必要としたため、ネットワークI/Oは常に最大でした。それに対してLOAD DATA INFILEとCOPYメソッドは、CPU使用率を約34%に保ち、最初の90秒でネットワーク転送を完了し、残りの時間をディスクI/Oとインデックス構築に費やしました。

ほとんどの開発者が理解していないことは、個々のINSERT文を使用すると、データを送信するだけでなく、各行に対してSQLステートメント全体の構造を送信していることです。10列のテーブルの場合、実際のデータ150バイトごとに200バイトのSQLオーバーヘッドを送信する可能性があります。これは133%のオーバーヘッド比率です。バルクローディングメソッドはこのオーバーヘッドを完全に排除し、最小限のプロトコルラップで生のデータを送信します。

メモリフットプリントは別の物語を語ります。Pythonスクリプトアプローチでは、処理の前に全CSVをメモリに保持し、アプリケーションサーバーで1.2GBのRAMを消費しました。バルクローディングメソッドはデータをデータベースに直接ストリーミングし、アプリケーションメモリは50MB未満で済みました。この違いは、同時に複数のインポートを実行したり、大きなファイルを扱ったりする場合に重要になります。

MySQL LOAD DATA INFILE：MySQLインポートのためのファストトラック

MySQLのLOAD DATA INFILEは、MySQLデータベースで作業する際に最初に手に取るツールです。これはデータベースエンジンに直接組み込まれており、最大スループットのためにCコードレベルで最適化されています。私の経験では、アプリケーションレベルのインポートスクリプトよりも一貫して15〜25倍のパフォーマンスを提供し、その特異性を理解すれば驚くほど簡単に使えます。

基本的な構文は次のようになります：LOAD DATA INFILE '/path/to/file.csv' INTO TABLE your_table FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS; しかし、詳細に注意することで、10分のインポートと3時間の悪夢の違いが生まれます。

最初に、LOCALキーワードを理解してください。LOAD DATA LOCAL INFILEを使用すると、MySQLはクライアントマシンからファイルを読み取り、ネットワーク経由で転送します。LOCALを使用しない場合、MySQLはファイルがデータベースサーバー自体にあることを期待します。これによって、開発者が何度もつまずくのを見てきました。彼らは「ファイルが見つかりません」というエラーを受け取ります。なぜなら、彼らのラップトップからリモートRDSインスタンスにファイルをロードしようとしてLOCALを使っていないからです。パフォーマンスの違いも重要です：LOCALはネットワーク転送時間を追加しますが、それでもINSERT文よりも圧倒的に速いです。私のテストでは、LOCALはサーバー側のローディングと比較してインポート時間に約40%を追加しましたが、それでも代替案よりも10倍速いです。

文字エンコーディングは別の地雷です。デフォルトでは、MySQLはCSVがサーバーの文字セットにあると仮定しますが、これがファイルと一致しない場合があります。私はいつも文字セットを明示的に指定します：CHARACTER SET utf8mb4。これにより、特定の文字が疑問符として表示されたり、インポートが途中で失敗したりする理由を何時間もデバッグするのを避けることができました。4バイト対応のUTF-8（utf8mb4）は、現代のデータセットでますます一般的な絵文字や特殊文字を扱います。

これは、複雑な説明を含む商品カタログデータをインポートしたプロジェクトからの実際の例です：LOAD DATA LOCAL INFILE 'products.csv' INTO TABLE products CHARACTER SET utf8mb4 FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' IGNORE 1 ROWS (product_id, name, description, price, stock_quantity) SET created_at = NOW(), updated_at = NOW(); CSVの列をテーブルの列にマッピングし、計算値を持つ追加フィールドを設定できる点に注意してください。この柔軟性により、CSVをテーブル構造に正確に合わせるために前処理を行う必要がなくなります。

重要な最適化の一つ：大規模なインポートの前にインデックスを無効にし、その後再構築します。3つのインデックスを持つテーブルでは、インデックスを削除し、インポートし、その後再作成することで3.2倍の速度向上を測定しました。コマンドの順序は次のとおりです：ALTER TABLE your_table DISABLE KEYS; 次に、LOAD DATA文、その後ALTER TABLE your_table ENABLE KEYS; MySQLはすべてのインデックスを一度のパスで再構築します。これは、挿入された各行に対してそれらを更新するよりもはるかに効率的です。

PostgreSQL COPY：パフォーマンスチャンピオン

MySQLのLOAD DATA INFILEが速いなら、PostgreSQLのCOPYコマンドはロケットシップです。私が実施したすべてのベンチマークでは、COPYは同等のMySQLインポートを20〜40%上回り、複雑なデータシナリオを扱う際により柔軟性を提供します。両方のシステムで広範に作業した後、私は重いデータインポート作業にPostgreSQLを好むようになり、COPYがその大きな理由の一つです。

「ほとんどの開発者はCSVのインポートを一度限りのタスクとして扱い、便利さのために最適化します。しかし、大量のデータを処理する際には、複雑さや制約が課されます。」