MySQLを高速化する10の方法

ちょっとキャッチ−なタイトルをつけてしまったが、今日は独断と偏見でMySQLを高速化する方法を10個紹介しよう。MySQLサーバをチューニングするときや初期導入する場合などに参考にしてもらいたい。

1. バッファを増やす、または減らす

チューニングの基本中の基本であるが、適切なバッファサイズを設定することはパフォーマンスチューニングの要である。主なバッファは次の通り。

• innodb_buffer_pool_size・・・InnoDBだけを利用する場合は空きメモリの7〜8割程度を割り当てる最も重要なバッファである。余談だが、実際にはここで割り当てた値の5〜10%ぐらいを多めにメモリを使うので注意が必要だ。
• key_buffer_size・・・MyISAMだけを利用する場合は、空きメモリの3割程度を割り当てるといい。残りはファイルシステムのキャッシュ用に残しておこう。
• sort_buffer_size・・・ソート処理に利用するバッファである。OLTPでは256K〜1Mぐらいを割り当てると良い。これがあまり大きすぎると、メモリの割り当てのオーバーヘッドが大きくなるので注意しよう。DWH系の処理などで大きなソートが必要な場合、セッションごとに動的に調整すると良い。
• read_buffer_size・・・全件スキャンをするときに利用するバッファ。OLTPでは128K〜512Kぐらいを割り当てると良い。
• read_rnd_buffer_size・・・ソート処理でインデックスを利用する場合に利用するバッファ。OLTPでは256K〜1MぐらいをDWH系の処理などで大きなソートが必要な場合、セッションごとに動的に調整すると良い。

バッファは増やせば増やすほどいいかと言えばそうではない。メモリの割り当てがオーバーヘッドになるので、無駄に大きくし過ぎることは禁物である。また、バッファを増やしすぎたためにスワップが発生するとパフォーマンスが悲惨なことになるのでくれぐれも空きメモリ容量には注意しよう。

2. 高速なディスクを利用する

MySQLだけに限った話ではないが、RDBMSのボトルネックは99.99999%がディスクI/Oである。特にディスクのシークタイムによる待ち時間が大きい。理想的にはバッテリーバックアップ付きのRAID装置を利用するのがいい。最近はRAID装置に匹敵するほど高速なSSDが出てきているので楽しみである。

前述のようにバッファを大きくするとディスクI/Oの回数や量が減るので、必ずしも高速なディスクが性能を向上させるというわけではないが、データサイズが大きくてバッファに収まりきらない場合などにはどうしてもI/Oが大量に発生してしまう。そんな時は高速なディスク装置を利用するといい。

3. クエリを最適化する

実は最も大事なのがクエリの最適化である。いくら他の部分を最適化したところで、毎回全件スキャンが発生していたのでは話にならない。適切にインデックスを使ったり、サブクエリをJOINに書き換えたりすることで、フェッチしないといけない行数ができるだけ少なくなるようにクエリを書きかえよう。クエリを最適化するには、まずEXPLAINで実行計画をチェックしよう。EXPLAINの見方についてはいずれ解説しようと思う。

また、テーブルから全件フェッチしてからアプリケーション側で行を絞り込むというようなロジックを実装してはいけない。必ずSQL文、つまりWHERE句で行の絞り込みができるようにしよう。

クエリを手当たり次第チューニングしていてはいくら時間があっても足りないだろう。問題のあるクエリだけをチューニングするべきであるが、そのようなクエリを見付けるにはスロークエリログや商用のクエリアナライザを用いると効果的である。

4. テーブルを最適化する

基本中の基本は、適切なデータタイプを使うということである。できるだけカラムサイズが小さくなるようなデータタイプを選ぼう。数値をVARCHAR(桁数)などのデータタイプで格納しているのをたまに見かけるが、これは誤りである。INTまたはBIGINTなどを利用したほうがずっとデータサイズが小さくなるし高速である。

また、適切なカラムに対してインデックスをつけるのも重要である。どのカラムにインデックスをつけるかは、クエリのパターンに因る。インデックスが多すぎると更新時のオーバーヘッドが大きくなるだけでなく、インデックスツリーを格納するためのデータ容量が増えてしまうので、インデックスのつけすぎには注意しよう。たまに全てのカラムにインデックスがついているテーブルを見かけるが、そのようなテーブル設計は誤りである。クエリのパターンによっては、マルチカラムインデックスやパーティショニングが必要になるなどいろいろと工夫が必要になる。

カラム数がが多くなりすぎたら、まずは正規化できるかどうかを検討してみて欲しい。DWH用途などでは逆に非正規化すると性能が向上する場合がある。

5. 目的に合ったストレージエンジンを選択する

これはMySQLの醍醐味である。ストレージエンジンはそれぞれ性能特性がまったく違うので、目的に合ったストレージエンジンを選択すると劇的に性能が向上する場合がある。例えば、OLTPではInnoDB、参照系が多い場合はMyISAM、ログ目的であればARCHIVE、リアルタイム並列処理であればNDBCLUSTERなど。他にもSun/MySQL以外のサードベンダーやコミュニティからリリースされているストレージエンジン（SPIDER、PBXT、XtraDB、Q4M、Infobright、Kickfireなど）もあるので、目的に合わせて色々検討してみるといいだろう。

6. レプリケーションで負荷分散する

MySQLほどお手軽に、そして安価にレプリケーションを利用出来るRDBMSは他にないだろう。レプリケーションを用いてたくさんのスレーブへ参照系の処理を負荷分散するテクニックは、Webサイトなどで頻繁に利用されているテクニックである。参照系の負荷分散を行う場合だけでなく、例えばOLTPのデータを元にBIなどの処理を毎日行う場合などにも有効である。スレーブ上でBIを行えば、マスター上のOLTP系の処理に影響を与えることがない。

7. ストアドプログラムを多用しない

残念ながら、MySQLはストアドプロシージャ、ストアドファンクション、トリガなどの性能はあまりよくない。出来るだけそれらを利用せずに、ロジックをアプリケーション側に持っていくといいだろう。

8. ファイルシステムをチューニングする

Linuxであればデフォルトはext3（そろそろext4になっていくだろうか？）であるが、ext3ではなくXFSを利用すると性能が向上する場合がある。また、I/Oスケジューラを変更することで、同じext3であっても性能特性が変化する。SolarisではUFS、ZFS、QFSなどの利用を検討するといいだろう。WindowsならNTFS以外にあまり選択肢はないが、MyISAMの場合はLargeSystemCacheを有効にするなどのチューニングが必要である。

9. コネクションプールを利用する

アプリケーションがDB操作が必要なときに都度MySQLサーバへ接続していたのでは、接続のためのオーバーヘッドが無視出来なくなる。そんなときはコネクションプールを利用するといい。

10. ベンチマークする

どんなチューニングでも、実際に効果があるかどうかは測定してみるまで分からない。また、あるアプリケーションで効果があるチューニングでも、他のアプリケーションの負荷パターンでは逆効果になってしまうということは多々ある。なので、アプリケーションの負荷を擬似的に作り出してチューニングの効果を測定することはとても重要なのである。