hironomiu's Blog

こそっと書いていきます。twitter_id:hironomiu

mysql バージョン5.6で使えるoptimizer_trace

MySQL5.6で実装されたoptimizer_traceを触ってみた

設定できるパラメータ

enabled 初期値:off
end_marker 初期値:off
one_line 初期値:off

設定

mysql> show variables like 'opttimizer_trace';
Empty set (0.00 sec)

mysql> show variables like 'optimizer_trace';
 +-----------------+-----------------------------------------+
 | Variable_name   | Value                                   |
 +-----------------+-----------------------------------------+
 | optimizer_trace | enabled=off,end_marker=off,one_line=off |
 +-----------------+-----------------------------------------+
1 row in set (0.00 sec)

mysql> set optimizer_trace='enabled=on';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'optimizer_trace';
 +-----------------+----------------------------------------+
 | Variable_name   | Value                                  |
 +-----------------+----------------------------------------+
 | optimizer_trace | enabled=on,end_marker=off,one_line=off |
 +-----------------+----------------------------------------+
1 row in set (0.00 sec)

SQL実行、information_schema.optimizer_trace参照

mysql> select * from hoge;
Empty set (0.00 sec)

mysql> select * from information_schema.OPTIMIZER_TRACE;
 +--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+-------------------------+
| QUERY              | TRACE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            | MISSING_BYTES_BEYOND_MAX_MEM_SIZE | INSUFFICIENT_PRIVILEGES |
 +--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+-------------------------+
| select * from hoge | {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `hogedb`.`hoge`.`id` AS `id` from `hogedb`.`hoge`"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "table_dependencies": [
              {
                "database": "hogedb",
                "table": "hoge",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ]
              }
            ]
          },
          {
            "rows_estimation": [
              {
                "database": "hogedb",
                "table": "hoge",
                "table_scan": {
                  "rows": 1,
                  "cost": 1
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "database": "hogedb",
                "table": "hoge",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "scan",
                      "rows": 1,
                      "cost": 1,
                      "chosen": true
                    }
                  ]
                },
                "cost_for_plan": 1.2,
                "rows_for_plan": 1,
                "chosen": true
              }
            ]
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": null,
              "attached_conditions_computation": [
              ],
              "attached_conditions_summary": [
                {
                  "database": "hogedb",
                  "table": "hoge",
                  "attached": null
                }
              ]
            }
          },
          {
            "refine_plan": [
              {
                "database": "hogedb",
                "table": "hoge",
                "access_type": "table_scan"
              }
            ]
          }
        ]
      }
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ]
      }
    }
  ]
} |                                 0 |                       0 |
 +--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+-------------------------+
1 row in set (0.00 sec)

推奨ではないけどこういう設定方法も出来る

mysql> show variables like 'optimizer_trace';
 +-----------------+-----------------------------------------+
 | Variable_name   | Value                                   |
 +-----------------+-----------------------------------------+
 | optimizer_trace | enabled=off,end_marker=off,one_line=off |
 +-----------------+-----------------------------------------+
1 row in set (0.00 sec)

mysql> set optimizer_trace=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'optimizer_trace';
 +-----------------+----------------------------------------+
 | Variable_name   | Value                                  |
 +-----------------+----------------------------------------+
 | optimizer_trace | enabled=on,end_marker=off,one_line=off |
 +-----------------+----------------------------------------+
1 row in set (0.00 sec)