mysql5.7优化工具_MySQL5.7追踪优化器

# 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=, optimizer_trace_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 OFFSET ;

which is a restriction on the trace consumer level and saves almost nothing.

五、Example

六、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：

为了方便大家交流，本人开通了微信公众号，和QQ群291519319。喜欢技术的一起来交流吧