optimizer_trace

otpimzer trace功能的作用和优化的大致阶段

• 1.这个功能可以让我们方便的查看优化器生成执行计划的整个过程
• 2.prepare阶段
• 3.optimize阶段
• 4.execute阶段
• 5.基于成本的优化主要集中在optimize阶段
• 6.单表查询来说，我们主要关注optimize阶段的"rows_estimation"这个过程，这个过程深入分析了对单表查询的各种执行方案的成本
• 7.对于多表连接查询来说，我们更多需要关注"considered_execution_plans"这个过程，这个过程里会写明各种不同的连接方式所对应的成本

作者：简书徐小耳
链接：https://www.jianshu.com/p/161f634f5db3
来源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

(apple) > set optimizer_trace='enabled=on'; 
Query OK, 0 rows affected (0.00 sec)

Fri Sep  4 16:24:44 2020(apple) > set optimizer_trace_max_mem_size=1000000;  
Query OK, 0 rows affected (0.00 sec)

Fri Sep  4 16:24:53 2020(apple) > set end_markers_in_json=on;
Query OK, 0 rows affected (0.00 sec)

Fri Sep  4 16:25:03 2020(apple) > select * from information_schema.optimizer_traceG;
*************************** 1. row ***************************
                            QUERY: set end_markers_in_json=on
                            TRACE: {
  "steps": [
  ]
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
          INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)

ERROR: 
No query specified

Fri Sep  4 16:25:09 2020(apple) > select * from user where name='guolicheng';
+----+------------+--------------------+---------+------+
| id | name       | ids                | address | inno |
+----+------------+--------------------+---------+------+
|  1 | guolicheng | 422802199909136854 | 1       |      |
+----+------------+--------------------+---------+------+
1 row in set (0.00 sec)

Fri Sep  4 16:25:45 2020(apple) > select * from information_schema.optimizer_traceG;
*************************** 1. row ***************************
                            QUERY: select * from user where name='guolicheng'
                            TRACE: {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `user`.`id` AS `id`,`user`.`name` AS `name`,`user`.`ids` AS `ids`,`user`.`address` AS `address`,`user`.`inno` AS `inno` 
             from `user` where (`user`.`name` = 'guolicheng')"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "(`user`.`name` = 'guolicheng')",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(`user`.`name` = 'guolicheng')"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(`user`.`name` = 'guolicheng')"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(`user`.`name` = 'guolicheng')"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {
            "table_dependencies": [
              {
                "table": "`user`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "`user`",
                "field": "name",
                "equals": "'guolicheng'",
                "null_rejecting": false
              }
            ] /* ref_optimizer_key_uses */
          },
          {
            "rows_estimation": [
              {
                "table": "`user`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 4,
                    "cost": 3.9
                  } /* table_scan */,
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "uk_ids",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_name",
                      "usable": true,
                      "key_parts": [
                        "name",
                        "id"
                      ] /* key_parts */
                    },
                    {
                      "index": "idx_inno_address",
                      "usable": false,
                      "cause": "not_applicable"
                    }
                  ] /* potential_range_indexes */,
                  "setup_range_conditions": [
                  ] /* setup_range_conditions */,
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_group_by_or_distinct"
                  } /* group_index_range */,
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "idx_name",
                        "ranges": [
                          "guolicheng <= name <= guolicheng"
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 1,
                        "cost": 2.21,
                        "chosen": true
                      }
                    ] /* range_scan_alternatives */,
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    } /* analyzing_roworder_intersect */
                  } /* analyzing_range_alternatives */,
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "idx_name",
                      "rows": 1,
                      "ranges": [
                        "guolicheng <= name <= guolicheng"
                      ] /* ranges */
                    } /* range_access_plan */,
                    "rows_for_plan": 1,
                    "cost_for_plan": 2.21,
                    "chosen": true
                  } /* chosen_range_access_summary */
                } /* range_analysis */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`user`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "idx_name",
                      "rows": 1,
                      "cost": 1.2,
                      "chosen": true
                    },
                    {
                      "access_type": "range",
                      "range_details": {
                        "used_index": "idx_name"
                      } /* range_details */,
                      "chosen": false,
                      "cause": "heuristic_index_cheaper"
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 1,
                "cost_for_plan": 1.2,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "(`user`.`name` = 'guolicheng')",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`user`",
                  "attached": "(`user`.`name` = 'guolicheng')"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "refine_plan": [
              {
                "table": "`user`"
              }
            ] /* 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)

ERROR: 
No query specified

Fri Sep  4 16:25:48 2020