MySQLのクエリを最適化する10のTipsPHPプロ！ニュース

Jaslabs: High performance phpでMySQLのクエリを最適化するための10のTipsが公開されています。explainを利用した一般的な方法から、SELECT・INSERTのクエリオプションを使用するような方法まで紹介されています。MySQLクエリの速度低下にお悩みの方は一度読んでみるとよいでしょう。

1. explainコマンドの使用

explainコマンドはクエリでどのインデックスが指定されているのか、などの情報が取得できます。 explainコマンドの実行結果を見ながらインデックスが使用されるようにクエリを修正したり、インデックスの追加を行います。 詳細についてはMySQL 4.1リファレンスマニュアルを参照してください。

2. 権限の簡易化

テーブルの権限が複雑になればなるほど、オーバーヘッドが大きくなります。GRANT文で設定された権限が単純であれば、クライアントがSQLを実行したときのオーバーヘッドが少なくなります。

3. MySQL関数をBENCHMARK関数でテストする

特定のMySQL関数などが問題になっている可能性がある場合、BENCHMARK関数を使用してmysqlクライアントから実行時間を調べることができます。以下のように使用します。ここでは、CONCAT関数にかかるコストを調べています。実際にこのBENCHMARKが有用となるのは、複雑な関数を実行したりしている場合のオーバーヘッドを調べるときなどが考えられます。BENCHMARK関数についてはマニュアルを参照してください。

mysql> SELECT BENCHMARK(10000000, CONCAT('foo', 'bar'));
+-------------------------------------------+
| BENCHMARK(10000000, CONCAT('foo', 'bar')) |
+-------------------------------------------+
|                                         0 |
+-------------------------------------------+
1 row in set (2.67 sec)

mysql> SELECT BENCHMARK(10000000, 'foobar');
+-------------------------------+
| BENCHMARK(10000000, 'foobar') |
+-------------------------------+
|                             0 |
+-------------------------------+
1 row in set (0.14 sec)

4. WHERE句の最適化

• 不要な括弧「()」は除く
• 単一のテーブルにWHERE句なしのCOUNT(*)を実行すると、MyISAMとMEMORYテーブルではテーブル情報から直接取得することができます。また、テーブル１つのみで使用する場合はすべてのNOT NULL式でも実行されます。
• SQL_SMALL_RESULTオプションを使用すると、MySQLはメモリ内のテンプテーブルを使用します。

SELECT SQL_SMALL_RESULT * FROM table_name;

5. OPTIMIZE TABLEの実行

このコマンドはテーブルをデフラグします。大量のデータを削除した場合などに実行すると効果があるでしょう。

6. 可変長カラム型

頻繁に更新されるMyISAMテーブルでは、可変長カラム型（VARCHAR, BLOG, TEXT）は使用するべきではありません。マニュアルに可変長カラム型を使用した動的テーブルについての詳細があるので、そちらを参照するとよいでしょう。

7. INSERT DELAYED構文

INSERT DELAYED構文はデータの書き込み終了を検知する必要がない場合に使用します。そうすることで、一度のディスク書き込みで多くの行を書き込むことができるので、インサートのオーバーヘッドを少なくすることができます。

ただし、マニュアルによるとMyISAMでは同時SELECTと同時INSERTが可能であるため、MyISAMでは使用する必要はほとんどないとのことです。

8. 優先度の指定

• INSERT LOW_PRIORITY構文を使用すると、INSERT文の優先度が下がり、相対的に他のSELECT文の優先されるようになります。
• SELECT HIGH_PRIORITY構文を使用すると、キューに優先度が指定されていないSQL文があっても、先にそのSELECT文が実行されます

9. 複数行の同時インサート

複数行を同時にINSERTする方が、複数回のINSERT文を実行するよりも効率が高くなります。

10. データ型の同期

同じ情報を扱うカラムが複数のテーブルにある場合、同じ名前、同じ型にする。

MySQLの最適化についてはマニュアルにもかなり詳しく記述があるので、そちらも参照するとよいでしょう。