MYSQL 8 優化之04 (通过trace分析)

1.打开trace & 设置格式为json，并设置trace的最大能够使用内存大小

root@127.0.0.1 : testdb【09:39:14】37 SQL->set optimizer_trace="enabled=on",end_markers_in_json=on;

root@127.0.0.1 : testdb【09:40:20】38 SQL->set optimizer_trace_max_mem_size=1000000;

2. trace 分析

#执行想做trace的SQL
root@127.0.0.1 : testdb【09:41:13】39 SQL->select * from country where country='中国';
+------------+---------+---------------------+
| country_id | country | last_update         |
+------------+---------+---------------------+
|       1003 | 中国    | 2019-07-21 21:17:27 |
+------------+---------+---------------------+

#分析上面的SQL
root@127.0.0.1 : testdb【09:41:44】40 SQL->select * from information_schema.optimizer_trace \G;
*************************** 1. row ***************************
                            QUERY: select * from country where country='中国'
                            TRACE: {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `country`.`country_id` AS `country_id`,`country`.`country` AS `country`,`country`.`last_update` AS `last_update` from `country` where (`country`.`country` = '中国')"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "(`country`.`country` = '中国')",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "multiple equal('中国', `country`.`country`)"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "multiple equal('中国', `country`.`country`)"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "multiple equal('中国', `country`.`country`)"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {
            "table_dependencies": [
              {
                "table": "`country`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "ref_optimizer_key_uses": [
            ] /* ref_optimizer_key_uses */
          },
          {
            "rows_estimation": [
              {
                "table": "`country`",
                "table_scan": {
                  "rows": 5,
                  "cost": 0.25
                } /* table_scan */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`country`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 5,
                      "access_type": "scan",
                      "resulting_rows": 5,
                      "cost": 0.75,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 5,
                "cost_for_plan": 0.75,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "(`country`.`country` = '中国')",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`country`",
                  "attached": "(`country`.`country` = '中国')"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "finalizing_table_conditions": [
              {
                "table": "`country`",
                "original_table_condition": "(`country`.`country` = '中国')",
                "final_table_condition   ": "(`country`.`country` = '中国')"
              }
            ] /* finalizing_table_conditions */
          },
          {
            "refine_plan": [
              {
                "table": "`country`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
          INSUFFICIENT_PRIVILEGES: 0