MySQL5.6 's Optimizer_trace

MySQL's explain is the result of a variety of execution plan choices, and if you want to see the entire execution plan and how to choose between multiple indexing scenarios?

This feature is supported in MySQL5.6, Optimizer_trace

This is the MySQL parameter, which is closed by default

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)

　　

How do you use it specifically?

You need to set the following:

1, open Optimizer_trace, the default is closed

SET optimizer_trace= "Enabled=on";   

　

2, set the size of optimizer_trace memory

SET optimizer_trace_max_mem_size=1000000

　　

3. Explain query statement

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. Find the results for

Mysql> SELECT * from Information_schema.optimizer_trace\g

　

This table consists of 4 fields

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 (a) NOT NULL Default ' 0 ',  ' insufficient_privileges ' tinyint (1) Not NULL default ' 0 ') Engine=myisam default Charset=utf8

　

Mainly see the Trace field, is the JSON string, JSON parsing results are as follows:

including 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 "

　

This is the result of using extend to see

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)

　　

Resources

1, http://guilhembichot.blogspot.com/2011/09/optimizer-tracing-how-to-configure-it.html

MySQL5.6 's Optimizer_trace