OPTIMIZER_TRACE表提供由跟踪语句的优化程序跟踪功能生成的信息。 要启用跟踪，请使用optimizer_trace系统变量。 有关详细信息，请参阅MySQL内部：跟踪优化程序。
OPTIMIZER_TRACE表有以下列：
QUERY ：跟踪的文本TRACE ：以JSON格式跟踪MISSING_BYTES_BEYOND_MAX_MEM_SIZE：跟踪的每个文件是一个字符串，随着优化的进行而扩展，并将数据附加到该字符串。 optimizer_trace_max_mem_size变量设置所有当前记住的跟踪使用的内存总量的限制。 如果达到此限制，则不会扩展当前跟踪（因此不完整），并且MISSING_BYTES_BEYOND_MAX_MEM_SIZE列显示跟踪中缺少的字节数。INSUFFICIENT_PRIVILEGES ：如果跟踪查询使用具有值为DEFINER的SQL SECURITY的视图或存储例程，则可能会拒绝定义者以外的用户查看查询的跟踪。 在这种情况下，跟踪显示为空，INSUFFICIENT_PRIVILEGES的值为1.否则，该值为0。示例
mysql> SET optimizer_trace="enabled=on"; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM test.t1; Empty set (0.00 sec) mysql> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G *************************** 1. row *************************** QUERY: SELECT * FROM test.t1 TRACE: { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `t1`.`s1` AS `s1`,`t1`.`s2` AS `s2`,`t1`.`s3` AS `s3` from `t1`" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 1, "cost": 1 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 1, "access_type": "scan", "resulting_rows": 1, "cost": 1.2, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 1, "cost_for_plan": 1.2, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] } }, { "refine_plan": [ { "table": "`t1`" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ ] } } ] } MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 INSUFFICIENT_PRIVILEGES: 0 1 row in set (0.00 sec) mysql> SET optimizer_trace="enabled=off"; Query OK, 0 rows affected (0.00 sec)转载于:https://www.cnblogs.com/wanbin/p/9514637.html
