MySQLのデータ挿入（insert）の高速化方法

最近までほとんどデータベース関連は触れておらず，SQL雑魚勢だった自分ですが，最近触れる機会が増えてきたのでメモを書いておきます．

データベースはMySQLでGCP（googleのクラウド）上のCloud SQLにデータベースを作成しています．

cloud.google.com

タスクとしては，csvファイルの各行の情報をそれぞれMySQLに挿入していくというもの pythonのMySQLライブラリから，データベースに接続して，データを挿入していきます

import pandas as pd
import mysql.connector

conn = connector.connect(
    host='IP address',
    port='3306',
    user='user_name',
    password='password'
)

cursor = conn.cursor()
data = pd.read_csv('data')

for d in data.iterrows():
    cursor.execute("insert into table_name(data1, data2, data3) values (d[0],d[1],d[2])")
conn.commit()

↑のように最初は１件づつデータを取り出して挿入してましたが，なんか時間がかかるなーと思って時間を計測してみると．．．

cursor.execute()１実行あたり0.009秒もかかってる🥺

時間的に短そうに見えますが，例えばデータが500万件（500万行）あったとしたら，12時間はかかります．そんな待てない🥺

多分ボトルネックはCloud SQLへのアクセスだと思ったので，バッチ処理できないか調べたところやはり出来るようでした．バルクインサートというらしい．

SQLのインサートとバルクインサート - Qiita

自分も上のコードをバクルインサート用に書き直してみると

insert_data = ''
count = 0
for d in data.iterrows():
    count+=1
    tmp_d = "({}, {}, {})".format(d[0],d[1],d[2])
    if insert_data == '':
        insert_data = tmp_d
    else:
        insert_data = insert_data + "," + tmp_d
    if count%100==0:
        cursor.execute("insert into table_name(data1, data2, data3) values "+insert_data)
conn.commit()

↑のように100件をまとめてバルクインサートさせてみました，すると

cursor.execute()の時間は１実行あたり0.015秒になりました． 挿入する件数を100倍にしたので，１実行あたりの時間が増えるのは当然ですね．

じゃあ，データ1件あたりにかかる時間を比較してみると．．．

1. 1件ごとのインサート：0.9*e-2秒
2. 100件ごとのバルクインサート：1.5 * e-4

つまり，100件ごとのバルクインサートで処理速度が60倍になったことがわかります（効率厨歓喜）

じゃあ，1000，10000件とバルクインサートの件数を増やしたらどうなるの？？？？

インサート件数	1件あたりの時間
1	0.9e-2
10	0.1e-2
100	1.5e-4
1000	8.6e-5
10000	6.4e-5

今回，自分の環境ではこんな結果になりました，1000件と10000件ではさほど差は現れませんでした，つまり，通信のオーバーヘッドよりも別の処理の比重が重くなったことがわかります．

みんなもバルクインサート使おうな！（初心者からの伝言）