MySQL複合INDEXの威力

予想以上の効果がでたので記事に残します。
DBの情報を元に統計を行うSQLとプログラムの性能改善で、複合INDEXを試してみました。
MySQLは、4.2です。

対象となったSQLは複数ありますが、そのうちほとんどは4つのテーブルをJOINしています。取得条件を変えながら情報を取得し、最後に目的別に合算するっていうことをやっています。4つのテーブルのうち3つはデータ数200万オーバー、1つは100万オーバー。

最初は、こんな状態

1
2
3
4
5
6
7
8
+----+-------------+-------+--------+----------------------------------------------+
| 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カラム)を設定してみる。

すると変化が現れた。

1
2
3
4
5
6
7
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が表示されるようになった。

1
2
3
4
5
6
7
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; 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 のカラムでかけても取得結果は変わらないので、試してみることに。

その結果がこれ

1
2
3
4
5
6
7
8
+----+-------------+-------+------+--------------------------+
| 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

  • トラックバック 停止中
  • コメント (0)
  1. コメントはまだありません。

コメント 停止中

%d人のブロガーが「いいね」をつけました。