MySQLにおける10の最適化設計

さて、ここでコーヒーブレークして、MySQLの最適化について考えてみたいと思います。MySQLデータベース分散処理では、SQLを複数のサーバーやテーブルへと分散する方法についてご紹介させて頂きましたが、こちらは主に、個々のDB サーバーの最適化について考えてみたいと思います。

大きくわけると、MySQLにおいては、以下のことがポイントになるかなと思います。（レプリケーションなどの分散系は他でさんざん触れているのでここでは割愛します）

重要だと思う順番に列挙します。

１．とにかく分析。できるだけ実際に近いデータでEXPLAINの結果を見たり、slow-queryを分析します。また、CPUの使用状況として、vmstatなどで、できるだけ常にiowaitが0近い状態にします。

MySQLは癖が強いので、特に慣れていない人はセオリー通りにいかないことは結構あると思います。ですので、実際の分析を何よりも重視してください。また、INDEXの使われ方やパフォーマンスは、データ数やSQL発行順序において、できるだけ実際の環境に近い状況を作り出してテストを行わないと、意味がないので注意してください。

例えば、私の場合であれば、アプリケーションをデバッグモードにすることで、自動的にすべてのSQLが記録され、さらにすべてのSELECT文に対して、自動的にEXPLAINの結果を保存するような仕組みとなっています。これで想定にできるだけ近いデータを使い、JMeterなどを使って負荷をかけることで、実際にどのようなSQLがどのくらいの効率で実行されているかを検証することができます。

２．SQLの回数はできるだけ減らす。MySQLには、REPLACEやINSERT IGNORE、DUPLICATE KEYS UPDATEなどの方言も多数存在します。

例えば、SELECT文で予めレコードが存在するか確認して、存在すればUPDATE、しなければINSERT、みたいな方法であれば、そのSELECTの条件にUNIQUE制約をかけてREPLACE文を発行すればSQL一つで完了します。

同じようにINSERT IGNOREやDUPLICATE KEYS UPDATE なども使ってSQLの回数を減らすことができます。

これは、たとえばforeachなどでSELECT文を複数回発行している場合についても同様のことが言えます。

３．それぞれのSQLに的確なINDEX。ひとつひとつのSQLに的確な複合INDEXを張ります。

MySQLではPostgresやOracleなどと違って、一つのSQL（一つのテーブルごと）に一つのINDEXしか使われません。

例えば、以下のようなSQLがあったとします。

SELECT ... FROM examples WHERE a = 1 AND b = 2 AND c = 3 ORDER BY d;

このSQLを最適化させるためには、a,b,c,dに個別にINDEXを張るのではなく以下のようなINDEXを生成する必要があります。（逆に以下のINDEXがあれば、上記のSQLに関しては、a,b,c,d個別のINDEXは不要です）

ALTER TABLE examples ADD KEY (a,b,c,d);

複合INDEXは、実際のSQLと同じ順番でなければうまく適応されません。例えば上記の例でいえば、必ず a,b,c,dの順序でINDEXを生成しておく必要があります。

４．confファイルの設定ではまず、バッファサイズを最適化する。

confファイルのチューニングで最も重要なのはバッファサイズです。

innodb_buffer_pool_size・・・InnoDBのデータやインデックスをキャッシュするためのバッファサイズ。InnoDBだけを利用する場合、一般的に空きメモリの７割～８割程度を割り当てるとよいと言われています。
key_buffer_size・・・MyISAMのインデックスをキャッシュするバッファサイズ。空きメモリの３割程度。
sort_buffer_size・・・ORDER BYやGROUP BYのときに使われるバッファサイズ。スレッドバッファなので、割り当て過ぎには注意。256K～1M程度。
read_buffer_size・・・INDEXを使わず全件スキャンする際のバッファサイズ。そもそもINDEXを使わないSQLを発行するべきではない。128K～1M程度。
read_rnd_buffer_size・・・INDEXによるソート後にレコードを読むときに使われるバッファサイズ。スレッドバッファなので、割り当て過ぎには注意。256K～1M程度。

より詳細な情報は以下のページを参考にするとよいかと思います。

DSAS開発者の部屋:5分でできる、MySQLのメモリ関係のチューニング！

５．前方一致でないLIKE検索はできるだけ避ける。

LIKE検索においては、検索内容が % で始まる場合、INDEXが使われません。

SELECT ... FROM examples WHERE e LIKE '%hoge%'

このような場合、できればSennaなどによる全文検索を利用するか、正規化、あるいはその両方を行ったほうがよいでしょう。

６．正規化などを駆使してできるだけ重いSQLを個別のテーブルに切り離す。

これは前項のような重いLIKE検索が走るような場合にも言えますが、MySQLの場合、以下のようにロックがかかります。

MyIsam
- すべてのSELECT/INSERT/UPDATE/DELETE文において、常にテーブルロック
InnoDB
- プライマリーキーによるSELECT => 行ロック
- それ以外のSELECT/INSERT/UPDATE/DELETE文 => テーブルロック

なので、LIKE検索や全文検索のような重いSQLはできるだけ、それ専用のテーブルを別に作ったほうがよいかと思います。

７．INはINDEXが有効でしかも結構速いが、ORは左辺と右辺のどちらかのみのINDEX適応（基本的には左辺）となる。

できるだけORを使うSQLを発行しなくてすむような設計にすることが望ましいですが、多くの要件では100%は難しいと思うので、その場合には、ANDで結合できる部分をできるだけ前に持ってきて、ORの後ろの条件をできるだけ簡潔なものとする（なぜなら原則としてORの後ろはINDEXが聞かないから）

また、ORで複数の=を使う場合、IN演算子を使ったほうがはるかに高速に動作します。

SELECT ... FROM examples WHERE a = 1 OR a = 2 OR a = 3 ;

よりも

SELECT ... FROM examples WHERE a IN (1,2,3);

のほうが高速で、かつINDEXが的確に適応されます。

８．出来るだけテーブルを固定長になるようにする

可変長のテーブルよりも固定長のテーブルのほうがSELECT文が速く実行できます。

MySQLにおける固定長のテーブルとは、TEXT型やBLOB型のような可変長カラムを含まないテーブルのことです。

９．ポイントポイントでキャッシュする。

例えば、SNSであれば、メールの未読件数について毎回、「SELECT COUNT(1) ...」というSQLを発行する代わりにユーザーのテーブルにmail_notread_numのようなカラムを用意しておき、メールの受信をトリガーにキャッシュ用カラムを更新する、といった設計にすることで、比較的重いCOUNT()関数のSQLの発行回数を削減できます。

同じように、別のテーブルのカラムだったり、あるいはmemcachedやファイルなどにSQLの結果などをキャッシュしておくことができます。