twitter
facebook
InsTagram
YouTubeMySQLの最適化
※以前別の場所で書いた文章を備忘的に書き記しておきます。
【投稿年月日】2007-01-15 【ジャンル】PHP/MySQL
処理速度改善等にあたって実施したMySQLの最適化についてメモ的に残しておきます。大量のデータを扱うSQLスクリプトを記述する際、役に立つかもしれません。
- 可能な限りSQL文は後から一括して実行。
- LIMITを使い、呼び出すレコード数を制限。
- SELECT等においてフィールドを呼び出す際、インデックスがあるものを優先して指定。
- ソートする場合はインデックスがあるフィールドを「ORDER BY」において指定。
- グループ化（「GROUP BY」）する際にソートする必要がない場合でも「ORDER BY NULL」と指定。
- マルチプル「INSERT INTO」を利用。（※後から一括してSQL文を実行）
- UPDATEは遅い。一度「TRUNCATE TABLE」でテーブルを空にした後に更新されたデータを一気にマルチプル「INSERT INTO」することを検討。
- DELETEは遅い。「TRUNCATE TABLE」を検討。
- 大量のレコードをDELETEした後は「OPTIMIZE TABLE」でテーブルを最適化。
- WHERE節で不必要な()を使わない。
- WHERE節で「LIKE」の代わりに「REGEXP」や「in」が使えないか検討。
以下の２つについては分かりにくいかもしれないので、PHPを使ったソースで解説しておきます。
- マルチプル「INSERT INTO」を利用。（※後から一括してSQL文を実行）
- UPDATEは遅い。一度「TRUNCATE TABLE」でテーブルを空にした後に更新されたデータを一気にマルチプル「INSERT INTO」することを検討。
// 今日の日付を取得
$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");
// 同じ月のレコードの個数（１個or０個）
$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文を実行していたので、大量のデータを扱わなければならない場合は処理速度が極端に遅くなり、タイムアウトしてしまうことがありました。$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");
// 同じ月のレコードの個数（１個or０個）
$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);
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を超えてしまうのです。しかも１時間あたり2,000前後のページビューがあるので、アクセス解析処理中はMySQLデータベースの負担が極めて大きくなります。
MySQLの最適化について、さらに理解を深めたい場合は下記ページが参考になるかと思われます。
dev.mysql.com/doc/refman/4.1/ja/query-speed.html
なお、マルチプル「INSERT INTO」については下記ページを参考にしました。
www.mysql.gr.jp/mysqlml/mysql/msg/9658
【追記】 残念ながら、UPDATE文をマルチプル「INSERT INTO」に変更すると、集計結果がうまく得られないことが判明しました。仕方がないので以前のUPDATE文に戻しました。パフォ－マンスが落ちる可能性が高いので悲しいです。
【追記２】 続編の「MySQLの最適化－２」をエントリーしました。（2007-06-05）
EDIUNET | PHP/MySQL | 独り言 | 提供サービス | JavaScript