2016/10/18 20:19:04

MySQLのINSERTを速くしたい PHP/PDOでのマルチプルインサート編

目次(クリックするとジャンプします)
  • 1:MySQLのINSERTを速くしたい PHP/PDOでのマルチプルインサート編
  • 1.1:遅いこと山のごとし
  • 1.2:ちょっとした知識
  • 1.2.1:INSERT1回あたりのコスト
  • 1.2.2:行をまとめてINSERT
  • 1.3:マルチプルインサートを試してみる
  • 1.3.1:テスト環境・テストコード
  • 1.3.2:100行おまとめ
  • 1.3.3:1000行おまとめ
  • 1.3.4:10000行おまとめ
  • 1.3.5:バインドする処理時間はどうなのさ?
  • 1.4:まとめ

MySQLのINSERTを速くしたい PHP/PDOでのマルチプルインサート編

遅いこと山のごとし

前回@MINOのノートパソコンの腐れ具合を再確認できたのですが、今回はストレージではなくプログラミング上でINSERTの速度を改善したいと思います。

前回の記事はこちらです。ぜひどうぞ。

MySQLでINSERTが遅いならストレージを見なおしてみたら良いかも

ちょっとした知識

INSERT1回あたりのコスト

行の挿入には以下のようなコストがかかっているのだそうです。数字はコストの比率みたいなものです。

  • 接続 3
  • サーバーへのクエリーの送信 2
  • クエリーの解析 2
  • 行の挿入 1 ×行サイズ
  • インデックスの挿入 1 ×インデックス数
  • クローズ 1

つまり1回のINSERTにこれらのコストが掛かっているということですね。

接続、送信、解析なんかはかなりコスト高めのようです。

このように1回のINSERTに掛かるコストがある程度決まっているので、そもそものストレージの速さがものを言うのですが、INSERTの回数を減らせるとしたらどうでしょう?

行をまとめてINSERT

INSERTでは複数行をまとめて処理できるSQLの書き方があります。

こんな感じです。

INSERT INTO testdb.inserttest (cipher, numbers) VALUE  (’skidjikdo', 10292029), ('dhisdlhs',19820333), ('ofnoadfie', 5748080);

INSERTするデータをかっこでくくってコンマでつなげていく書き方です。例の場合は3行分のINSERTに相当します。

このINSERTをマルチプルインサートなどと言うそうですよ。@MINOはおまとめINSERTなどと呼んでいます。

今回はこのマルチプルインサートをつかって速度改善を目指してみたいと思います。

マルチプルインサートを試してみる

テスト環境・テストコード

テスト環境は前回の記事と同じ@MINOの腐れノートをホストにしたDockerコンテナです。

  • MySQL稼働環境
  • Docker公式MySQLコンテナ(5.7.12)
  • Docker公式PHPコンテナ(7.0.6-apache)
  • コンテナにメモリ上限・CPU上限は設定していません
  • Dockerホスト
  • OS:GUN/LINUX Debian8
  • CPU: Pentium B980(泣)
  • メモリ:6GB
  • HDD:250GB

今回のINSERT用のスクリプトはこんな感じ。

相変わらず無能なコードですが、ちょっとだけ説明。

今回の検証では一度に多くの行をINSERTします。2~3行なら手書きでプリペアードステートメントを書いてもいいのですが、100行とかになるとしんどいです。

そのためプリペアードステートメントは独自関数で生成するようにしました。

これがその関数です。なんか文字列に数値をくっつけて気持ち悪いですね。

//プリペアードステートメント生成
function preparedSt(int $pross)
{
    $insertSQL = 
        "INSERT INTO testdb.inserttest (cipher, numbers) VALUE ";
    for ($i=0; $i < $pross; $i++) {
        if($i !== ($pross - 1)){
            $insertSQL .= "(:cipher{$i}, :numbers{$i}),";
        }else{
            $insertSQL .= "(:cipher{$i}, :numbers{$i});";
        }
    }
    return $insertSQL;
}

引数に一度にINSERTしたい行数をいれれば、その分のプリペアードステートメントをつくります。

この関数で以下のようにプリペアードステートメントが簡単に生成できるようになりました。これは100行分のプリペアードステートメントです。

string(2434) "INSERT INTO testdb.inserttest (cipher, numbers) VALUE (:cipher0, :numbers0),(:cipher1, :numbers1),(:cipher2, :numbers2),(:cipher3, :numbers3),(:cipher4, :numbers4),(:cipher5, :numbers5),(:cipher6, :numbers6),(:cipher7, :numbers7),(:cipher8, :numbers8),(:cipher9, :numbers9),(:cipher10, :numbers10),(:cipher11, :numbers11),(:cipher12, :numbers12),(:cipher13, :numbers13),(:cipher14, :numbers14),(:cipher15, :numbers15),(:cipher16, :numbers16),(:cipher17, :numbers17),(:cipher18, :numbers18),(:cipher19, :numbers19),(:cipher20, :numbers20),(:cipher21, :numbers21),(:cipher22, :numbers22),(:cipher23, :numbers23),(:cipher24, :numbers24),(:cipher25, :numbers25),(:cipher26, :numbers26),(:cipher27, :numbers27),(:cipher28, :numbers28),(:cipher29, :numbers29),(:cipher30, :numbers30),(:cipher31, :numbers31),(:cipher32, :numbers32),(:cipher33, :numbers33),(:cipher34, :numbers34),(:cipher35, :numbers35),(:cipher36, :numbers36),(:cipher37, :numbers37),(:cipher38, :numbers38),(:cipher39, :numbers39),(:cipher40, :numbers40),(:cipher41, :numbers41),(:cipher42, :numbers42),(:cipher43, :numbers43),(:cipher44, :numbers44),(:cipher45, :numbers45),(:cipher46, :numbers46),(:cipher47, :numbers47),(:cipher48, :numbers48),(:cipher49, :numbers49),(:cipher50, :numbers50),(:cipher51, :numbers51),(:cipher52, :numbers52),(:cipher53, :numbers53),(:cipher54, :numbers54),(:cipher55, :numbers55),(:cipher56, :numbers56),(:cipher57, :numbers57),(:cipher58, :numbers58),(:cipher59, :numbers59),(:cipher60, :numbers60),(:cipher61, :numbers61),(:cipher62, :numbers62),(:cipher63, :numbers63),(:cipher64, :numbers64),(:cipher65, :numbers65),(:cipher66, :numbers66),(:cipher67, :numbers67),(:cipher68, :numbers68),(:cipher69, :numbers69),(:cipher70, :numbers70),(:cipher71, :numbers71),(:cipher72, :numbers72),(:cipher73, :numbers73),(:cipher74, :numbers74),(:cipher75, :numbers75),(:cipher76, :numbers76),(:cipher77, :numbers77),(:cipher78, :numbers78),(:cipher79, :numbers79),(:cipher80, :numbers80),(:cipher81, :numbers81),(:cipher82, :numbers82),(:cipher83, :numbers83),(:cipher84, :numbers84),(:cipher85, :numbers85),(:cipher86, :numbers86),(:cipher87, :numbers87),(:cipher88, :numbers88),(:cipher89, :numbers89),(:cipher90, :numbers90),(:cipher91, :numbers91),(:cipher92, :numbers92),(:cipher93, :numbers93),(:cipher94, :numbers94),(:cipher95, :numbers95),(:cipher96, :numbers96),(:cipher97, :numbers97),(:cipher98, :numbers98),(:cipher99, :numbers99);"

これにズッコンバッコン、データをバインドしてばよえ〜んとexecuteします。

100行おまとめ

では実際に100行をおまとめINSERTしてみました。結果を表にまとめます。

1行INSERT 100行おまとめINSERT時 速さの倍率
100行 3.9636571407318秒 0.088943958282471秒 44倍
1000行 41.069952011108秒 0.5466091632843秒 75倍
10000行 429.5260488987秒 5.8137850761414秒 73倍
100000行 4290.681718111秒 54.683377981186秒 78倍

まさに雲泥の差。行数が多いほうがより速度差がでるのかもしれません。

1000行おまとめ

さてそれでは一度に入れる行数が多いほうがより速くなるんでしょうか?今度は一度に1000行をためしてみます。

100行INSERT 1000行おまとめINSERT時 速さの倍率
1000行 0.5466091632843秒 0.20011115074158秒 2.7倍
10000行 5.8137850761414秒 2.542356967926秒 2.28倍
100000行 54.683377981186秒 13.332988023758秒 4.1倍

まだ早くなる余地があったんですね。しかし1件と100件の場合と比べると劇的な速さの改善とはなっていないようです。

ただ100000行の場合で約4倍と上々の結果が出ています。INSERTする件数が大きく行もまとめられる時に速度がでるんでしょうか?

10000行おまとめ

では一度に10000行行ってみましょう!!

1000行INSERT 10000行おまとめINSERT時 速さの倍率
10000行 2.542356967926秒 1.97900390625秒 1.28倍
100000行 13.332988023758秒 7.826287984848秒 2.28倍

改善幅は随分小さくなってしまっていますが、まだ速くなっていますね。なかなかすごい。

バインドする処理時間はどうなのさ?

ここまで行をまとめるとバインド処理にも時間が掛かってそうですね。ちょっと調べてみました。以下のようにしてバインド部分の経過時間を計ってみました。

$bindstartTime=microtime(true);//タイマースタート
    for ($z=0; $z < $insertAllAtOnce; $z++) {
        $prepare->bindValue(":cipher{$z}", ingredients("cip"));
        $prepare->bindValue(":numbers{$z}", (int)ingredients("num"), PDO::PARAM_INT);
    }
    $bindendTime=microtime(true);//タイマーストップ

こんな結果になりました。

バインド数 経過時間
100 0.0075259208679199秒
1000 0.067105054855347秒
10000 0.31266093254089秒

10000行になるとそれなりに掛かってはいますが、全体の時間から考えればほとんど気にならない感じですね。

まとめ

今回のINSERTはごく簡単で単純な例でした。これにインデックスや行の大きさが関わってくるともう少し複雑な感じになるんじゃないかと思うのですが、少なくともINSERTはマルチプルインサートを使ったほうが速度的にはアドバンテージがあると言えそうです。

結果的にはまとめればまとめるほど速度改善が見込めていたので、できるだけ大きな行数でまとめた方がいいのかもしれません。

でもあんまり大きいとメモリとの兼ね合いもでてきますよね。そのへんは検証の必要がありそうです。

ともかくも、できるだけ行をまとめるというのは鉄板ということでよさそうです。