MySQLのチューニング（インデックス＆SQL）

2010/7/7 2016/12/10 MYSQL

【この記事の所要時間：約 7 分】

アクセスが集中したサイトで負荷が増大する事態が発生。調査してみると、アクセスに比例してMySQLへの負荷が上がっていた。調べてみるとボトルネックは、やはりデータベースっぽい。サイトオープンからチューニングを一切やっていなかったので、まずは基本的な部分からMySQLのクエリチューニングをやってみた。
MySQLのパラメータチューニングについてはかなり前だが以前エントリーした。
MySQLのパラメータチューニング
 MySQLのパラメータ設計について
クエリチューニングでまず最初に行うことは、スロークエリログの調査である。my.cnfの設定にて、実行時間が2秒を超えたクエリをログファイルに記録するようにしていたので、そのログを解析することにした。解析は、mysqldumpslowを利用。

$ mysqldumpslow -s t /var/lib/mysql/mysqld-slow.log

スロークエリログを直接見てもよくわからなかった情報が、mysqldumpslow を使うと、実行されたSQLが種類別に集計されて実行時間が長いものから順番にレポートされている。これを上からつぶしていくことにする。まずはEXPLAINコマンドを使って実行時間が長いクエリの解析を行った。
EXPLAINコマンドは、クエリオプティマイザがクエリをどのように実行するのかを解明する主な手段であり、SELECT文の前にEXPLAINをはさめばいいだけである。実行すると、結果にはいくつかの列が表示される。

列名	内容
id	クエリID （テーブルIDではない）
select_type	クエリの種類
table	対象となるテーブル名
type	レコードアクセスタイプ
possible_keys	利用可能インデックス
key	使用するインデックス
key_len	インデックスキーのサイズ(バイト数)
ref	検索に使用するフィールド
rows	行数の概算見積り
Extra	追加情報（オプティマイザヒント）

この列の中でチューニングに大切な項目が、type、key、rows、Extra である。基本的な対策は、処理する行数を減らし、全体の処理回数を減少させることである。具体的な目標としては、行数の概算見積りである rows列の値をできるだけ少なくすることである。ちなみに以下は、テスト環境ならびにテーブル名、カラム名などは修正を加えたものであり本番環境のものではない。（実際のクエリチューニングは本番環境でやっているが、以下のデータは本エントリーのために別途テストデータなどを用意した結果である）

typeが「ALL」というのは、テーブルスキャンしているということ。インデックスがまったく利用されていないので、要チューニングである。よってまずは where句の ar_saiyo_flg にインデックスを貼る。（こんな基本的なこともやっていないのか？と叱られそうだが、自分で作ったものじゃないシステムを運用することはよくある。）そして再度、EXPLAINコマンドを実行してみると以下のようになった。

possible_keys列に作成したar_saiyo_flg のインデックスが入っているが、key列には何も入っていないので、実際にはインデックスは使われていない。なぜかなあ～と考えていたら大きなミスを発見！それは、ar_saiyo_flgは、varchar型であるのにクエリをシングルクオートで囲っていない。たぶんコレが原因ではないかと思い、修正してみると・・・・

予想通り、key列にインデックスが入り、インデックスが使われるようになった。それに伴い、type列も「ALL」からユニークではないインデックスを利用した検索であることを示す「ref」へ変わった。そして、チューニングの目標である rows も大きく減った。次は、where句の ar_top_flg にもインデックスを貼る。そしてEXPLAINコマンドを実行してみると以下のようになった。

前回の反省をいかして ar_top_flg もvarchar型であるのでシングルクオートで囲ってあげると、2つのインデックスが結合されて利用されていることがわかる。そしてその結果、rows が劇的に減少したが、Extra列に「Using filesort」が表示されたままである。この表示があっても並べ替え対象件数が少なければたいしたことはないが、件数が多い場合は、チューニングが必須である。今回は件数が少ないので、これ以上チューニングする必要はないかと思うが、ソートしているカラムである ar_disp_date にもインデックスを貼ってEXPLAINコマンドを実行してみると・・・・・
実は、ar_disp_date にインデックスを貼る前と同じ結果になる。これは、絞込み用のインデックスとソート用のインデックスが併用できないためである。では、Extra列の「Using filesort」をどのように消すかといえば、複合インデックスである。インデックスマージは、複数のインデックスを読み込むため、複合インデックスに比べると効率は悪い。よって、ar_saiyo_flg, ar_top_flg, ar_disp_date の3列による複合インデックスを作成する。作成方法は以下の通り。

mysql> ALTER TABLE hogehoge ADD INDEX idx_saiyo_top_disp_date(ar_saiyo_flg,ar_top_flg,ar_disp_date);

複合インデックスを使うので、それまでに作った3つの単一インデックスを削除してからEXPLAINコマンドを実行してみると・・・・・

rows列の数は少し増えたが、Extra列の「Using filesort」を消すことは成功した。最初に述べたようにチューニングに大切な項目は、type、key、rows、Extra であり、type列にALL、indexがある場合、Extra列にUsing temporary、Using filesortがある場合はパフォーマンスに悪影響が出やすいので見直しが必要かと思われる。その中で、key列のインデックスが入っていて、できるだけrows列が少ないクエリがパフォーマンスが良いクエリと言えるだろう。
ちなみに、MySQLのインデックスは、4系まではテーブル1つにつき、1つのインデックスしか使われなかったが、5系になってインデックスマージ（index_merge）という機能が追加されて自動的？にオプティマイザが判断して使える場合は使っているようである。
このようにして１つづつスロークエリログで実行時間が長くなっていたクエリのチューニングを行っていくと、だいぶクエリのパフォーマンスが良くなった。クエリのチューニングは地道な作業であるが、意外とアプリ構築時には忙しくてできなかった部分でもあるので、やっておくとあとあと楽である。