OPTIMIZER_TRACE - xiaoboluo768/mysql-system-schema GitHub Wiki
- 该表提供优化程序跟踪功能产生的信息。 跟踪功能默认关闭，使用optimizer_trace系统变量启用跟踪功能
- 如果开启该功能，则每个会话只能跟踪他自己执行的语句，不能看到其他会话执行的语句，且每个会话只能记录最后一个跟踪的SQL语句
- 该表为InnoDB引擎临时表
- 表定义语句
CREATE TEMPORARY TABLE `OPTIMIZER_TRACE` (
`QUERY` longtext NOT NULL,
`TRACE` longtext NOT NULL,
`MISSING_BYTES_BEYOND_MAX_MEM_SIZE` int(20) NOT NULL DEFAULT '0',
`INSUFFICIENT_PRIVILEGES` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8- 表字段含义
- QUERY：被跟踪的SQL语句文本
- TRACE：JSON格式的跟踪信息
- MISSING_BYTES_BEYOND_MAX_MEM_SIZE：optimizer_trace_max_mem_size系统变量(默认16384字节)设置跟踪SQL语句时用于存放跟踪信息的内存总量限制，当跟踪的信息超过了该变量设置的值时，则将会被截断（记录不完整），在OPTIMIZER_TRACE表中的MISSING_BYTES_BEYOND_MAX_MEM_SIZE字段用于记录被截断的字节数
- INSUFFICIENT_PRIVILEGES：被跟踪的SQL如果是带有"SQL SECURITY DEFINER"语句的视图或存储过程时，可能会因为跟踪该SQL的会话对应的用户缺少执行权限而被拒绝执行该SQL， 在这种情况下，跟踪信息为空，在OPTIMIZER_TRACE表中的INSUFFICIENT_PRIVILEGES列将显示数字“1”
- 表记录内容示例
# 启用trace功能
root@localhost : (none) 11:45:21> SET optimizer_trace="enabled=on";
Query OK, 0 rows affected (0.00 sec)
# 执行查询语句
root@localhost : (none) 11:55:02> select * from sbtest.sbtest1 where id=1;
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-----+
| id | k | c | pad |
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-----+
| 1 | 2493947 | 44401167605-95921109806-49205991371-78375263823-83309869774-25157184837-97554765438-15989585205-62089403228-04207686848 | xxx |
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-----+
1 row in set (0.00 sec)
# 查询 INFORMATION_SCHEMA.OPTIMIZER_TRACE表
root@localhost : (none) 11:57:28> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G;
*************************** 1. row ***************************
QUERY: select * from sbtest.sbtest1 where id=1
TRACE: {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `sbtest`.`sbtest1`.`id` AS `id`,`sbtest`.`sbtest1`.`k` AS `k`,`sbtest`.`sbtest1`.`c` AS `c`,`sbtest`.`sbtest1`.`pad` AS `pad` from `sbtest`.`sbtest1` where (`sbtest`.`sbtest1`.`id` = 1)"
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "(`sbtest`.`sbtest1`.`id` = 1)",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "multiple equal(1, `sbtest`.`sbtest1`.`id`)"
},
{
"transformation": "constant_propagation",
"resulting_condition": "multiple equal(1, `sbtest`.`sbtest1`.`id`)"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "multiple equal(1, `sbtest`.`sbtest1`.`id`)"
}
]
}
},
{
"substitute_generated_columns": {
}
},
{
"table_dependencies": [
{
"table": "`sbtest`.`sbtest1`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
}
]
},
{
"ref_optimizer_key_uses": [
{
"table": "`sbtest`.`sbtest1`",
"field": "id",
"equals": "1",
"null_rejecting": false
}
]
},
{
"rows_estimation": [
{
"table": "`sbtest`.`sbtest1`",
"rows": 1,
"cost": 1,
"table_type": "const",
"empty": false
}
]
},
{
"condition_on_constant_tables": "1",
"condition_value": true
},
{
"attaching_conditions_to_tables": {
"original_condition": "1",
"attached_conditions_computation": [
],
"attached_conditions_summary": [
]
}
},
{
"refine_plan": [
]
}
]
}
},
{
"join_execution": {
"select#": 1,
"steps": [
]
}
}
]
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)
# 使用完trace功能之后，记得关闭。关闭之后该表中仍然会记录着关闭之前的最后一条跟踪记录
root@localhost : (none) 11:57:40> SET optimizer_trace="enabled=off";
Query OK, 0 rows affected (0.00 sec)
上一篇：KEY_COLUMN_USAGE表 |下一篇：PARAMETERS表