使用 Trace 进行执行计划分析

使用 Trace 进行执行计划分析

 
SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on;
SET OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000;
 
#需要分析的SQL语句
SELECT xxx;
 
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

 

这个json就是Trace的内容：

 

Trace中几个重要的点：

1、考虑使用的执行计划：  considered_execution_plans（复杂查询时有些表的index的选择 没在 considered_execution_plans中）

   "considered_execution_plans": [
             {
               "plan_prefix": [
               ] /* plan_prefix */,
               "table": "`audit_log` `auditlogpo0_`",
               "best_access_path": {
                 "considered_access_paths": [
                   {
                     "access_type": "ref",
                     "index": "hasDel",
                     "rows": 139033,
                     "cost": 41708,
                     "chosen": true
                   },
                   {
                     "access_type": "ref",
                     "index": "UDX_QUERY",
                     "rows": 139033,
                     "cost": 25486,
                     "chosen": true
                   },
                   {
                     "access_type": "range",
                     "range_details": {
                       "used_index": "UDX_QUERY"
                     } /* range_details */,
                     "chosen": false,
                     "cause": "heuristic_index_cheaper"
                   }
                 ] /* considered_access_paths */
               } /* best_access_path */,
               "condition_filtering_pct": 100,
               "rows_for_plan": 139033,       
               "cost_for_plan": 25486,        #与此处花费对同的那条index 就是选定的index
               "chosen": true
             }
           ] /* considered_execution_plans */
         },

 

2、最终优化的where条件： finalizing_table_conditions

            "finalizing_table_conditions": [
              {
                "table": "`audit_log` `auditlogpo0_`",
                "original_table_condition": "((`auditlogpo0_`.`tenant_id` = '2c9048c271548aa0017157631118003c') and (`auditlogpo0_`.`opera_object` = 'Account') and (`auditlogpo0_`.`has_del` = '0'))",
                "final_table_condition   ": null
              }
            ] /* finalizing_table_conditions */

 

更多细节可以参考：   MYSQL sql执行过程的一些跟踪分析(二.mysql优化器追踪分析)