3
optimizer tracing
source link: https://wakzz.cn/2019/04/06/mysql/optimizer%20tracing/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
optimizer tracing
祈雨的博客
2019-04-06
mysql从5.6的版本开始提供optimizer tracing功能，开发者可以通过该功能查看mysql的sql解释器的整个执行过程。
# Turn tracing on (it's off by default):
SET optimizer_trace="enabled=on";
SELECT ...; # your query here
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
# possibly more queries...
# When done with tracing, disable it:
SET optimizer_trace="enabled=off";
创建一个如下测试表，给name和age加上索引。
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(35) NOT NULL,
`age` int(11) NOT NULL,
`city` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`) USING BTREE,
KEY `age` (`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
explain
explain select * from user where name = 'tom' and age = 23 order by city;
+----+-------------+-------+------------+-------------+---------------+----------+---------+------+------+----------+--------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------------+---------------+----------+---------+------+------+----------+--------------------------------------------------------+
| 1 | SIMPLE | user | NULL | index_merge | name,age | name,age | 107,4 | NULL | 1 | 100 | Using intersect(name,age); Using where; Using filesort |
+----+-------------+-------+------------+-------------+---------------+----------+---------+------+------+----------+--------------------------------------------------------+
explain显示mysql同时使用了name和age两个索引（索引合并index_merge）。
optimizer trace
SET optimizer_trace="enabled=on";
select * from user where name = 'tom' and age = 23 order by city;
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
SET optimizer_trace="enabled=off";
{
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `user`.`id` AS `id`,`user`.`name` AS `name`,`user`.`age` AS `age`,`user`.`city` AS `city` from `user` where ((`user`.`name` = 'tom') and (`user`.`age` = 23)) order by `user`.`city`"
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((`user`.`name` = 'tom') and (`user`.`age` = 23))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "((`user`.`name` = 'tom') and multiple equal(23, `user`.`age`))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "((`user`.`name` = 'tom') and multiple equal(23, `user`.`age`))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "((`user`.`name` = 'tom') and multiple equal(23, `user`.`age`))"
}
]
}
},
{
"substitute_generated_columns": {}
},
{
"table_dependencies": [
{
"table": "`user`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": []
}
]
},
{
"ref_optimizer_key_uses": [
{
"table": "`user`",
"field": "name",
"equals": "'tom'",
"null_rejecting": false
},
{
"table": "`user`",
"field": "age",
"equals": "23",
"null_rejecting": false
}
]
},
{
"rows_estimation": [ // 预估不同表访问方法的访问成本
{
"table": "`user`",
"range_analysis": {
"table_scan": { // 全表扫描的行数和成本
"rows": 5,
"cost": 4.1
},
"potential_range_indexes": [ // 分析可能使用的索引
{ // 主键索引不可用
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{ // name索引可用
"index": "name",
"usable": true,
"key_parts": [
"name",
"id"
]
},
{ // age索引可用
"index": "age",
"usable": true,
"key_parts": [
"age",
"id"
]
}
],
"setup_range_conditions": [],
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
},
"analyzing_range_alternatives": { // 分析各种可能使用的索引的成本
"range_scan_alternatives": [
{
"index": "name",
"ranges": [
"tom <= name <= tom"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false, // 是否索引覆盖
"rows": 2, // 使用该索引获取的记录条数
"cost": 3.41, // 使用该索引的成本
"chosen": true // 是否使用该索引
},
{
"index": "age",
"ranges": [
"23 <= age <= 23"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 2,
"cost": 3.41,
"chosen": false,
"cause": "cost"
}
],
"analyzing_roworder_intersect": { // 分析使用索引合并的成本
"intersecting_indexes": [
{
"index": "name",
"index_scan_cost": 1.0135,
"cumulated_index_scan_cost": 1.0135,
"disk_sweep_cost": 1.75,
"cumulated_total_cost": 2.7635,
"usable": true,
"matching_rows_now": 2,
"isect_covering_with_this_index": false,
"chosen": true
},
{
"index": "age",
"index_scan_cost": 1.001,
"cumulated_index_scan_cost": 2.0145,
"disk_sweep_cost": 0,
"cumulated_total_cost": 2.0145,
"usable": true,
"matching_rows_now": 0.8,
"isect_covering_with_this_index": false,
"chosen": true
}
],
"clustered_pk": {
"clustered_pk_added_to_intersect": false,
"cause": "no_clustered_pk_index"
},
"rows": 1,
"cost": 2.0145,
"covering": false,
"chosen": true
}
},
"chosen_range_access_summary": { // 最佳查询方案
"range_access_plan": {
"type": "index_roworder_intersect",
"rows": 1,
"cost": 2.0145,
"covering": false,
"clustered_pk_scan": false,
"intersect_of": [
{
"type": "range_scan",
"index": "name",
"rows": 2,
"ranges": [
"tom <= name <= tom"
]
},
{
"type": "range_scan",
"index": "age",
"rows": 2,
"ranges": [
"23 <= age <= 23"
]
}
]
},
"rows_for_plan": 1,
"cost_for_plan": 2.0145,
"chosen": true
}
}
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [],
"table": "`user`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "name",
"rows": 2,
"cost": 2.4,
"chosen": true
},
{
"access_type": "ref",
"index": "age",
"rows": 2,
"cost": 2.4,
"chosen": false
},
{
"rows_to_scan": 1,
"access_type": "range",
"range_details": {
"used_index": "intersect(name,age)"
},
"resulting_rows": 1,
"cost": 2.2145,
"chosen": true,
"use_tmp_table": true
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 1,
"cost_for_plan": 2.2145,
"sort_cost": 1,
"new_cost_for_plan": 3.2145,
"chosen": true
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": "((`user`.`age` = 23) and (`user`.`name` = 'tom'))",
"attached_conditions_computation": [],
"attached_conditions_summary": [
{
"table": "`user`",
"attached": "((`user`.`age` = 23) and (`user`.`name` = 'tom'))"
}
]
}
},
{
"clause_processing": {
"clause": "ORDER BY",
"original_clause": "`user`.`city`",
"items": [
{
"item": "`user`.`city`"
}
],
"resulting_clause_is_simple": true,
"resulting_clause": "`user`.`city`"
}
},
{
"refine_plan": [
{
"table": "`user`"
}
]
}
]
}
},
{
"join_execution": {
"select#": 1,
"steps": [
{
"filesort_information": [
{
"direction": "asc",
"table": "`user`",
"field": "city"
}
],
"filesort_priority_queue_optimization": {
"usable": false,
"cause": "not applicable (no LIMIT)"
},
"filesort_execution": [],
"filesort_summary": {
"rows": 1,
"examined_rows": 1,
"number_of_tmp_files": 0,
"sort_buffer_size": 261880,
"sort_mode": "<sort_key, packed_additional_fields>"
}
}
]
}
}
]
}
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK