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はマルチプルインサートを使ったほうが速度的にはアドバンテージがあると言えそうです。
結果的にはまとめればまとめるほど速度改善が見込めていたので、できるだけ大きな行数でまとめた方がいいのかもしれません。
でもあんまり大きいとメモリとの兼ね合いもでてきますよね。そのへんは検証の必要がありそうです。
ともかくも、できるだけ行をまとめるというのは鉄板ということでよさそうです。
