※そもそも環境の違いで全然ベンチになっていなかったのでこの記事は当てにしないでください!
なぜ遅い結果が出てしまったかの神アンサーいただきました。
結局、Go言語をやめる理由はなかった件
2020/12/21 追記
自分の環境でもgoが最速になる結果が出ました。
ここまで差が出なければ言語の問題ではなくなりましたね
コメント等くださったみなさんのおかげです。ありがとうございました
Go go1.15.5
平均秒数:6.39370223 (0x0,0x0)
Python 3.9.0 (default, Nov 25 2020, 02:36:55)
平均秒数:7.017076
実行環境
# PC
MacBook Pro (15-inch, 2019)
プロセッサ:2.6 GHz 6コアIntel Core i7
メモリ:32 GB 2400 MHz DDR4
SSD 250GB
# 実行ツール
Docker Desktop for Mac v3.0.2
go計測
# 計測手順
docker-compose exec golang sh
golang# cd /var/www/web/go
golang# go build . && time ./go
# ①記事公開時のソース再計測結果
Go go1.15.5
平均秒数:13.22204324 (0x0,0x0)
real 2m 12.25s
user 0m 7.09s
sys 0m 25.75s
# ②プレースホルダー使用時結果
# https://github.com/okdyy75/bench-docker/commit/a6b4b94b5703cf242a0fc3a785490989e5518386
Go go1.15.5
平均秒数:10.87066524 (0x0,0x0)
real 1m 48.74s
user 0m 4.78s
sys 0m 16.57s
# ③ファイル書き込みにbufio使用時結果
# https://github.com/okdyy75/bench-docker/commit/26dc6f4a0e7b1a9f9236f573cb6227e038a9bfe8
Go go1.15.5
平均秒数:6.39370223 (0x0,0x0)
real 1m 3.94s
user 0m 3.08s
sys 0m 9.67s
python計測
# 計測手順
docker-compose exec python sh
python# cd /var/www/web/python
python# pip install -r ./requirements.txt
python# time python bench.py
# ①記事公開時のソース再計測結果
Python 3.9.0 (default, Nov 25 2020, 02:36:55)
平均秒数:7.017076
real 1m 10.21s
user 0m 7.62s
sys 0m 4.65s
↓以下最初の記事
Go言語がとにかく速いと聞くので各言語でベンチマークをとって見ました。
確認して見たところ意外な結果に
ベンチマーク計測方法
- 1万行のCSVを読み込んでDBに登録
- 登録したDBのデータをCSVに吐き出す
- 入力CSVと出力CSVを突合して確認
- 計10回実行してその平均を出す
結果
- Python
- PHP
- Ruby
- Go
MySQL 5.7 + Python 3.9.0
平均秒数:7.586802
MySQL 5.7 + PHP 7.4.12
平均秒数:11.787367296219
MySQL 5.7 + Ruby 2.7.2
平均秒数:11.840328
MySQL 5.7 + Go 1.15.5
平均秒数:15.42766124
Go言語が一番遅い?どうして・・・
考察
各言語で一番速度が出た処理を比較してみる
Python
# python
2020-12-04 18:48:09.914944
2020-12-04 18:48:09.935228 import CSV start
2020-12-04 18:48:16.725192 import CSV end
2020-12-04 18:48:16.876930 export CSV start
2020-12-04 18:48:17.105134 export CSV end
2020-12-04 18:48:17.105216 compare CSV start
2020-12-04 18:48:17.208220 compare CSV end
2020-12-04 18:48:17.215074
7.300130
# CSV読み込み+DBインサート
6.789964
# DBセレクト
0.151738
# CSV書き込み
0.228204
# CSV読み込み+突合
0.103004
# php
2020-12-04 18:38:18.282300
2020-12-04 18:38:18.299000 import CSV start
2020-12-04 18:38:24.610800 import CSV end
2020-12-04 18:38:24.692000 export CSV start
2020-12-04 18:38:28.719200 export CSV end
2020-12-04 18:38:28.719200 compare CSV start
2020-12-04 18:38:28.865900 compare CSV end
2020-12-04 18:38:28.868100
10.585803985596
# CSV読み込み+DBインサート
6.311800
# DBセレクト
0.081200
# CSV書き込み
4.027200
# CSV読み込み+突合
0.146700
# ruby
"2020-12-04 18:43:08.038825"
"2020-12-04 18:43:08.053449 import CSV start"
"2020-12-04 18:43:16.653697 import CSV end"
"2020-12-04 18:43:16.683815 export CSV start"
"2020-12-04 18:43:17.118668 export CSV end"
"2020-12-04 18:43:17.118718 compare CSV start"
"2020-12-04 18:43:19.045548 compare CSV end"
"2020-12-04 18:43:19.045579"
"11.006754"
# CSV読み込み+DBインサート
8.600248
# DBセレクト
0.030118
# CSV書き込み
0.434853
# CSV読み込み+突合
1.926830
# go
2020-12-04 18:33:26.164199
2020-12-04 18:33:26.180394 import CSV start
2020-12-04 18:33:35.627523 import CSV end
2020-12-04 18:33:35.628442 export CSV start
2020-12-04 18:33:40.295815 export CSV end
2020-12-04 18:33:40.295867 compare CSV start
2020-12-04 18:33:40.321120 compare CSV end
2020-12-04 18:33:40.321268
14.1570699 (0x0,0x0)
# CSV読み込み+DBインサート
9.447129
# DBセレクト
0.000919
# CSV書き込み
4.667373
# CSV読み込み+突合
0.025253
まとめ
- Pythonはファイル操作に長けている。全体的に処理も早い。ただ他の言語と比較してselectが若干遅い
- PHPはファイル読み込みは速いが、書き込みは遅い
- Rubyはファイル読み込みは遅いが、書き込みは速い
- Goは処理速度自体は非常に速いが、DB処理が非常に遅い。(updateも確認してみると同じくらい遅い)
Goは単純な処理は早いですが、DB処理が非常に遅いので、バッチ処理するならPythonの方が良さそうですね。またRubyとPHPを使うなら、PHPの書き込み速度がネックになりそうなので、Rubyの方が良さそうです。
盲目的にGoが速いという理由で使うのは危ないかも知れないです。目的に沿った言語を選択していくのがベストかと
apiとしてGoを使うなら速いかもしれないですが、これもまたベンチを取って見ないと分からないですね
だから僕はGo言語を辞めた
- 例外が発生しない(返り値でくる)ので
- 全て処理するなら半分以上がerr処理のコードになってしまう。
- 処理しないなら、サイレントに処理が完了するのでバグに気付けない。例えばinsert文が間違っているのに普通に処理が完了する。その影響で全然違う部分でエラーが発生してしまう。
db, err := sql.Open("mysql", dsn)
if err != nil {
panic(err)
}
- 三項演算子が無い。
- 痒いところに手が届かない。例えばsum関数が無い。
- 日時のフォーマット指定が
CreatedAt.Format("2006-01-02 15:04:05")
でちょっと気持ち悪い。
色々な言語で書いて見ましたが、Goはくどい書き方になりがちですね。場合によっては普通に遅いので、あまりメリットを見出せなかったです。。。
ソース
ソース
Go
package main
import (
"database/sql"
"encoding/csv"
"fmt"
"io"
"os"
"runtime"
"time"
_ "github.com/go-sql-driver/mysql"
)
// DB取得
func getDB() *sql.DB {
host := os.Getenv("DB_HOST")
if host == "" {
host = "127.0.0.1"
}
port := os.Getenv("DB_PORT")
if port == "" {
port = "3306"
}
name := os.Getenv("DB_NAME")
if name == "" {
name = "db"
}
user := os.Getenv("DB_USER")
if user == "" {
user = "root"
}
password := os.Getenv("DB_PASSWORD")
if password != "" {
password = ":" + password
}
dsn := fmt.Sprintf(
"%s%s@tcp(%s:%s)/%s?charset=utf8&parseTime=true",
user, password, host, port, name)
db, err := sql.Open("mysql", dsn)
if err != nil {
panic(err)
}
return db
}
// 初期化
func initialize(db *sql.DB) {
var cmd string
cmd = `
CREATE TABLE IF NOT EXISTS users (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
name varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
email varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
email_verified_at timestamp NULL DEFAULT NULL,
password varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
remember_token varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
created_at timestamp NULL DEFAULT NULL,
updated_at timestamp NULL DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEY users_email_unique (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
`
_, err := db.Exec(cmd)
if err != nil {
panic(err)
}
cmd = "TRUNCATE users;"
_, err = db.Exec(cmd)
if err != nil {
panic(err)
}
// var cnt int
// err = db.QueryRow("select count(*) as cnt from users;").Scan(&cnt)
// println(cnt)
}
// User ユーザー情報
type User struct {
ID int
Name string
Email string
EmailVerifiedAt time.Time
Password string
RememberToken string
CreatedAt time.Time
UpdatedAt time.Time
}
// CSV読み込み&DBインサート&CSV書き出し
func work(db *sql.DB) {
var (
err error
file *os.File
reader *csv.Reader
lines []string
line string
rows *sql.Rows
)
// CSV読み込み
printTime("import CSV start")
file, err = os.Open("../import_users.csv")
if err != nil {
panic(err)
}
defer file.Close()
reader = csv.NewReader(file)
_, err = reader.Read() // ヘッダースキップ
for {
lines, err = reader.Read()
if err != nil {
break
}
// lines[0]はidのため1から
_, err = db.Exec(`
INSERT INTO users (
name,
email,
email_verified_at,
password,
remember_token,
created_at,
updated_at
) values (
?, ?, ?, ?, ?, ?, ?
);
`, lines[1], lines[2], lines[3], lines[4], lines[5], lines[6], lines[7])
if err != nil {
panic(err)
}
}
printTime("import CSV end")
rows, err = db.Query("select * from users order by id")
if err != nil {
panic(err)
}
// CSV書き出し
printTime("export CSV start")
file, err = os.Create("./export_users.csv")
if err != nil {
panic(err)
}
defer file.Close()
// ヘッダー書き込み
_, err = file.WriteString(`"id","name","email","email_verified_at","password","remember_token","created_at","updated_at"` + "\n")
if err != nil {
panic(err)
}
var user User
for rows.Next() {
err = rows.Scan(
&user.ID,
&user.Name,
&user.Email,
&user.EmailVerifiedAt,
&user.Password,
&user.RememberToken,
&user.CreatedAt,
&user.UpdatedAt,
)
if err != nil {
panic(err)
}
line = fmt.Sprintf(
`%d,"%s","%s","%s","%s","%s","%s","%s"`+"\n",
user.ID,
user.Name,
user.Email,
user.EmailVerifiedAt.Format("2006-01-02 15:04:05"),
user.Password,
user.RememberToken,
user.CreatedAt.Format("2006-01-02 15:04:05"),
user.UpdatedAt.Format("2006-01-02 15:04:05"))
_, err = file.WriteString(line)
if err != nil {
panic(err)
}
}
printTime("export CSV end")
// 入力CSVと出力CSVを突合
printTime("compare CSV start")
var (
file1 *os.File
file2 *os.File
reader1 *csv.Reader
reader2 *csv.Reader
row1 []string
row2 []string
err1 error
err2 error
)
file1, err1 = os.Open("../import_users.csv")
if err1 != nil {
panic(err1)
}
file2, err2 = os.Open("./export_users.csv")
if err2 != nil {
panic(err2)
}
defer file1.Close()
defer file2.Close()
reader1 = csv.NewReader(file1)
reader2 = csv.NewReader(file2)
for {
row1, err1 = reader1.Read()
row2, err2 = reader2.Read()
if err1 == io.EOF && err2 == io.EOF {
// 処理終了
break
}
if !(row1[0] == row2[0] &&
row1[1] == row2[1] &&
row1[2] == row2[2] &&
row1[3] == row2[3] &&
row1[4] == row2[4] &&
row1[5] == row2[5] &&
row1[6] == row2[6] &&
row1[7] == row2[7]) {
panic("入力CSVと出力CSVが一致しません")
}
}
printTime("compare CSV end")
}
// 実行時間を出力
func printTime(message string) {
println(time.Now().Format("2006-01-02 15:04:05.000000") + " " + message)
}
// メイン処理
func main() {
println("Go " + runtime.Version())
db := getDB()
defer db.Close()
times := []float64{}
for i := 1; i <= 10; i++ {
start := time.Now()
startNanoTime := start.UnixNano()
println(start.Format("2006-01-02 15:04:05.000000"))
// 初期化
initialize(db)
// 負荷処理
work(db)
end := time.Now()
endNanoTime := end.UnixNano()
println(end.Format("2006-01-02 15:04:05.000000"))
// 秒に変換
s := float64(endNanoTime-startNanoTime) / float64(1000*1000*1000) // ミリ * マイクロ * ナノ
println(fmt.Printf("%f", s))
times = append(times, s)
}
var sum float64
for _, s := range times {
sum += s
}
avg := float64(sum / float64(len(times)))
println(fmt.Printf("平均秒数:%f", avg))
}
PHP
<?php
/**
* DB取得
*
* @return PDO
*/
function getDB()
{
$host = getenv('DB_HOST') ?: 'localhost';
$port = getenv('DB_PORT') ?: '3306';
$dbname = getenv('DB_NAME') ?: 'db';
$user = getenv('DB_USER') ?: 'root';
$password = getenv('DB_PASSWORD') ?: '';
$dsn = "mysql:host={$host};port={$port};dbname={$dbname};charset=utf8";
$db = new PDO(
$dsn,
$user,
$password,
[
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
]
);
return $db;
}
/**
* 初期化
*
* @param PDO $db
* @return void
*/
function initialize($db)
{
$sql = <<<EOT
CREATE TABLE IF NOT EXISTS `users` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`email_verified_at` timestamp NULL DEFAULT NULL,
`password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`remember_token` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `users_email_unique` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
EOT;
$db->query($sql);
$db->query('TRUNCATE users;');
}
/**
* CSV読み込み&DBインサート&CSV書き出し
*
* @param PDO $db
* @return void
*/
function work($db)
{
// CSV読み込み
printTime('import CSV start');
$handle = fopen('../import_users.csv', 'r');
fgetcsv($handle); // ヘッダースキップ
while (($row = fgetcsv($handle)) !== false) {
$sql = <<<EOT
INSERT INTO users (
name,
email,
email_verified_at,
password,
remember_token,
created_at,
updated_at
) values (
:name,
:email,
:email_verified_at,
:password,
:remember_token,
:created_at,
:updated_at
);
EOT;
$stmt = $db->prepare($sql);
$stmt->bindValue(':name', $row[1]);
$stmt->bindValue(':email', $row[2]);
$stmt->bindValue(':email_verified_at', $row[3]);
$stmt->bindValue(':password', $row[4]);
$stmt->bindValue(':remember_token', $row[5]);
$stmt->bindValue(':created_at', $row[6]);
$stmt->bindValue(':updated_at', $row[7]);
$stmt->execute();
}
fclose($handle);
printTime('import CSV end');
$stmt = $db->query('select * from users order by id');
$stmt->execute();
$users = $stmt->fetchall();
// CSV書き出し ※fputcsvは一部ダブルクォーテーションで囲まれたり囲まれなかったりするので使わない
printTime('export CSV start');
$fp = fopen('./export_users.csv', 'w');
fwrite($fp, '"id","name","email","email_verified_at","password","remember_token","created_at","updated_at"' . "\n");
foreach ($users as $user) {
// 数値以外はダブルクォーテーションで囲む
$user = array_map(function ($item) {
return is_numeric($item) ? $item : '"' . $item . '"';
}, $user);
fwrite($fp, implode(',', [
$user['id'],
$user['name'],
$user['email'],
$user['email_verified_at'],
$user['password'],
$user['remember_token'],
$user['created_at'],
$user['updated_at'],
]) . PHP_EOL);
}
fclose($fp);
printTime('export CSV end');
// 入力CSVと出力CSVを突合
printTime('compare CSV start');
$handle1 = fopen('../import_users.csv', 'r');
$handle2 = fopen('./export_users.csv', 'r');
while (true) {
$row1 = fgetcsv($handle1);
$row2 = fgetcsv($handle2);
if ($row1 === false && $row2 === false) {
// 処理終了
break;
}
if (!($row1[0] === $row2[0]
&& $row1[1] === $row2[1]
&& $row1[2] === $row2[2]
&& $row1[3] === $row2[3]
&& $row1[4] === $row2[4]
&& $row1[5] === $row2[5]
&& $row1[6] === $row2[6]
&& $row1[7] === $row2[7])) {
throw new Exception('入力CSVと出力CSVが一致しません');
}
}
fclose($handle1);
fclose($handle2);
printTime('compare CSV end');
}
/**
* 実行時間を出力
*
* @param string $message
* @return void
*/
function printTime($message)
{
echo DateTime::createFromFormat('U.u', microtime(true))->format('Y-m-d H:i:s.u') . ' ' . $message . PHP_EOL;
}
/**
* メイン処理
*
* @return void
*/
function main()
{
echo 'PHP ' . phpversion() . PHP_EOL;
$db = getDB();
$times = [];
for ($i = 1; $i <= 10; $i++) {
$startMicroTime = microtime(true);
$start = DateTime::createFromFormat('U.u', $startMicroTime);
echo $start->format('Y-m-d H:i:s.u') . PHP_EOL;
// 初期化
initialize($db);
// 負荷処理
work($db);
$endMicroTime = microtime(true);
$end = DateTime::createFromFormat('U.u', $endMicroTime);
echo $end->format('Y-m-d H:i:s.u') . PHP_EOL;
// 秒数
$s = ($endMicroTime - $startMicroTime);
echo $s . PHP_EOL;
$times[] = $s;
}
$avg = array_sum($times) / count($times);
echo '平均秒数:' . $avg . PHP_EOL;
}
// メイン処理
main();
Python
import csv
import os
import sys
from itertools import zip_longest
from datetime import datetime
import MySQLdb.cursors
config = {
'db_host': os.environ.get('DB_HOST', 'localhost'),
'db_port': int(os.environ.get('DB_PORT', '3306')),
'db_name': os.environ.get('DB_NAME', 'db'),
'db_user': os.environ.get('DB_USER', 'root'),
'db_password': os.environ.get('DB_PASSWORD', ''),
}
def get_db():
"""DB取得
:rtype: MySQLdb.Connection
"""
db = MySQLdb.connect(
host=config['db_host'],
port=config['db_port'],
user=config['db_user'],
passwd=config['db_password'],
db=config['db_name'],
charset='utf8',
cursorclass=MySQLdb.cursors.DictCursor,
autocommit=True,
)
return db
def initialize(db):
"""初期化
:param db: MySQLdb.Connection
:rtype: None
"""
cur = db.cursor()
cur.execute('''
CREATE TABLE IF NOT EXISTS `users` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`email_verified_at` timestamp NULL DEFAULT NULL,
`password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`remember_token` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `users_email_unique` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
''')
cur.execute('TRUNCATE users;')
def work(db):
"""CSV読み込み&DBインサート&CSV書き出し
:param db: MySQLdb.Connection
:rtype: None
"""
# CSV読み込み
print_time('import CSV start')
with open('../import_users.csv') as f:
reader = csv.reader(f)
next(reader)
for row in reader:
cur = db.cursor()
cur.execute('''
INSERT INTO users (
name,
email,
email_verified_at,
password,
remember_token,
created_at,
updated_at
) values (
%(name)s,
%(email)s,
%(email_verified_at)s,
%(password)s,
%(remember_token)s,
%(created_at)s,
%(updated_at)s
);
''', {
'name': row[1], # row[0]はidのため1から
'email': row[2],
'email_verified_at': row[3],
'password': row[4],
'remember_token': row[5],
'created_at': row[6],
'updated_at': row[7]
})
print_time('import CSV end')
cur = db.cursor()
cur.execute('select * from users order by id')
users = cur.fetchall()
# CSV書き出し
print_time('export CSV start')
with open('./export_users.csv', 'w') as f:
writer = csv.writer(f, delimiter=',', quotechar='"', quoting=csv.QUOTE_NONNUMERIC)
writer.writerow(['id', 'name', 'email', 'email_verified_at', 'password', 'remember_token', 'created_at', 'updated_at'])
for user in users:
writer.writerow([
user['id'],
user['name'],
user['email'],
user['email_verified_at'],
user['password'],
user['remember_token'],
user['created_at'],
user['updated_at']
])
print_time('export CSV end')
# 入力CSVと出力CSVを突合
print_time('compare CSV start')
f1 = open('../import_users.csv')
f2 = open('./export_users.csv')
reader1 = csv.reader(f1)
reader2 = csv.reader(f2)
for row1, row2 in zip_longest(reader1, reader2):
if not (
row1[0] == row2[0]
and row1[1] == row2[1]
and row1[2] == row2[2]
and row1[3] == row2[3]
and row1[4] == row2[4]
and row1[5] == row2[5]
and row1[6] == row2[6]
and row1[7] == row2[7]
):
raise Exception('入力CSVと出力CSVが一致しません')
print_time('compare CSV end')
def print_time(message):
"""メイン処理
:param message string:
:type message: int
:rtype: None
"""
print(datetime.now().strftime("%Y-%m-%d %H:%M:%S.%f") + ' ' + message)
def main():
"""メイン処理
:rtype: None
"""
print('Python ' + sys.version)
db = get_db()
times = []
for i in range(10):
start = datetime.now().timestamp()
print(datetime.fromtimestamp(start).strftime("%Y-%m-%d %H:%M:%S.%f"))
# 初期化
initialize(db)
# 負荷処理
work(db)
end = datetime.now().timestamp()
print(datetime.fromtimestamp(end).strftime("%Y-%m-%d %H:%M:%S.%f"))
s = end - start
print('%f' % s)
times.append(s)
svg = sum(times) / len(times)
print('平均秒数:%f' % svg)
if __name__ == '__main__':
# メイン
main()
Ruby
require "mysql2"
require "csv"
# DB取得
# @return [Mysql2::Client] db_client
def get_db
db_client = Mysql2::Client.new(
host: ENV.fetch("DB_HOST") { "localhost" },
port: ENV.fetch("DB_PORT") { "3306" },
username: ENV.fetch("DB_USER") { "root" },
password: ENV.fetch("DB_PASSWORD") { "" },
database: ENV.fetch("DB_NAME") { "db" },
encoding: "utf8",
)
db_client
end
# 初期化
# @param [Mysql2::Client] db
def init(db)
db.query(<<~EOS)
CREATE TABLE IF NOT EXISTS `users` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`email_verified_at` timestamp NULL DEFAULT NULL,
`password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`remember_token` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `users_email_unique` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
EOS
db.query("TRUNCATE users;")
# p db.query("select count(*) as cnt from users;").to_a
end
# CSV読み込み&DBインサート&CSV書き出し
# @param [Mysql2::Client] db
def work(db)
# CSV読み込み
print_time("import CSV start")
CSV.foreach("../import_users.csv", headers: true) do |row|
statement = db.prepare(<<~EOS)
INSERT INTO users (
name,
email,
email_verified_at,
password,
remember_token,
created_at,
updated_at
) values (
?, ?, ?, ?, ?, ?, ?
);
EOS
# row[0]はidのため1から
statement.execute(row[1], row[2], row[3], row[4], row[5], row[6], row[7])
end
print_time("import CSV end")
users = db.query("select * from users order by id")
# CSV書き出し
print_time("export CSV start")
file = File.open("./export_users.csv", "w")
file.write('"id","name","email","email_verified_at","password","remember_token","created_at","updated_at"' << "\n")
users.each do |row|
user = row.map do |k, v|
v = v.strftime("%Y-%m-%d %H:%M:%S") if v.is_a?(Time)
v = %Q{"#{v}"} if !v.is_a?(Integer)
v
end
file.write(user.join(",") << "\n")
end
file.close
print_time("export CSV end")
# 入力CSVと出力CSVを突合
print_time("compare CSV start")
file1 = File.open("../import_users.csv", "r")
file2 = File.open("./export_users.csv", "r")
while true
line1 = file1.gets
line2 = file2.gets
if line1.nil? && line2.nil?
# 処理終了
break
end
row1 = CSV.parse_line(line1)
row2 = CSV.parse_line(line2)
if !(row1[1] == row2[1] &&
row1[2] == row2[2] &&
row1[3] == row2[3] &&
row1[4] == row2[4] &&
row1[5] == row2[5] &&
row1[6] == row2[6] &&
row1[7] == row2[7])
raise("入力CSVと出力CSVが一致しません")
end
end
file1.close
file2.close
print_time("compare CSV end")
end
# 実行時間を出力
# @param [String]
def print_time(message)
p Time.now.strftime("%Y-%m-%d %H:%M:%S.%6N") << " " << message
end
# メイン処理
def main
p "Ruby " << RUBY_VERSION
db = get_db
times = []
for i in 1..10
start_time = Time.now
p start_time.strftime("%Y-%m-%d %H:%M:%S.%6N")
# 初期化
init(db)
# 負荷処理
work(db)
end_time = Time.now
p end_time.strftime("%Y-%m-%d %H:%M:%S.%6N")
s = end_time - start_time
p sprintf("%.6f", s)
times << s
end
avg = times.sum / times.length
p "平均秒数:" << sprintf("%.6f", avg)
end
# メイン
main
結果ログ
平均秒数:7.586802 平均秒数:11.787367296219 平均秒数:11.840328 平均秒数:15.42766124
結果ログ
MySQL 5.7 + Python 3.9.0
Python 3.9.0 (default, Nov 25 2020, 02:36:55)
[GCC 9.3.0]
2020-12-04 18:48:09.914944
2020-12-04 18:48:09.935228 import CSV start
2020-12-04 18:48:16.725192 import CSV end
2020-12-04 18:48:16.876930 export CSV start
2020-12-04 18:48:17.105134 export CSV end
2020-12-04 18:48:17.105216 compare CSV start
2020-12-04 18:48:17.208220 compare CSV end
2020-12-04 18:48:17.215074
7.300130
2020-12-04 18:48:17.215234
2020-12-04 18:48:17.231558 import CSV start
2020-12-04 18:48:24.170420 import CSV end
2020-12-04 18:48:24.322432 export CSV start
2020-12-04 18:48:24.539213 export CSV end
2020-12-04 18:48:24.539270 compare CSV start
2020-12-04 18:48:24.640157 compare CSV end
2020-12-04 18:48:24.647255
7.432021
2020-12-04 18:48:24.647339
2020-12-04 18:48:24.660681 import CSV start
2020-12-04 18:48:31.513771 import CSV end
2020-12-04 18:48:31.661461 export CSV start
2020-12-04 18:48:31.880751 export CSV end
2020-12-04 18:48:31.880851 compare CSV start
2020-12-04 18:48:31.982702 compare CSV end
2020-12-04 18:48:31.989244
7.341905
2020-12-04 18:48:31.989320
2020-12-04 18:48:32.004352 import CSV start
2020-12-04 18:48:38.953776 import CSV end
2020-12-04 18:48:39.115304 export CSV start
2020-12-04 18:48:39.336671 export CSV end
2020-12-04 18:48:39.336729 compare CSV start
2020-12-04 18:48:39.440064 compare CSV end
2020-12-04 18:48:39.446754
7.457434
2020-12-04 18:48:39.446884
2020-12-04 18:48:39.460438 import CSV start
2020-12-04 18:48:46.680622 import CSV end
2020-12-04 18:48:46.845855 export CSV start
2020-12-04 18:48:47.071742 export CSV end
2020-12-04 18:48:47.071842 compare CSV start
2020-12-04 18:48:47.180303 compare CSV end
2020-12-04 18:48:47.187811
7.740927
2020-12-04 18:48:47.187978
2020-12-04 18:48:47.199864 import CSV start
2020-12-04 18:48:54.077490 import CSV end
2020-12-04 18:48:54.242379 export CSV start
2020-12-04 18:48:54.466550 export CSV end
2020-12-04 18:48:54.466608 compare CSV start
2020-12-04 18:48:54.579028 compare CSV end
2020-12-04 18:48:54.586011
7.398033
2020-12-04 18:48:54.586152
2020-12-04 18:48:54.601874 import CSV start
2020-12-04 18:49:01.765982 import CSV end
2020-12-04 18:49:01.939747 export CSV start
2020-12-04 18:49:02.163829 export CSV end
2020-12-04 18:49:02.163930 compare CSV start
2020-12-04 18:49:02.276158 compare CSV end
2020-12-04 18:49:02.283455
7.697303
2020-12-04 18:49:02.283594
2020-12-04 18:49:02.297535 import CSV start
2020-12-04 18:49:09.510598 import CSV end
2020-12-04 18:49:09.691481 export CSV start
2020-12-04 18:49:09.911890 export CSV end
2020-12-04 18:49:09.912001 compare CSV start
2020-12-04 18:49:10.031758 compare CSV end
2020-12-04 18:49:10.039325
7.755731
2020-12-04 18:49:10.039711
2020-12-04 18:49:10.052662 import CSV start
2020-12-04 18:49:17.193991 import CSV end
2020-12-04 18:49:17.383815 export CSV start
2020-12-04 18:49:17.620209 export CSV end
2020-12-04 18:49:17.620323 compare CSV start
2020-12-04 18:49:17.740548 compare CSV end
2020-12-04 18:49:17.747949
7.708238
2020-12-04 18:49:17.748085
2020-12-04 18:49:17.762492 import CSV start
2020-12-04 18:49:25.248833 import CSV end
2020-12-04 18:49:25.417406 export CSV start
2020-12-04 18:49:25.659209 export CSV end
2020-12-04 18:49:25.659332 compare CSV start
2020-12-04 18:49:25.777076 compare CSV end
2020-12-04 18:49:25.784383
8.036298
平均秒数:7.586802
MySQL 5.7 + PHP 7.4.12
PHP 7.4.12
2020-12-04 18:38:18.282300
2020-12-04 18:38:18.299000 import CSV start
2020-12-04 18:38:24.610800 import CSV end
2020-12-04 18:38:24.692000 export CSV start
2020-12-04 18:38:28.719200 export CSV end
2020-12-04 18:38:28.719200 compare CSV start
2020-12-04 18:38:28.865900 compare CSV end
2020-12-04 18:38:28.868100
10.585803985596
2020-12-04 18:38:28.868200
2020-12-04 18:38:28.882700 import CSV start
2020-12-04 18:38:35.479400 import CSV end
2020-12-04 18:38:35.557000 export CSV start
2020-12-04 18:38:39.653300 export CSV end
2020-12-04 18:38:39.653300 compare CSV start
2020-12-04 18:38:39.806300 compare CSV end
2020-12-04 18:38:39.808800
10.940591096878
2020-12-04 18:38:39.808800
2020-12-04 18:38:39.823300 import CSV start
2020-12-04 18:38:46.457900 import CSV end
2020-12-04 18:38:46.535800 export CSV start
2020-12-04 18:38:50.731800 export CSV end
2020-12-04 18:38:50.731800 compare CSV start
2020-12-04 18:38:50.886400 compare CSV end
2020-12-04 18:38:50.889400
11.080587148666
2020-12-04 18:38:50.889500
2020-12-04 18:38:50.904600 import CSV start
2020-12-04 18:38:57.589400 import CSV end
2020-12-04 18:38:57.665800 export CSV start
2020-12-04 18:39:02.105100 export CSV end
2020-12-04 18:39:02.105200 compare CSV start
2020-12-04 18:39:02.275100 compare CSV end
2020-12-04 18:39:02.277800
11.388316869736
2020-12-04 18:39:02.277900
2020-12-04 18:39:02.291100 import CSV start
2020-12-04 18:39:09.488200 import CSV end
2020-12-04 18:39:09.569100 export CSV start
2020-12-04 18:39:14.799000 export CSV end
2020-12-04 18:39:14.799100 compare CSV start
2020-12-04 18:39:14.964100 compare CSV end
2020-12-04 18:39:14.967000
12.689137935638
2020-12-04 18:39:14.967100
2020-12-04 18:39:14.981200 import CSV start
2020-12-04 18:39:22.239800 import CSV end
2020-12-04 18:39:22.320300 export CSV start
2020-12-04 18:39:26.854400 export CSV end
2020-12-04 18:39:26.854400 compare CSV start
2020-12-04 18:39:27.016000 compare CSV end
2020-12-04 18:39:27.018700
12.05156493187
2020-12-04 18:39:27.018700
2020-12-04 18:39:27.032500 import CSV start
2020-12-04 18:39:34.412000 import CSV end
2020-12-04 18:39:34.492700 export CSV start
2020-12-04 18:39:39.111500 export CSV end
2020-12-04 18:39:39.111500 compare CSV start
2020-12-04 18:39:39.275300 compare CSV end
2020-12-04 18:39:39.277700
12.259013175964
2020-12-04 18:39:39.277800
2020-12-04 18:39:39.292200 import CSV start
2020-12-04 18:39:46.841400 import CSV end
2020-12-04 18:39:46.921200 export CSV start
2020-12-04 18:39:51.522900 export CSV end
2020-12-04 18:39:51.523000 compare CSV start
2020-12-04 18:39:51.686700 compare CSV end
2020-12-04 18:39:51.689100
12.411306858063
2020-12-04 18:39:51.689200
2020-12-04 18:39:51.703500 import CSV start
2020-12-04 18:39:59.070800 import CSV end
2020-12-04 18:39:59.149200 export CSV start
2020-12-04 18:40:03.809000 export CSV end
2020-12-04 18:40:03.809200 compare CSV start
2020-12-04 18:40:03.974900 compare CSV end
2020-12-04 18:40:03.977800
12.288660049438
2020-12-04 18:40:03.977900
2020-12-04 18:40:03.992100 import CSV start
2020-12-04 18:40:11.318800 import CSV end
2020-12-04 18:40:11.398800 export CSV start
2020-12-04 18:40:15.989500 export CSV end
2020-12-04 18:40:15.989500 compare CSV start
2020-12-04 18:40:16.154000 compare CSV end
2020-12-04 18:40:16.156600
12.178690910339
平均秒数:11.787367296219
MySQL 5.7 + Ruby 2.7.2
"Ruby 2.7.2"
"2020-12-04 18:43:08.038825"
"2020-12-04 18:43:08.053449 import CSV start"
"2020-12-04 18:43:16.653697 import CSV end"
"2020-12-04 18:43:16.683815 export CSV start"
"2020-12-04 18:43:17.118668 export CSV end"
"2020-12-04 18:43:17.118718 compare CSV start"
"2020-12-04 18:43:19.045548 compare CSV end"
"2020-12-04 18:43:19.045579"
"11.006754"
"2020-12-04 18:43:19.045597"
"2020-12-04 18:43:19.059034 import CSV start"
"2020-12-04 18:43:27.713840 import CSV end"
"2020-12-04 18:43:27.743303 export CSV start"
"2020-12-04 18:43:28.170391 export CSV end"
"2020-12-04 18:43:28.170440 compare CSV start"
"2020-12-04 18:43:30.270370 compare CSV end"
"2020-12-04 18:43:30.270421"
"11.224824"
"2020-12-04 18:43:30.270443"
"2020-12-04 18:43:30.284968 import CSV start"
"2020-12-04 18:43:39.172574 import CSV end"
"2020-12-04 18:43:39.201146 export CSV start"
"2020-12-04 18:43:39.655885 export CSV end"
"2020-12-04 18:43:39.655931 compare CSV start"
"2020-12-04 18:43:41.750329 compare CSV end"
"2020-12-04 18:43:41.750426"
"11.479984"
"2020-12-04 18:43:41.750587"
"2020-12-04 18:43:41.765985 import CSV start"
"2020-12-04 18:43:50.794890 import CSV end"
"2020-12-04 18:43:50.823680 export CSV start"
"2020-12-04 18:43:51.273359 export CSV end"
"2020-12-04 18:43:51.273473 compare CSV start"
"2020-12-04 18:43:53.387215 compare CSV end"
"2020-12-04 18:43:53.387289"
"11.636703"
"2020-12-04 18:43:53.387634"
"2020-12-04 18:43:53.403591 import CSV start"
"2020-12-04 18:44:02.679752 import CSV end"
"2020-12-04 18:44:02.708117 export CSV start"
"2020-12-04 18:44:03.200543 export CSV end"
"2020-12-04 18:44:03.200570 compare CSV start"
"2020-12-04 18:44:05.416933 compare CSV end"
"2020-12-04 18:44:05.416986"
"12.029352"
"2020-12-04 18:44:05.417084"
"2020-12-04 18:44:05.433885 import CSV start"
"2020-12-04 18:44:14.360653 import CSV end"
"2020-12-04 18:44:14.393191 export CSV start"
"2020-12-04 18:44:14.846919 export CSV end"
"2020-12-04 18:44:14.846964 compare CSV start"
"2020-12-04 18:44:17.096839 compare CSV end"
"2020-12-04 18:44:17.096873"
"11.679788"
"2020-12-04 18:44:17.096890"
"2020-12-04 18:44:17.115601 import CSV start"
"2020-12-04 18:44:26.344854 import CSV end"
"2020-12-04 18:44:26.375012 export CSV start"
"2020-12-04 18:44:26.924188 export CSV end"
"2020-12-04 18:44:26.924239 compare CSV start"
"2020-12-04 18:44:29.146173 compare CSV end"
"2020-12-04 18:44:29.146230"
"12.049340"
"2020-12-04 18:44:29.146314"
"2020-12-04 18:44:29.161678 import CSV start"
"2020-12-04 18:44:38.720743 import CSV end"
"2020-12-04 18:44:38.753229 export CSV start"
"2020-12-04 18:44:39.245264 export CSV end"
"2020-12-04 18:44:39.245313 compare CSV start"
"2020-12-04 18:44:41.506689 compare CSV end"
"2020-12-04 18:44:41.506787"
"12.360473"
"2020-12-04 18:44:41.507112"
"2020-12-04 18:44:41.521976 import CSV start"
"2020-12-04 18:44:51.120442 import CSV end"
"2020-12-04 18:44:51.151450 export CSV start"
"2020-12-04 18:44:51.659697 export CSV end"
"2020-12-04 18:44:51.659790 compare CSV start"
"2020-12-04 18:44:53.925933 compare CSV end"
"2020-12-04 18:44:53.925989"
"12.418878"
"2020-12-04 18:44:53.926027"
"2020-12-04 18:44:53.942581 import CSV start"
"2020-12-04 18:45:03.745834 import CSV end"
"2020-12-04 18:45:03.781359 export CSV start"
"2020-12-04 18:45:04.277777 export CSV end"
"2020-12-04 18:45:04.277939 compare CSV start"
"2020-12-04 18:45:06.443145 compare CSV end"
"2020-12-04 18:45:06.443212"
"12.517185"
"平均秒数:11.840328"
MySQL 5.7 + Go 1.15.5
Go go1.15.5
2020-12-04 18:32:56.731407
2020-12-04 18:32:56.770553 import CSV start
2020-12-04 18:33:06.746475 import CSV end
2020-12-04 18:33:06.747334 export CSV start
2020-12-04 18:33:11.803909 export CSV end
2020-12-04 18:33:11.803947 compare CSV start
2020-12-04 18:33:11.835890 compare CSV end
2020-12-04 18:33:11.835999
15.1045939 (0x0,0x0)
2020-12-04 18:33:11.836135
2020-12-04 18:33:11.864054 import CSV start
2020-12-04 18:33:21.292816 import CSV end
2020-12-04 18:33:21.293667 export CSV start
2020-12-04 18:33:26.140855 export CSV end
2020-12-04 18:33:26.140887 compare CSV start
2020-12-04 18:33:26.163898 compare CSV end
2020-12-04 18:33:26.164127
14.3279929 (0x0,0x0)
2020-12-04 18:33:26.164199
2020-12-04 18:33:26.180394 import CSV start
2020-12-04 18:33:35.627523 import CSV end
2020-12-04 18:33:35.628442 export CSV start
2020-12-04 18:33:40.295815 export CSV end
2020-12-04 18:33:40.295867 compare CSV start
2020-12-04 18:33:40.321120 compare CSV end
2020-12-04 18:33:40.321268
14.1570699 (0x0,0x0)
2020-12-04 18:33:40.321443
2020-12-04 18:33:40.340157 import CSV start
2020-12-04 18:33:50.046656 import CSV end
2020-12-04 18:33:50.049821 export CSV start
2020-12-04 18:33:54.967411 export CSV end
2020-12-04 18:33:54.967458 compare CSV start
2020-12-04 18:33:54.997644 compare CSV end
2020-12-04 18:33:54.998142
14.6766999 (0x0,0x0)
2020-12-04 18:33:54.998539
2020-12-04 18:33:55.019787 import CSV start
2020-12-04 18:34:05.519525 import CSV end
2020-12-04 18:34:05.522320 export CSV start
2020-12-04 18:34:10.696589 export CSV end
2020-12-04 18:34:10.696631 compare CSV start
2020-12-04 18:34:10.722159 compare CSV end
2020-12-04 18:34:10.722387
15.7238489 (0x0,0x0)
2020-12-04 18:34:10.722584
2020-12-04 18:34:10.739626 import CSV start
2020-12-04 18:34:21.561216 import CSV end
2020-12-04 18:34:21.561748 export CSV start
2020-12-04 18:34:26.668071 export CSV end
2020-12-04 18:34:26.668182 compare CSV start
2020-12-04 18:34:26.694219 compare CSV end
2020-12-04 18:34:26.694356
15.9717729 (0x0,0x0)
2020-12-04 18:34:26.694437
2020-12-04 18:34:26.712859 import CSV start
2020-12-04 18:34:36.890619 import CSV end
2020-12-04 18:34:36.891380 export CSV start
2020-12-04 18:34:41.626598 export CSV end
2020-12-04 18:34:41.627004 compare CSV start
2020-12-04 18:34:41.657552 compare CSV end
2020-12-04 18:34:41.657670
14.9632329 (0x0,0x0)
2020-12-04 18:34:41.657765
2020-12-04 18:34:41.674880 import CSV start
2020-12-04 18:34:51.565415 import CSV end
2020-12-04 18:34:51.566141 export CSV start
2020-12-04 18:34:56.747330 export CSV end
2020-12-04 18:34:56.747368 compare CSV start
2020-12-04 18:34:56.773639 compare CSV end
2020-12-04 18:34:56.774093
15.1163289 (0x0,0x0)
2020-12-04 18:34:56.776348
2020-12-04 18:34:56.793556 import CSV start
2020-12-04 18:35:08.849634 import CSV end
2020-12-04 18:35:08.850730 export CSV start
2020-12-04 18:35:14.480905 export CSV end
2020-12-04 18:35:14.480942 compare CSV start
2020-12-04 18:35:14.510017 compare CSV end
2020-12-04 18:35:14.510196
17.7338489 (0x0,0x0)
2020-12-04 18:35:14.510872
2020-12-04 18:35:14.532623 import CSV start
2020-12-04 18:35:25.683906 import CSV end
2020-12-04 18:35:25.684857 export CSV start
2020-12-04 18:35:30.976413 export CSV end
2020-12-04 18:35:30.976596 compare CSV start
2020-12-04 18:35:31.011995 compare CSV end
2020-12-04 18:35:31.012100
16.5012289 (0x0,0x0)
平均秒数:15.42766124 (0x0,0x0)