さて、ちょっと予定には無かったんですけど、
最近この辺を考える事がやけに多く、
ということで、こんな記事を書いてみようかなぁと思います。
ちなみに、非エンジニアがエンジニアを評価（出来るのか？って話もありますが）するにあたって、
チューニングをした結果の「レスポンスが１０秒→１秒になった」というのは、
スキルジャッジとしては分かりやすい指標だろうなぁとは思うので、
そういう意味でも「チューニング実績」というのは良いのかもと思いますね。
では、本題です。
が、急いでいる人？の為に、結論を先に。
１．JOINが出来るなら、まずはJOINを検討。あと、OFFSETとLIMITが使えるなら、よりベター。
↓
２．JOINが出来ないなら、NoSQLを上手く使う。その際、出来るだけループ内でデータ取得処理を行わない様に工夫する
で、基本は、上記の流れで良いんじゃぁないかと。
というのが結論なので、どうしてこういう結論になったかは、以下を読んでくださいということで。
あと、最近読んでいる以下の本（かなり面白い）を見て思ったんですけど、
チューニングと一言でいっても色々あるわけでして。
で、今回はタイトルにもある通り、
プログラム（といっても大体SQLのSELECT関連）サイドのチューニングになります。
www.amazon.co.jp
あと、以前に以下の論理削除の記事でも若干触れた、
「マスタデータを何処に持つか」という話も若干絡みますね。
kitoku-magic.hatenablog.com
あと、地味かもしれないですが、
以下の記事もかなり役に立ってます。
というか、この記事見て完全にピンと来たんですけどね。
php-sql-gdgd.jugem.jp
では、「どういう時のボトルネックに対するチューニングなのか」ですが、
「ユーザーIDをキーにして、DBのトランザクションデータからデータを取得（データA）して、その後データAの中のマスタデータのIDをキーにしてマスタデータからデータを取得（データB）する」という様なプログラムです。
勿論、更に続きでデータC・データDと取得したりする事もザラではありますけど。
で、プログラム自体はよくあるプログラムだと思うので、
結構色々な環境で使えるんじゃないかとも思うんですよね。
じゃなきゃ、わざわざ書かないんですけどね（笑）
では、サンプルプログラム（合計９パターン）のご紹介です。
殆ど変わらないのもあるんで、５～６パターンでも良い気もするんですが、折角なので全部。
先にパターンと実行平均時間と高速度順の順位だけ紹介しておきますと、以下の通りです。
【１．トランザクションデータとマスタデータをJOINする】0.0067554741秒（１位）
【２．DBサーバーが別だとJOIN出来ないので、クエリを分ける】0.5855079975秒（８位）
【３．DBサーバーが別だとJOIN出来ないので、クエリを分ける ＆ MySQL複数接続】0.6397109158秒（９位）
【４．マスタをMemcachedにキャッシュする】0.5397023411秒（７位）
【５．マスタをMemcachedにキャッシュする ＆ キャッシュのキーが一つ】0.0144977431秒（２位）
【６．マスタをRedisにキャッシュする ＆ キャッシュのキーが一つ】0.015953728秒（３位）
【７．先にマスタデータを全件取っておく】0.1823107789秒（６位）
【８．マスタデータを事前にAPCに突っ込んでおく】0.122105834秒（４位）
【９．マスタデータを事前にRedisに突っ込んでおく】0.180010453秒（５位）
まずは、DB関連から。
【０．テーブル定義とデータ投入】
// トランザクションデータが入るテーブル
CREATE TABLE `test` (
`user_id` int(11) NOT NULL,
`code` int(11) NOT NULL,
PRIMARY KEY (`user_id`, `code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
// マスタデータが入るテーブル
CREATE TABLE `code_master` (
`code` int(11) NOT NULL,
`name` varchar(100) NOT NULL,
PRIMARY KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
// データ投入シェルスクリプト
file_name='insert_code_master.csv'
for i in `seq 1 100000`
do
echo "${i},${i}" >> $file_name
done
file_name='insert_test.csv'
for i in `seq 1 100000`
do
for j in `seq 1 1000`
do
echo "${i},${j}" >> $file_name
done
done
LOAD DATA LOCAL INFILE 'insert_code_master.csv' INTO TABLE code_master FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'insert_test.csv' INTO TABLE test FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
まず、ここまでです。
ちなみに、以下のインデックスも張ろうかと思いましたが、
結果的に誤差レベルの差で殆ど変わらなかった（今回のクエリではですが）ので、
張ってません。
CREATE INDEX test_code ON test (code);
次に、以下から実際のコードになりますが、
実行方法は以下の様な事をして、EXCELで平均秒を算出しています。
■実行方法
for i in `seq 1 1 1000`; do php {PHPファイル名}.php >> result_{PHPファイル名}.xls; done &
// apcuの時のみ以下
for i in `seq 1 1 1000`; do curl http://localhost/apcu.php >> result_apcu.xls; done &
【１．トランザクションデータとマスタデータをJOINする】
<?php
ini_set('memory_limit', -1);
$conn = new mysqli('DBホスト名', 'ユーザー名', 'パスワード', 'データベース名', 'ポート番号');
if (!$conn) {
die('データベース接続エラー: ' . mysql_error());
exit();
}
$user_id = 859;
$start = microtime(true);
$result_array = array();
$sql = 'SELECT cm.name FROM test t LEFT JOIN code_master cm ON t.code = cm.code WHERE t.user_id = ?';
$stmt = $conn->prepare($sql);
$stmt->bind_param('i', $user_id);
$stmt->execute();
$stmt->bind_result($name);
while (true === $stmt->fetch())
{
$result_array[] = $name;
}
echo microtime(true) - $start . "\n";
1000回平均：0.0067554741秒
う～ん、速い？
というか、他のプログラムと比較しないと分からないので、次に。
【２．DBサーバーが別だとJOIN出来ないので、クエリを分ける】
<?php
ini_set('memory_limit', -1);
$conn = new mysqli('DBホスト名', 'ユーザー名', 'パスワード', 'データベース名', 'ポート番号');
if (!$conn) {
die('データベース接続エラー: ' . mysql_error());
exit();
}
$user_id = 859;
$start = microtime(true);
$result_array = array();
$sql = 'SELECT code FROM test WHERE user_id = ?';
$stmt = $conn->prepare($sql);
$stmt->bind_param('i', $user_id);
$stmt->execute();
$stmt->bind_result($code);
$code_array = array();
while (true === $stmt->fetch())
{
$code_array[] = $code;
}
$stmt->close();
$sql = "SELECT name FROM code_master WHERE code = ?";
$stmt = $conn->prepare($sql);
foreach ($code_array as $code)
{
$stmt->bind_param('i', $code);
$stmt->execute();
$stmt->bind_result($name);
while (true === $stmt->fetch())
{
$result_array[] = $name;
}
}
echo microtime(true) - $start . "\n";
1000回平均：0.5855079975秒
あら、JOINに比べてだいぶ遅くなりました。
ちなみに遅い原因は、「ループの中でSQL投げてるから」でしょう。
一回一回のSQLは「軽い」と思います（上記だとPKのインデックス効いてますし）けど、
でも「千回も一万回」も投げたら流石にねぇと。
ところが、つい先日までの僕自身の書き方もそうだったんですけど、
こういうプログラム、かなりの現場に潜んでいるんじゃなかろうか？と。
というか、これまでの経験だけでも、結構見覚えが・・・
いや、勿論この速度はデータ量にもよりますし、
そもそも0.6秒が問題にならないシステムなら良いんですけど、
案件によってはね・・・っていう。
ひとまず、次にいきましょう。
【３．DBサーバーが別だとJOIN出来ないので、クエリを分ける ＆ MySQL複数接続】
<?php
ini_set('memory_limit', -1);
$conn = new mysqli('DBホスト名', 'ユーザー名', 'パスワード', 'データベース名', 'ポート番号');
if (!$conn || $conn->connect_errno) {
die('データベース接続エラー: ' . $conn->connect_error);
exit();
}
$user_id = 859;
$start = microtime(true);
$result_array = array();
$sql = 'SELECT code FROM test WHERE user_id = ?';
$stmt = $conn->prepare($sql);
$stmt->bind_param('i', $user_id);
$stmt->execute();
$stmt->bind_result($code);
$conn1 = new mysqli('DBホスト名', 'ユーザー名', 'パスワード', 'データベース名', 'ポート番号');
if (!$conn1 || $conn1->connect_errno) {
die('データベース接続エラー: ' . $conn1->connect_error);
exit();
}
$sql = "SELECT name FROM code_master WHERE code = ?";
$stmt1 = $conn1->prepare($sql);
while (true === $stmt->fetch())
{
$stmt1->bind_param('i', $code);
$stmt1->execute();
$stmt1->bind_result($name);
while (true === $stmt1->fetch())
{
$result_array[] = $name;
}
}
echo microtime(true) - $start . "\n";
1000回平均：0.6397109158秒
こちらは、２番のプログラムの中の「$code_array」配列に要素を突っ込むのと、
その後に、$code_array配列内の要素を一つずつ取り出す部分の、
「繰り返し処理を一つにまとめた」パターンなんですが、
１ユーザーでDB接続数２以上って事で、逆に遅くなってますかね。
どっちにしても、１ユーザーでDB接続数２以上って時点で速くてもイヤですけど。
ちなみに、繰り返し処理を一つにまとめる他の方法として、
２番のプログラムの最初の$stmt->fetch()辺りの箇所を、
$stmt->get_result()とすれば、また同じ$stmt変数使えて繰り返し処理も一つに出来ます。
ただ、それをしても大体「0.59秒」ぐらいということで、特に変化無かったですね。
では、次。
【４．マスタをMemcachedにキャッシュする】
<?php
ini_set('memory_limit', -1);
$conn = new mysqli('DBホスト名', 'ユーザー名', 'パスワード', 'データベース名', 'ポート番号');
if (!$conn) {
die('データベース接続エラー: ' . mysql_error());
exit();
}
$user_id = 859;
$start = microtime(true);
$result_array = array();
$sql = 'SELECT code FROM test WHERE user_id = ?';
$stmt = $conn->prepare($sql);
$stmt->bind_param('i', $user_id);
$stmt->execute();
$stmt->bind_result($code);
$code_array = array();
while (true === $stmt->fetch())
{
$code_array[] = $code;
}
$stmt->close();
$sql = "SELECT name FROM code_master WHERE code = ?";
$memcached = new Memcached();
$memcached->addServer('Memcachedホスト名', ポート番号);
foreach ($code_array as $code)
{
$cache_key = $user_id . '_' . $code;
$name = $memcached->get($cache_key);
if (false === $name)
{
$stmt = $conn->prepare($sql);
$stmt->bind_param('i', $code);
$stmt->execute();
$stmt->bind_result($name);
while (true === $stmt->fetch())
{
$result_array[] = $name;
$memcached->set($cache_key, $name);
}
}
else
{
$result_array[] = $name;
}
}
echo microtime(true) - $start . "\n";
1000回平均：0.5397023411秒
おお、流石Memcachedと言いたい所ですけど、
でも、それでも「JOINより遅い」んですよね。
・・・ふと思ったんですけど、
これ「ネットワークの通信（あるいは回線？）速度（あるいは回数？）」も、結構影響大きいのでは？（今回、Web・DB・KVS全てが別サーバ）とも思いますね。
いや、今回その辺までは調べてませんけど。
ひとまず、次に。
【５．マスタをMemcachedにキャッシュする ＆ キャッシュのキーが一つ】
<?php
ini_set('memory_limit', -1);
$conn = new mysqli('DBホスト名', 'ユーザー名', 'パスワード', 'データベース名', 'ポート番号');
if (!$conn) {
die('データベース接続エラー: ' . mysql_error());
exit();
}
$user_id = 859;
$start = microtime(true);
$result_array = array();
$sql = 'SELECT code FROM test WHERE user_id = ?';
$stmt = $conn->prepare($sql);
$stmt->bind_param('i', $user_id);
$stmt->execute();
$stmt->bind_result($code);
$code_array = array();
while (true === $stmt->fetch())
{
$code_array[] = $code;
}
$stmt->close();
$sql = "SELECT name FROM code_master WHERE code = ?";
$memcached = new Memcached();
$memcached->addServer('Memcachedホスト名', ポート番号);
$cache_key = 'code_master' . '_' . $user_id;
$name_array = array();
$result = $memcached->get($cache_key);
foreach ($code_array as $code)
{
if (false === $result || false === isset($result[$code]))
{
$stmt = $conn->prepare($sql);
$stmt->bind_param('i', $code);
$stmt->execute();
$stmt->bind_result($name);
while (true === $stmt->fetch())
{
$name_array[$code] = $name;
$result_array[] = $name;
}
}
else
{
$name_array[$code] = $result[$code];
$result_array[] = $result[$code];
}
}
if (0 < count($name_array))
{
$memcached->set($cache_key, $name_array);
}
echo microtime(true) - $start . "\n";
1000回平均：0.0144977431秒
あら～なんと、これはJOINとほぼ同じですね。
ただ、このプログラムの問題は「メモリ食う」ですね。
・・・あ、これ、$resultと$name_arrayは変数を一つにまとめても問題無さそうですね。
これを書いている途中で気付きました（苦笑）
ということで、まだ速くなりそうですし、別にメモリもさほど食わないかも（苦笑）
あと、ついでにもう一つ書いてる途中に気付いた事として、
ループ内で「$stmt = $conn->prepare($sql);」と書いてある場合は、
大抵はループ外に出せますね（元々が「準備された文」だから当然か）。
ひとまず、次行きましょう。
【６．マスタをRedisにキャッシュする ＆ キャッシュのキーが一つ】
<?php
ini_set('memory_limit', -1);
$conn = new mysqli('DBホスト名', 'ユーザー名', 'パスワード', 'データベース名', 'ポート番号');
if (!$conn) {
die('データベース接続エラー: ' . mysql_error());
exit();
}
$user_id = 859;
$start = microtime(true);
$result_array = array();
$sql = 'SELECT code FROM test WHERE user_id = ?';
$stmt = $conn->prepare($sql);
$stmt->bind_param('i', $user_id);
$stmt->execute();
$stmt->bind_result($code);
$code_array = array();
while (true === $stmt->fetch())
{
$code_array[] = $code;
}
$stmt->close();
$sql = "SELECT name FROM code_master WHERE code = ?";
$name_array = array();
$redis = new Redis();
$redis->connect('Redisホスト名', ポート番号);
$cache_key = 'code_master' . '_' . $user_id;
$result = $redis->get($cache_key);
if (false !== $result)
{
$result = json_decode($result, true);
}
foreach ($code_array as $code)
{
if (false === $result || false === isset($result[$code]))
{
$stmt = $conn->prepare($sql);
$stmt->bind_param('i', $code);
$stmt->execute();
$stmt->bind_result($name);
while (true === $stmt->fetch())
{
$name_array[$code] = $name;
$result_array[] = $name;
}
}
else
{
$name_array[$code] = $result[$code];
$result_array[] = $result[$code];
}
}
if (0 < count($name_array))
{
$redis->set($cache_key, json_encode($name_array));
}
echo microtime(true) - $start . "\n";
1000回平均：0.015953728秒
Memcachedと殆ど同じという事で。
ただ、こっちはjson_encode & decode（Redisの他のデータ型なら不要かもしれないですけど）の分、
処理がメンドイと。
っていうのもあるし、Redisは無駄に多機能な事を考えても、
今回の様な用途なら、Memcachedの方が良いのではとは思いますね。
次。
【７．先にマスタデータを全件取っておく】
<?php
ini_set('memory_limit', -1);
$conn = new mysqli('DBホスト名', 'ユーザー名', 'パスワード', 'データベース名', 'ポート番号');
if (!$conn) {
die('データベース接続エラー: ' . mysql_error());
exit();
}
$user_id = 859;
$start = microtime(true);
$result_array = array();
$sql = 'SELECT code FROM test WHERE user_id = ?';
$stmt = $conn->prepare($sql);
$stmt->bind_param('i', $user_id);
$stmt->execute();
$stmt->bind_result($code);
$code_array = array();
while (true === $stmt->fetch())
{
$code_array[$code] = $code;
}
$stmt->close();
$redis = new Redis();
$redis->connect('Redisホスト名', ポート番号);
$redis_key = 'code_master' . '_' . $user_id;
$all_master_data = $redis->get($redis_key);
if (false === $all_master_data)
{
$all_master_data = array();
$sql = "SELECT code, name FROM code_master";
$stmt = $conn->prepare($sql);
$stmt->execute();
$stmt->bind_result($code, $name);
while (true === $stmt->fetch())
{
$all_master_data[$code] = $name;
}
if (0 < count($all_master_data))
{
$redis->set($redis_key, json_encode($all_master_data));
}
}
else
{
$all_master_data = json_decode($all_master_data, true);
}
foreach ($code_array as $code)
{
$result_array[] = $all_master_data[$code];
}
echo microtime(true) - $start . "\n";
1000回平均：0.1823107789秒
さて、こちらは前述の通り「ループ内でSQLを投げる」のが遅いということなので、
「ループ外でSQL１回投げて全件取って、ループ内でSQLは投げない様にする」という改良を入れたプログラムです。
で、上記の平均はRedisのキャッシュ効いている状態の平均ですが、
このプログラムをRedisだけ使わない様に修正しても、「0.35秒ぐらい」ということで、
以下の３つがAND条件で全てYesの場合には、
今回のパターン内では一番速い（ただ、マスタ全件を配列に格納するので、メモリ不足には注意）ですね。
・JOIN出来ない
・KVSも無い
・後述のAPCも無い
ということで、まぁまぁオススメかと。
では、次。
【８．マスタデータを事前にAPCに突っ込んでおく】
<?php
ini_set('memory_limit', -1);
$conn = new mysqli('DBホスト名', 'ユーザー名', 'パスワード', 'データベース名', 'ポート番号');
if (!$conn) {
die('データベース接続エラー: ' . mysql_error());
exit();
}
$user_id = 859;
$apc_key = 'code_master' . '_' . $user_id;
$all_master_data = apc_fetch($apc_key, $success);
if (false === $all_master_data || false === $success)
{
$all_master_data = array();
$sql = "SELECT code, name FROM code_master";
$stmt = $conn->prepare($sql);
$stmt->execute();
$stmt->bind_result($code, $name);
while (true === $stmt->fetch())
{
$all_master_data[$code] = $name;
}
if (0 < count($all_master_data))
{
apc_store($apc_key, $all_master_data);
}
$stmt->close();
}
$start = microtime(true);
$result_array = array();
$sql = 'SELECT code FROM test WHERE user_id = ?';
$stmt = $conn->prepare($sql);
$stmt->bind_param('i', $user_id);
$stmt->execute();
$stmt->bind_result($code);
while (true === $stmt->fetch())
{
$result_array[] = $all_master_data[$code];
}
echo microtime(true) - $start . "\n";
1000回平均：0.122105834秒
まず、このプログラムのマスタから「事前に」って言ってる所についての補足ですが、
「事前に」っていうのは、この「個別」処理を行う前の「共通」処理で事前に行うって意味です。
なので、このプログラムは、「$start = microtime(true);」の位置が途中からになっています。
その点、ご注意を。
で、まぁ、速度的に悪くはないわけですけど、
「マスタ全件をキャッシュ」よりは、
「存在したマスタのID分のデータだけキャッシュ」の方が当然、効率は良いですよね。
ただ、このプログラム「だけ」ならそうなんですけど、「全体」で見た場合にはどうかなと。
「共有可能なデータ」は、共有した方が良いでしょうし。
まぁ、共有したけど使われない処理が殆どだったってケースも考えられるので、「共有するデータの選定」がポイントでしょうけど。
あと、APCに関しては、チラっとしか見てないですけど、
キャッシュがクリアされないだの何だのといったトラブル事例（回避策も同時に載っていた記憶もかすかにあるが）もチラホラ見かけましたので、
まぁ、あんまりじゃあないかという気もしますね。
あと、APCってWebサーバ毎にキャッシュ持つと思うんですけど、
そうすると、キャッシュのヒット率は低いのではってのもありますかね？
なので、特に理由無ければMemcachedなどの方が良いかなぁと。
じゃあ、次で最後です。
【９．マスタデータを事前にRedisに突っ込んでおく】
<?php
ini_set('memory_limit', -1);
$conn = new mysqli('DBホスト名', 'ユーザー名', 'パスワード', 'データベース名', 'ポート番号');
if (!$conn) {
die('データベース接続エラー: ' . mysql_error());
exit();
}
$user_id = 859;
$start = microtime(true);
$redis = new Redis();
$redis->connect('Redisホスト名', ポート番号);
$cache_key = 'code_master' . '_' . $user_id;
$all_master_data = $redis->get($cache_key);
if (false === $all_master_data)
{
$all_master_data = array();
$sql = "SELECT code, name FROM code_master";
$stmt = $conn->prepare($sql);
$stmt->execute();
$stmt->bind_result($code, $name);
while (true === $stmt->fetch())
{
$all_master_data[$code] = $name;
}
if (0 < count($all_master_data))
{
$redis->set($cache_key, json_encode($all_master_data));
}
$stmt->close();
}
else
{
$all_master_data = json_decode($all_master_data, true);
}
$result_array = array();
$sql = 'SELECT code FROM test WHERE user_id = ?';
$stmt = $conn->prepare($sql);
$stmt->bind_param('i', $user_id);
$stmt->execute();
$stmt->bind_result($code);
while (true === $stmt->fetch())
{
$result_array[] = $all_master_data[$code];
}
echo microtime(true) - $start . "\n";
1000回平均：0.180010453秒
APCをRedisに変えただけなので特に。
Redisの方が遅いのは意外でしたけど。
ということで、結論に。
【結論】
１．JOINが出来るなら、まずはJOINを検討。あと、OFFSETとLIMITが使えるなら、よりベター。
↓
２．JOINが出来ないなら、NoSQLを上手く使う。その際、出来るだけループ内でデータ取得処理を行わない様に工夫する
基本は、上記の流れで良いんじゃぁないかと。
あと、とにかく重要なのが、「ループ内でデータ取得（SQLも、NoSQLのget()も）を出来るだけ控えろ」という点。
更に、画面設計的にページング（スマホだとスクロールが同義語？）処理にするとかも大事ですね、って基本ですけど。
あと最後に、チューニングにおける「鉄則中の鉄則」ですけど、
「推測するな、計測せよ」ってのは、
「JOINが遅いのも、ケースバイケース」ということからも感じた所ですので、
胸に刻み込んで、日々の業務に携わるのが良いんじゃないかと思いましたね。
いつもの事ですが、何かご意見やご感想などありましたら、遠慮無くいただければと思います。
