mysql optimize 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: ref

possible_keys: uid

key: uid

key_len: 15

ref: const,const

rows: 1

Extra: Using index condition

1 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_TRACE

Create 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: ref

possible_keys: uid

key: uid

key_len: 15

ref: const,const

rows: 1

filtered: 100.00

Extra: Using index condition

1 row in set, 1 warning (0.01 sec)

mysql> show warnings\G

*************************** 1. row ***************************

Level: Note

Code: 1003

Message: /* 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,1

1 row in set (0.00 sec)

[参考资料]