OPTIMIZER_TRACEでMySQLの気持ちを理解する

こんにちは。マイケルです。 前回、MySQLのEXPLAINコマンドの結果を見やすく整形してくれる pt-visual-explain というツールの紹介をしました。 Percona Took Kitでビジュアルに実行計画を確認しよう

ツリー構造である実行計画を、ちゃんとツリー構造で表示してれるため、処理やJOINの順番が明確にわかるというものでした。

pt-visual-explain でもわからないこと

上記の記事のように、どのテーブルからどの順番でテーブルがJOINされていくのかを確認していると、"なぜその順番で処理しようとしたのか" がわからない場合があります。 具体的には以下のようなSQLの場合です。 (前回の記事と同じサンプルデータを使用しています。また★マークは私が書き加えたコメントです)

SQL

EXPLAIN SELECT
    e.emp_no,
    e.emp_name,
    e.dept_no,
    d.dept_name,
    d.location_no,
    l.location_name
FROM
    emp e
INNER JOIN
    dept d
ON
    e.dept_no = d.dept_no
INNER JOIN
    location l
ON
    d.location_no = l.location_no
WHERE
    e.dept_no = 10;

pt-visual-explain の結果

JOIN
+- Bookmark lookup
|  +- Table
|  |  table          e
|  |  possible_keys  emp_dept_no_idx
|  +- Index lookup
|     key            e-&>emp_dept_no_idx
|     possible_keys  emp_dept_no_idx
|     key_len        5
|     ref            const
|     rows           5
+- JOIN
   +- Bookmark lookup
   |  +- Table
   |  |  table          l
   |  |  possible_keys  PRIMARY
   |  +- Constant index lookup
   |     key            l-&>PRIMARY
   |     possible_keys  PRIMARY
   |     key_len        4
   |     ref            const
   |     rows           1
   +- Bookmark lookup       -- ★1番最初にアクセスされたテーブル ここから
      +- Table
      |  table          d   -- ★deptテーブル
      |  possible_keys  PRIMARY,dept_loc_no_idx
      +- Constant index lookup
         key            d-&>PRIMARY  -- ★dept_no列のINDEX
         possible_keys  PRIMARY,dept_loc_no_idx
         key_len        4
         ref            const
         rows           1   -- ★1行ヒット

まず、WHERE句の絞り込み条件を見ると、 e.dept_no = 10 とあります。 つまり、empテーブルからdept_noが10の行を取得しようとしています。 それに対して、 pt-visual-explain の結果をみると、一番最初にアクセスされているのはdeptテーブルになっています。 そして、使用されたINDEXとその結果の行数をみると、 dept_no列のINDEXを使用して1行のみヒットしています。

つまり、empテーブルのdept_noが10のものを探そうとしたのに、なぜかdeptテーブルのdept_noが10のものを取り出しているわけです。 SQLで命令したのとは異なる動きをしています。

なぜこんなことをしているのか。 MySQLさんの気持ち、さっぱりわからないわー。 もうマジMySQLさんキマグレ。

というようなことがたまに起こります。 特にINDEXチューニングしようとしていると、想定とは異なるJOINの順番になっていて、なんでやー!となったりします。

OPTIMIZER_TRACE

上記のように、「なんでそんな風にしようと思ったん?」と感じたときに非常に便利なのが、 OPTIMIZER_TRACE 機能です。 これは、MySQLのOPTIMIZERが、どんな順番で何を考えたのかの思考の手順をトレースしてくれる機能です。

使い方は以下の簡単3ステップ

  1. set optimizer_trace="enabled=on";
  2. SQL文実行
  3. SELECT * FROM information_schema.optimizer_trace\G

関連するパラメータや詳細な見方は、以下のエントリーが詳しいので合わせて御覧ください。 http://nippondanji.blogspot.jp/2015/12/blog-post.html

さっそくやってみる

先程のSQLの OPTIMIZER_TRACEを早速確認してみます。 結果は以下の通り。 (長いんですけど、いったん全部出します)

mysql> SELECT * FROM information_schema.optimizer_trace\G
*************************** 1. row ***************************
                            QUERY: SELECT e.emp_no, e.emp_name, e.dept_no, d.dept_name, d.location_no, l.location_name FROM emp e INNER JOIN dept d ON e.dept_no = d.dept_no INNER JOIN location l ON d.location_no = l.location_no WHERE e.dept_no = 10        -- ★(1)
                            TRACE: {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `e`.`emp_no` AS `emp_no`,`e`.`emp_name` AS `emp_name`,`e`.`dept_no` AS `dept_no`,`d`.`dept_name` AS `dept_name`,`d`.`location_no` AS `location_no`,`l`.`location_name` AS `location_name` from ((`emp` `e` join `dept` `d` on((`e`.`dept_no` = `d`.`dept_no`))) join `location` `l` on((`d`.`location_no` = `l`.`location_no`))) where (`e`.`dept_no` = 10)"   -- ★(2)
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "transformations_to_nested_joins": {    -- ★(3)
              "transformations": [
                "JOIN_condition_to_WHERE",
                "parenthesis_removal"
              ],
              "expanded_query": "/* select#1 */ select `e`.`emp_no` AS `emp_no`,`e`.`emp_name` AS `emp_name`,`e`.`dept_no` AS `dept_no`,`d`.`dept_name` AS `dept_name`,`d`.`location_no` AS `location_no`,`l`.`location_name` AS `location_name` from `emp` `e` join `dept` `d` join `location` `l` where ((`e`.`dept_no` = 10) and (`d`.`location_no` = `l`.`location_no`) and (`e`.`dept_no` = `d`.`dept_no`))"
            }
          },
          {
            "condition_processing": {       -- ★(4)
              "condition": "WHERE",
              "original_condition": "((`e`.`dept_no` = 10) and (`d`.`location_no` = `l`.`location_no`) and (`e`.`dept_no` = `d`.`dept_no`))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(multiple equal(10, `e`.`dept_no`, `d`.`dept_no`) and multiple equal(`d`.`location_no`, `l`.`location_no`))"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(multiple equal(10, `e`.`dept_no`, `d`.`dept_no`) and multiple equal(`d`.`location_no`, `l`.`location_no`))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(multiple equal(10, `e`.`dept_no`, `d`.`dept_no`) and multiple equal(`d`.`location_no`, `l`.`location_no`))"
                }
              ]
            }
          },
          {
            "table_dependencies": [
              {
                "table": "`emp` `e`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ]
              },
              {
                "table": "`dept` `d`",
                "row_may_be_null": false,
                "map_bit": 1,
                "depends_on_map_bits": [
                ]
              },
              {
                "table": "`location` `l`",
                "row_may_be_null": false,
                "map_bit": 2,
                "depends_on_map_bits": [
                ]
              }
            ]
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "`emp` `e`",
                "field": "dept_no",
                "equals": "10",
                "null_rejecting": false
              },
              {
                "table": "`dept` `d`",
                "field": "dept_no",
                "equals": "10",
                "null_rejecting": false
              },
              {
                "table": "`dept` `d`",
                "field": "location_no",
                "equals": "`l`.`location_no`",
                "null_rejecting": false
              },
              {
                "table": "`dept` `d`",
                "field": "dept_no",
                "equals": "10",
                "null_rejecting": false
              },
              {
                "table": "`location` `l`",
                "field": "location_no",
                "equals": "`d`.`location_no`",
                "null_rejecting": true
              }
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "`emp` `e`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 24,
                    "cost": 7.9
                  },
                  "potential_range_indices": [
                    {
                      "index": "PRIMARY",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "emp_dept_no_idx",
                      "usable": true,
                      "key_parts": [
                        "dept_no",
                        "emp_no"
                      ]
                    },
                    {
                      "index": "emp_emp_name_idx",
                      "usable": false,
                      "cause": "not_applicable"
                    }
                  ],
                  "setup_range_conditions": [
                  ],
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_single_table"
                  },
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "emp_dept_no_idx",
                        "ranges": [
                          "10 lt;= dept_no lt;= 10"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 5,
                        "cost": 7.01,
                        "chosen": true
                      }
                    ],
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    }
                  },
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "emp_dept_no_idx",
                      "rows": 5,
                      "ranges": [
                        "10 lt;= dept_no lt;= 10"
                      ]
                    },
                    "rows_for_plan": 5,
                    "cost_for_plan": 7.01,
                    "chosen": true
                  }
                }
              },
              {
                "table": "`dept` `d`",
                "rows": 1,
                "cost": 1,
                "table_type": "const",
                "empty": false
              },
              {
                "table": "`location` `l`",
                "rows": 1,
                "cost": 1,
                "table_type": "const",
                "empty": false
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                  "`dept` `d`",
                  "`location` `l`"
                ],
                "table": "`emp` `e`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "emp_dept_no_idx",
                      "rows": 5,
                      "cost": 3.4,
                      "chosen": true
                    },
                    {
                      "access_type": "range",
                      "cause": "heuristic_index_cheaper",
                      "chosen": false
                    }
                  ]
                },
                "cost_for_plan": 3.4,
                "rows_for_plan": 5,
                "chosen": true
              }
            ]
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "((`e`.`dept_no` = 10))",
              "attached_conditions_computation": [
              ],
              "attached_conditions_summary": [
                {
                  "table": "`emp` `e`",
                  "attached": null
                }
              ]
            }
          },
          {
            "refine_plan": [
              {
                "table": "`emp` `e`"
              }
            ]
          }
        ]
      }
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ]
      }
    }
  ]
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
          INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)

大量の出力で拒絶反応がでちゃいますか? それともワクワクしますか? 私はこういうトレース結果を読み解いたりするのが大好きなので、めっちゃテンションあがります。

結果の確認

とりあえず今回注目して頂きたい点だけ抜粋します。

(1) 元SQL トレース結果の1行目にあるのが、我々が発行した元のSQLです。

(2) 整形後のSQL 決められたフォーマットにSQLを整形しています。 ここではあくまでも整形しているだけで、どんな順でJOINするかなどはまだ考慮していません。

(3) transformations_to_nested_joins ON句に記述されていた結合条件を、全てWHERE句に移動させる形にSQLを変形しています。 結合条件はONでもWHEREでも書けるので、SQLの意味に変更はありません。

(4) condition_processing 所謂「述語の推移」を行っている部分です。 推移というのが数学の話なので、こういう言葉を使うと拒絶反応を起こす人もいると思いますが、中身を見ていけばなんとくやっていることがわかると思います。 該当部分だけ抜粋します。

            "condition_processing": {       -- ★(4)
              "condition": "WHERE",
              "original_condition": "((`e`.`dept_no` = 10) and (`d`.`location_no` = `l`.`location_no`) and (`e`.`dept_no` = `d`.`dept_no`))",   -- ★(4)-1
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(multiple equal(10, `e`.`dept_no`, `d`.`dept_no`) and multiple equal(`d`.`location_no`, `l`.`location_no`))"  -- ★(4)-2
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(multiple equal(10, `e`.`dept_no`, `d`.`dept_no`) and multiple equal(`d`.`location_no`, `l`.`location_no`))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(multiple equal(10, `e`.`dept_no`, `d`.`dept_no`) and multiple equal(`d`.`location_no`, `l`.`location_no`))"
                }
              ]
            }

(4)-1 ではいったんWHERE句に移動させた条件がそのまま出力されています。 (4)-2 では、複数の条件式の中で、同じ意味(結果)になるものをひとまとめにしています。 具体的には

`e`.`dept_no` = 10

がある状態で、

`e`.`dept_no` = `d`.`deptno_no`

があるのなら、 e.dept_no = 10 でも、 d.dept_no = 10 で絞り込んでも、結局出てくるのは10番のデータだけということになります。 それを

(multiple equal(10, `e`.`dept_no`, `d`.`dept_no`)

のように表現します。 これによって、今回のSQLでは、empテーブルのdept_no列に対して絞り込み条件が書かれていましたが、これはdeptテーブルのdept_no列への絞り込に書き換えたとしても、結果は同じであるということが決定されます。

ここまでできれば、あとはどっちで絞り込んだほうが速いのかの判定です。 MySQLでは、SQLの各処理で必要となるリソース(ディスクI/O、メモリ使用量、CPU使用量、時間など)をコストという単位に数値化して内部で管理しています。 コストが高い = 遅い コストが低い = 速い と思って頂ければだいたい合っています。 OPTIMIZERは、どの順番でJOINして、どのINDEXを使えば最もコストが低くなるのかを見積もって、一番コストが低くなるやり方を採用します。 そこらへんの判断の過程もトレース結果には出ているのですが、長くなるので今回は省略します。 いずれ別のエントリーで説明ができればと思っています。

まとめ

今回のポイントは以下の3点です。

  • 元は empテーブルのdept_noに絞り込み条件を設定していた
  • MySQLのOPTIMIZERが、deptテーブルのdept_noへの絞り込みにしても結果が変わらないことを数学的に確定させた
  • empテーブルで絞り込むのがよいか、deptテーブルで絞り込むのがよいか考えて、よさそうな方を選択する

で、結果として、deptテーブルで絞り込むという選択をしたのが、EXPLAINの結果だったわけです。 このように、OPTIMIZER_TRACEの結果を読み解いていくことで、どんなふうにMySQLSQLを処理しているのか、つまりMySQLの気持ちが見えてきます。 (今回省略したコスト計算の辺りも理解すると、もっと深くMySQLの気持ちがわかるようになります)

なんか思うようにSQLのパフォーマンスが出ないなーとか、思った通りのINDEXを使ってくれないなーという時は、EXPLAINの結果と合わせて是非OPTIMIZER_TRACEの結果も眺めてみてください。 きっと、今よりももっと、MySQLと仲良しになれると思います。