予想以上の効果がでたので記事に残します。
DBの情報を元に統計を行うSQLとプログラムの性能改善で、複合INDEXを試してみました。
MySQLは、4.2です。
対象となったSQLは複数ありますが、そのうちほとんどは4つのテーブルをJOINしています。取得条件を変えながら情報を取得し、最後に目的別に合算するっていうことをやっています。4つのテーブルのうち3つはデータ数200万オーバー、1つは100万オーバー。
最初は、こんな状態
+----+-------------+-------+--------+----------------------------------------------+
| id | select_type | table | type | Extra |
+----+-------------+-------+--------+----------------------------------------------+
| 1 | SIMPLE | tb | range | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | tc | ref | Using where |
| 1 | SIMPLE | tp | ref | Using where |
| 1 | SIMPLE | td | eq_ref | Using where |
+----+-------------+-------+--------+----------------------------------------------+
まずは、一番最初に検索されている tb に複合インデックス(検索条件カラム、取得カラム全部、合計11カラム)を設定してみるも結果変わらず。カラムを検索条件だけにしてみたり、更に減らしたりと試すも結果変わらず。
とりあえず、最初に設定した複合インデックスを設定して、tcにも複合インデックス(全8カラム)を設定してみる。
すると変化が現れた。
+----+-------------+-------+--------+----------------------------------------------------------+
| id | select_type | table | type | Extra |
+----+-------------+-------+--------+----------------------------------------------------------+
| 1 | SIMPLE | tc | ref | Using where; Using index Using temporary; Using filesort |
| 1 | SIMPLE | tb | ref | Using where |
| 1 | SIMPLE | tp | ref | Using where |
| 1 | SIMPLE | td | eq_ref | Using where |
+----+-------------+-------+--------+----------------------------------------------------------+
tc に設定したINDEXが利用されてuse indexが出てきたのはわかるが、テーブルをJOINする順番が変わっている!!気になって、tb に設定した複合INDEXを削除すると、またtb からJOINされるようになった、possible key(利用可能index)にはいて、key(利用index)にはでてこないけど、なんらかの影響は及ぼしているようだ。
td tpにも同じ要領で検索対象、取得対象のカラムに複合INDEXを設定する。その結果、td tpにもuse indexが表示されるようになった。
+----+-------------+-------+------+----------------------------------------------------------+
| id | select_type | table | type | Extra |
+----+-------------+-------+------+----------------------------------------------------------+
| 1 | SIMPLE | tc | ref | Using where; Using index Using temporary; Using filesort |
| 1 | SIMPLE | tb | ref | Using where |
| 1 | SIMPLE | tp | ref | Using where; Using index |
| 1 | SIMPLE | td | ref | Using where; Using index |
+----+-------------+-------+------+----------------------------------------------------------+
だが、肝心のusing temporary、using filesortが消えない。using filesortって、今回のSQLにはgroup by は入っているけど、order byは入っていない。いくつかサイトを見たけど、order by には言及してあるものが多かった。そこで、order by をgroup by に読み替えて、実験してみることにした。
http://www.mysqlpracticewiki.com/index.php/Extra_field
上記のサイトと、リンク先を読んでいて今回のケースに当てはまりそうなものは、最初にJOINされるテーブルではないカラムで、group by を掛けていること。tbのカラムでかけていた。
幸いtc tbは1対1対応のテーブルでで、group by をtc のカラムでかけても取得結果は変わらないので、試してみることに。
その結果がこれ
+----+-------------+-------+------+--------------------------+
| id | select_type | table | type | Extra |
+----+-------------+-------+------+--------------------------+
| 1 | SIMPLE | tc | ref | Using where; Using index |
| 1 | SIMPLE | tb | ref | Using where |
| 1 | SIMPLE | tp | ref | Using where; Using index |
| 1 | SIMPLE | td | ref | Using where; Using index |
+----+-------------+-------+------+--------------------------+
消えた!やった!
ってことで、実行されるSQL(20ちょいくらい)にexplainをかけてみた。サブクエリ以外でexplainをとってみると、ほとんどのものでusing temporary、using filesortが消えていた。消えなかったのは、取得したいデータの量的にどうしようもなさそうなもの(データの半分以上を一旦取得するもの)くらい。
これで統計プログラムを実行したところ、実行時間が40分オーバーから5分前後へ!
かなり早くなった。予想以上の結果にびっくりしたので、メモ!
複合INDEXについては、以下のサイトも参考にさせていただきました。
http://d.hatena.ne.jp/k_yamamot/20100831/1283262245
http://txqz.net/blog/2006/12/13/0943
http://dev.seesaa.net/article/238633.html
One thought to “MySQL複合INDEXの威力”
Comments are closed.