MySQLの最適化

※以前別の場所で書いた文章を備忘的に書き記しておきます。

【投稿年月日】2007-01-15 【ジャンル】PHP/MySQL


 処理速度改善等にあたって実施したMySQLの最適化についてメモ的に残しておきます。大量のデータを扱うSQLスクリプトを記述する際、役に立つかもしれません。

 以下の2つについては分かりにくいかもしれないので、PHPを使ったソースで解説しておきます。
 「log」テーブルからデータを取り出して集計し、「page」テーブルにその集計結果を流し込む処理です。同じ月のデータが既にある場合は新規にデータを追加するのではなく、アップデートするという仕様です。
// 今日の日付を取得
$ldate = date("Y-m-d");
// 「log」テーブルから「lid」フィールドと「lid」の数を取り出す
$page_groups = mysql_query("SELECT lid, COUNT(lid) as lcount FROM log GROUP BY lid ORDER BY null");
// ループ開始
while($row = mysql_fetch_assoc($page_groups)) {
 $lid = $row['lid'];
 $lcount = $row['lcount'];
// 「page」テーブルから同じ月のレコードを取得
 $tp = mysql_query("SELECT pid, pdate, pcount FROM page WHERE LEFT(pdate, 7) = '".date("Y-m")."' and pid = '".$lid."' LIMIT 1");
// 同じ月のレコードの個数(1個or0個)
 $lo = mysql_num_rows($tp);
// アップデートするか否かの判定
 if($lo) {
  while($res = mysql_fetch_assoc($tp)) {
   $lcount = $res['pcount'] + $lcount;
// アップデートするデータを「$i_page」に累積させる
   $i_page .= "('".$lid."', '".$ldate."', '".$lcount."'),";
  }
  mysql_free_result($tp);
 }else {
// 新たに追加するデータを「$i_page」に累積させる
  $i_page .= "('".$lid."', '".$ldate."', '".$lcount."'),";
 }
// ループ終了
}
// 「$i_page」の最後の一文字を除去
$i_page = substr($i_page ,0, -1);
if($i_page) {
// 「page」のバックアップテーブル「page_bk」を作成(※テーブルのコピー)
 mysql_query("CREATE TABLE IF NOT EXISTS page_bk as SELECT * FROM page");
// 「page」テーブルを空にする
 mysql_query("TRUNCATE TABLE page);
// 「page」テーブルでマルチプル「INSERT INTO」。一気にSQL文を実行。
 mysql_query("INSERT INTO page VALUES ".$i_page);
// バックアップテーブル「page_bk」を削除
 mysql_query("DROP TABLE page_bk);
}
mysql_free_result($page_groups);
 今までは「while($row = mysql_fetch_assoc($page_groups)) { }」内において条件に合致するごとにUPDATE文を実行していたので、大量のデータを扱わなければならない場合は処理速度が極端に遅くなり、タイムアウトしてしまうことがありました。
 UPDATE文でもマルチプル「INSERT INTO」のように後から一括して実行できるSQL文ができないかと悪戦苦闘していたとき、ふと「テーブルを空にした上で一気にINSERTすれば同じことができるのではないか」とひらめきました。具体的な手順は上記ソースの通りです。途中でINSERTが止まることも考えられるので、念のためにバックアップテーブルも作成することにしました。

 これで処理スピードの高速化が実現されました。特にマルチプルINSERTの効果は絶大で、while内で一回一回INSERTやUPDATEするのに比べると、処理速度は数倍違ってきます。
 例えば、while内でUPDATE文を5,000回実行するのと、while処理の後にINSERT文(※ただし長文)を1回だけ実行するのでは、MySQLデータベースへの接続回数が5,000倍違ってくるので、当然ながらサーバへの負担も格段の差が出ますし、処理速度も大きく変わってきます。
 私が管理している某サイトのアクセス解析処理においては、まさに「while内でUPDATE文を5,000回実行」中に、タイムアウト(あるいはテーブルロック)が頻発したのです。某サイトはページ数が500近くあるので、ログファイルからデータを取り出してページ移動パターンを集計処理すると、レコード数が5,000を超えてしまうのです。しかも1時間あたり2,000前後のページビューがあるので、アクセス解析処理中はMySQLデータベースの負担が極めて大きくなります。

 MySQLの最適化について、さらに理解を深めたい場合は下記ページが参考になるかと思われます。
dev.mysql.com/doc/refman/4....

 なお、マルチプル「INSERT INTO」については下記ページを参考にしました。
www.mysql.gr.jp/mysqlml/mys...


【追記】 残念ながら、UPDATE文をマルチプル「INSERT INTO」に変更すると、集計結果がうまく得られないことが判明しました。仕方がないので以前のUPDATE文に戻しました。パフォ-マンスが落ちる可能性が高いので悲しいです。

【追記2】 続編の「MySQLの最適化-2」をエントリーしました。(2007-06-05)

EDIUNET | PHP/MySQL | 独り言 | 提供サービス | JavaScript