SHOEISHA iD

※旧SEメンバーシップ会員の方は、同じ登録情報(メールアドレス&パスワード)でログインいただけます

EnterpriseZine(エンタープライズジン)編集部では、情報システム担当、セキュリティ担当の方々向けに、EnterpriseZine Day、Security Online Day、DataTechという、3つのイベントを開催しております。それぞれ編集部独自の切り口で、業界トレンドや最新事例を網羅。最新の動向を知ることができる場として、好評を得ています。

最新イベントはこちら!

Security Online Day 2025 春の陣(開催予定)

2025年3月18日(火)オンライン開催

EnterpriseZine(エンタープライズジン)編集部では、情報システム担当、セキュリティ担当の方々向けの講座「EnterpriseZine Academy」や、すべてのITパーソンに向けた「新エバンジェリスト養成講座」などの講座を企画しています。EnterpriseZine編集部ならではの切り口・企画・講師セレクトで、明日を担うIT人材の育成をミッションに展開しております。

お申し込み受付中!

EnterpriseZine(エンタープライズジン)

EnterpriseZine編集部が最旬ITトピックの深層に迫る。ここでしか読めない、エンタープライズITの最新トピックをお届けします。

『EnterpriseZine Press』

2024年秋号(EnterpriseZine Press 2024 Autumn)特集「生成AI時代に考える“真のDX人材育成”──『スキル策定』『実践』2つの観点で紐解く」

MySQLチューニング虎の巻

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

003


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()をインデックスを使って解決できる場合には少しだけ効率的になるのである。

次のページ
サブクエリ

この記事は参考になりましたか?

  • Facebook
  • X
  • Pocket
  • note
MySQLチューニング虎の巻連載記事一覧

もっと読む

この記事の著者

奥野 幹也 (オクノ ミキヤ)

日本オラクル株式会社
MySQL Global Business Unitテクニカルアナリスト

※プロフィールは、執筆時点、または直近の記事の寄稿時点での内容です

この記事は参考になりましたか?

この記事をシェア

EnterpriseZine(エンタープライズジン)
https://enterprisezine.jp/article/detail/3695 2012/02/10 18:27

Job Board

AD

おすすめ

アクセスランキング

アクセスランキング

イベント

EnterpriseZine(エンタープライズジン)編集部では、情報システム担当、セキュリティ担当の方々向けに、EnterpriseZine Day、Security Online Day、DataTechという、3つのイベントを開催しております。それぞれ編集部独自の切り口で、業界トレンドや最新事例を網羅。最新の動向を知ることができる場として、好評を得ています。

新規会員登録無料のご案内

  • ・全ての過去記事が閲覧できます
  • ・会員限定メルマガを受信できます

メールバックナンバー

アクセスランキング

アクセスランキング