MySQL5.6版本后提供了对SQL的跟踪工具trace，通过使用trace可以让我们明白optimizer如何选择执行计划的。
使用过程：
- 打开trace，设置格式为JSON，设置trace的缓存大小，避免因为容量大小而不能显示完整的跟踪过程。
mysql> set optimizer_trace="enabled=on",end_markers_in_JSON=on;
Query OK, 0 rows affected (0.00 sec)
- 执行想做trace的SQL语句，例如执行sakila数据库中staff中address_id=3且store_id=1的记录:
mysql> select address_id, store_id from staff where address_id=3 and store_id=1;
+------------+----------+
| address_id | store_id |
+------------+----------+
| 3 | 1 |
+------------+----------+
1 row in set (0.00 sec)
- 查询information_schema.optimizer_trace，可以看到跟踪记录了
mysql> select *from information_schema.optimizer_trace \G;
*************************** 1. row ***************************
QUERY: select address_id, store_id from staff where address_id=3 and store_id=1
TRACE: {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `staff`.`address_id` AS `address_id`,`staff`.`store_id` AS `store_id` from `staff` where ((`staff`.`address_id` = 3) and (`staff`.`store_id` = 1))"
}
] /* steps */
} /* join_preparation */
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((`staff`.`address_id` = 3) and (`staff`.`store_id` = 1))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(multiple equal(3, `staff`.`address_id`) and multiple equal(1, `staff`.`store_id`))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(multiple equal(3, `staff`.`address_id`) and multiple equal(1, `staff`.`store_id`))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(multiple equal(3, `staff`.`address_id`) and multiple equal(1, `staff`.`store_id`))"
}
] /* steps */
} /* condition_processing */
},
{
"table_dependencies": [
{
"table": "`staff`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
] /* depends_on_map_bits */
}
] /* table_dependencies */
},
{
"ref_optimizer_key_uses": [
{
"table": "`staff`",
"field": "store_id",
"equals": "1",
"null_rejecting": false
},
{
"table": "`staff`",
"field": "address_id",
"equals": "3",
"null_rejecting": false
}
] /* ref_optimizer_key_uses */
},
{
"rows_estimation": [
{
"table": "`staff`",
"range_analysis": {
"table_scan": {
"rows": 2,
"cost": 6.5
} /* table_scan */,
"potential_range_indices": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_fk_store_id",
"usable": true,
"key_parts": [
"store_id",
"staff_id"
] /* key_parts */
},
{
"index": "idx_fk_address_id",
"usable": true,
"key_parts": [
"address_id",
"staff_id"
] /* key_parts */
}
] /* potential_range_indices */,
"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": "idx_fk_store_id",
"ranges": [
"1 <= store_id <= 1"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 1,
"cost": 2.21,
"chosen": true
},
{
"index": "idx_fk_address_id",
"ranges": [
"3 <= address_id <= 3"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 1,
"cost": 2.21,
"chosen": false,
"cause": "cost"
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"intersecting_indices": [
{
"index": "idx_fk_store_id",
"index_scan_cost": 1,
"cumulated_index_scan_cost": 1,
"disk_sweep_cost": 0.9125,
"cumulated_total_cost": 1.9125,
"usable": true,
"matching_rows_now": 1,
"isect_covering_with_this_index": false,
"chosen": true
},
{
"index": "idx_fk_address_id",
"index_scan_cost": 1,
"cumulated_index_scan_cost": 2,
"disk_sweep_cost": 0,
"cumulated_total_cost": 2,
"usable": true,
"matching_rows_now": 0.5,
"isect_covering_with_this_index": true,
"chosen": false,
"cause": "does_not_reduce_cost"
}
] /* intersecting_indices */,
"clustered_pk": {
"clustered_pk_added_to_intersect": false,
"cause": "no_clustered_pk_index"
} /* clustered_pk */,
"chosen": false,
"cause": "too_few_indexes_to_merge"
} /* analyzing_roworder_intersect */
} /* analyzing_range_alternatives */,
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "idx_fk_store_id",
"rows": 1,
"ranges": [
"1 <= store_id <= 1"
] /* ranges */
} /* range_access_plan */,
"rows_for_plan": 1,
"cost_for_plan": 2.21,
"chosen": true
} /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
},
{
"considered_execution_plans": [
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`staff`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "idx_fk_store_id",
"rows": 1,
"cost": 1.2,
"chosen": true
},
{
"access_type": "ref",
"index": "idx_fk_address_id",
"rows": 1,
"cost": 1.2,
"chosen": false
},
{
"access_type": "range",
"cause": "heuristic_index_cheaper",
"chosen": false
}
] /* considered_access_paths */
} /* best_access_path */,
"cost_for_plan": 1.2,
"rows_for_plan": 1,
"chosen": true
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": {
"original_condition": "((`staff`.`store_id` = 1) and (`staff`.`address_id` = 3))",
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`staff`",
"attached": "(`staff`.`address_id` = 3)"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"refine_plan": [
{
"table": "`staff`"
}
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
"join_execution": {
"select#": 1,
"steps": [
] /* steps */
} /* join_execution */
}
] /* steps */
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)
网友评论