The OPTIMIZER_TRACE table was added in MySQL 5.6.3.
The OPTIMIZER_TRACE table provides information produced by the optimizer tracing capability.
The OPTIMIZER_TRACE table contains information about traced statements.
The table has these columns:
• QUERY: the statement text
• TRACE: the trace, in JSON format (see json.org: basically it has scalars (number, string, bool) and structures (either arrays or associative arrays))
• MISSING_BYTES_BEYOND_MAX_MEM_SIZE (explained further below)
• MISSING_PRIVILEGES (explained further below).

参考:
http://dev.mysql.com/doc/refman/5.6/en/optimizer-trace-table.html
http://dev.mysql.com/doc/internals/en/optimizer-trace-system-variable.html
http://dev.mysql.com/doc/internals/en/information-schema-optimizer-trace-table.html

通常運用は、EXPLAINで良さそうです。
EXPLAINよりも詳しく見たい場合に使う感じでしょうかね。

mysql> show variables like 'OPTIMIZER_TRACE';
+-----------------+--------------------------+
| Variable_name   | Value                    |
+-----------------+--------------------------+
| optimizer_trace | enabled=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> select * from payment where payment_id >= 1 and payment_id <= 10;
+------------+-------------+----------+-----------+--------+---------------------+---------------------+
| payment_id | customer_id | staff_id | rental_id | amount | payment_date        | last_update         |
+------------+-------------+----------+-----------+--------+---------------------+---------------------+
|          1 |           1 |        1 |        76 |   2.99 | 2005-05-25 11:30:37 | 2006-02-15 22:12:30 |
|          2 |           1 |        1 |       573 |   0.99 | 2005-05-28 10:35:23 | 2006-02-15 22:12:30 |
|          3 |           1 |        1 |      1185 |   5.99 | 2005-06-15 00:54:12 | 2006-02-15 22:12:30 |
|          4 |           1 |        2 |      1422 |   0.99 | 2005-06-15 18:02:53 | 2006-02-15 22:12:30 |
|          5 |           1 |        2 |      1476 |   9.99 | 2005-06-15 21:08:46 | 2006-02-15 22:12:30 |
|          6 |           1 |        1 |      1725 |   4.99 | 2005-06-16 15:18:57 | 2006-02-15 22:12:30 |
|          7 |           1 |        1 |      2308 |   4.99 | 2005-06-18 08:41:48 | 2006-02-15 22:12:30 |
|          8 |           1 |        2 |      2363 |   0.99 | 2005-06-18 13:33:59 | 2006-02-15 22:12:30 |
|          9 |           1 |        1 |      3284 |   3.99 | 2005-06-21 06:24:45 | 2006-02-15 22:12:30 |
|         10 |           1 |        2 |      4526 |   5.99 | 2005-07-08 03:17:05 | 2006-02-15 22:12:30 |
+------------+-------------+----------+-----------+--------+---------------------+---------------------+
10 rows in set (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE \G
*************************** 1. row ***************************
                            QUERY: select * from payment where payment_id >= 1 and payment_id <= 10
                            TRACE: {
  "steps": &#91;
    {
      "join_preparation": {
        "select#": 1,
        "steps": &#91;
          {
            "expanded_query": "/* select#1 */ select `payment`.`payment_id` AS `payment_id`,`payment`.`customer_id` AS `customer_id`,`payment`.`staff_id` AS `staff_id`,`payment`.`rental_id` AS `rental_id`,`payment`.`amount` AS `amount`,`payment`.`payment_date` AS `payment_date`,`payment`.`last_update` AS `last_update` from `payment` where ((`payment`.`payment_id` >= 1) and (`payment`.`payment_id` <= 10))"
          }
        &#93;
      }
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": &#91;
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "((`payment`.`payment_id` >= 1) and (`payment`.`payment_id` <= 10))",
              "steps": &#91;
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "((`payment`.`payment_id` >= 1) and (`payment`.`payment_id` <= 10))"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "((`payment`.`payment_id` >= 1) and (`payment`.`payment_id` <= 10))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "((`payment`.`payment_id` >= 1) and (`payment`.`payment_id` <= 10))"
                }
              &#93;
            }
          },
          {
            "table_dependencies": &#91;
              {
                "table": "`payment`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": &#91;
                &#93;
              }
            &#93;
          },
          {
            "ref_optimizer_key_uses": &#91;
            &#93;
          },
          {
            "rows_estimation": &#91;
              {
                "table": "`payment`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 16086,
                    "cost": 3316.3
                  },
                  "potential_range_indices": &#91;
                    {
                      "index": "PRIMARY",
                      "usable": true,
                      "key_parts": &#91;
                        "payment_id"
                      &#93;
                    },
                    {
                      "index": "idx_fk_staff_id",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_fk_customer_id",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "fk_payment_rental",
                      "usable": false,
                      "cause": "not_applicable"
                    }
                  &#93;,
                  "setup_range_conditions": &#91;
                  &#93;,
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_group_by_or_distinct"
                  },
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": &#91;
                      {
                        "index": "PRIMARY",
                        "ranges": &#91;
                          "1 <= payment_id <= 10"
                        &#93;,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 10,
                        "cost": 3.0369,
                        "chosen": true
                      }
                    &#93;,
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    }
                  },
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "PRIMARY",
                      "rows": 10,
                      "ranges": &#91;
                        "1 <= payment_id <= 10"
                      &#93;
                    },
                    "rows_for_plan": 10,
                    "cost_for_plan": 3.0369,
                    "chosen": true
                  }
                }
              }
            &#93;
          },
          {
            "considered_execution_plans": &#91;
              {
                "plan_prefix": &#91;
                &#93;,
                "table": "`payment`",
                "best_access_path": {
                  "considered_access_paths": &#91;
                    {
                      "access_type": "range",
                      "rows": 10,
                      "cost": 5.0369,
                      "chosen": true
                    }
                  &#93;
                },
                "cost_for_plan": 5.0369,
                "rows_for_plan": 10,
                "chosen": true
              }
            &#93;
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "((`payment`.`payment_id` >= 1) and (`payment`.`payment_id` <= 10))",
              "attached_conditions_computation": &#91;
              &#93;,
              "attached_conditions_summary": &#91;
                {
                  "table": "`payment`",
                  "attached": "((`payment`.`payment_id` >= 1) and (`payment`.`payment_id` <= 10))"
                }
              &#93;
            }
          },
          {
            "refine_plan": &#91;
              {
                "table": "`payment`",
                "access_type": "range"
              }
            &#93;
          }
        &#93;
      }
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": &#91;
        &#93;
      }
    }
  &#93;
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
          INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.01 sec)

mysql> SET OPTIMIZER_TRACE="enabled=off";
Query OK, 0 rows affected (0.00 sec)

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

mysql>

Comments are closed.

Post Navigation