[MySQL 5.6] 初识5.6的optimizer trace

在MySQL5.6中,支持将执行的SQL的查询计划树记录下来,目前来看,即使对于非常简单的查询,也会打印出冗长的查询计划,看起来似乎不是很可读,不过对于一个经验丰富,对查询计划的生成过程比较了解的DBA而言,这是一个优化SQL的宝藏,因为暴露了大量的内部产生查询计划的信息给用户,这意味着,我们可以对开销较大的部分进行优化。

新参数optimizer_trace可以控制是否为执行的SQL生成查询计划树,默认关闭,我们也建议关闭,因为它会产生额外的性能开销(dimitrik的评测:http://dimitrik.free.fr/blog/archives/2012/01/mysql-performance-overhead-of-optimizer-tracing-in-mysql-56.html)。

我在自己的机器上使用sysbench测试,64个并发,select.lua,纯内存操作,QPS从112,000下降到88,000。

这是session级别的参数,如果需要是,可以在session级别打开,线程只能看到当前会话的查询计划,无法看到其他会话的。

使用也很简单:

打开optimizer_trace

mysql> set session optimizer_trace=’enabled=on’;

Query OK, 0 rows affected (0.00 sec)

<执行你的SQL>  (例如,这里执行select * from sbtest1 order by k limit 3;)

然后查询information_schema.optimizer_trace表,输出如下

| select * from sbtest1 order by k limit 3 | {

  “steps”: [
    {
      “join_preparation”: {
        “select#”: 1,
        “steps”: [
          {
            “expanded_query”: “/* select#1 */ select `sbtest1`.`id` AS `id`,`sbtest1`.`k` AS `k`,`sbtest1`.`c` AS `c`,
`sbtest1`.`pad` AS `pad` from `sbtest1` order by `sbtest1`.`k` limit 3″
          }
        ]
      }
    },
    {
      “join_optimization”: {
        “select#”: 1,
        “steps”: [
          {
            “table_dependencies”: [
              {
                “table”: “`sbtest1`”,
                “row_may_be_null”: false,
                “map_bit”: 0,
                “depends_on_map_bits”: [
                ]
              }
            ]
          },
          {

            “rows_estimation”: [
              {
                “table”: “`sbtest1`”,
                “table_scan”: {
                  “rows”: 986400,
                  “cost”: 13741
                }
              }
            ]
          },
          {
            “considered_execution_plans”: [
              {
                “plan_prefix”: [
                ],
                “table”: “`sbtest1`”,
                “best_access_path”: {
                  “considered_access_paths”: [
                    {
                      “access_type”: “scan”,
                      “rows”: 986400,
                      “cost”: 211021,
                      “chosen”: true
                    }
                  ]
                },
                “cost_for_plan”: 211021,
                “rows_for_plan”: 986400,
                “chosen”: true
              }
            ]
          },
          {
            “attaching_conditions_to_tables”: {
              “original_condition”: null,
              “attached_conditions_computation”: [
              ],
              “attached_conditions_summary”: [
                {
                  “table”: “`sbtest1`”,
                  “attached”: null
                }
              ]
            }
          },
          {
            “clause_processing”: {
              “clause”: “ORDER BY”,
              “original_clause”: “`sbtest1`.`k`”,
              “items”: [
                {
                  “item”: “`sbtest1`.`k`”
                }
              ],
              “resulting_clause_is_simple”: true,
              “resulting_clause”: “`sbtest1`.`k`”
            }
          },
          {
            “refine_plan”: [
              {
                “table”: “`sbtest1`”,
                “access_type”: “table_scan”
              }
            ]
          },
          {
            “reconsidering_access_paths_for_index_ordering”: {
              “clause”: “ORDER BY”,
              “index_order_summary”: {
                “table”: “`sbtest1`”,
                “index_provides_order”: true,
                “order_direction”: “asc”,
                “index”: “k”,
                “plan_changed”: true,
                “access_type”: “index_scan”
              }
            }
          }
        ]
      }
    },
    {
      “join_execution”: {
        “select#”: 1,
        “steps”: [
        ]
      }
    }
  ]
}
###############################################################

主要分为三个部分 

join_preparation:SQL的准备阶段,sql被格式化
对应函数 JOIN::prepare

例如 * 被扩展开来

join_optimization:SQL优化阶段
对应函数JOIN::optimize

join_execution:SQL执行阶段
对应函数:JOIN::exec

可以看到,即便是一条非常简单的SQL,也会打印出很冗长的查询计划。

当然你也可以把查询计划导入到文件中去,例如导入到一个命名为xx.trace的文件,然后用JSON阅读器来查看     

SELECT TRACE INTO DUMPFILE “xx.trace” FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

官方文档给出了一个不错的例子,比这里的这个要复杂多了,有兴趣的同学可自行翻阅:http://dev.mysql.com/doc/internals/en/tracing-example.html

之前也写过博客,提及到optimizer trace的相关选项(http://mysqllover.com/?p=470 ):

optimizer_trace有两个字段:

“enabled=on,one_line=off” ,可以通过set 进行字符串更新,前者表示打开optimizer_trace,后者表示打印的查询计划是否以一行显示,还是以json树的形式显示
我们可以在session级别来设这这个参数。

默认optimizer_trace_limit值为1,因此只会保存一条记录。这个设置需要重连session才能生效,另外一个变量optimizer_trace_offset通常与之配合使用,默认值为-1

例如,offset=-1, limit=1将显示最近一次trace
offset=-2,limit=1将显示最近的前一个trace。

offset=-5,limit=5 将最近的5次trace打印出来

 总的来说:
当offset大于0时,则会显示老的从offset开始的limit个trace,也就是说,新的trace没有记下来。

当offset小于0时,则会显示最新的-offset开始的limit个trace,也就是说,只显示新的trace

 注意重设变量会导致trace被清空

另外由于trace数据是存储在内存中的,因此还需要设置optimizer_trace_max_mem_size来限制内存的使用量,否则意外的设置可能导致内存爆掉。这是session级别,不应该设置的过大

optimizer_trace_limit和optimizer_trace_offset也影响占用内存大小,但不应该超过OPTIMIZER_TRACE_MAX_MEM_SIZE

 
另外,还有个参数optimizer_trace_features,可以控制打印到查询计划树的项,默认情况下是全部打开的,如下:

mysql> show variables like ‘optimizer_trace_features’;

+————————–+—————————————————————————-+
| Variable_name            | Value                                                                      |
+————————–+—————————————————————————-+
| optimizer_trace_features | greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on |
+————————–+—————————————————————————-+
1 row in set (0.00 sec)

如果你不关心某些查询计划选项,可以将其关闭掉,只打印你关注的,这样可以减小查询计划树的输出,让其更可读一点。

greedy_search:对于有N个表的join操作,可能产生N的阶乘的查询计划路径;
range_optimizer:range优化
dynamic_range:dynamic range optimizer(也就是”range checked  for each row”,每个外部列会执行一次range optimizer);如果关闭该选项的话,只有第一次调用JOIN_TAB::SQL_SELECT才被跟踪
repeated_subselect:子查询,如果关闭的话,只有第一次调用Item_subselect 才被跟踪

—————————————————-
TODO PLAN:增加阈值(读取的行数,或者执行的时间),超过阈值时,自动将trace导入到某个文件中,这样可以便于在线debug
参考:

原创文章,转载请注明: 转载自Simple Life

本文链接地址: [MySQL 5.6] 初识5.6的optimizer trace

Post Footer automatically generated by wp-posturl plugin for wordpress.


Comments

Leave a Reply

Your email address will not be published. Name and email are required


Current month ye@r day *