2026.10.30

【エンジニアブログ】PHP×MySQLの大量データ移行を高速化する方法

エンジニアブログ

はじめに

こんにちは、株式会社エヌ・エイ・シー・ケア　システムインテグレーション事業部です。
今回は「PHP×MySQLの大量データ移行を高速化する方法」をご紹介します。

数億件規模のデータ移行を、PHPとMySQLで行う機会がありました。バルクインサートにして、SQLの実行回数や通信のオーバーヘッドを減らしたにもかかわらず、思ったほど速くなりませんでした。本番データは数億件ですが、まず数千万件の開発環境で試したところ、処理が終わる気配がなく、途中で止めました。それまでの計測結果から見積もると、13時間以上かかる計算でした。

調べてみると、遅かったのは書き込み（INSERT）ではなく、読み込み（SELECT）でした。本記事では、その原因と対策をまとめます。

1. 普通のやり方：LIMITでページング＋バルクインサート

PDOでMySQLに接続する場合、デフォルトでは「バッファクエリ」が有効です。SELECT * FROM src_table を実行すると、結果セット全体がPHPのメモリ上に読み込まれるため、件数が多いとメモリ超過になります。

そこで次のように、LIMITで分割取得するのが一般的です。

SELECT * FROM src_table LIMIT $i * 10000, 10000

取得した1万件ごとにバルクインサートする、という流れです。

2. 問題：後ろに行くほど遅くなる

実際に動かすと、処理が進むほど1回のSELECTが遅くなりました。次のようなSELECTに約50秒かかっていました。

SELECT * FROM src_table LIMIT 28290000, 10000

原因は LIMIT offset, count の仕組みにあります。このSQLは、結果の先頭から約2,829万行を読み進めてスキップし、そのあとの10,000件を返します。MySQLは必要な10,000件だけを直接取り出せるわけではなく、一般的な実行計画では、スキップする行も読み進める必要があります。そのため、最初のうちは短時間で終わっていたSELECTも、offsetが大きくなるほど読み飛ばす行数が増え、後半ほど遅くなります。

残りが約1,000万件だとすると、1万件ずつ取得して約1,000回のSELECTが必要です。1回50秒としても、1,000回 × 50秒＝ 50,000秒（約13.9時間）となり、SELECTだけで14時間近くかかる計算です。後半ほどさらに遅くなるため、実際にはこれより長くなります。

3. 対策1：主キーを利用してデータを取得する

OFFSETによるページング処理を改善する方法として、主キーなどのインデックスを利用し、前回取得したデータの続きから取得する方法があります。

一般に「キーベースページネーション」や「シーク法（Keyset Pagination）」などと呼ばれる方式です。

SELECT *
FROM src_table
WHERE id > :last_id
ORDER BY id ASC
LIMIT 10000;

方式	SQL	動作
offset方式	`LIMIT 25000000, 10000`	2500万行を読み飛ばしてから取得
シーク法	`WHERE id > 前回の最後のID ORDER BY id LIMIT 10000`	インデックスで該当位置へ移動して取得

利点は2つあります。

id が主キーであれば、MySQLは主キーのインデックスを利用して取得開始位置を探し、その後のデータを取得できます。その結果、OFFSET方式のように、先頭から大量の行を読み飛ばす必要がないため、後半でも1回あたりの処理時間が増加しにくくなります。
最後に処理したIDを記録しておけば、途中で落ちても再開できます。

注意：ORDER BY は必須です。これがないと順序が保証されず、重複や取りこぼしが起こり得ます。

4. 対策2：非バッファクエリでストリーミングする

複雑なJOINやビューなどの事情により、適切なインデックスがない場合や、ビュー・結合を含む重いクエリの場合は、非バッファクエリが有効です。

4-1. 非バッファクエリとは？

非バッファクエリでは、LIMIT も WHERE も使わず、SELECT * FROM src_table を1回だけ実行します。MySQLサーバーはこのクエリを1回評価し、結果の行を順に送り出します。PHP側は、届いた行を1行ずつ読み出して処理します。

$readPdo->setAttribute(
    PDO::MYSQL_ATTR_USE_BUFFERED_QUERY,
    false
);

$stmt = $readPdo->query(
    'SELECT * FROM src_table'
);

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    // 1件ずつデータを取得して処理する
}

この方法では、「どこまで読み飛ばすか」を毎回やり直すコストがありません。重いクエリが走るのは最初の1回だけで、あとは読んだ分だけ流れてきます。そのため、LIMITのoffset方式のように後半ほど遅くなることがなく、最初から最後まで、ほぼ一定のペースでデータを取得できます。

ただし、非バッファモードはSELECT自体の実行を必ず高速化するものではありません。主なメリットは、結果全体をPHP側のメモリに保持せずに処理できることと、OFFSETによる繰り返しの読み飛ばしを避けられることです。

4-2. 10,000件ごとにバルクインサートする

非バッファクエリを使っても、1件ずつINSERTしていては、登録処理に時間がかかる可能性があります。そこで、SELECTは1件ずつ順次取得しながら、INSERTは10,000件ごとにまとめて実行します。

4-3. 注意点

SELECTの結果を最後まで取得する前に、同じPDO接続で別のSQLを実行することは原則としてできない：接続を2つ用意し、読み込み用と書き込み用に分けます。
対策1の方が、再開位置を管理しやすい：再開が必要な場合は、対策1のほうが向いています。

5. どちらを選ぶべきか

主キーなどの適切なインデックスがあり、効率よく取得できるテーブルであれば、対策1（シーク法）は有力な選択肢です。途中で止まっても、最後に処理したIDから再開しやすいのも利点です。

一方、今回の移行では対象テーブルが複数あり、中には1回のSELECTに約10分かかるものもありました。テーブルごとにインデックスや取得条件を検討するより、すべて非バッファクエリで統一したほうが都合が良かったため、今回は対策2を採用しました。全テーブルで同じ処理を使えるからです。

なお、複雑なSQLだから必ず非バッファクエリが有利というわけではありません。まず実行計画を確認し、SELECTそのものが遅いのか、OFFSETによる読み飛ばしが原因なのかを切り分けることが重要です。

結果

非バッファクエリに切り替えたあと、全テーブルの移行を最後まで動かして計測しました。

その結果、非バッファ方式では、1,000万件の処理に、約26分で完了できました。

一方、LIMITのoffset方式では、offset約2,829万の時点で1回のSELECTに約50秒かかっていました。同じ1,000万件を1万件ずつ処理すると約1,000回のSELECTが必要で、50秒として計算しても約13.9時間かかる計算でした（途中で止めたため、これは実測ではなく見積もりです）。しかも、offsetが大きいほどさらに遅くなるため、実際にはこれ以上かかると考えられます。

約26分の実測値にはINSERTなどの時間も含まれ、また、実測条件が異なるため厳密な性能比較ではありませんが、それでも、単純計算による見積もりとの比較では約30倍の高速化になりました。

まとめ

今回のデータ移行では、バルクインサートを導入しても期待したほど高速化できず、原因を調査した結果、INSERTではなくSELECTに時間がかかっていることが分かりました。
大量データを扱う場合、LIMITによるページング処理は一度に取得するデータ量を抑え、メモリ使用量を抑えるうえで有効ですが、OFFSETが大きくなると、データの取得に時間がかかる場合があります。

対策としては、次の2つが考えられます。

主キーによる取得（シーク法）： インデックスを利用して、前回取得した位置から次のデータを取得する。途中から再開しやすい点もメリットです。
非バッファクエリ： SELECT結果を順次取得し、メモリ使用量を抑えながら処理する。OFFSETによる繰り返しの読み飛ばしを避けられます。

どちらが適しているかは、テーブル構造やSQLの内容、移行要件によって異なります。まずはSELECTとINSERTそれぞれの処理時間を計測し、ボトルネックを特定することが重要です。

大量データの移行で想定以上に時間がかかっている場合は、INSERTの方法だけでなく、データの取得方法も見直してみてください。

PREV To list