首页 > 代码库 > MySQL5.6的optimizer_trace
MySQL5.6的optimizer_trace
MySQL的explain是各种执行计划选择的结果，如果想看整个执行计划以及对于多种索引方案之间是如何选择的？
MySQL5.6中支持这个功能，optimizer_trace
这个是mysql的参数，默认是关闭的
mysql> set optimizer_trace="enabled=on";Query OK, 0 rows affected (0.00 sec)mysql> show variables like ‘%optimizer_trace%‘;+------------------------------+----------------------------------------------------------------------------+| Variable_name | Value |+------------------------------+----------------------------------------------------------------------------+| optimizer_trace | enabled=on,one_line=off || optimizer_trace_features | greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on || optimizer_trace_limit | 1 || optimizer_trace_max_mem_size | 16384 || optimizer_trace_offset | -1 |+------------------------------+----------------------------------------------------------------------------+5 rows in set (0.00 sec)
具体这么使用呢？
需要设置如下：
1、开启optimizer_trace，默认是关闭的
SET optimizer_trace="enabled=on";
2、设置optimizer_trace内存的大小
SET OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000
3、explain查询语句
mysql> explain SELECT * FROM atomuser WHERE `uid` =28778731 AND ptype = "photo" LIMIT 0 , 1\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: atomuser type: refpossible_keys: uid key: uid key_len: 15 ref: const,const rows: 1 Extra: Using index condition1 row in set (0.01 sec)
4、查找对于的结果
mysql> select * from information_schema.optimizer_trace\G
这个表包括4个字段
mysql> show create table information_schema.optimizer_trace\G*************************** 1. row *************************** Table: OPTIMIZER_TRACECreate Table: CREATE TEMPORARY TABLE `OPTIMIZER_TRACE` ( `QUERY` longtext NOT NULL, `TRACE` longtext NOT NULL, `MISSING_BYTES_BEYOND_MAX_MEM_SIZE` int(20) NOT NULL DEFAULT ‘0‘, `INSUFFICIENT_PRIVILEGES` tinyint(1) NOT NULL DEFAULT ‘0‘) ENGINE=MyISAM DEFAULT CHARSET=utf8
主要看TRACE字段，是json串，json解析结果如下：
包括join_preparation,join_optimization,join_explain
join_preparation
"/* select#1 */ select `atomuser`.`id` AS `id`,`atomuser`.`uid` AS `uid`,`atomuser`.`ptype` AS `ptype`,`atomuser`.`regtime` AS `regtime` from `atomuser` where ((`atomuser`.`uid` = 28778731) and (`atomuser`.`ptype` = ‘photo‘)) limit 0,1"
这个是使用extend看到的结果
mysql> explain extended SELECT * FROM atomuser WHERE `uid` =28778731 AND ptype = "photo" LIMIT 0 , 1\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: atomuser type: refpossible_keys: uid key: uid key_len: 15 ref: const,const rows: 1 filtered: 100.00 Extra: Using index condition1 row in set, 1 warning (0.01 sec)mysql> show warnings\G*************************** 1. row *************************** Level: Note Code: 1003Message: /* select#1 */ select `test`.`atomuser`.`id` AS `id`,`test`.`atomuser`.`uid` AS `uid`,`test`.`atomuser`.`ptype` AS `ptype`,`test`.`atomuser`.`regtime` AS `regtime` from `test`.`atomuser` where ((`test`.`atomuser`.`ptype` = ‘photo‘) and (`test`.`atomuser`.`uid` = 28778731)) limit 0,11 row in set (0.00 sec)
[参考资料]
1、http://guilhembichot.blogspot.com/2011/09/optimizer-tracing-how-to-configure-it.html
MySQL5.6的optimizer_trace
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。