用MySQL的optimizer_trace进行sql调优

在我们调优MySQL的SQL时候,通常使用三种工具进行查看sql执行的效率,explain、profile、optimizer_trace。前两个经常被人使用,由于第三个难度较大,大家使用的较少,下面简单说下如何使用。
 
opitimizer_trace的使用:
# 开启跟踪,默认是关闭的
SET optimizer_trace="enabled=on";      
#执行你的sql语句
select ....
#查看trace信息
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
# 执行完你需要的sql语句就可以关闭trace了
SET optimizer_trace="enabled=off";
 
支持的trace语句有:
1.SELECT;
2.INSERT or REPLACE (with VALUES or SELECT);
3.UPDATE/DELETE and their multi-table variants;
4.all the previous ones prefixed by EXPLAIN;
5.SET (unless it manipulates the optimizer_trace system variable);
6.DO;
7.DECLARE/CASE/IF/RETURN (stored routines language elements);
8.CALL. 
 
 
如下这个语句可以把结果插入到一个文件里:
SELECT TRACE INTO DUMPFILE <filename> FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
在mysqld里进行设置相关参数:
A brief overview from "mysqld --verbose --help":
--optimizer-trace=name
Controls tracing of the Optimizer:
--optimizer-trace-features=name
Enables/disables tracing of selected features of the
Optimizer:
optimizer_trace_features=option=val[,option=val...],
where option is one of {greedy_search, range_optimizer,
dynamic_range, repeated_subselect} and val is one of {on,
off, default}
--optimizer-trace-limit=#
Maximum number of shown optimizer traces
--optimizer-trace-max-mem-size=#
Maximum allowed cumulated size of stored optimizer traces
--optimizer-trace-offset=#
Offset of first optimizer trace to show; see manual
--end-markers-in-json=#
In JSON output ("EXPLAIN FORMAT=JSON" and optimizer
trace), if set to 1, repeats the structure's key (if it
has one) near the closing bracket
 
 具体跟踪的json格式的信息:
select * from test1,test2 where test1.id=test2.id and test1.id>4999900 | {
  "steps": [
    {
      "join_preparation": {           --连接准备
        "select#": 1,                 --join准备的第一步
        "steps": [                    --解析成编号,解析数据库和表
          {
            "expanded_query": "/* select#1 */ select `test1`.`id` AS `id`,`test1`.`k` AS `k`,`test1`.`c` AS `c`,`test1`.`pad` AS `pad`,`test2`.`id` AS `id`,`test2`.`k` AS `k`,`test2`.`c` AS `c`,`test2`.`pad` AS `pad` from `test1` join `test2` where ((`test1`.`id` = `test2`.`id`) and (`test1`.`id` > 4999900))"
          }
        ]
      }
    },
    {
      "join_optimization": {         --join优化
        "select#": 1,
        "steps": [
          {
            "condition_processing": {       --where条件
              "condition": "WHERE",
              "original_condition": "((`test1`.`id` = `test2`.`id`) and (`test1`.`id` > 4999900))",
              "steps": [       --优化的步骤
                {
                  "transformation": "equality_propagation",         --等值优化
                  "resulting_condition": "((`test1`.`id` > 4999900) and multiple equal(`test1`.`id`, `test2`.`id`))"   --把test.id>4999900放到前面,test1.id=test2.id使用多等值连接
                },
                {
                  "transformation": "constant_propagation",        --常量优化
                  "resulting_condition": "((`test1`.`id` > 4999900) and multiple equal(`test1`.`id`, `test2`.`id`))"
                },
                {
                  "transformation": "trivial_condition_removal",   --琐碎的条件排除
                  "resulting_condition": "((`test1`.`id` > 4999900) and multiple equal(`test1`.`id`, `test2`.`id`))"
                }
              ]
            }
          },
          {
            "table_dependencies": [     --表依赖
              {
                "table": "`test1`",        --表名
                "row_may_be_null": false,  --是否有null值,flase是没有
                "map_bit": 0,
                "depends_on_map_bits": [
                ]
              },
              {
                "table": "`test2`",
                "row_may_be_null": false,
                "map_bit": 1,
                "depends_on_map_bits": [
                ]
              }
            ]
          },
          {
            "ref_optimizer_key_uses": [    --相关优化索引使用
              {
                "table": "`test1`",  
                "field": "id",             --索引字段
                "equals": "`test2`.`id`",  --连接的等值字段
                "null_rejecting": false
              },
              {
                "table": "`test2`",
                "field": "id",
                "equals": "`test1`.`id`",
                "null_rejecting": false
              }
            ]
          },
          {
            "rows_estimation": [          --行评估
              {
                "table": "`test1`",
                "range_analysis": {          --范围分析
                  "table_scan": {
                    "rows": 4804854,          --4804854行数据
                    "cost": 1.03e6            --花费1.03e6
                  },
                  "potential_range_indices": [   --可能的范围指数
                    {
                      "index": "PRIMARY",
                      "usable": true,           --可使用的索引
                      "key_parts": [
                        "id"
                      ]                         --可使用的索引字段
                    },
                    {
                      "index": "k_1",
                      "usable": false,         --不能使用的索引
                      "cause": "not_applicable"  --不被应用
                    }
                  ],
                  "setup_range_conditions": [     --设置范围条件
                  ],
                  "group_index_range": {          --组范围索引
                    "chosen": false,              --不选择的
                    "cause": "not_single_table"   --goup by不是一个表的,所以不选择
                  },
                  "analyzing_range_alternatives": {     --分析每个索引做范围扫描的花费
                    "range_scan_alternatives": [       --范围扫描花费
                      {
                        "index": "PRIMARY",
                        "ranges": [
                          "4999900 < id"
                        ],
                        "index_dives_for_eq_ranges": true,    --索引驱动等值范围扫描
                        "rowid_ordered": true,                --rowid是顺序的
                        "using_mrr": false,                   --不能使用mrr,因为是主键
                        "index_only": false,                  
                        "rows": 99,                           --过滤出来99行
                        "cost": 21.434,                       --花费21.434
                        "chosen": true                        --这个索引被选择选择
                      }
                    ],
                    "analyzing_roworder_intersect": {         --分析执行顺序阶段
                      "usable": false,                        --不可使用
                      "cause": "too_few_roworder_scans"       --少数的执行顺序扫描
                    }
                  },
                  "chosen_range_access_summary": {           --选择范围访问概述
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "PRIMARY",
                      "rows": 99,
                      "ranges": [
                        "4999900 < id"
                      ]
                    },
                    "rows_for_plan": 99,
                    "cost_for_plan": 21.434,
                    "chosen": true
                  }
                }
              },
              {
                "table": "`test2`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 4804854,
                    "cost": 1.03e6
                  },
                  "potential_range_indices": [
                    {
                      "index": "PRIMARY",
                      "usable": true,
                      "key_parts": [
                        "id"
                      ]
                    },
                    {
                      "index": "k_2",
                      "usable": false,
                      "cause": "not_applicable"
                    }
                  ],
                  "setup_range_conditions": [
                  ],
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_single_table"
                  },
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "PRIMARY",
                        "ranges": [
                          "4999900 < id"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 99,
                        "cost": 21.433,
                        "chosen": true
                      }
                    ],
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    }
                  },
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "PRIMARY",
                      "rows": 99,
                      "ranges": [
                        "4999900 < id"
                      ]
                    },
                    "rows_for_plan": 99,
                    "cost_for_plan": 21.433,
                    "chosen": true
                  }
                }
              }
            ]
          },
          {
            "considered_execution_plans": [   --决定执行计划
              {
                "plan_prefix": [             --计划前
                ],
                "table": "`test1`",          --test1表的执行计划
                "best_access_path": {        --最好的访问路径
                  "considered_access_paths": [  --决定的访问路径
                    {
                      "access_type": "ref",     --访问类型是ref
                      "index": "PRIMARY",       --使用的索引是主键
                      "usable": false,          
                      "chosen": false
                    },
                    {
                      "access_type": "range",
                      "rows": 99,
                      "cost": 41.234,
                      "chosen": true
                    }
                  ]
                },
                "cost_for_plan": 41.234,
                "rows_for_plan": 99,
                "rest_of_plan": [
                  {
                    "plan_prefix": [
                      "`test1`"
                    ],
                    "table": "`test2`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "ref",
                          "index": "PRIMARY",
                          "rows": 1,
                          "cost": 99.2,
                          "chosen": true
                        },
                        {
                          "access_type": "range",
                          "cause": "heuristic_index_cheaper",
                          "chosen": false
                        }
                      ]
                    },
                    "cost_for_plan": 160.03,
                    "rows_for_plan": 99,
                    "chosen": true
                  }
                ]
              },
              {
                "plan_prefix": [
                ],
                "table": "`test2`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "PRIMARY",
                      "usable": false,
                      "chosen": false
                    },
                    {
                      "access_type": "range",
                      "rows": 99,
                      "cost": 41.233,
                      "chosen": true
                    }
                  ]
                },
                "cost_for_plan": 41.233,
                "rows_for_plan": 99,
                "rest_of_plan": [
                  {
                    "plan_prefix": [
                      "`test2`"
                    ],
                    "table": "`test1`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "ref",
                          "index": "PRIMARY",
                          "rows": 1,
                          "cost": 99.2,
                          "chosen": true
                        },
                        {
                          "access_type": "range",
                          "cause": "heuristic_index_cheaper",
                          "chosen": false
                        }
                      ]
                    },
                    "cost_for_plan": 160.03,
                    "rows_for_plan": 99,
                    "pruned_by_cost": true
                  }
                ]
              }
            ]
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "((`test2`.`id` = `test1`.`id`) and (`test1`.`id` > 4999900))",
              "attached_conditions_computation": [
              ],
              "attached_conditions_summary": [
                {
                  "table": "`test1`",
                  "attached": "(`test1`.`id` > 4999900)"
                },
                {
                  "table": "`test2`",
                  "attached": null
                }
              ]
            }
          },
          {
            "refine_plan": [
              {
                "table": "`test1`",
                "access_type": "range"
              },
              {
                "table": "`test2`"
              }
            ]
          }
        ]
      }
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ]
      }
    }
  ]
}
posted @ 2018-06-28 17:51  katechun  阅读(1850)  评论(0编辑  收藏  举报