twitter
facebook
InsTagram
YouTubeサイトリニューアルに伴なうMySQLの最適化
※以前別の場所で書いた文章を備忘的に書き記しておきます。
【投稿年月日】2008-03-08 【ジャンル】PHP/MySQL
ここ数日間に渡り行なっていたサイトリニューアル作業ですが、ようやくほぼ完了しました。外観をシンプルなものにした上で、サイト全体の安定化・高速化をはかりました。当サイトは「Nucleus」というCMSで構築していますが、利便性を高めるために何個かプラグインを導入しています。それら一つ一つについて見直しを行なうと同時に、当サイトに特化したプラグインを新たに作成しました。
そもそもリニューアルをしようと思ったのは、NP_AnalyzeというNucleusのアクセス解析プラグインが不安定であり、また、MySQLの接続エラーが頻発していたためです。要するに当サイトで利用しているプログラムに問題がある可能性があったのです。
このNP_Analyzeは、PHPもMySQLもよく分っていない時期に自作したものですが、久しぶりにソースを眺めたところ、あまりの読みにくさに頭が痛くなりました。この頃は、PHPのClassがよく分っていなかったし、MySQLをEXPLAINでチューニングすることも知らなかったから、見通しのいい安定したプログラムを書くことは難しかったとは言え、ヒドい出来としか言いようがありません。乱暴な言い方をすると、「取り合えず動けばいい」という感じでしょうか。
と言う訳で、リニューアル作業は、NP_Analyzeの大幅な書換えから始めることにしましたが、これがかなり大変でした。。。
サイトリニューアルの重点項目は以下の通り。
- サイト表示の安定化・高速化
- アクセス解析の安定化・高速化
- サイト内検索の負荷軽減
MySQLのquery発行回数は、外道なやり方（※$_POST[]を利用）なども駆使して極限まで減らしましたし、新たに作成したプラグインは、寄り道なしの最短コードで構成しました。
最も大変だったのは「2.アクセス解析の安定化・高速化」で、前述したNP_Analyzeというプログラムの最適化作業です。読みにくいソースを、めげそうになりながらも整理した上で、MySQLのquery発行回数を少しずつ減らしていきました。
このプログラムのボトルネックは明白で、１日１回行なわれるアクセス解析のための集計作業です。数百、数千というqueryをUPDATEする必要があるので、どうしても時間がかかりますし、タイムアウトも何度となく経験しています。でも、あの当時はうまい方法が思いつかなかったので、対応が十分にできていませんでした。
じっくり考えた結果、以下のようなステップを踏むことでMySQLのquery発行数を大きく減らすことにしました。
- その月の前日までの集計結果をMySQLのSELECTで取得。（※３月８日だったら、３月７日までの３月分の集計結果を取得）
- 1.で取得したデータをPHPの連想配列に格納。
- その日の集計データをアクセスログ・テーブルからMySQLのSELECTで取得。
- 3.で取得したデータをPHPの連想配列に格納。
- 2.と4.の連想配列から今月分のデータを集計。
- MySQLのquery発行のために5.のデータを蓄積。
- その月の前日までの集計結果をMySQLのDELETEで削除。
- マルチプル「INSERT INTO」で、一気にSQL文を実行。
// 今日の年月を取得
$ldate = date("Y-m");
// 「page」テーブルから同じ月のレコードを取得
$tp = mysql_query("SELECT pid, hit, pv FROM page WHERE pdate >= '".$this->q($ldate)."-01'");
// 「pid」をキーとする多次元連想配列$x_arrayに格納
while($row0 = mysql_fetch_assoc($tp)) {
$x = $row0['pid'];
$x_array[$x] = array($row0['hit'], $row0['pv']);
}
mysql_free_result($tp);
// 「log」テーブルから「lid」フィールドと「log」の数を取り出す
$pg = mysql_query("SELECT lid, COUNT(log) as count FROM log GROUP BY lip, lid ORDER BY null");
// 「lid」をキーとする多次元連想配列$page_arrayに格納
while($row1 = mysql_fetch_assoc($pg)) {
$lid = $row1["lid"];
$page_array[$lid][0]++;
$page_array[$lid][1] += $row1["count"];
}
mysql_free_result($pg);
// 前日までのデータ$x_arrayと当日のデータ$page_arrayを、今月分のデータとして結合
$mix = (count($x_array)>0) ? array_merge_recursive($x_array, $page_array) : $page_array;
// 多次元連想配列$mixから「pv」と「hit」の値を得る
foreach($mix as $pid=>$hitpv) {
$pv = $hitpv[0] + $hitpv[2];
$hit = $hitpv[1] + $hitpv[3];
// MySQLのquery発行のためにデータを「$q_page」に蓄積させる
$q_page .= "('".$this->q($pid)."', '".$this->q(date("Y-m-d"))."', '".intval($pv)."', '".intval($hit)."'),";
$hitpv = "";
}
if($q_page) {
// 「page」テーブルから同じ月のレコードを削除
mysql_query("DELETE FROM page WHERE pdate >= '".$this->q($ldate)."-01'");
// 「$q_page」の最後の一文字を除去
$q_page = substr($i_page ,0, -1);
// 「page」テーブルでマルチプル「INSERT INTO」。一気にSQL文を実行。
mysql_query("INSERT INTO page VALUES ".$q_page);
}
$ldate = date("Y-m");
// 「page」テーブルから同じ月のレコードを取得
$tp = mysql_query("SELECT pid, hit, pv FROM page WHERE pdate >= '".$this->q($ldate)."-01'");
// 「pid」をキーとする多次元連想配列$x_arrayに格納
while($row0 = mysql_fetch_assoc($tp)) {
$x = $row0['pid'];
$x_array[$x] = array($row0['hit'], $row0['pv']);
}
mysql_free_result($tp);
// 「log」テーブルから「lid」フィールドと「log」の数を取り出す
$pg = mysql_query("SELECT lid, COUNT(log) as count FROM log GROUP BY lip, lid ORDER BY null");
// 「lid」をキーとする多次元連想配列$page_arrayに格納
while($row1 = mysql_fetch_assoc($pg)) {
$lid = $row1["lid"];
$page_array[$lid][0]++;
$page_array[$lid][1] += $row1["count"];
}
mysql_free_result($pg);
// 前日までのデータ$x_arrayと当日のデータ$page_arrayを、今月分のデータとして結合
$mix = (count($x_array)>0) ? array_merge_recursive($x_array, $page_array) : $page_array;
// 多次元連想配列$mixから「pv」と「hit」の値を得る
foreach($mix as $pid=>$hitpv) {
$pv = $hitpv[0] + $hitpv[2];
$hit = $hitpv[1] + $hitpv[3];
// MySQLのquery発行のためにデータを「$q_page」に蓄積させる
$q_page .= "('".$this->q($pid)."', '".$this->q(date("Y-m-d"))."', '".intval($pv)."', '".intval($hit)."'),";
$hitpv = "";
}
if($q_page) {
// 「page」テーブルから同じ月のレコードを削除
mysql_query("DELETE FROM page WHERE pdate >= '".$this->q($ldate)."-01'");
// 「$q_page」の最後の一文字を除去
$q_page = substr($i_page ,0, -1);
// 「page」テーブルでマルチプル「INSERT INTO」。一気にSQL文を実行。
mysql_query("INSERT INTO page VALUES ".$q_page);
}
mysql_queryは全部で４回。内訳は、SELECTが２回に、DELETEとINSERT INTOが各１回です。UPDATEを使う代りに、DELETEとINSERT INTOで一気に処理しています。ちなみにインデックスはpdateに貼っています。
以前「MySQLの最適化」において、マルチプル「INSERT INTO」について言及しているので、比較してみるのも面白いかもしれません。
また、以前テンポラリーテーブルを使って一工程多い集計作業を行なっていましたが、連想配列の格納方法を工夫することによって省略することができました。
２つの配列の結合にはarray_merge_recursive()を使っています。array_merge_recursive()の動作を確認したところ、多次元配列を結合する際にキーが同一の場合、値は上書きされずに順次追加されるようです。と言う訳で、「hit」は配列$mixの0番目と2番目の、「pv」は配列$mixの1番目と3番目の値を合計しています。
PHPの多次元連想配列に格納するコストが結構かかりそうですが、MySQLのquery発行のコストに比べると低そうなので、おそらくサーバへの負荷は減少しているものと思われます。実際、９個のテーブル（検索語やリファー元、ページごとのアクセス数など）の更新をはじめ、アクセス解析のための集計処理時間が２～５秒程度で収まるようになりました。
でも、かなりチューニングをしたのですが、サイトに繋がりにくい時があるんですよね、結構。どうやらレンタル先のサーバ環境を含めて考えなければならないみたいです。
と言うことで、レンタルサーバ会社の変更を真剣に検討しているところです。
「3.サイト内検索の負荷軽減」については長くなったので、別エントリーにて言及します。
EDIUNET | PHP/MySQL | 独り言 | 提供サービス | JavaScript