Node.js VS PHP PDO Insert MySQL

最近在考慮將公司某些專案的排程改到 node.js 上面，主要是程式本體的 driver 跟 MySQL 之間的效能，先講結論，如果有用 MVC 的人不要用內建的 ORM 跑大資料寫入的排程，雖然 ORM 慢是大家都知道的事情，畢竟他本來就是圖方便的工具，但沒想到大量資料寫入他可以慢到將近一倍，本測試執行 node.js、PHP PDO，一次跑 3000 個 insert 各跑十次，以下是寫法以及結果。

環境說明

1. PHP 5.6.15
2. MySQL Ver 15.1 Distrib 10.1.9-MariaDB
3. node.js 5.8.0
4. Windows 7 64bit
5. 8GB Ram

套件說明

1. npm-mysql
2. drealecs/time-benchmark

程式寫法

node.js

console.time('add');
console.log('start');

var mysql = require('mysql');
var connection = mysql.createConnection({
    host     : 'localhost',
    user     : '',
    password : '',
    database : 'test',
});
var length = process.argv[2] || 100;
var counter = 1;

connection.connect();

var add = (counter) => {
    connection.query('INSERT INTO `users` SET ?', {name: 'test'}, (err, rows, fields) => {
        if (err) {
            console.log(err);
        }

        if (counter < length) {
            add(++counter);
        } else {
            console.log('all done');
            console.timeEnd('add');
            connection.end();
        }
    });
};

add(counter);

PHP PDO

<?php

include 'vendor/autoload.php';

echo 'start'.PHP_EOL;

use TimeBenchmark\Stopwatch;

$length = isset($argv[1]) ? $argv[1] : 100;
$counter = 1;
$dsn = 'mysql:host=localhost;dbname=test;charset=utf8';
$db = new PDO($dsn, '');

$stopwatch = Stopwatch::create();
$stopwatch->start();

$add = function($counter) use ($length, $db, $stopwatch, &$add) {
    $sql = "INSERT INTO `users`(`name`) VALUES(:name)";
    $stmt = $db->prepare($sql);
    $stmt->bindValue(':name', 'test');
    $stmt->execute();

    if ($counter < $length) {
        $add(++$counter);
    } else {
        $stopwatch->stop();
        echo $stopwatch->getElapsedMilliseconds().'ms'.PHP_EOL;
    }
};

$add($counter);

測試結果

node.js

1. 75500.335ms
2. 76234.479ms
3. 60025.145ms
4. 68833.407ms
5. 71067.471ms
6. 63238.021ms
7. 63908.226ms
8. 57938.820ms
9. 66872.004ms
10. 66817.116ms

PHP PDO

1. 63311.331ms
2. 66825.682ms
3. 67424.741ms
4. 67320.732ms
5. 62134.213ms
6. 65586.558ms
7. 67117.711ms
8. 54427.443ms
9. 66046.604ms
10. 67741.773ms

結論

其實數據沒有差很多，但 node.js 比較會飄，所以還是觀察一下吧。

$.Deferred VS Promise

Promise 是非常好用的一個特性，主要用來處理 async 的工作，在 jQuery 上很早就有 deferred 的實作，而 JavaScript ES6 也把這個功能加了進來，而且寫法非常清爽，但 ES6 要普及應該還要一段時間，jQuery 的 deferred 比較沒有相容性的問題，在這邊列一下同樣功能怎樣在雙邊實現。

單一運用

$.Deferred

let response = () => {
    return $.Deferred(dfd => {
        setTimeout(() => {
            dfd.resolve('done');
        }, 2000)
    }).promise();
}

$.when(response()).then(response => {
    console.log(response);
});

// or

let response = () => {
    let dfd = $.Deferred();

    setTimeout(() => {
        dfd.resolve('done');
    }, 2000)

    return dfd.promise();
}

$.when(response()).then(response => {
    console.log(response);
});

Promise

let response = () => {
    return new Promise((resolve, reject) => {
        setTimeout(() => {
            resolve('done');
        }, 2000);
    });
}

response().then(response => {
    console.log(response);
});

等待多重結果

$.Deferred

let results = [];

[2000, 3000, 4000].forEach(timer => {
    results.push($.Deferred(dfd => {
        setTimeout(() => {
            console.log('single done!');
            dfd.resolve();
        }, timer);
    }));
});

$.when.apply($, results).then(() => {
    console.log('all done');
});

promise

let promises = [];

[2000, 3000, 4000].forEach(timer => {
    promises.push(new Promise(resolve => {
        setTimeout(() => {
            console.log('single promise done!');
            resolve();
        }, timer);
    }));
});

Promise.all(promises).then(() => {
    console.log('promise all done');
});

apt and yum

purpose	apt	aptitude	yum
update repo list	apt-get update	aptitude update	none
install package	apt-get install package_name	aptitude install package_name	yum install package_name
intall from file	dpkg -i package.deb		yum install package.rpm rpm -ivh package.rpm
remove package	apt-get remove package_name	aptitude remove package_name	yum remove package_name
check outdated package	apt-get -s upgrade	aptitude -s upgrade	yum check-update
check single outdated package	apt-get -s upgrade packge_name apt-cache policy package_name	aptitude -s upgrade package_name	yum check-update package_name
update package	apt-get upgrade package_name	aptitude package_name	yum update package_name rpm -Uvh package_name
search package	apt-cache search package_name	aptitude search package_name	yum search package_name
list installed package	dpkg -l		yum list installed rpm -qa
show package information	apt-cache show package_name	aptitude show package_name	yum info package_name
remove packages from the local cache directory	apt-get clean	aptitude clean	yum clean all

Reference

ubuntu.com