# Turn tracing on (it's off by default):
SET optimizer_trace="enabled=on";
SELECT ...; # your query here
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
# possibly more queries...
# When done with tracing, disable it:
SET optimizer_trace="enabled=off";
#show variables
root@localhost [(none)]>show variables like 'optimizer_trace';
+-----------------+--------------------------+
| Variable_name | Value |
+-----------------+--------------------------+
| optimizer_trace | enabled=off,one_line=off |
+-----------------+--------------------------+
1 row in set (0.09 sec)
##enable:allows to enable/disable tracing
##one_line:if on, the trace will have no whitespace; it's unreadable for humans but readable for JSON parsers (they ignore whitespace); the only advantage is a saving on space
二、System Variables Controlling the Trace
--optimizer-trace=name
Controls tracing of the Optimizer:
--optimizer-trace-features=name
Enables/disables tracing of selected features of the
Optimizer:
optimizer_trace_features=option=val[,option=val...],
where option is one of {greedy_search, range_optimizer,
dynamic_range, repeated_subselect} and val is one of {on,
off, default}
--optimizer-trace-limit=#
Maximum number of shown optimizer traces
--optimizer-trace-max-mem-size=#
Maximum allowed cumulated size of stored optimizer traces
--optimizer-trace-offset=#
Offset of first optimizer trace to show; see manual
--end-markers-in-json=#
In JSON output ("EXPLAIN FORMAT=JSON" and optimizer
trace), if set to 1, repeats the structure's key (if it
has one) near the closing bracket
三、The INFORMATION_SCHEMA.OPTIMIZER_TRACE Table
The OPTIMIZER_TRACE table contains information about traced statements. The table has these columns:
· QUERY
: The statement text.
· TRACE
: The trace, in JSON format (see json.org: basically it has scalars (number, string, bool) and structures (either arrays or associative arrays)).
· MISSING_BYTES_BEYOND_MAX_MEM_SIZE
: Explained further below.
· INSUFFICIENT_PRIVILEGES
: Explained further below.
四、Tuning Trace Purging
This is done with
SET optimizer_trace_offset=<OFFSET>, optimizer_trace_limit=<LIMIT>
where OFFSET is a signed integer, and LIMIT is a positive integer. The default for optimizer_trace_offset is -1; the default for optimizer_trace_limit is 1. The SET statement has the following effects:
l All remembered traces are cleared
l A later SELECT on the OPTIMIZER_TRACE table returns the first LIMIT traces of the OFFSET oldest remembered traces (if OFFSET ≥ 0), or the first LIMIT
For example, a combination of
OFFSET=-1 and LIMIT=1 will make the last trace be shown (as is default),
OFFSET=-2 and LIMIT=1 will make the next-to-last be shown,
OFFSET=-5 and LIMIT=5 will make the last five traces be shown.
Such negative OFFSET can be useful when one knows that the interesting substatements are the few last ones of a stored routine, like this:
SET optimizer_trace_offset=-5, optimizer_trace_limit=5;
CALL stored_routine(); # more than 5 substatements in this routine
SELECT * FROM information_schema.OPTIMIZER_TRACE; # see only last 5 traces
The more accurately those two variables are adjusted, the less memory is used. For example, OFFSET=0 and LIMIT=5 will use memory to remember 5 traces, so if only the three first are needed, OFFSET=0 and LIMIT=3 is better (tracing stops after LIMIT traces, so the 4th and 5th trace are not created and take up no memory).
If OFFSET≥0, only LIMIT traces are kept in memory. If OFFSET<0, that is not true。
Such memory and speed gains are the reason why optimizer_trace_offset and optimizer_trace_limit, which are restrictions at the trace producer level, are offered. They are better than using
SELECT * FROM OPTIMIZER_TRACE LIMIT <LIMIT> OFFSET <OFFSET>;
which is a restriction on the trace consumer level and saves almost nothing.
五、Example
http://dev.mysql.com/doc/internals/en/tracing-example.html
六、Preventing Use of Optimizer Trace
If for some reason, as DBA of a MySQL Server, you wish to prevent all users from seeing traces of their queries, start the server with these options:
--maximum-optimizer-trace-max-mem-size=0 --optimizer-trace-max-mem-size=0
Reference Documentation For English:
http://dev.mysql.com/doc/internals/en/optimizer-tracing.html
为了方便大家交流,本人开通了微信公众号,和QQ群291519319。喜欢技术的一起来交流吧