« VB WebBrowser HTML取得時間 IEバージョン差比較 | トップページ | VB 文字列連結 高速化 »

2015年3月 6日 (金)

MySQL Insertが遅い! ので実験してみた

独学でMySQLを使い始めて2年くらいになりますが,最近,CSVファイルから大量のデータを読んでデータベースに書き込みさせる機会があり,その際にとてつもなく時間が掛かることが判明しました。丸3日間くらい掛かってようやく書き込みが終わる事態に…
いくらなんでも遅すぎると言うことでネット検索してみたら同様の案件が多数あり。結論として「マルチプルインサート」を使うと見違えるほど早くなるとのこと。

通常のSQLインサート文(シングルインサート)
INSERT INTO test1 (date,dataA,dataB,dataC,dataD) VALUES
('2015/3/1','テスト',100,1234567890,1234567.89);

マルチプルインサートのSQL文
INSERT INTO test1 (date,dataA,dataB,dataC,dataD) VALUES
('2015/3/1','テスト1',101,1234567891,1234567.00),
('2015/3/2','テスト2',102,1234567892,2234567.11),
('2015/3/3','テスト3',103,1234567893,3234567.22),
('2015/3/4','テスト4',104,1234567894,4234567.33),
('2015/3/5','テスト5',105,1234567895,5234567.44),
('2015/3/6','テスト6',106,1234567896,6234567.55);
複数行のデータをつなげて記述。

実際に実験した結果報告もあり効果のほどは疑いようが無いのですが疑問が1つ湧きました。

マルチプルインサートで早くなるのは分かったが,逆にSQL文の文字列作成で時間が掛かって相殺されるのでは?

と言うことで早速実験してみました。

VisualBasic2013
MySQL Server 5.6
MySQL Connector Net 6.9.5
を使って10万行のデータを書き込むプログラムコードを作成。

使用テーブル ※2つ目のテーブルはインデックス付き
CREATE TABLE test1 (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
date DATETIME,
dataA VARCHAR(30),
dataB INT(8),
dataC BIGINT(12),
dataD DECIMAL(10,3)
);
CREATE TABLE test2 (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
date DATETIME,
dataA VARCHAR(30),
dataB INT(8),
dataC BIGINT(12),
dataD DECIMAL(10,3),
index(date),
index(dataB)
);

1行のSQL文を10万回実行する方法から最大で1万行分のデータを一まとめにして10回のSQL文で実行する方法まで9段階を試し,それぞれでSQL文作成に掛かる時間,SQL実行に掛かる時間を計測し集計しました。
1行×10万回
5行×2万回
10行×1万回
50行×2000回
100行×1000回
500行×200回
1000行×100回
5000行×20回
1万行×10回

結果
10万行のSQL実行時間(単位:秒) ※インデックス無しテーブルの場合

SQL行結合 実行回数 SQL文作成 SQL実行 トータル
1 100000 1.0 51.4 52.4
5 20000 0.5 12.9 13.4
10 10000 0.6 8.2 8.8
50 2000 1.6 5.8 7.4
100 1000 3.0 5.2 8.2
500 200 11.4 6.9 18.3
1000 100 27.7 6.9 34.6
5000 20 151.5 3.0 154.5
10000 10 282.6 3.3 285.9

SQL実行時間(10万行)

結論として
・SQL実行時間差は最大で17倍。マルチプルインサートの効果あり。
・但し1000回実行あたりから頭打ち。
・SQL文作成時間は行数が増えるほど悪化。
・よって両者のトレードオフになる。
 今回の場合は100行をまとめて1000回実行するラインがベストだった。
 その場合,シングルインサート比の時間差で約6倍早い。

なお,テーブルにインデックスがある場合と無い場合の時間差も調べましたが大差が無かったため掲載を割愛しました(インデックスありの方が遅いはず?)。インデックスの付け方が悪かったかも知れないので後日再度検証予定です。


うーむ,それにしても,これが会社業務なら「知らなかった」では済まされませんね。他に似たようなロスが沢山転がっていそうです。

さて,どうしたものか…


検証に使用したVisualBasicのソースコード
-----------------------------------------------------------
Imports MySql.Data.MySqlClient

Public Class Form1

Private Sub Button1_Click(sender As Object, e As EventArgs) _
Handles Button1.Click

Dim swMake As New System.Diagnostics.Stopwatch()
Dim swRun As New System.Diagnostics.Stopwatch()
Dim Connection As New MySqlConnection
Dim Command As MySqlCommand
Dim ConnectStr As String
Dim sqlStr As String
Dim repeat1 As Integer
Dim repeat2 As Integer
Dim index As String = Nothing
Dim mes As String
Dim loop1 As Integer
Dim loop2 As Integer
Dim loop3 As Integer

ConnectStr = "Database=test;"
ConnectStr &= "Data Source=localhost;"
ConnectStr &= "User Id=XXXXXX;"
ConnectStr &= "Password=YYYYYY;"
Connection.ConnectionString = ConnectStr
Connection.Open()
Command = Connection.CreateCommand

For loop1 = 1 To 9
If loop1 = 1 Then repeat1 = 1
If loop1 = 2 Then repeat1 = 5
If loop1 = 3 Then repeat1 = 10
If loop1 = 4 Then repeat1 = 50
If loop1 = 5 Then repeat1 = 100
If loop1 = 6 Then repeat1 = 500
If loop1 = 7 Then repeat1 = 1000
If loop1 = 8 Then repeat1 = 5000
If loop1 = 9 Then repeat1 = 10000
repeat2 = 100000 / repeat1
For loop2 = 1 To 2
If loop2 = 1 Then index = "インデックス無し "
If loop2 = 2 Then index = "インデックスあり "
For loop3 = 1 To repeat2
swMake.Start()
sqlStr = makeSqlStr(loop3, repeat1)
swMake.Stop()
swRun.Start()
Command.CommandText = sqlStr
Command.ExecuteNonQuery()
swRun.Stop()
Next loop3
mes = index & "文字列作成 " & repeat1 & "×" & repeat2 & " :"
Console.WriteLine(mes & swMake.Elapsed.ToString)
mes = index & "実行 " & repeat1 & "×" & repeat2 & " :"
Console.WriteLine(mes & swRun.Elapsed.ToString)
swMake.Reset()
swRun.Reset()
Next loop2
Next loop1

Command.Dispose()
Connection.Close()
Connection.Dispose()

Application.Exit()

End Sub

Private Function makeSqlStr(selectTabele As Integer, _
                             repeat As Integer) As String

Dim workStr As String
Dim dtToday As DateTime = DateTime.Today
Dim loop1 As Integer

If selectTabele = 1 Then
workStr = "INSERT INTO test1 "
Else
workStr = "INSERT INTO test2 "
End If
workStr &= "(date,dataA,dataB,dataC,dataD) "
workStr &= "VALUES "
For loop1 = 1 To repeat
workStr &= "('" & dtToday.ToString & "',"
workStr &= "'テストテストテスト',"
workStr &= loop1.ToString & ","
workStr &= "1234567890,"
workStr &= "1234567.89"
workStr &= "),"
Next
workStr = workStr.TrimEnd(","c)
workStr &= ";"

Return workStr

End Function

End Class

 

|

« VB WebBrowser HTML取得時間 IEバージョン差比較 | トップページ | VB 文字列連結 高速化 »

コメント

コメントを書く



(ウェブ上には掲載しません)




トラックバック

この記事のトラックバックURL:
http://app.cocolog-nifty.com/t/trackback/206107/61237381

この記事へのトラックバック一覧です: MySQL Insertが遅い! ので実験してみた:

« VB WebBrowser HTML取得時間 IEバージョン差比較 | トップページ | VB 文字列連結 高速化 »