[Python]MongoDBとMySQL比較検証

2019年3月30日2019年4月16日

MongoDBとMySQLの実装・パフォーマンスの比較検証をしてみた。
PythonでのMongoDB/MySQLの操作(ファイルの入力、簡単なSELECT文)をはじめ、各種細かな手順をまとめておく。

Contents

1. TL;DR
- 1.1. 検証概要
2. 環境
3. 実装コード
4. 詳細手順

TL;DR

MongoDBは使いやすい(主観)
MongoDBの読み込みはMySQLよりだいぶ速い。
MySQLでのロード(LOAD DATA LOCAL INFILE)は、MongoDBよりも速い。

検証概要

CSVファイルをDBにインポート
INDEXカラムのUNIQUEなリストを取得するSELECT文を発行
をMongoDB, MySQLそれぞれで実行し、各フェーズでの所要時間を計測した。

結果は以下の通り

LOAD(CSVファイルの読み込み)

レコード数	MongoDB(単位:秒)	MySQL(単位:秒)
100	0.79	0.21
1000	0.24	0.11
100000	4.66	0.98
1000000	48.14	7.92
5000000	274.07	35.84
10000000	659.40	225.58

SELECT

レコード数	MongoDB(単位:秒)	MySQL(単位:秒)
100	0.05	0.02
1000	0.01	0.01
100000	0.10	0.07
1000000	0.31	0.44
5000000	0.66	1.82
10000000	2.10	17.57

環境

macOS 10.13.6(MacBook Air/プロセッサ1.6 GHz Intel Core i5/メモリ8GB)
Python 3.7.1
MongoDB 4.0.6
MySQL 5.6

実装コード

MongoDBの処理

mongo_bench.py


import sys, os
from pymongo import MongoClient
import pandas as pd
import timer # 自作時間計測クラス

# DB setting
client = MongoClient('localhost', 27017)
db = client.test_database

def get_data_from_file(file_name, delimiter, has_header, names=[]):
    if has_header:
        pd_dict = pd.read_csv(file_name, sep=delimiter)
    else:
        pd_dict = pd.read_csv(file_name, sep=delimiter, header=None, names=names)

    result = pd_dict.to_dict(orient="record")
    return result

# コレクション、インデックス作成
def init_coll(collection_name, index_info=[('uid',1)]):
    db[collection_name].drop()
    db.create_collection(collection_name)
    col = db[collection_name]
    col.create_index(index_info)

# データ挿入
def insert_data(collection_name, dict_objs):
    col = db[collection_name]
    col.insert_many(dict_objs)

# SELECT
def select_distinct(collection_name, filepath,unique_col='uid'):
    col = db[collection_name]

    cur = col.distinct(unique_col)
    print("count:", str(len(cur)))

    # 結果をファイルに書き出し
    with open(filepath, 'w') as fw:
        for value in cur:
            print(value,file=fw)

# CSVファイルのデータをコレクションにロード
def load_data(filepath, table_name,is_init=False):
    if is_init:
        init_coll(table_name)

    dict_objs = get_data_from_file(filepath, ',', True)
    insert_data(table_name, dict_objs)

# メイン処理
def exec_bench(filepath):
    # ファイル名、DB名、結果ファイル名の生成
    dir, filename = os.path.split(filepath)
    table_name = filename.split('.')[0]
    output_filepath = os.path.join(dir, table_name + "_output_mongodb.tsv")

    # 時間計測準備
    tm = timer.Timer()
    label_import = 'MongoDB_import_' + table_name
    label_select = 'MongoDB_select_' + table_name

    # import
    tm.start(label_import)
    load_data(filepath, table_name,is_init=True)
    tm.stop(label_import)

    # select
    tm.start(label_select)
    select_distinct(table_name, output_filepath)
    tm.stop(label_select)

def main(argv):
    # 引数設定
    filepath = argv[1]
    mongo_bench(filepath)

if __name__ == "__main__":
    main(sys.argv)

MySQLの処理

mysql_bench.py


import mysql.connector
import sys,os
import timer # 自作時間計測クラス

# 接続
config = {
    'user': 'sample_user',
    'password': 'password',
    'host': 'localhost',
    'database': 'sample_db',
    'allow_local_infile': True # LOAD DATA LOCAL INFILEのクエリ発行に必要
}
cnx = mysql.connector.connect(**config)
cur = cnx.cursor()

# DBを削除して作成
def create_table(table_name):
    query = "DROP TABLE IF EXISTS " + table_name
    cur.execute(query)

    query = """CREATE TABLE IF NOT EXISTS """ + table_name + """ (
    uid int NOT NULL,
    device VARCHAR(20) NOT NULL,
    time int NOT NULL,
    INDEX(uid)
    )"""
    cur.execute(query)

# データ挿入
def load_data(filepath, table_name):
    query = """
    LOAD DATA LOCAL INFILE '""" + filepath + """' INTO TABLE `""" + table_name + """` FIELDS TERMINATED BY ',' ENCLOSED BY '"' IGNORE 1 LINES
    """
    cur.execute(query)

    # データのコミット
    cnx.commit()

# SELECT
def select_distinct(table_name, filepath):
    query = """
    SELECT DISTINCT(uid) FROM """ + table_name

    cur.execute(query)

    rows = cur.fetchall()

    print("count:", str(len(rows)))

    with open(filepath, 'w') as fw:
        for row in rows:
            uid = row[0]
            print(uid,file=fw)

def exec_bench(filepath):
    # ファイル名、DB名、結果ファイル名の生成
    dir, filename = os.path.split(filepath)
    table_name = filename.split('.')[0]
    output_filepath = os.path.join(dir, table_name + '_output_mysql.tsv')

    # 時間計測準備
    tm = timer.Timer()
    label_import = 'MySQL_import_' + table_name
    label_select = 'MySQL_select_' + table_name

    # import
    tm.start(label_import)
    create_table(table_name)
    load_data(filepath, table_name)
    tm.stop(label_import)

    # select
    tm.start(label_select)
    select_distinct(table_name, output_filepath)
    tm.stop(label_select)

def main(argv):
    # 引数設定
    filepath = argv[1]
    exec_bench(filepath)

if __name__ == '__main__':
    main(sys.argv)

CSVファイルをまとめて指定し、MongoDB/MySQLそれぞれで実行する

bench_main.py


import mongo_bench
import mysql_bench

def benchmark(data_sources):
    # mongodb
    for data_source in data_sources:
        mongo_bench.exec_bench(data_source)

    # mysql
    for data_souce in data_sources:
        mysql_bench.exec_bench(data_souce)

def main():
    data_sources = [
        'login_100.csv',
        'login_1000.csv',
        'login_100000.csv',
        'login_1000000.csv',
        'login_5000000.csv',
        'login_10000000.csv'
    ]
    benchmark(data_sources)

if __name__ == '__main__':
    main()

時間計測クラス

timer.py


import time

# 時間計測用クラス
class Timer:
    def __init__(self):
        self.time_dict = {}

    def start(self,key):
        self.time_dict[key] = time.time()

    def stop(self,key):
        res = time.time() - self.time_dict[key]
        print (key + "\t{:.2f}".format(res))

詳細手順

インストール

MongoDB, MySQLのインストールについては割愛。まだの方は以下の記事などを参考に。

CSVファイルの準備

サンプルデータを取得

パフォーマンス比較のために大量データで検証したいので、数千万規模のデータテーブルを探す。
Treasure Dataのチュートリアル「データ分析のためのSQL講座」に素敵なデータセットがあったのでお借りする。
このセクションで使用するサンプルデータセット – Arm Treasure Data

こういうデータが入ってる


uid,device,time
42622832,fp,1356285602
9476464,fp,1356285618
36454879,fp,1356285625
16710566,sp,1356285633
31796396,sp,1356285635
31987837,fp,1356285642
31303042,fp,1356285673
1943498,fp,1356285716

サンプルデータを1万,10万,100万,1000万で切り出す

データ量ごとに計測したいので切り分ける。

$ head -n 10000 logs.csv > logs_10000.csv
で行頭から指定の数を切り出して別ファイルに書き出す。

MongoDB実装

いよいよMongoDBの実装。

コネクタ作成


from pymongo import MongoClient

# (略)

# DB setting
client = MongoClient('localhost', 27017)
db = client.test_database

CSVファイルのインポート

コレクション(テーブル)の作成とインデックス作成


def init_coll(collection_name, index_info=[('uid',1)]):
    db[collection_name].drop()
    db.create_collection(collection_name)
    col = db[collection_name]
    col.create_index(index_info)

CSVファイルを読み込み


def get_data_from_file(file_name, delimiter, has_header, names=[]):
    if has_header:
        pd_dict = pd.read_csv(file_name, sep=delimiter)
    else:
        pd_dict = pd.read_csv(file_name, sep=delimiter, header=None, names=names)

    result = pd_dict.to_dict(orient="record")
    return result

csvの読み込みにはpandasを使う。
pandas.to_dict()で読み込んだデータを入力できる構造に変換している。

参考 : pandas.DataFrame, Seriesを辞書に変換（to_dict） | note.nkmk.me

コレクションにデータをロード


# CSVファイルのデータをコレクションにロード
def load_data(filepath, table_name,is_init=False):
    if is_init:
        init_coll(table_name)

    # CSVにヘッダーが含まれているのでそのまま使う
    dict_objs = get_data_from_file(filepath, ',', True)

    insert_data(table_name, dict_objs)

# データ挿入
def insert_data(collection_name, dict_objs):
    col = db[collection_name]
    col.insert_many(dict_objs)

今回はCSVにヘッダーが含まれているのでスルーしているが、カラム名と型を指定したロードもできるみたい。

参考 : MongoDBへCSVインポートするときフィールドの型を指定するには

SELECT結果の取得・書き出し

一意のuidのリストを取得しテキストに書き出すシンプルなクエリ


def select_distinct(collection_name, filepath,unique_col='uid'):
    col = db[collection_name]

    cur = col.distinct(unique_col)
    print("count:", str(len(cur)))

    # 結果をファイルに書き出し
    with open(filepath, 'w') as fw:
        for value in cur:
            print(value,file=fw)

MySQL実装

コネクタ作成


import mysql.connector

# 中略

# 接続
config = {
    'user': 'sample_user',
    'password': 'password',
    'host': 'localhost',
    'database': 'sample_db',
    'allow_local_infile': True # LOAD DATA LOCAL INFILEのクエリ発行に必要
}
cnx = mysql.connector.connect(**config)
cur = cnx.cursor()

configのハマりどころ : allow_local_infile

mysql-connectorでLOAD DATA LOCAL INFILEを実行するときはこの設定が無いと権限エラーで失敗するので注意

参考 : MySQL :: MySQL Connector/Python Developer Guide :: 7.1 Connector/Python Connection Arguments

CSVファイルのインポート

テーブル作成
LOAD DATA LOCAL INFILEのクエリを実行


def create_table(table_name):
    query = "DROP TABLE IF EXISTS " + table_name
    cur.execute(query)

    query = """CREATE TABLE IF NOT EXISTS """ + table_name + """ (
    uid int NOT NULL,
    device VARCHAR(20) NOT NULL,
    time int NOT NULL,
    INDEX(uid)
    )"""
    cur.execute(query)

# データ挿入
def load_data(filepath, table_name):
    query = """
    LOAD DATA LOCAL INFILE '""" + filepath + """' INTO TABLE `""" + table_name + """` FIELDS TERMINATED BY ',' ENCLOSED BY '"' IGNORE 1 LINES
    """
    cur.execute(query)

    # データのコミット
    cnx.commit()

SELECT結果の取得・書き出し


def select_distinct(table_name, filepath):
    query = """
    SELECT DISTINCT(uid) FROM """ + table_name

    cur.execute(query)

    rows = cur.fetchall()

    print("count:", str(len(rows)))

    with open(filepath, 'w') as fw:
        for row in rows:
            uid = row[0]
            print(uid,file=fw)

共通部分

テーブル/コレクション名、出力ファイルパスを入力ファイルパスから生成

都度テーブル名や出力ファイルパスを書くのはシンドイし、特段個別で設定したいモチベーションも無いので入力ファイルパスから生成する。


# ファイル名、DB名、結果ファイル名の生成
dir, filename = os.path.split(filepath)
table_name = filename.split('.')[0]
output_filepath = os.path.join(dir, table_name + '_output_mysql.tsv')

参考 : Pythonでパス文字列からファイル名・フォルダ名・拡張子を取得、結合 | note.nkmk.me

時間計測クラス

ベンチマークテストなので処理時間を計測する用のクラス(timer.py)を作っておく。
以下のように使う。


tm = Timer() # インスタンス生成
tm.start('some_process')
# DO SOMETHING
tm.stop('some_process') # some_processにかかった時間を出力

まとめ

複数回ベンチマークテストを実施してみて、結果は冒頭のようになった。

MongoDBはMySQLより数倍速いイメージだったので、書き込みでMySQLに軍配が上がったのが意外。
とはいえ、実装方法によるところが大きいので、DBの速度というより、Python+今回の実装の結果として参考にする。

今後やってみたいこと

MySQLのLOAD DATA LOCAL INFILEのような高速ロードをMongoDBでやる方法(mongoimportはPythonコードから呼び出せるのだろうか)
今回は都度新規テーブル作成+フルスキャンなので、「繰り返しの読み込み」や「インデックスを使った範囲検索」などでのベンチも取ってみたい。
今回サンプルデータはカラムが3列だったので、カラム数が多いデータでのベンチも取ってみたい。

以上。

開発MongoDB, MySQL, python, 比較

Google Spreadsheetで上下中央揃えとテキストの折り返しショートカット