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": [ { "join_preparation": { "select#": 1, "steps": [ { "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))" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "((`payment`.`payment_id` >= 1) and (`payment`.`payment_id` <= 10))", "steps": [ { "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))" } ] } }, { "table_dependencies": [ { "table": "`payment`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ ] }, { "rows_estimation": [ { "table": "`payment`", "range_analysis": { "table_scan": { "rows": 16086, "cost": 3316.3 }, "potential_range_indices": [ { "index": "PRIMARY", "usable": true, "key_parts": [ "payment_id" ] }, { "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" } ], "setup_range_conditions": [ ], "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" }, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "PRIMARY", "ranges": [ "1 <= payment_id <= 10" ], "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 10, "cost": 3.0369, "chosen": true } ], "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": [ "1 <= payment_id <= 10" ] }, "rows_for_plan": 10, "cost_for_plan": 3.0369, "chosen": true } } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`payment`", "best_access_path": { "considered_access_paths": [ { "access_type": "range", "rows": 10, "cost": 5.0369, "chosen": true } ] }, "cost_for_plan": 5.0369, "rows_for_plan": 10, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "((`payment`.`payment_id` >= 1) and (`payment`.`payment_id` <= 10))", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`payment`", "attached": "((`payment`.`payment_id` >= 1) and (`payment`.`payment_id` <= 10))" } ] } }, { "refine_plan": [ { "table": "`payment`", "access_type": "range" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ ] } } ] } 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>