sql执行过程分析optimizer tracing

蔚落 2022-02-13 08:39 250阅读 0赞

## 介绍 ##

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;

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>"
                            }
                        }
                    ]
                }
            }
        ]
    }

[optimizer tracing]: https://dev.mysql.com/doc/internals/en/optimizer-tracing.html