information_schema.optimizer_trace学习

时间:2023-11-28 14:39:02

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)