皆さん、こんばんは。笹亀です。
7月もあっという間に10日間が過ぎて、夏真っ盛りになってきました。
自分も夏対策でアイス眠というマットレスを購入して夏を乗り越えようと思っております。
さて、本日はストアドプロシージャについて検証をしてみたいと思います。
ストアドプロシージャとは、一連のSQL文や処理に名前をつけて保存したものです。
PHPの関数と同じでSQLを関数みたいにしたものがストアドプロシージャになります。
MySQLではストアドプロシージャはMySQLでは5.0から利用が可能です。
今回はMySQLのストアドプロシージャの使い方だけではなく、
生PHPで記載したときとのパフォーマンスを比較してみたいと思います。
MySQLのストアドプロシージャを呼び出して処理をするPHPプログラムとストアドプロシージャで作成したものと同じSQLを実行するPHPプログラムを作成します。
上記２つのプログラムを前回のブログで紹介したJMeterで負荷をかけて、パフォーマンス値を検証してみたいと思います。
まずはテスト用のデータ保存用のテーブルを作成します。
CREATE TABLE `t_test_procedure` (
`id` int(20) NOT NULL AUTO_INCREMENT,
`body` text,
`created_at` datetime,
`updated_at` datetime,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
次にMySQL用の簡単なストアドプロシージャを作成します。
※IDをSELECTしてそのIDのデータがあればUPDATE、なければINSERTする
delimiter $$
DROP PROCEDURE IF EXISTS TEST_PROCEDURE$$
-- usage : call TEST_PROCEDURE('1', 'てきすとデータほげほげ', @code);
-- usage : call TEST_PROCEDURE('2', 'てきすとデータほげほげ', @code);
/*
* テスト用のプロシジャープログラム
IDでSELECTして同じものがあれば更新するプロシジャー
*/
CREATE PROCEDURE TEST_PROCEDURE (
IN v_id BIGINT, -- ID
IN v_text TEXT, -- テストテキスト
OUT code TEXT -- 結果コード
)
ESC:BEGIN
DECLARE cnt BIGINT;
DECLARE EXIT HANDLER FOR SQLWARNING, SQLEXCEPTION, NOT FOUND
BEGIN
SET code = '-99';
END;
/* データ既に登録されているか確認 */
SELECT COUNT(id) INTO cnt FROM t_test_procedure
WHERE id = v_id;
/* 登録されていない場合はINSERTそれ以外はUPDATE */
IF cnt = 0 THEN
INSERT INTO t_test_procedure VALUES (v_id, v_text, now(), now());
ELSE
UPDATE t_test_procedure SET body = v_text, updated_at = now() WHERE id = v_id;
END IF;
SET code = '0'; -- 正常コードを返す
END;
$$
delimiter ;
次にプロシージャを実行するPHPプログラムとプロシージャを展開したPHPプログラムを準備します。
■プロシージャ版
<?php
$dsn = 'mysql:dbname=test;host=localhost;unix_socket=/tmp/mysql.sock';
$user = 'root';
$password = 'pass';
try{
$dbh = new PDO($dsn, $user, $password);
}catch (PDOException $e){
print('Error:'.$e->getMessage());
die();
}
$stamp = microtime();
list($msec, $sec) = explode(" ", $stamp);
$text ='TESTTEST String BODY DATE = ';
$text .= date('Y/m/d H:i:s.') . (float)$msec;
$dbh->query("START TRANSACTION");
try {
$sql = 'call TEST_PROCEDURE(?,?,@res)';
$sth = $dbh->prepare($sql);
$sth->execute(array($_GET['id'], $text));
} catch(Exception $e) {
$dbh->query("ROLLBACK");
exit;
}
$dbh->query("COMMIT");
//$sth = $dbh->prepare("SELECT @res");
//$sth->execute();
//$result = $sth->fetchAll();
var_dump($result[0][0]);
$dbh = null;
■展開版
<?php
$dsn = 'mysql:dbname=test;host=localhost;unix_socket=/tmp/mysql.sock';
$user = 'root';
$password = 'pass';
try{
$dbh = new PDO($dsn, $user, $password);
}catch (PDOException $e){
print('Error:'.$e->getMessage());
die();
}
$stamp = microtime();
list($msec, $sec) = explode(" ", $stamp);
$text ='TESTTEST String BODY DATE = ';
$text .= date('Y/m/d H:i:s.') . (float)$msec;
$dbh->query("START TRANSACTION");
try {
$sth = $dbh->prepare("SELECT count(id) FROM t_test_procedure WHERE id = ?");
$sth->execute(array($_GET['id']));
$result = $sth->fetchAll();
if ($result[0][0] == 0) {
$sql = 'INSERT INTO t_test_procedure VALUES(?, ?, now(), now())';
$sth = $dbh->prepare($sql);
$sth->execute(array($_GET['id'], $text));
} else {
$sql = 'UPDATE t_test_procedure SET body=?, updated_at=now() WHERE id=?';
$sth = $dbh->prepare($sql);
$sth->execute(array($text, $_GET['id']));
}
} catch(Exception $e) {
$dbh->query("ROLLBACK");
exit;
}
$dbh->query("COMMIT");
$dbh = null;
上記２つのプログラムを実行するそれぞれのテストケースをJMeterで作成します。JMeterについては前回ブログに使い方などを記載しておりますので、参考にしてください。
※1〜20,000までのIDを発行して登録して１〜20,000までを発行しおわった後に更新するテストケースを作成します（。
同時アクセス100で200回処理（計：20,000アクセス）をさせました結果を表示します。
注目する箇所は「Throughput」部分です。
プロシージャ版の負荷テスト結果
Throughput: 248.5/sec（合計値
展開版の負荷テスト結果
Throughput: 231.0/sec（合計値
予想ではプロシージャの方が処理が遅いと思っていましたが、展開版の方が時間が処理能力がよくありませんでした。
以前のプロジェクトで複雑な処理をしたプロシージャがあり、負荷テストをした際にそのプロシージャの実行に時間がかかっておりました。対応として今回の検証のようにPHPで展開したものを新たに用意して両方の負荷テストしました。その際にはPHPで展開して実装した方が2倍くらいの処理能力が向上しました。その経緯があったので展開版の方が処理能力がいいと思っておりました。
今回の検証でシンプルな処理をまとめてするのであればプロシージャを利用してもさほど処理能力は変わらないとがわかりました！ただ、プロシージャに頼り過ぎて複雑にSQL文と処理を組み込んでしまうとパフォーマンス低下につながりますので、うまく使い分けをして利用することをお薦め致します＾＾