information_schema.optimizer_trace 用于追踪优化器的优化过程；
通常来说这张表中是没有数据的，要想开户追踪要把 @@session.optimizer_trace='enabled=on,one_line=on'
information_schema.optimizer_trace表的常用列：
1、query :查询语句
2、trace:查询的追踪信息
例子：
1、没有打开@@session.optmizer_trace='enabled=on,one_line=on'时information_schema.optimizer_trace表是空的
select * from information_schema.optimizer_trace;
2、打开
set @@session.optimizer_trace='enabled=on,one_line=on';
3、执行查询
select user,host from mysql.user;
+------------+------------+
| user | host |
+------------+------------+
| admin | % |
| zabbix | % |
| backupuser | 127.0.0.1 |
| root | 127.0.0.1 |
| zabbix | 127.0.0.1 |
| root | ::1 |
| | localhost |
| backupuser | localhost |
| root | localhost |
| zabbix | localhost |
| | workstudio |
| root | workstudio |
+------------+------------+
4、查看优化器信息
select * from information_schema.optimizer_trace \G
*************************** 1. row ***************************
QUERY: select user,host from mysql.user
TRACE: {"steps": [{"join_preparation": {"select#": 1,"steps": [{"expanded_query": "/* select#1 */ select `mysql`.`user`.`User` AS `user`,`mysql`.`user`.`Host` AS `host` from `mysql`.`user`"}]}},{"join_optimization": {"select#": 1,"steps": [{"table_dependencies": [{"table": "`mysql`.`user`","row_may_be_null": false,"map_bit": 0,"depends_on_map_bits": []}]},{"rows_estimation": [{"table": "`mysql`.`user`","table_scan": {"rows": 12,"cost": 2}}]},{"considered_execution_plans": [{"plan_prefix": [],"table": "`mysql`.`user`","best_access_path": {"considered_access_paths": [{"access_type": "scan","rows": 12,"cost": 4.6813,"chosen": true}]},"cost_for_plan": 4.6813,"rows_for_plan": 12,"chosen": true}]},{"attaching_conditions_to_tables": {"original_condition": null,"attached_conditions_computation": [],"attached_conditions_summary": [{"table": "`mysql`.`user`","attached": null}]}},{"refine_plan": [{"table": "`mysql`.`user`","access_type": "index_scan"}]}]}},{"join_execution": {"select#": 1,"steps": []}}]}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)