[MySQL 5.6] First knowledge of the optimizer trace 5.6

In MySQL5.6, it is supported to record the query plan tree of the SQL being executed, and for the time being, even for very simple queries, a lengthy query plan may not seem very readable, but for an experienced DBA who knows more about the query plan's build process, this is an optimized SQL treasure, because exposure A large number of internally generated query plan information to the user, which means that we can optimize the cost of the larger part.

The new parameter optimizer_trace can control whether the query plan tree is generated for the executed SQL, which is closed by default, and we also recommend shutting it down as it will incur additional performance overhead (Dimitrik's evaluation: http://dimitrik.free.fr/blog/ archives/2012/01/mysql-performance-overhead-of-optimizer-tracing-in-mysql-56.html).  I use the sysbench test on my own machine, 64 concurrent, Select.lua, pure memory operations, and QPS drops from 112,000 to 88,000. This is the session-level parameter and, if needed, can be opened at the session level, and the thread can only see the query plan for the current session and cannot see other sessions. The use is also very simple: open optimizer_trace

Mysql> set session optimizer_trace= ' Enabled=on ';

Query OK, 0 rows Affected (0.00 sec)

< execute your sql> (for example, here execute SELECT * from Sbtest1 ORDER by K limit 3;) Then query the Information_schema.optimizer_trace table and output the following

| 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 ' f Rom ' sbtest1 ' ORDER by ' sbtest1 '. ' k ' Limit 3″         }        ]     }   },    {       "join_optimization": {        "select#": 1,         "Steps": [          {             "Table_dependencies": [   &NBsp;          {                 "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 ' ",                 &nbsP "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": [       ]     }    } ]}

###############################################################   Mainly divided into three parts   In the join_preparation:sql preparation phase, SQL is formatted with the corresponding function join::p repare  such as * are extended       join_ Optimization:sql Optimization phase correspondence function join::optimize                                                                                                       join_ Execution:sql Execution Phase Correspondence function: Join::exec

As you can see, even a very simple SQL will print a lengthy query plan. Of course you can also import the query plan into a file, such as importing to a file named Xx.trace, and then using a JSON reader to view the select TRACE into DumpFile "Xx.trace" from INFORMATION_SCHEMA. Optimizer_trace; Official documents give a good example, more complicated than this one, interested students can self-browsing: http://dev.mysql.com/doc/internals/en/tracing-example.html also wrote a blog before, Refer to the relevant options for optimizer trace (http://mysqllover.com/?p=470):

Optimizer_trace has two fields:

"Enabled=on,one_line=off", which can be used to update strings by set, which means open optimizer_trace, which indicates whether the printed query plan is displayed on a single line, Or in the form of a JSON tree we can set this parameter at the session level. The default Optimizer_trace_limit value is 1, so only one record is saved. This setting needs to be re-attached to the session to take effect, and the other variable optimizer_trace_offset is usually used in conjunction with the default value of-1 for example, Offset=-1, Limit=1 will show the most recent Traceoffset=-2,limit The =1 will display the nearest previous trace.

Offset=-5,limit=5 prints the last 5 traces.

Overall: When offset is greater than 0 o'clock, the old limit trace, starting with offset, is displayed, meaning that the new trace is not written down.

When offset is less than 0 o'clock, a limit trace that starts with the latest-offset is displayed, that is, only the new trace is displayed

Note Resetting the variable causes the trace to be emptied

In addition, because the trace data is stored in memory, you also need to set optimizer_trace_max_mem_size to limit the amount of memory used, or unexpected settings can cause memory to explode. This is the session level, should not be set too large optimizer_trace_limit and optimizer_trace_offset also affect the memory size, but should not exceed optimizer_trace_max_mem_size additional , there is also a parameter optimizer_trace_features, you can control the printing to the query plan tree items, by default, all open, as follows:

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)

If you don't care about some of the query plan options, you can turn it off and print only what you care about, which reduces the output of the query plan tree and makes it a little more readable. Greedy_search: For join operations with n tables, a query plan path that can produce a factorial of n, range_optimizer:range optimization dynamic_range:dynamic range Optimizer (that is, " Range checked for every row, each external column executes a range optimizer), and if this option is turned off, only the first call to Join_tab::sql_select is tracked Repeated_ Subselect: subquery, if closed, only the first call to Item_subselect is tracked —————————————————-TODO PLAN: Increase the threshold (number of rows read, or time of execution), when the threshold is exceeded, The trace is automatically imported into a file, which makes it easy to debug online. Reference: http://dev.mysql.com/doc/internals/en/optimizer-features-to-trace.html/HTTP Dev.mysql.com/doc/internals/en/system-variables-controlling-trace.html http://guilhembichot.blogspot.com/2011/ 09/optimizer-tracing-how-to-configure-it.html

Http://jorgenloland.blogspot.com/2011/10/optimizer-tracing-query-execution-plan.html

Original articles, reproduced please specify: Reproduced from simple life

This article link address: [MySQL 5.6] first Knowledge 5.6 of the optimizer trace

The footnote information of the article is automatically generated by the WordPress Wp-posturl plugin.

[MySQL 5.6] First knowledge of the optimizer trace 5.6