バルクインサートとインサートで処理速度を比較してみた

MySQLのインサートにバルクインサートという"まとめていっきにインサート"するバルクインサートというものがあります。

通常のインサートより、処理速度が早いということなのですが、実際にどれくらい違うのか試してみたいと思います。

以下、環境。

OS:Mac OS X 10.7.5 CPU:1.7GHz Intel Core i5 メモリ:4GB MySQL:5.1

結果を言うとバルクインサートはめちゃ早いです。郵便局のデータ約12万件を2秒程度でインサートできます。通常だと約11秒ほど。

郵便局のデータはこちらのブログの記事にあるものを使用させて頂きました。ありがとうございます。 MySQLにサンプルデータをinしてみる::郵便番号 | e2esound.com業務日誌

用意するテーブルはかなりざっくりと。

create table `address` ( `id` int(11), `ad1` text, `ad2` text, `ad3` text ) engine=InnoDB default charset=utf8;

+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | 0       |       |
| ad1   | text    | YES  |     | NULL    |       |
| ad2   | text    | YES  |     | NULL    |       |
| ad3   | text    | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+

つぎにコード。

#!/usr/bin/env python
# -*- coding: utf-8 -*-
import MySQLdb
import sys
import csv
import datetime


def main():
    con = MySQLdb.connect(
        host='localhost',
        db='',
        user='',
        passwd='',
        charset='utf8'
    )
    cursor = con.cursor()
    f = open(sys.argv[1], 'rU')
    reader = csv.reader(f)
    print "========================================"
    print "INSERT START"
    print "========================================"
    start = datetime.datetime.now()
    for row in reader:
        cursor.execute(
            "INSERT INTO address(id, ad1, ad2, ad3) VALUES(%s, %s, %s, %s) ",
            (row[0], row[1], row[2], row[3])
        )
    con.commit()
    print "end", datetime.datetime.now() - start
    f.close()
    
    cursor.execute("truncate table address")
    
    f = open(sys.argv[1], 'rU')
    reader = csv.reader(f)
    print "========================================"
    print "BULKINSERT START"
    print "========================================"
    val_list = []
    start = datetime.datetime.now()

    for row in reader:
        val_list.append((row[0], '"' + row[1] + '"', '"' + row[2] + '"', '"' + row[3] + '"'))
        if len(val_list) >= 1000:
            cursor.executemany(
                "INSERT INTO address(id, ad1, ad2, ad3) VALUES(%s, %s, %s, %s) ",
                val_list
            )
            val_list = []

    if val_list:
        cursor.executemany(
            "INSERT INTO address(id, ad1, ad2, ad3) VALUES(%s, %s, %s, %s) ",
            val_list
        )           
    con.commit()
    print "end", datetime.datetime.now() - start
    cursor.close()
    con.close()
    

if __name__ == '__main__':

    main()

実行結果

========================================
INSERT START
========================================
end 0:00:12.227141
========================================
BULKINSERT START
========================================
end 0:00:02.329721

圧倒的にバルクインサートが早いですね。素晴らしい。

まとめ

業務でバルクインサートは使っているのを知っていたのですが、具体的にどういうSQLなのかまでは知らなかった。今回の検証でSQLと通常のインサートとどの程度違うのか試せたのは良かったと思う。

バルクインサートが早い理由は一括でインサートするから。通常のインサートをループするとI/Oがループの回数分発生してしまう。しかし、一括でインサートする場合だとI/Oが少ない為、MySQLの処理が早いと思われる。（この辺ちょっと自信ないので、だれかおしえてー）また、一括でインサートできるが、一度インサートに失敗するとすべてのインサートがなかったことになるらしい。（今度試してみる）