[Python]MongoDBとMySQL比較検証
PythonでのMongoDB/MySQLの操作(ファイルの入力、簡単なSELECT文)をはじめ、各種細かな手順をまとめておく。
Contents
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.pyimport 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.pyimport 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.pyimport 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.pyimport 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 resultcsvの読み込みには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列だったので、カラム数が多いデータでのベンチも取ってみたい。
ディスカッション
コメント一覧
まだ、コメントがありません