Optimizer TraceとMySQL 5.7におけるEXPLAIN FORMAT=JSON

出典について

この記事は、Master MySQL内のMorgan Tocker氏による「Optimizer Trace and EXPLAIN FORMAT=JSON in 5.7」(2015/05/25)を翻訳したものです。

今朝、偶然にもこのStack Overflowの記事に遭遇した。

次のクエリがパフォーマンス観点で何らかの違いがあるのか知りたい

SELECT ... FROM ... WHERE someFIELD IN(1,2,3,4);

SELECT ... FROM ... WHERE someFIELD between 0 AND 5;

SELECT ... FROM ... WHERE someFIELD = 1 OR someFIELD = 2 OR someFIELD = 3 ...;

これはとても興味深い質問で、2009年に問われたら答える為の良い方法がなかった。 全てのクエリはEXPLAINにおいては同じ出力となる。 ここにsakilaスキーマを使った例を示す。

mysql> EXPLAIN SELECT * FROM film WHERE film_id BETWEEN 1 AND 5\G
mysql> EXPLAIN SELECT * FROM film WHERE film_id IN (1,2,3,4,5)\G
mysql> EXPLAIN SELECT * FROM film WHERE film_id =1 or film_id=2 or film_id=3 or film_id=4 or film_id=5\G
********* 1. row *********
           id: 1
  select_type: SIMPLE
        table: film
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 2
          ref: NULL
         rows: 5
     filtered: 100.00
        Extra: Using where

時がたって、現在ではこの違いを示すのに有効ないくつかの手段がある :)

オプティマイザトレース(Optimizer Trace)

オプティマイザトレースはMySQL 5.6から導入された診断ツールであり、オプティマイザが内部でどのように動作しているかを表す。 EXPLAINと似たものであるが、次の2, 3の注目すべき違いがある。

• 実行しようとしている実行計画を表示するだけでなく、代替となる選択肢も表示する
• オプティマイザトレースを有効にしてから、実際のクエリを実行する
• 出力結果がはるかに詳細である

ここに3種類のクエリに対する出力結果を示す。

1. SELECT * FROM film WHERE film_id BETWEEN 1 AND 5
2. SELECT * FROM film WHERE film_id IN (1,2,3,4,5)
3. SELECT * FROM film WHERE film_id =1 or film_id=2 or film_id=3 or film_id=4 or film_id=5

違いは何か?

オプティマイザトレースの出力は、最初のクエリが1つのレンジで実行され、2つ目と3つ目のクエリは一方で5つの1つの値を持つレンジで実行されることを示している。

              "chosen_range_access_summary": {
                "range_access_plan": {
                  "type": "range_scan",
                  "index": "PRIMARY",
                  "rows": 5,
                  "ranges": [
                    "1 <= film_id <= 1",
                    "2 <= film_id <= 2",
                    "3 <= film_id <= 3",
                    "4 <= film_id <= 4",
                    "5 <= film_id <= 5"
                  ]
                },
                "rows_for_plan": 5,
                "cost_for_plan": 6.0168,
                "chosen": true
              }

これは、SHOW STATUSから得られるhandler countsからも確認できる。

BETWEEN 1 AND 5: 
 Handler_read_key: 1
 Handler_read_next: 5
IN (1,2,3,4,5):
 Handler_read_key: 5
film_id =1 or film_id=2 or film_id=3 or film_id=4 or film_id=5:
 Handler_read_key: 5

したがって、「BETWEEN 1 AND 5」が最も安価であるだろうといえ、その理由はキーが1つで、あとは終わりまでnextをを繰り返している為である。 オプティマイザも私の考えに同意しているようである。 1つのレンジと、5回のnextのコストは、先程が6.0168であったのに対し、2.0168となっている。

              "chosen_range_access_summary": {
                "range_access_plan": {
                  "type": "range_scan",
                  "index": "PRIMARY",
                  "rows": 5,
                  "ranges": [
                    "1 <= film_id <= 5"
                  ]
                },
                "rows_for_plan": 5,
                "cost_for_plan": 2.0168,
                "chosen": true
              }
            }

補足をすると、コストの単位は、だいたいランダムIO 1回に対する論理的な表現である。これは異なる実行計画のコストを比較するのに安定した指標である。

レンジは全て等しくない

これを実際に示すのにより良い例は、次の2つのレンジの違いであろう。

• SELECT * FROM film WHERE film_id BETWEEN 1 and 20
• SELECT * FROM film WHERE (film_id BETWEEN 1 and 10) or (film_id BETWEEN 911 and 920)

2番目の例は2つの別々のレンジを実行する必要があることは明らかである。EXPLANでは 違いは示されず 、両方のクエリは同じようにみえるだろう。

********* 1. row *********
           id: 1
  select_type: SIMPLE
        table: film
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 2
          ref: NULL
         rows: 20
     filtered: 100.00
        Extra: Using where

2つの異なった別々のレンジは、別のページかもしれないし、バッファプール上でのキャッシュ効率が違うはずである。この2つは区別しうるはずである。

EXPLAIN FORMAT=JSON

EXPLAIN FORMAT=JSONは、MySQL 5.6でオプティマイザトレースとともに導入されたが、MySQL 5.7で本当に便利なものとなった。 JSON出力にコスト情報(attached_conditionとして別々のレンジもわかる)が含まれるようになった。

********* 1. row *********
EXPLAIN: {
  "query_block": {
"select_id": 1,
"cost_info": {
  "query_cost": "10.04"
},
"table": {
  "table_name": "film",
  "access_type": "range",
  "possible_keys": [
    "PRIMARY"
  ],
  "key": "PRIMARY",
  "used_key_parts": [
    "film_id"
  ],
  "key_length": "2",
  "rows_examined_per_scan": 20,
  "rows_produced_per_join": 20,
  "filtered": "100.00",
  "cost_info": {
    "read_cost": "6.04",
    "eval_cost": "4.00",
    "prefix_cost": "10.04",
    "data_read_per_join": "15K"
  },
  "used_columns": [
    "film_id",
    "title",
    "description",
    "release_year",
    "language_id",
    "original_language_id",
    "rental_duration",
    "rental_rate",
    "length",
    "replacement_cost",
    "rating",
    "special_features",
    "last_update"
  ],
  "attached_condition": "((`film`.`film_id` between 1 and 10) or (`film`.`film_id` between 911 and 920))"
}
  }
}

FORMAT=JSONをつけると出力にコストが表示され、2つのレンジのコストが10.04であることが分かり、1つの大きなレンジが9.04(ここには示されていない)であるのと比較できる。 これらのクエリはEXPLAINの出力が同じでも、コストが異なる。

まとめ

多くのユーザが「JOINは遅い」といっているのを聞いてきたが、このような広義な主張には大きさや影響度(magnitude)が欠けている。 EXPLAINのコスト情報を含めることで、全てのユーザーと共通言語で会話することが出来る。今や、「このJOINは高価である」といえ、これはよりよく区別できる :)

今やオプティマイザトレースを使い始めるときであり、そして、とりわけMySQL 5.7ではEXPLAINを使うのをやめ、代わりにEXPLAIN FORMAT=JSONを使うのである。