MySQL性能分析(三)之optimizer_trace详解
<h1 id="一概述">一、概述</h1><p><code>optimizer_trace</code>是<code>MySQL 5.6</code>引入的一项跟踪功能，它可以跟踪优化器做出的各种决策（比如访问表的方法、各种开销计算、各种转换等），并将跟踪结果记录到<code>information_schema.optimizer_trace</code>表中。此功能默认关闭，开启后，可分析如下语句：</p>
<ul>
<li>select</li>
<li>insert</li>
<li>replace</li>
<li>update</li>
<li>delete</li>
<li>explain</li>
<li>set</li>
<li>declare</li>
<li>case</li>
<li>if</li>
<li>return</li>
<li>call</li>
</ul>
<h1 id="二开启及关闭">二、开启及关闭</h1>
<h2 id="21-开启optimizer_trace">2.1 开启optimizer_trace</h2>
<p>在<code>mysql</code>命令行中，使用如下命令开启<code>optimizer_trace</code>：</p>
<pre><code class="language-mysql">set optimizer_trace="enabled=on",end_markers_in_json=on;
</code></pre>
<p>也可用<code>set global</code>全局开启。但即使全局开启<code>optimizer_trace</code>，每个<code>Session</code>也只能跟踪它自己执行的语句：</p>
<pre><code class="language-mysql">set global optimizer_trace="enabled=on",end_markers_in_json=on;
</code></pre>
<h2 id="22-关闭optimizer_trace">2.2 关闭optimizer_trace</h2>
<p>在<code>mysql</code>命令行中，使用如下命令关闭<code>optimizer_trace</code>：</p>
<pre><code class="language-mysql">SET optimizer_trace="enabled=off";
</code></pre>
<h2 id="23-相关参数">2.3 相关参数</h2>
<p><strong>optimizer_trace</strong></p>
<ul>
<li>optimizer_trace总开关，默认值：enabled=off,one_line=off</li>
<li>enabled：是否开启optimizer_trace；on表示开启，off表示关闭。</li>
<li>one_line：是否开启单行存储。on表示开启；off表示关闭，将会用标准的JSON格式化存储。设置成on将会有良好的格式，设置成off可节省一些空间。</li>
</ul>
<p><strong>optimizer_trace_features</strong></p>
<ul>
<li>控制optimizer_trace跟踪的内容，默认值：greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on，表示开启所有跟踪项。</li>
</ul>
<p><strong>greedy_search</strong>：是否跟踪贪心搜索</p>
<ul>
<li>range_optimizer：是否跟踪范围优化器</li>
</ul>
<p><strong>dynamic_range</strong>：是否跟踪动态范围优化</p>
<ul>
<li>repeated_subselect：是否跟踪子查询，如果设置成off，只跟踪第一条Item_subselect的执行</li>
</ul>
<p><strong>optimizer_trace_limit</strong></p>
<ul>
<li>控制optimizer_trace展示多少条结果，默认1</li>
</ul>
<p><strong>optimizer_trace_max_mem_size</strong></p>
<ul>
<li>optimizer_trace堆栈信息允许的最大内存，默认1048576</li>
</ul>
<p><strong>optimizer_trace_offset</strong></p>
<ul>
<li>第一个要展示的optimizer trace的偏移量，默认-1。</li>
</ul>
<p><strong>end_markers_in_json</strong></p>
<ul>
<li>如果JSON结构很大，则很难将右括号和左括号配对。为了帮助读者阅读，可将其设置成on，这样会在右括号附近加上注释，默认off。</li>
</ul>
<blockquote>
<p>optimizer_trace_limit和optimizer_trace_offset这两个参数经常配合使用，例如：SET optimizer_trace_offset=<offset>, optimizer_trace_limit=<limit><br>
这两个参数配合使用，有点类似MySQL里面的limit语句。<br>
默认情况下，由于optimizer_trace_offset=-1，optimizer_trace_limit=1，记录最近的一条SQL语句，展示时，每次展示1条数据；<br>
如果改成SET optimizer_trace_offset=-2, optimizer_trace_limit=1，则会记录倒数第二条SQL语句；</limit></offset></p>
</blockquote>
<h1 id="三使用optimizer_trace">三、使用optimizer_trace</h1>
<h2 id="31-展示条目">3.1 展示条目</h2>
<p>开启<code>optimizer_trace</code>功能，并设置要展示的数据条目数：</p>
<pre><code>set optimizer_trace="enabled=on", end_markers_in_json=on;
set optimizer_trace_offset=-30, optimizer_trace_limit=30;
</code></pre>
<h2 id="32-分析sql语句">3.2 分析SQL语句</h2>
<p>发送你想要分析的<code>SQL</code>语句，例如：</p>
<pre><code class="language-mysql">select *
from salaries
where from_date = '1986-06-26'
and to_date = '1987-06-26';
</code></pre>
<p>使用如下语句分析，即可获得类似如下的结果：</p>
<pre><code class="language-text">mysql> select * from information_schema.optimizer_trace limit 30 \G;
*************************** 1. row ***************************
QUERY: select *
from salaries
where from_date = '1986-06-26'
and to_date = '1987-06-26'
TRACE: {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `salaries`.`emp_no` AS `emp_no`,`salaries`.`salary` AS `salary`,`salaries`.`from_date` AS `from_date`,`salaries`.`to_date` AS `to_date` from `salaries` where ((`salaries`.`from_date` = '1986-06-26') and (`salaries`.`to_date` = '1987-06-26'))"
}
] /* steps */
} /* join_preparation */
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((`salaries`.`from_date` = '1986-06-26') and (`salaries`.`to_date` = '1987-06-26'))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(multiple equal('1986-06-26', `salaries`.`from_date`) and multiple equal('1987-06-26', `salaries`.`to_date`))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(multiple equal('1986-06-26', `salaries`.`from_date`) and multiple equal('1987-06-26', `salaries`.`to_date`))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(multiple equal(DATE'1986-06-26', `salaries`.`from_date`) and multiple equal(DATE'1987-06-26', `salaries`.`to_date`))"
}
] /* steps */
} /* condition_processing */
},
{
"substitute_generated_columns": {
} /* substitute_generated_columns */
},
{
"table_dependencies": [
{
"table": "`salaries`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
] /* depends_on_map_bits */
}
] /* table_dependencies */
},
{
"ref_optimizer_key_uses": [
{
"table": "`salaries`",
"field": "from_date",
"equals": "DATE'1986-06-26'",
"null_rejecting": false
},
{
"table": "`salaries`",
"field": "to_date",
"equals": "DATE'1987-06-26'",
"null_rejecting": false
}
] /* ref_optimizer_key_uses */
},
{
"rows_estimation": [
{
"table": "`salaries`",
"range_analysis": {
"table_scan": {
"rows": 2838216,
"cost": 286799
} /* table_scan */,
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "salaries_from_date_to_date_index",
"usable": true,
"key_parts": [
"from_date",
"to_date",
"emp_no"
] /* key_parts */
}
] /* potential_range_indexes */,
"setup_range_conditions": [
] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
} /* group_index_range */,
"skip_scan_range": {
"potential_skip_scan_indexes": [
{
"index": "salaries_from_date_to_date_index",
"usable": false,
"cause": "query_references_nonkey_column"
}
] /* potential_skip_scan_indexes */
} /* skip_scan_range */,
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "salaries_from_date_to_date_index",
"ranges": [
"0xda840f <= from_date <= 0xda840f AND 0xda860f <= to_date <= 0xda860f"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 86,
"cost": 50.909,
"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": "salaries_from_date_to_date_index",
"rows": 86,
"ranges": [
"0xda840f <= from_date <= 0xda840f AND 0xda860f <= to_date <= 0xda860f"
] /* ranges */
} /* range_access_plan */,
"rows_for_plan": 86,
"cost_for_plan": 50.909,
"chosen": true
} /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
},
{
"considered_execution_plans": [
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`salaries`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "salaries_from_date_to_date_index",
"rows": 86,
"cost": 50.412,
"chosen": true
},
{
"access_type": "range",
"range_details": {
"used_index": "salaries_from_date_to_date_index"
} /* range_details */,
"chosen": false,
"cause": "heuristic_index_cheaper"
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 86,
"cost_for_plan": 50.412,
"chosen": true
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": {
"original_condition": "((`salaries`.`to_date` = DATE'1987-06-26') and (`salaries`.`from_date` = DATE'1986-06-26'))",
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`salaries`",
"attached": "((`salaries`.`to_date` = DATE'1987-06-26') and (`salaries`.`from_date` = DATE'1986-06-26'))"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"finalizing_table_conditions": [
{
"table": "`salaries`",
"original_table_condition": "((`salaries`.`to_date` = DATE'1987-06-26') and (`salaries`.`from_date` = DATE'1986-06-26'))",
"final_table_condition ": null
}
] /* finalizing_table_conditions */
},
{
"refine_plan": [
{
"table": "`salaries`"
}
] /* 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)
</code></pre>
<p>由上面的结果可知，<code>OPTIMIZER_TRACE</code>有四个字段：</p>
<ul>
<li>query：查询语句</li>
<li>trace：query字段对应语句的跟踪信息</li>
<li>missing_bytes_beyond_max_mem_size：跟踪信息过长时，被截断的跟踪信息的字节数。</li>
<li>insufficient_privileges：执行跟踪语句的用户是否有查看对象的权限。当不具有权限时，该列信息为1且<code>trace</code>字段为空，一般在调用带有<code>sql security definer</code>的视图或者是存储过程的情况下，会出现此问题。</li>
</ul>
<h1 id="四结果分析">四、结果分析</h1>
<p>最核心的是<code>trace</code>字段的内容。我们逐段分析：</p>
<h2 id="41-join_preparation">4.1 join_preparation</h2>
<p><code>join_preparation</code>段落展示了准备阶段的执行过程。</p>
<pre><code class="language-text">{
"join_preparation": {
"select#": 1,
"steps": [
{
-- 对比下原始语句，可以知道，这一步做了个格式化。
"expanded_query": "/* select#1 */ select `salaries`.`emp_no` AS `emp_no`,`salaries`.`salary` AS `salary`,`salaries`.`from_date` AS `from_date`,`salaries`.`to_date` AS `to_date` from `salaries` where ((`salaries`.`from_date` = '1986-06-26') and (`salaries`.`to_date` = '1987-06-26'))"
}
]
/* steps */
}
/* join_preparation */
}
</code></pre>
<h2 id="42-join_optimization">4.2 join_optimization</h2>
<p><code>join_optimization</code>展示了优化阶段的执行过程，是分析<code>optimizer trace</code>的重点。这段内容超级长，而且分了好多步骤，不妨按照步骤逐段分析：</p>
<h3 id="421-condition_processing">4.2.1 condition_processing</h3>
<p>该段用来做条件处理，主要对<code>where</code>条件进行优化处理。</p>
<pre><code class="language-text">"condition_processing": {
"condition": "WHERE",
"original_condition": "((`salaries`.`from_date` = '1986-06-26') and (`salaries`.`to_date` = '1987-06-26'))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(multiple equal('1986-06-26', `salaries`.`from_date`) and multiple equal('1987-06-26', `salaries`.`to_date`))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(multiple equal('1986-06-26', `salaries`.`from_date`) and multiple equal('1987-06-26', `salaries`.`to_date`))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(multiple equal(DATE'1986-06-26', `salaries`.`from_date`) and multiple equal(DATE'1987-06-26', `salaries`.`to_date`))"
}
] /* steps */
} /* condition_processing */
</code></pre>
<p>其中：</p>
<ul>
<li>condition：优化对象类型。where条件句或者是having条件句</li>
<li>original_condition：优化前的原始语句</li>
<li>steps：主要包括三步，分别是quality_propagation（等值条件句转换），constant_propagation（常量条件句转换），trivial_condition_removal（无效条件移除的转换）
<ul>
<li>transformation：转换类型句</li>
<li>resulting_condition：转换之后的结果输出</li>
</ul>
</li>
</ul>
<h3 id="422-substitute_generated_columns">4.2.2 substitute_generated_columns</h3>
<p><code>substitute_generated_columns</code>用于替换虚拟生成列</p>
<pre><code class="language-text">"substitute_generated_columns": {
} /* substitute_generated_columns */
</code></pre>
<h3 id="423-table_dependencies">4.2.3 table_dependencies</h3>
<p>分析表之间的依赖关系</p>
<pre><code class="language-text">{
"table_dependencies": [
{
"table": "`salaries`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
] /* depends_on_map_bits */
}
] /* table_dependencies */
}
</code></pre>
<p>其中：</p>
<ul>
<li>table：涉及的表名，如果有别名，也会展示出来</li>
<li>row_may_be_null：行是否可能为null，这里是指join操作之后，这张表里的数据是不是可能为null。如果语句中使用了left join，则后一张表的row_may_be_null会显示为true</li>
<li>map_bit：表的映射编号，从0开始递增</li>
<li>depends_on_map_bits：依赖的映射表。主要是当使用straight_join强行控制连接顺序或者left join/right join有顺序差别时，会在depends_on_map_bits中展示前置表的map_bit值。</li>
</ul>
<h3 id="424-ref_optimizer_key_uses">4.2.4 ref_optimizer_key_uses</h3>
<p>列出所有可用的ref类型的索引。如果使用了组合索引的多个部分（例如本例，用到了index(from_date, to_date) 的多列索引），则会在<code>ref_optimizer_key_uses</code>下列出多个元素，每个元素中会列出<code>ref</code>使用的索引及对应值。</p>
<pre><code class="language-text">{
"ref_optimizer_key_uses": [
{
"table": "`salaries`",
"field": "from_date",
"equals": "DATE'1986-06-26'",
"null_rejecting": false
},
{
"table": "`salaries`",
"field": "to_date",
"equals": "DATE'1987-06-26'",
"null_rejecting": false
}
] /* ref_optimizer_key_uses */
}
</code></pre>
<h3 id="425-rows_estimation">4.2.5 rows_estimation</h3>
<p>顾名思义，用于估算需要扫描的记录数。</p>
<pre><code class="language-text">{
"rows_estimation": [
{
"table": "`salaries`",
"range_analysis": {
"table_scan": {
"rows": 2838216,
"cost": 286799
} /* table_scan */,
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "salaries_from_date_to_date_index",
"usable": true,
"key_parts": [
"from_date",
"to_date",
"emp_no"
] /* key_parts */
}
] /* potential_range_indexes */,
"setup_range_conditions": [
] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
} /* group_index_range */,
"skip_scan_range": {
"potential_skip_scan_indexes": [
{
"index": "salaries_from_date_to_date_index",
"usable": false,
"cause": "query_references_nonkey_column"
}
] /* potential_skip_scan_indexes */
} /* skip_scan_range */,
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "salaries_from_date_to_date_index",
"ranges": [
"0xda840f <= from_date <= 0xda840f AND 0xda860f <= to_date <= 0xda860f"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 86,
"cost": 50.909,
"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": "salaries_from_date_to_date_index",
"rows": 86,
"ranges": [
"0xda840f <= from_date <= 0xda840f AND 0xda860f <= to_date <= 0xda860f"
] /* ranges */
} /* range_access_plan */,
"rows_for_plan": 86,
"cost_for_plan": 50.909,
"chosen": true
} /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
}
</code></pre>
<p>其中：</p>
<ul>
<li>table：表名</li>
<li>range_analysis：
<ul>
<li>table_scan：如果全表扫描的话，需要扫描多少行（row，2838216），以及需要的代价（cost，286799）</li>
<li>potential_range_indexes：列出表中所有的索引并分析其是否可用。如果不可用的话，会列出不可用的原因是什么；如果可用会列出索引中可用的字段；</li>
<li>setup_range_conditions：如果有可下推的条件，则带条件考虑范围查询</li>
<li>group_index_range：当使用了group by或distinct时，是否有合适的索引可用。当未使用group by或distinct时，会显示chosen=false, cause=not_group_by_or_distinct；如使用了group by或distinct，但是多表查询时，会显示chosen=false，cause =not_single_table。其他情况下会尝试分析可用的索引（potential_group_range_indexes）并计算对应的扫描行数及其所需代价</li>
<li>skip_scan_range：是否使用了skip scan</li>
<li>analyzing_range_alternatives：分析各个索引的使用成本
<ul>
<li>range_scan_alternatives：range扫描分析
<ul>
<li>index：索引名</li>
<li>ranges：range扫描的条件范围</li>
<li>index_dives_for_eq_ranges：是否使用了index dive，该值会被参数eq_range_index_dive_limit变量值影响。</li>
<li>rowid_ordered：该range扫描的结果集是否根据pk值进行排序</li>
<li>using_mrr：是否使用了mrr</li>
<li>index_only：表示是否使用了覆盖索引</li>
<li>rows：扫描的行数</li>
<li>cost：索引的使用成本</li>
<li>chosen：表示是否使用了该索引</li>
</ul>
</li>
<li>analyzing_roworder_intersect：分析是否使用了索引合并（index merge），如果未使用，会在cause中展示原因；如果使用了索引合并，会在该部分展示索引合并的代价。</li>
</ul>
</li>
<li>chosen_range_access_summary：在前一个步骤中分析了各类索引使用的方法及代价，得出了一定的中间结果之后，在summary阶段汇总前一阶段的中间结果确认最后的方案
<ul>
<li>range_access_plan：range扫描最终选择的执行计划。
<ul>
<li>type：展示执行计划的type，如果使用了索引合并，则会显示index_roworder_intersect</li>
<li>index：索引名</li>
<li>rows：扫描的行数</li>
<li>ranges：range扫描的条件范围</li>
</ul>
</li>
<li>rows_for_plan：该执行计划的扫描行数</li>
<li>cost_for_plan：该执行计划的执行代价</li>
<li>chosen：是否选择该执行计划</li>
</ul>
</li>
</ul>
</li>
</ul>
<h3 id="426-considered_execution_plans重要">4.2.6 considered_execution_plans(重要)</h3>
<p>负责对比各可行计划的开销，并选择相对最优的执行计划。</p>
<pre><code class="language-text">{
"considered_execution_plans": [
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`salaries`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "salaries_from_date_to_date_index",
"rows": 86,
"cost": 50.412,
"chosen": true
},
{
"access_type": "range",
"range_details": {
"used_index": "salaries_from_date_to_date_index"
} /* range_details */,
"chosen": false,
"cause": "heuristic_index_cheaper"
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 86,
"cost_for_plan": 50.412,
"chosen": true
}
] /* considered_execution_plans */
}
</code></pre>
<p>其中：</p>
<ul>
<li>plan_prefix：当前计划的前置执行计划。</li>
<li>table：涉及的表名，如果有别名，也会展示出来</li>
<li>best_access_path：通过对比considered_access_paths，选择一个最优的访问路径
<ul>
<li>considered_access_paths：当前考虑的访问路径
<ul>
<li>access_type：使用索引的方式，可参考explain中的type字段</li>
<li>index：索引</li>
<li>rows：行数</li>
<li>cost：开销</li>
</ul>
</li>
</ul>
</li>
<li>chosen：是否选用这种执行路径</li>
<li>condition_filtering_pct：类似于explain的filtered列，是一个估算值</li>
<li>rows_for_plan：执行计划最终的扫描行数，由considered_access_paths.rows X condition_filtering_pct计算获得。</li>
<li>cost_for_plan：执行计划的代价，由considered_access_paths.cost相加获得</li>
<li>chosen：是否选择了该执行计划</li>
</ul>
<h3 id="427-attaching_conditions_to_tables">4.2.7 attaching_conditions_to_tables</h3>
<p>基于<code>considered_execution_plans</code>中选择的执行计划，改造原有<code>where</code>条件，并针对表增加适当的附加条件，以便于单表数据的筛选。</p>
<blockquote>
<p>TIPS</p>
<p>这部分条件的增加主要是为了便于ICP（索引条件下推），但ICP是否开启并不影响这部分内容的构造。<br>
ICP参考文档：https://www.cnblogs.com/Terry-Wu/p/9273177.html</p>
</blockquote>
<pre><code class="language-text">{
"attaching_conditions_to_tables": {
"original_condition": "((`salaries`.`to_date` = DATE'1987-06-26') and (`salaries`.`from_date` = DATE'1986-06-26'))",
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`salaries`",
"attached": "((`salaries`.`to_date` = DATE'1987-06-26') and (`salaries`.`from_date` = DATE'1986-06-26'))"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
}
</code></pre>
<p>其中：</p>
<ul>
<li>original_condition：原始的条件语句</li>
<li>attached_conditions_computation：使用启发式算法计算已使用的索引，如果已使用的索引的访问类型是ref，则计算用range能否使用组合索引中更多的列，如果可以，则用range的方式替换ref。</li>
<li>attached_conditions_summary：附加之后的情况汇总
<ul>
<li>table：表名</li>
<li>attached：附加的条件或原语句中能直接下推给单表筛选的条件。</li>
</ul>
</li>
</ul>
<h3 id="428-finalizing_table_conditions">4.2.8 finalizing_table_conditions</h3>
<p>最终的、经过优化后的表条件。</p>
<pre><code class="language-text">{
"finalizing_table_conditions": [
{
"table": "`salaries`",
"original_table_condition": "((`salaries`.`to_date` = DATE'1987-06-26') and (`salaries`.`from_date` = DATE'1986-06-26'))",
"final_table_condition ": null
}
] /* finalizing_table_conditions */
}
</code></pre>
<h3 id="429-refine_plan">4.2.9 refine_plan</h3>
<p>改善执行计划：</p>
<pre><code class="language-text">{
"refine_plan": [
{
"table": "`salaries`"
}
] /* refine_plan */
}
</code></pre>
<p>其中：</p>
<ul>
<li>table：表名及别名</li>
</ul>
<h2 id="43-join_execution">4.3 join_execution</h2>
<p><code>join_execution</code>段落展示了执行阶段的执行过程。</p>
<pre><code class="language-text">"join_execution": {
"select#": 1,
"steps": [
] /* steps */
}
</code></pre>
<h1 id="五ps无法替代的原因">五、PS无法替代的原因</h1>
<p><code>Performance Schema</code>（PS）与<code>optimizer_trace</code>定位本质不同，前者无法替代后者，核心差异体现在三方面：</p>
<h2 id="51-目标不同看执行结果-vs-查优化过程">5.1 目标不同：看“执行结果” vs 查“优化过程”</h2>
<ul>
<li>PS：聚焦<code>SQL</code>执行阶段的宏观资源消耗（耗时、锁等待、IO等），仅能回答“SQL执行慢不慢”；</li>
<li>optimizer_trace：穿透优化器内部，还原执行计划决策过程，能解释“为什么选这个执行计划”。</li>
</ul>
<h2 id="52-粒度不同宏观汇总-vs-微观拆解">5.2 粒度不同：宏观汇总 vs 微观拆解</h2>
<ul>
<li>PS：输出事件级统计汇总值（如总执行时间、锁等待次数），无优化器内部细节；</li>
<li>optimizer_trace：细化到优化器决策原子步骤（条件化简、索引成本计算、计划选型逻辑等），输出结构化<code>JSON</code>，可定位索引误选等根因。</li>
</ul>
<h2 id="53-场景不同定位瓶颈-vs-调试计划">5.3 场景不同：定位瓶颈 vs 调试计划</h2>
<ul>
<li>PS：用于全局性能监控，定位慢<code>SQL</code>、锁阻塞、<code>IO/CPU</code>瓶颈等宏观问题；</li>
<li>optimizer_trace：专注执行计划调试，解决索引不走、联表顺序错误、计划选型异常等问题。</li>
</ul>
<table>
<thead>
<tr>
<th>对比维度</th>
<th>optimizer_trace</th>
<th>Performance Schema</th>
</tr>
</thead>
<tbody>
<tr>
<td>核心目标</td>
<td>还原优化器决策过程（为什么选此计划）</td>
<td>监控执行阶段资源消耗（执行得慢不慢）</td>
</tr>
<tr>
<td>数据粒度</td>
<td>微观决策细节（成本计算、索引评估、计划淘汰）</td>
<td>宏观统计汇总（耗时、锁等待、IO次数）</td>
</tr>
<tr>
<td>典型场景</td>
<td>索引误选、执行计划异常、联表顺序错误</td>
<td>慢查询定位、锁阻塞、IO/CPU瓶颈分析</td>
</tr>
<tr>
<td>数据形式</td>
<td>结构化JSON（决策链路）</td>
<td>表数据（统计值、计数）</td>
</tr>
</tbody>
</table>
<p>因此，在实际调优中，二者通常配合使用：先用<code>PS</code>定位到执行缓慢的<code>SQL</code>（“找到慢SQL”），再用<code>optimizer_trace</code>调试其执行计划异常的根因（“分析为什么慢”），二者缺一不可。</p>
<h1 id="六总结">六、总结</h1>
<p>在整个<code>optimizer_trace</code>中我们重点其实就是在跟踪记录<code>trace</code>的<code>json</code>树，我们通过这棵树中的内容可以具体去分析优化器究竟做了什么事情，进行了哪些选择，是基于什么原因做的选择，选择的结果及依据。这一系列都可以辅助验证我们的一些观点及优化，更好的帮助我们对我们的数据库的实例进行调整。</p><br><br>
来源：https://www.cnblogs.com/ciel717/p/19113713
頁:
[1]