MySQLチューニング虎の巻／ソートに関連するトピックとクエリの書き換え

003

更新日: 2012/02/10
公開日: 2012/01/17

通知

GROUP BY

　ORDER BYでインデックスが使われる場合がどのようなものかということについて理解したならば、GROUP BYでインデックスが使われる条件について理解するのも難しいくはない。

　インデックスを使ってORDER BY（ソート）を解決できない場合には、ファイルソートというアルゴリズムが使われるのはすでに解説した通りである。いっぽう、インデックスを使ってGROUP BYを解決できない場合には、テンポラリテーブルを使ってクエリを解決する。GROUP BYでは、インデックスを使った場合とテンポラリテーブルを使う場合では、アルゴリズムがかなり異なるのである。

　まず、インデックスを使わない場合、つまりテンポラリテーブルでGROUP BYを解決する場合についてみてみよう。

mysql> EXPLAIN SELECT CountryCode, SUM(Population) sump FROM City GROUP BY Countrycode\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: city
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4079
        Extra: Using temporary; Using filesort
1 row in set (0.00 sec)

　賢明な読者諸君はここで「おや？」と思われるかも知れない。なぜUsing filesortが表示されているのであろうか。これは、GROUP BYを実行すると、デフォルトではMySQLはテンポラリテーブルの中身をGROUP BYで指定したカラムでソートして返すからである。このソートを省略したい場合には、ORDER BY NULLを指定すると良い。

　このクエリをインデックスを使って解決するために、Cityテーブルに次のようなインデックスをつけてみよう。

mysql> CREATE INDEX ix_city_cc_pop on City (CountryCode, Population);

　すると実行計画が次のように変化する。


mysql> EXPLAIN SELECT CountryCode, SUM(Population) sump FROM City GROUP BY Countrycode\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: city
         type: index
possible_keys: NULL
          key: ix_city_cc_pop
      key_len: 7
          ref: NULL
         rows: 4079
        Extra: Using index
1 row in set (0.00 sec)

　ExtraフィールドにUsing indexが表示されており、なおかつレコードアクセスタイプがindexになっている。これはオプティマイザがインデックススキャンを選択したことを意味している。対象のインデックスは先程作成したix_city_cc_popであり、エントリは全てのレコードが対象となっている。

　インデックスを使ったGROUP BYはどのように処理されるのだろうか？それを理解するには、まず次の表を見てもらいたい。これは、ix_city_cc_popインデックスにおいて、CountryCode=JPNになっているエントリ周辺の内容を表したものである。

　このようにGROUP BYで指定したカラム（CountryCode）の順序でレコードが並んでいれば、表中の緑の背景になっているレコードを順にフェッチしてPopulationの合計を計算すれば、CountryCode=JPNに対するSUM(Population)を計算することが出来る。この操作をCountryCodeの種類だけ繰り返せば良い。

　ちなみに、GROUP BYにおいても、ORDER BYと同じように、フェッチするインデックスの範囲を限定する検索条件を指定しつつ、クエリをインデックスを使って解決することが可能である。

mysql> EXPLAIN SELECT CountryCode, SUM(Population) sump FROM City WHERE CountryCode LIKE 'j%' GROUP BY Countrycode\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: City
         type: range
possible_keys: ix_city_cc_pop,ix_city_cc_d
          key: ix_city_cc_pop
      key_len: 3
          ref: NULL
         rows: 253
        Extra: Using where; Using index
1 row in set (0.00 sec)

　このようにインデックス順にレコードをフェッチしてGROUP BYクエリを解決するアルゴリズムを、タイトインデックススキャンという。タイトインデックススキャンはそこそこ効率的なアルゴリズムであるが、GROUP BYを用いたクエリの特性上、フェッチするレコードの件数が多くなってしまうので時間がかかることが多いので多用は禁物である。もちろんテンポラリテーブルを操作するよりもオーバーヘッドは少ないし、テーブルスキャンよりもインデックススキャンのほうが読み取るデータ量が少なくなるので、GROUP BYはインデックススキャンが使われるように心がけたい。

　GROUP BY句を用いたクエリでは、インデックスを用いてクエリを解決できるならば、MIN()またはMAX()を処理する場合に限り、ルースインデックススキャンというアルゴリズムが使われる。（タイトの反対はルースである。）以下はルースインデックススキャンが用いられるクエリの例である。

mysql> EXPLAIN SELECT CountryCode, MIN(Population) minp FROM City GROUP BY CountryCode\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: city
         type: range
possible_keys: NULL
          key: ix_city_cc_pop
      key_len: 3
          ref: NULL
         rows: 227
        Extra: Using index for group-by
1 row in set (0.00 sec)

　ルースインデックススキャンが用いられる場合、Using index for group-byというヒントがExtraフィールドに表示される。例えばCountryCode = JPNとなっているレコードの中でPopulationの最小値を求める場合、セカンダリインデックスからCountryCode = JPNとなっている最初のエントリを調べれば良い。同様にix_city_cc_popインデックスからそれぞれのCountryCodeの値における最初のエントリのPopulationを調べれば、GROUP BYクエリを解決できるのである。

　というわけで、GROUP BYではMIN()またはMAX()をインデックスを使って解決できる場合には少しだけ効率的になるのである。

次のページ
サブクエリ