MySQLは,シンプルな機能と高速性が特徴である。そのため,無造作に使用していても高速に動作すると思われがちである。しかし,MySQLといえども高速で動作させるためには,それなりのテクニックが必要である。MySQLの利用者の増加とともにこれらのテクニックを求める声が多くなっている。今回は,高速化を行うための解析にスポットを当てる。

SQL文のチューニング

 SQL文を改善するには,2つのステップで考えるといいだろう。どのSQL文が遅いのかを発見するのが最初のステップだ。発見したSQL文を改善することにより高速化が実現する。もし,ターゲットとなるSQL文がSELECT文なら,EXPLAINキーワードによって解析することが可能だ。

遅いSQL文の発見

 データベースに対する処理のすべてが遅いのであれば,それはデータベース構造やデータベース・サーバー(ハードウエアやネットワークも含む)を疑った方がいいだろう。

 しかし,実際には一部のデータベース処理だけが遅いという症状が多い。特に長時間の処理の中で,どのSQL文が遅いのか判らない場合には厄介である。

 MySQLでは,実行時間が設定時間以上経過したSQL文を記録するためにSlowLogと呼ぶ機能を用意している。

 今回は,以下のようなテーブルを使用した。

テーブル名 city
レコード数 4079
主なフィールド id(プライマリ・キー)
name(インデックス「i1」)
country
   :
テーブル名 country
レコード数 239
主なフィールド code(プライマリ・キー)
   :
テーブル名 language
レコード数 984
主なフィールド country(プライマリ・キー)
   :

SlowLogの設定

 環境設定ファイル(Windowsではmy.ini,Linuxではmy.cnf)に次のような設定を加えるとSlowLogが有効になる。


log-slow-queries                SlowLogの有効化(ログファイル名を指定可能)
long-query-time=2               SlowLogに記録する処理時間の上限
log-long-format                 インデックスを使用しないSQL文の記録
 long-query-timeパラメータは,SlowLogに記録するしきい値を秒単位で設定する。この場合には,2秒超える処理時間を費やしたSQL文を記録する。また,log-long-formatを指定すると,インデックスを使用しないSQL文もSlowLogに記録する。

SlowLogの確認

 SlowLogが動作しているかどうかは,次のコマンドで確認できる。log_slow_queriesがONであれば有効となっている。デフォルトのファイル名は,「サーバー名-slow.log」となる。


mysql> show variables like 'log%';
+-------------------+------------------+
| Variable_name     | Value            |
+-------------------+------------------+
| log               | OFF              |
| log_update        | OFF              |
| log_bin           | ON               |
| log_slave_updates | OFF              |
| log_slow_queries  | ON               |
| log_warnings      | OFF              |
| log_error         | .\zend-satou.err |
+-------------------+------------------+
7 rows in set (0.03 sec)
 long-query-timeの設定内容は,以下のコマンドで確認できる。

mysql> show variables like 'long%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| long_query_time |  2    |
+-----------------+-------+
1 row in set (0.00 sec)

SlowLogの表示例

 SlowLogは,テキスト形式のファイルだ。処理時間がlong-query-timeを超えたSQL文が記録される。以下のような4行で1セットだ。


# Time: 040624  1:25:24 
# User@Host: [ODBC] @ localhost [127.0.0.1]
# Query_time: 5  Lock_time: 0  Rows_sent: 30670  Rows_examined: 38828
select * from city ,country ,language where country.code=city.country
 and city.country=language.country;
 1行目 記録日時
 2行目 ユーザーIDとリクエストした端末
 3行目 Query_time(実行時間) Lock_time(ロック時間) Rows_sent(送信行数) Rows_examined(処理対象となった行数)
 4行目 SQL文

 このように設定時間を超えるSQL文が克明に記録される。

 また,log-long-formatを指定していれば,インデックスを使用しなかったSQL文も記録される。Query_timeに関係なく記録されるので,見分けられるだろう。


# Time: 040623 22:53:30
# User@Host: [ODBC] @ localhost [127.0.0.1]
# Query_time: 0  Lock_time: 0  Rows_sent: 239  Rows_examined: 239
select * from country;
 SELECT文だけなく,DELETE文などの更新系のSQL文も記録対象である。

# Time: 040623 23:28:39 
# User@Host: [ODBC] @ localhost [127.0.0.1]
# Query_time: 0  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
delete from city where country="USA";
 このようにSlowLogを活用すれば,極端に処理時間を費やすデータベース処理を探し出すことができる。データベースのチューニングだけでなく,実運用フェイズでの監視にも利用できる。

【訂正】
掲載当初、「SlowLogが動作しているかどうかは,次のコマンドで確認できる。log_slow_queriesがNOであれば有効となっている。」とありましたが誤りで、正しくは「log_slow_queriesがONであれば有効となっている。」です。お詫びして訂正します。

この先は会員の登録が必要です。有料会員(月額プラン)は登録月無料!

日経 xTECHには有料記事(有料会員向けまたは定期購読者向け)、無料記事(登録会員向け)、フリー記事(誰でも閲覧可能)があります。有料記事でも、登録会員向け配信期間は登録会員への登録が必要な場合があります。有料会員と登録会員に関するFAQはこちら