mysql trace工具_mysql--trace工具的使用

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)