MySQL5.6提供了对SQL的跟踪trace,通过trace文件能够进一步了解为什么优化器选择A执行计划而不是选择B执行计划,帮助我们更好地理解优化器行为。
使用方式:首先打开trace,设置格式为JSON,设置trace最大能够使用的内存大小,避免解析过程中因为默认内存过小而不能够完整显示。
mysql> SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on;
Query OK, 0 rows affected (0.01 sec)
mysql> SET OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000;
Query OK, 0 rows affected (0.02 sec)
接下来执行想做trace 的SQL语句,例如想了解租赁表rental 中库存编号inventory_id 为4466 的电影拷贝在出租日期rental_date 为
2005-05-25 4:00:00~~5:00:00之间出租的记录。
mysql> select rental_id from rental where 1=1 and rental_date >='2005-05-25 04:00:00'
-> and rental_date <='2005-05-25 05:00:00'and inventory_id=4466;
+-----------+
| rental_id |
+-----------+
| 39 |
+-----------+
1 row in set (0.18 sec)
最后,检查INFORMATION_SCHEMA.OPTIMIZER_TRACE 就可以知道MYSQL是如何执行SQL的:
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
最后会输出一个格式如下的跟踪文件:
-----------------------------+-----------------------------------+-------------------------+
| select rental_id from rental where 1=1 and rental_date >='2005-05-25 04:00:00'
and rental_date <='2005-05-25 05:00:00'and inventory_id=4466 | {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `rental`.`rental_id` AS `rental_id` from `rental` where ((1 = 1) and (`rental`.`rental_date` >= '2005-05-25 04:00:00') and (`rental`.`rental_date` <= '2005-05-25 05:00:00') and (`rental`.`inventory_id` = 4466))"
}
] /* steps */
} /* join_preparation */
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((1 = 1) and (`rental`.`rental_date` >= '2005-05-25 04:00:00') and (`rental`.`rental_date` <= '2005-05-25 05:00:00') and (`rental`.`inventory_id` = 4466))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "((1 = 1) and (`rental`.`rental_date` >= '2005-05-25 04:00:00') and (`rental`.`rental_date` <= '2005-05-25 05:00:00') and multiple equal(4466, `rental`.`inventory_id`))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "((1 = 1) and (`rental`.`rental_date` >= '2005-05-25 04:00:00') and (`rental`.`rental_date` <= '2005-05-25 05:00:00') and multiple equal(4466, `rental`.`inventory_id`))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "((`rental`.`rental_date` >= '2005-05-25 04:00:00') and (`rental`.`rental_date` <= '2005-05-25 05:00:00') and multiple equal(4466, `rental`.`inventory_id`))"
}
] /* steps */
} /* condition_processing */
},
{
"substitute_generated_columns": {
} /* substitute_generated_columns */
},
{
"table_dependencies": [
{
"table": "`rental`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
] /* depends_on_map_bits */
}
] /* table_dependencies */
},
{
"ref_optimizer_key_uses": [
{
"table": "`rental`",
"field": "inventory_id",
"equals": "4466",
"null_rejecting": false
}
] /* ref_optimizer_key_uses */
},
{
"rows_estimation": [
{
"table": "`rental`",
"range_analysis": {
"table_scan": {
"rows": 16008,
"cost": 1667.4
} /* table_scan */,
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "rental_date",
"usable": true,
"key_parts": [
"rental_date",
"inventory_id",
"customer_id"
] /* key_parts */
},
{
"index": "idx_fk_inventory_id",
"usable": true,
"key_parts": [
"inventory_id",
"rental_id"
] /* key_parts */
},
{
"index": "idx_fk_customer_id",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_fk_staff_id",
"usable": false,
"cause": "not_applicable"
}
] /* potential_range_indexes */,
"best_covering_index_scan": {
"index": "rental_date",
"cost": 1607.9,
"chosen": true
} /* best_covering_index_scan */,
"setup_range_conditions": [
] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
} /* group_index_range */,
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "rental_date",
"ranges": [
"0x9975b24000 <= rental_date <= 0x9975b25000"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": true,
"rows": 10,
"cost": 1.2638,
"chosen": true
},
{
"index": "idx_fk_inventory_id",
"ranges": [
"4466 <= inventory_id <= 4466"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 5,
"cost": 4.4994,
"chosen": false,
"cause": "cost"
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
} /* analyzing_range_alternatives */,
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "rental_date",
"rows": 10,
"ranges": [
"0x9975b24000 <= rental_date <= 0x9975b25000"
] /* ranges */
} /* range_access_plan */,
"rows_for_plan": 10,
"cost_for_plan": 1.2638,
"chosen": true
} /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
},
{
"considered_execution_plans": [
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`rental`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "idx_fk_inventory_id",
"rows": 5,
"cost": 3.8245,
"chosen": true
},
{
"rows_to_scan": 10,
"access_type": "range",
"range_details": {
"used_index": "rental_date"
} /* range_details */,
"resulting_rows": 10,
"cost": 2.2638,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 10,
"cost_for_plan": 2.2638,
"chosen": true
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": {
"original_condition": "((`rental`.`inventory_id` = 4466) and (`rental`.`rental_date` >= '2005-05-25 04:00:00') and (`rental`.`rental_date` <= '2005-05-25 05:00:00'))",
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`rental`",
"attached": "((`rental`.`inventory_id` = 4466) and (`rental`.`rental_date` >= '2005-05-25 04:00:00') and (`rental`.`rental_date` <= '2005-05-25 05:00:00'))"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"refine_plan": [
{
"table": "`rental`"
}
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
"join_execution": {
"select#": 1,
"steps": [
] /* steps */
} /* join_execution */
}
] /* steps */
} | 0 | 0 |