通过trace分析优化器如何选择执行计划

MySQL5.6提供了对MySQL的跟踪trace，通过trace文件能够进一步了解为什么优化器选择A执行计划而不选择B执行计划。

• 打开trace，设置格式为JSON

mysql> SET optimizer_trace="enabled=on",end_markers_in_json=ON;
Query OK, 0 ROWS affected (0.00 sec)

• 设置trace最大能使用的内存大小，避免解析过程中因默认内存过小而不能完成显示

mysql> SET optimizer_trace_max_mem_size=1000000;
Query OK, 0 ROWS affected (0.00 sec)

• 可选的参数内容见 官网

• 先执行想做trace的SQL

mysql> SELECT COUNT(1) FROM table_demo WHERE create_time > '2022-01-01';
......

• 通过查询informationschema.OPTIMIZERTRACE就可以知道MySQL执行SQL的过程

mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE\G
*************************** 1. ROW ***************************
                            QUERY: SELECT COUNT(1) FROM okami.table_demo WHERE create_time > '2022-01-01'
                            TRACE: {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select count(1) AS `count(1)` from `okami`.`table_demo` where (`okami`.`table_demo`.`create_time` > '2022-01-01')"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "(`okami`.`table_demo`.`create_time` > '2022-01-01')",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(`okami`.`table_demo`.`create_time` > '2022-01-01')"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(`okami`.`table_demo`.`create_time` > '2022-01-01')"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(`okami`.`table_demo`.`create_time` > '2022-01-01')"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {
            "table_dependencies": [
              {
                "table": "`okami`.`table_demo`",
                "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": "`okami`.`table_demo`",
                "table_scan": {
                  "rows": 17456,
                  "cost": 289
                } /* table_scan */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`okami`.`table_demo`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 17456,
                      "access_type": "scan",
                      "resulting_rows": 17456,
                      "cost": 3780.2,
                      "chosen": TRUE
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 17456,
                "cost_for_plan": 3780.2,
                "chosen": TRUE
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "(`okami`.`table_demo`.`create_time` > '2022-01-01')",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`okami`.`table_demo`",
                  "attached": "(`okami`.`table_demo`.`create_time` > '2022-01-01')"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "refine_plan": [
              {
                "table": "`okami`.`table_demo`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
          INSUFFICIENT_PRIVILEGES: 0
1 ROW IN SET (0.00 sec)

• 在Optimizer Trace的输出中，主要分为三个部分:

join_preparation  	SQL的准备阶段
join_optimization  	SQL优化阶段
join_execution  	SQL执行阶段