inner join执行计划变了

最新推荐文章于 2024-07-29 20:35:27 发布

YoohuDeLi

最新推荐文章于 2024-07-29 20:35:27 发布

阅读量359

点赞数

分类专栏： Java 问题记录数据库文章标签： mysql sql

本文链接：https://blog.youkuaiyun.com/u014800975/article/details/125091268

版权

Java 同时被 3 个专栏收录

14 篇文章

订阅专栏

数据库

8 篇文章

订阅专栏

问题记录

7 篇文章

订阅专栏

一、背景

同一个连接查询inner join，由于某一个条件in里多了几个值，执行计划变了。
执行计划一：
在这里插入图片描述
执行计划二：

二、外连接与内连接的区别

连接查询的成本计算公式是这样的：
连接查询总成本 = 单次访问驱动表的成本 + 驱动表扇出数 x 单次访问被驱动表的成本。

对于左（外）连接和右（外）连接查询来说，它们的驱动表是固定的，所以想要得到最优的查询方案只需要：分别为驱动表和被驱动表选择成本最低的访问方法。
对于内连接来说，驱动表和被驱动表的位置是可以互换的，所以需要考虑两个方面的问题：
（1）不同的表作为驱动表最终的查询成本可能是不同的，也就是需要考虑最优的表连接顺序。
（2）然后分别为驱动表和被驱动表选择成本最低的访问方法。

三、通过optimizer_trace查看决策过程

1、optimizer_trace的查看与开启
查看：show variables like ‘optimizer_trace’;
开启：set optimizer_trace=“enabled=on”;

2、optimizer_trace的使用
（1）打开optimizer trace功能（默认情况下它是关闭的）：
SET optimizer_trace=“enabled=on”;
（2）输入查询语句：
SELECT …;
（3）从OPTIMIZER_TRACE表中查看上一个查询的优化过程：
SELECT * FROM information_schema.OPTIMIZER_TRACE;

3、案例分析
（1）执行计划一的优化过程

{
  "steps": [
    {
      "join_preparation": { # prepare阶段
        "select#": 1,
        "steps": [
          {
            "IN_uses_bisection": true
          },
          {
            "expanded_query": "/* select#1 */ select distinct `q`.`question_id` AS `question_id` from (`tb_question` `q` join `tb_question_label` `label` on((`label`.`question_id` = `q`.`question_id`))) where ((`label`.`subject_id` = 11) and (`label`.`is_deleted` = 1) and (`label`.`label_id` in (197750,197637,197947)) and (`q`.`subject_id` = 11) and (`q`.`parent_id` = 0) and (`q`.`is_deleted` = 0) and (`q`.`quality` = 4)) limit 3000"
          },
          {
            "transformations_to_nested_joins": {
              "transformations": [
                "JOIN_condition_to_WHERE",
                "parenthesis_removal"
              ],
              "expanded_query": "/* select#1 */ select distinct `q`.`question_id` AS `question_id` from `tb_question` `q` join `tb_question_label` `label` where ((`label`.`subject_id` = 11) and (`label`.`is_deleted` = 1) and (`label`.`label_id` in (197750,197637,197947)) and (`q`.`subject_id` = 11) and (`q`.`parent_id` = 0) and (`q`.`is_deleted` = 0) and (`q`.`quality` = 4) and (`label`.`question_id` = `q`.`question_id`)) limit 3000"
            }
          }
        ]
      }
    },
    {
      "join_optimization": { # optimize阶段
        "select#": 1,
        "steps": [
          {
            "condition_processing": { # 处理搜索条件
              "condition": "WHERE",
              # 原始搜索条件
              "original_condition": "((`label`.`subject_id` = 11) and (`label`.`is_deleted` = 1) and (`label`.`label_id` in (197750,197637,197947)) and (`q`.`subject_id` = 11) and (`q`.`parent_id` = 0) and (`q`.`is_deleted` = 0) and (`q`.`quality` = 4) and (`label`.`question_id` = `q`.`question_id`))",
              "steps": [
                {
                  # 等值传递转换
                  "transformation": "equality_propagation",
                  "resulting_condition": "((`label`.`label_id` in (197750,197637,197947)) and multiple equal(11, `label`.`subject_id`) and multiple equal(1, `label`.`is_deleted`) and multiple equal(11, `q`.`subject_id`) and multiple equal(0, `q`.`parent_id`) and multiple equal(0, `q`.`is_deleted`) and multiple equal(4, `q`.`quality`) and multiple equal(`label`.`question_id`, `q`.`question_id`))"
                },
                {
                  # 常量传递转换
                  "transformation": "constant_propagation",
                  "resulting_condition": "((`label`.`label_id` in (197750,197637,197947)) and multiple equal(11, `label`.`subject_id`) and multiple equal(1, `label`.`is_deleted`) and multiple equal(11, `q`.`subject_id`) and multiple equal(0, `q`.`parent_id`) and multiple equal(0, `q`.`is_deleted`) and multiple equal(4, `q`.`quality`) and multiple equal(`label`.`question_id`, `q`.`question_id`))"
                },
                {
                  # 去除无用条件
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "((`label`.`label_id` in (197750,197637,197947)) and multiple equal(11, `label`.`subject_id`) and multiple equal(1, `label`.`is_deleted`) and multiple equal(11, `q`.`subject_id`) and multiple equal(0, `q`.`parent_id`) and multiple equal(0, `q`.`is_deleted`) and multiple equal(4, `q`.`quality`) and multiple equal(`label`.`question_id`, `q`.`question_id`))"
                }
              ]
            }
          },
          {
            # 替换虚拟生成列
            "substitute_generated_columns": {
            }
          },
          {
            # 表的依赖信息
            "table_dependencies": [
              {
                "table": "`tb_question` `q`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ]
              },
              {
                "table": "`tb_question_label` `label`",
                "row_may_be_null": false,
                "map_bit": 1,
                "depends_on_map_bits": [
                ]
              }
            ]
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "`tb_question` `q`",
                "field": "question_id",
                "equals": "`label`.`question_id`",
                "null_rejecting": false
              },
              {
                "table": "`tb_question` `q`",
                "field": "parent_id",
                "equals": "0",
                "null_rejecting": false
              },
              {
                "table": "`tb_question_label` `label`",
                "field": "question_id",
                "equals": "`q`.`question_id`",
                "null_rejecting": false
              }
            ]
          },
          {
            # 预估不同单表访问方法的访问成本
            "rows_estimation": [
              {
                "table": "`tb_question` `q`",
                "const_keys_added": {
                  "keys": [
                    "uk_question"
                  ],
                  "cause": "distinct"
                },
                "range_analysis": {
                  "table_scan": { # 全表扫描的行数及成本
                    "rows": 566210,
                    "cost": 118915
                  },
                  # 分析可能使用的索引
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY", # 主键不可用
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "uk_question", # uk_question可能被使用
                      "usable": true,
                      "key_parts": [
                        "question_id"
                      ]
                    },
                    {
                      "index": "idx_parent", # idx_parent可能被使用
                      "usable": true,
                      "key_parts": [
                        "parent_id",
                        "id"
                      ]
                    },
                    {
                      "index": "idx_adapt_from", # idx_adapt_from不可被使用
                      "usable": false,
                      "cause": "not_applicable"
                    }
                  ],
                  "setup_range_conditions": [
                  ],
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_single_table"
                  },
                  # 分析各种可能使用的索引的成本
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        # 使用idx_parent的成本分析
                        "index": "idx_parent",
                        "ranges": [
                          "0 <= parent_id <= 0"
                        ],
                        "index_dives_for_eq_ranges": true, # 是否使用index dive
                        "rowid_ordered": true, # 使用该索引获取的记录是否按照主键排序
                        "using_mrr": false, # 是否使用mrr
                        "index_only": false, # 是否是索引覆盖访问
                        "rows": 283105, # 使用该索引获取的记录条数
                        "cost": 339727, # 使用该索引的成本
                        "chosen": false, # 是否选择该索引
                        "cause": "cost"
                      }
                    ],
                    # 分析使用索引合并的成本
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    }
                  }
                }
              },
              {
                "table": "`tb_question_label` `label`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 685400,
                    "cost": 140576
                  },
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_label",
                      "usable": true,
                      "key_parts": [
                        "label_id",
                        "id"
                      ]
                    },
                    {
                      "index": "idx_question",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_sub_question",
                      "usable": false,
                      "cause": "not_applicable"
                    }
                  ],
                  "setup_range_conditions": [
                  ],
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_single_table"
                  },
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "idx_label",
                        "ranges": [
                          "197637 <= label_id <= 197637",
                          "197750 <= label_id <= 197750",
                          "197947 <= label_id <= 197947"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 3,
                        "cost": 6.61,
                        "chosen": true
                      }
                    ],
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    }
                  },
                  # 对于上述单表查询tb_question_label最优的访问方法
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "idx_label",
                      "rows": 3,
                      "ranges": [
                        "197637 <= label_id <= 197637",
                        "197750 <= label_id <= 197750",
                        "197947 <= label_id <= 197947"
                      ]
                    },
                    "rows_for_plan": 3,
                    "cost_for_plan": 6.61,
                    "chosen": true
                  }
                }
              }
            ]
          },
          {
            # 分析各种可能的执行计划（对多表查询这可能有很多种不同的方案）
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ],
                "table": "`tb_question_label` `label`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "idx_question",
                      "usable": false,
                      "chosen": false
                    },
                    {
                      "rows_to_scan": 3,
                      "access_type": "range",
                      "range_details": {
                        "used_index": "idx_label"
                      },
                      "resulting_rows": 0.05,
                      "cost": 7.21,
                      "chosen": true
                    }
                  ]
                },
                "condition_filtering_pct": 100,
                "rows_for_plan": 0.05,
                "cost_for_plan": 7.21,
                "rest_of_plan": [
                  {
                    "plan_prefix": [
                      "`tb_question_label` `label`"
                    ],
                    "table": "`tb_question` `q`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "eq_ref",
                          "index": "uk_question",
                          "rows": 1,
                          "cost": 0.06,
                          "chosen": true
                        },
                        {
                          "access_type": "ref",
                          "index": "idx_parent",
                          "chosen": false,
                          "cause": "heuristic_eqref_already_found"
                        },
                        {
                          "access_type": "scan",
                          "cost": 118913,
                          "rows": 566210,
                          "chosen": false,
                          "cause": "cost"
                        }
                      ]
                    },
                    "condition_filtering_pct": 100,
                    "rows_for_plan": 0.05,
                    "cost_for_plan": 7.27,
                    "chosen": true
                  }
                ]
              },
              {
                "plan_prefix": [
                ],
                "table": "`tb_question` `q`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "uk_question",
                      "usable": false,
                      "chosen": false
                    },
                    {
                      "access_type": "ref",
                      "index": "idx_parent",
                      "rows": 283105,
                      "cost": 73634,
                      "chosen": true
                    },
                    {
                      "rows_to_scan": 566210,
                      "access_type": "scan",
                      "resulting_rows": 283.11,
                      "cost": 118913,
                      "chosen": false
                    }
                  ]
                },
                "condition_filtering_pct": 0.1,
                "rows_for_plan": 283.11,
                "cost_for_plan": 73634,
                "pruned_by_cost": true
              }
            ]
          },
          {
            # 尝试给查询添加一些其他的查询条件
            "attaching_conditions_to_tables": {
              "original_condition": "((`q`.`question_id` = `label`.`question_id`) and (`q`.`quality` = 4) and (`q`.`is_deleted` = 0) and (`q`.`parent_id` = 0) and (`q`.`subject_id` = 11) and (`label`.`is_deleted` = 1) and (`label`.`subject_id` = 11) and (`label`.`label_id` in (197750,197637,197947)))",
              "attached_conditions_computation": [
              ],
              "attached_conditions_summary": [
                {
                  "table": "`tb_question_label` `label`",
                  "attached": "((`label`.`is_deleted` = 1) and (`label`.`subject_id` = 11) and (`label`.`label_id` in (197750,197637,197947)))"
                },
                {
                  "table": "`tb_question` `q`",
                  "attached": "((`q`.`quality` = 4) and (`q`.`is_deleted` = 0) and (`q`.`parent_id` = 0) and (`q`.`subject_id` = 11))"
                }
              ]
            }
          },
          {
            # 再稍稍的改进一下执行计划
            "refine_plan": [
              {
                "table": "`tb_question_label` `label`",
                "pushed_index_condition": "(`label`.`label_id` in (197750,197637,197947))",
                "table_condition_attached": "((`label`.`is_deleted` = 1) and (`label`.`subject_id` = 11))"
              },
              {
                "table": "`tb_question` `q`"
              }
            ]
          }
        ]
      }
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
          {
            "creating_tmp_table": {
              "tmp_table_info": {
                "table": "intermediate_tmp_table",
                "row_length": 9,
                "key_length": 8,
                "unique_constraint": false,
                "location": "memory (heap)",
                "row_limit_estimate": 3000
              }
            }
          }
        ]
      }
    }
  ]
}

（2）执行计划二的执行过程

{
  "steps": [
    {
      "join_preparation": { # prepare阶段
        "select#": 1,
        "steps": [
          {
            "IN_uses_bisection": true
          },
          {
            "expanded_query": "/* select#1 */ select distinct `q`.`question_id` AS `question_id` from (`tb_question` `q` join `tb_question_label` `label` on((`label`.`question_id` = `q`.`question_id`))) where ((`label`.`subject_id` = 11) and (`label`.`is_deleted` = 0) and (`label`.`label_id` in (197049,187007,187006)) and (`q`.`subject_id` = 11) and (`q`.`parent_id` = 0) and (`q`.`is_deleted` = 0) and (`q`.`quality` = 4)) limit 3000"
          },
          {
            "transformations_to_nested_joins": {
              "transformations": [
                "JOIN_condition_to_WHERE",
                "parenthesis_removal"
              ],
              "expanded_query": "/* select#1 */ select distinct `q`.`question_id` AS `question_id` from `tb_question` `q` join `tb_question_label` `label` where ((`label`.`subject_id` = 11) and (`label`.`is_deleted` = 0) and (`label`.`label_id` in (197049,187007,187006)) and (`q`.`subject_id` = 11) and (`q`.`parent_id` = 0) and (`q`.`is_deleted` = 0) and (`q`.`quality` = 4) and (`label`.`question_id` = `q`.`question_id`)) limit 3000"
            }
          }
        ]
      }
    },
    {
      "join_optimization": { # optimize阶段
        "select#": 1,
        "steps": [
          {
            "condition_processing": { # 处理搜索条件
              "condition": "WHERE",
              # 原始搜索条件
              "original_condition": "((`label`.`subject_id` = 11) and (`label`.`is_deleted` = 0) and (`label`.`label_id` in (197049,187007,187006)) and (`q`.`subject_id` = 11) and (`q`.`parent_id` = 0) and (`q`.`is_deleted` = 0) and (`q`.`quality` = 4) and (`label`.`question_id` = `q`.`question_id`))",
              "steps": [
                {
                  # 等值传递转换
                  "transformation": "equality_propagation",
                  "resulting_condition": "((`label`.`label_id` in (197049,187007,187006)) and multiple equal(11, `label`.`subject_id`) and multiple equal(0, `label`.`is_deleted`) and multiple equal(11, `q`.`subject_id`) and multiple equal(0, `q`.`parent_id`) and multiple equal(0, `q`.`is_deleted`) and multiple equal(4, `q`.`quality`) and multiple equal(`label`.`question_id`, `q`.`question_id`))"
                },
                {
                  # 常量传递转换
                  "transformation": "constant_propagation",
                  "resulting_condition": "((`label`.`label_id` in (197049,187007,187006)) and multiple equal(11, `label`.`subject_id`) and multiple equal(0, `label`.`is_deleted`) and multiple equal(11, `q`.`subject_id`) and multiple equal(0, `q`.`parent_id`) and multiple equal(0, `q`.`is_deleted`) and multiple equal(4, `q`.`quality`) and multiple equal(`label`.`question_id`, `q`.`question_id`))"
                },
                {
                  # 去除无用条件
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "((`label`.`label_id` in (197049,187007,187006)) and multiple equal(11, `label`.`subject_id`) and multiple equal(0, `label`.`is_deleted`) and multiple equal(11, `q`.`subject_id`) and multiple equal(0, `q`.`parent_id`) and multiple equal(0, `q`.`is_deleted`) and multiple equal(4, `q`.`quality`) and multiple equal(`label`.`question_id`, `q`.`question_id`))"
                }
              ]
            }
          },
          {
            "substitute_generated_columns": {
            }
          },
          {
          	# 替换虚拟生成列
            "table_dependencies": [
              {
                "table": "`tb_question` `q`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ]
              },
              {
                "table": "`tb_question_label` `label`",
                "row_may_be_null": false,
                "map_bit": 1,
                "depends_on_map_bits": [
                ]
              }
            ]
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "`tb_question` `q`",
                "field": "question_id",
                "equals": "`label`.`question_id`",
                "null_rejecting": false
              },
              {
                "table": "`tb_question` `q`",
                "field": "parent_id",
                "equals": "0",
                "null_rejecting": false
              },
              {
                "table": "`tb_question_label` `label`",
                "field": "question_id",
                "equals": "`q`.`question_id`",
                "null_rejecting": false
              }
            ]
          },
          {
          	# 预估不同单表访问方法的访问成本
            "rows_estimation": [
              {
                "table": "`tb_question` `q`",
                "const_keys_added": {
                  "keys": [
                    "uk_question"
                  ],
                  "cause": "distinct"
                },
                "range_analysis": {
                  "table_scan": { # 全表扫描的行数及成本
                    "rows": 566210,
                    "cost": 118915
                  },
                  # 分析可能使用的索引
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY", # 主键不可用
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "uk_question", # uk_question可能被使用
                      "usable": true,
                      "key_parts": [
                        "question_id"
                      ]
                    },
                    {
                      "index": "idx_parent", # idx_parent可能被使用
                      "usable": true,
                      "key_parts": [
                        "parent_id",
                        "id"
                      ]
                    },
                    {
                      "index": "idx_adapt_from", # idx_adapt_from不可被使用
                      "usable": false,
                      "cause": "not_applicable"
                    }
                  ],
                  "setup_range_conditions": [
                  ],
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_single_table"
                  },
                  # 分析各种可能使用的索引的成本
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                      	# 使用idx_parent的成本分析
                        "index": "idx_parent",
                        "ranges": [
                          "0 <= parent_id <= 0"
                        ],
                        "index_dives_for_eq_ranges": true, # 是否使用index dive
                        "rowid_ordered": true, # 使用该索引获取的记录是否按照主键排序
                        "using_mrr": false, # 是否使用mrr
                        "index_only": false, # 是否是索引覆盖访问
                        "rows": 283105, # 使用该索引获取的记录条数
                        "cost": 339727, # 使用该索引的成本
                        "chosen": false, # 是否选择该索引
                        "cause": "cost"
                      }
                    ],
                    # 分析使用索引合并的成本
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    }
                  }
                }
              },
              {
                "table": "`tb_question_label` `label`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 685400,
                    "cost": 140576
                  },
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_label",
                      "usable": true,
                      "key_parts": [
                        "label_id",
                        "id"
                      ]
                    },
                    {
                      "index": "idx_question",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_sub_question",
                      "usable": false,
                      "cause": "not_applicable"
                    }
                  ],
                  "setup_range_conditions": [
                  ],
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_single_table"
                  },
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "idx_label",
                        "ranges": [
                          "187006 <= label_id <= 187006",
                          "187007 <= label_id <= 187007",
                          "197049 <= label_id <= 197049"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 53010,
                        "cost": 63615,
                        "chosen": true
                      }
                    ],
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    }
                  },
                  # 对于上述单表查询tb_question_label最优的访问方法
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "idx_label",
                      "rows": 53010,
                      "ranges": [
                        "187006 <= label_id <= 187006",
                        "187007 <= label_id <= 187007",
                        "197049 <= label_id <= 197049"
                      ]
                    },
                    "rows_for_plan": 53010,
                    "cost_for_plan": 63615,
                    "chosen": true
                  }
                }
              }
            ]
          },
          {
          	# 分析各种可能的执行计划（对多表查询这可能有很多种不同的方案）
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ],
                "table": "`tb_question_label` `label`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "idx_question",
                      "usable": false,
                      "chosen": false
                    },
                    {
                      "rows_to_scan": 53010,
                      "access_type": "range",
                      "range_details": {
                        "used_index": "idx_label"
                      },
                      "resulting_rows": 530.1,
                      "cost": 74217,
                      "chosen": true
                    }
                  ]
                },
                "condition_filtering_pct": 100,
                "rows_for_plan": 530.1,
                "cost_for_plan": 74217,
                "rest_of_plan": [
                  {
                    "plan_prefix": [
                      "`tb_question_label` `label`"
                    ],
                    "table": "`tb_question` `q`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "eq_ref",
                          "index": "uk_question",
                          "rows": 1,
                          "cost": 636.12,
                          "chosen": true
                        },
                        {
                          "access_type": "ref",
                          "index": "idx_parent",
                          "chosen": false,
                          "cause": "heuristic_eqref_already_found"
                        },
                        {
                          "access_type": "scan",
                          "cost": 118913,
                          "rows": 566210,
                          "chosen": false,
                          "cause": "cost"
                        }
                      ]
                    },
                    "condition_filtering_pct": 5,
                    "rows_for_plan": 26.505,
                    "cost_for_plan": 74853,
                    "chosen": true
                  }
                ]
              },
              {
                "plan_prefix": [
                ],
                "table": "`tb_question` `q`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "uk_question",
                      "usable": false,
                      "chosen": false
                    },
                    {
                      "access_type": "ref",
                      "index": "idx_parent",
                      "rows": 283105,
                      "cost": 73634,
                      "chosen": true
                    },
                    {
                      "rows_to_scan": 566210,
                      "access_type": "scan",
                      "resulting_rows": 283.11,
                      "cost": 118913,
                      "chosen": false
                    }
                  ]
                },
                "condition_filtering_pct": 0.1,
                "rows_for_plan": 283.11,
                "cost_for_plan": 73634,
                "rest_of_plan": [
                  {
                    "plan_prefix": [
                      "`tb_question` `q`"
                    ],
                    "table": "`tb_question_label` `label`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "ref",
                          "index": "idx_question",
                          "rows": 1.4988,
                          "cost": 509.18,
                          "chosen": true
                        },
                        {
                          "access_type": "range",
                          "range_details": {
                            "used_index": "idx_label"
                          },
                          "cost": 74217,
                          "rows": 53010,
                          "chosen": false,
                          "cause": "cost"
                        }
                      ]
                    },
                    "condition_filtering_pct": 3.336,
                    "rows_for_plan": 14.155,
                    "cost_for_plan": 74143,
                    "chosen": true
                  }
                ]
              }
            ]
          },
          {
          	# 尝试给查询添加一些其他的查询条件
            "attaching_conditions_to_tables": {
              "original_condition": "((`label`.`question_id` = `q`.`question_id`) and (`q`.`quality` = 4) and (`q`.`is_deleted` = 0) and (`q`.`parent_id` = 0) and (`q`.`subject_id` = 11) and (`label`.`is_deleted` = 0) and (`label`.`subject_id` = 11) and (`label`.`label_id` in (197049,187007,187006)))",
              "attached_conditions_computation": [
              ],
              "attached_conditions_summary": [
                {
                  "table": "`tb_question` `q`",
                  "attached": "((`q`.`quality` = 4) and (`q`.`is_deleted` = 0) and (`q`.`subject_id` = 11))"
                },
                {
                  "table": "`tb_question_label` `label`",
                  "attached": "((`label`.`is_deleted` = 0) and (`label`.`subject_id` = 11) and (`label`.`label_id` in (197049,187007,187006)))"
                }
              ]
            }
          },
          {
          	# 再稍稍的改进一下执行计划
            "refine_plan": [
              {
                "table": "`tb_question` `q`"
              },
              {
                "table": "`tb_question_label` `label`"
              }
            ]
          }
        ]
      }
    },
    {
      "join_explain
(trace太长，后面文本字节被忽略)

（3）总结
通过（1）、（2）的optimizer_trace分析，可以看到，执行计划的变更，是根据连接查询的成本来考量的。