![variable.jp [データベース,パフォーマンス,運用]](https://variable.jp/wp-content/uploads/2013/09/cropped-variable.jp_.jpg)
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>