默认情况下，每个新的跟踪会覆盖之前的跟踪。因此，如果一个语句包含子语句（例如调用存储过程、存储函数或触发器），最顶层的语句和子语句各自会生成一个跟踪，但在执行结束时，只能看到最后一个子语句的跟踪。By default, each new trace overwrites the previous trace. Thus, if a statement contains substatements (such as invoking stored procedures, stored functions, or triggers), the topmost statement and substatements each generate one trace, but at the end of execution, the trace for only the last substatement is visible.

希望查看不同子语句跟踪的用户可以为所需的子语句启用或禁用跟踪，但这需要编辑例程代码，而这并不总是可行。另一种解决方案是调整跟踪清理。通过设置optimizer_trace_offset和optimizer_trace_limit系统变量来完成，如下所示：A user who wants to see the trace of a different substatement can enable or disable tracing for the desired substatement, but this requires editing the routine code, which may not always be possible. Another solution is to tune trace purging. This is done by setting the optimizer_trace_offset and optimizer_trace_limit system variables, like this:

 
SET optimizer_trace_offset=offset, optimizer_trace_limit=limit; 

offset是一个有符号整数（默认值-1）；limit是一个正整数（默认值1）。这样的SET语句具有以下效果：offset is a signed integer (default -1); limit is a positive integer (default 1). Such a SET statement has the following effects:

示例：Examples:

因此，当感兴趣的子语句是存储例程中的最后几个时，offset 的负值可能非常有用。例如：Negative values for offset can thus prove useful when the substatements of interest are the last few in a stored routine. For example:

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 the last 5 traces

当知道感兴趣的子语句是存储例程的前几个时，正的 offset 可能会很有用。A positive offset can be useful when one knows that the interesting substatements are the first few in a stored routine.

这两个变量设置得越准确，使用的内存就越少。例如，SET optimizer_trace_offset=0, optimizer_trace_limit=5 需要足够的内存来存储五个跟踪，因此如果只需要前三个，更好的做法是使用 SET optimizer_trace_offset=0, optimizer_trace_limit=3，因为跟踪在达到 limit 个跟踪后就会停止。存储过程可能有一个循环执行许多子语句，从而生成许多跟踪，这可能会使用大量内存；在这种情况下，为 offset 和 limit 选择适当的值可以将跟踪限制为例如循环的单次迭代。这也减少了跟踪对执行速度的影响。The more accurately these two variables are set, the less memory is used. For example, SET optimizer_trace_offset=0, optimizer_trace_limit=5 requires sufficient memory to store five traces, so if only the three first are needed, is is better to use SET optimizer_trace_offset=0, optimizer_trace_limit=3, since tracing stops after limit traces. A stored routine may have a loop which executes many substatements and thus generates many traces, which can use a lot of memory; in such cases, choosing appropriate values for offset and limit can restrict tracing to, for example, a single iteration of the loop. This also decreases the impact of tracing on execution speed.

如果offset大于或等于 0，则内存中只保留limit条跟踪。如果offset小于 0，则情况并非如此：相反，内存中会保留-offset条跟踪。即使limit小于-offset，排除最后一条语句，最后一条语句仍然必须被跟踪，因为执行多一条语句后，它将仍在限制范围内。由于小于 0 的 offset 是从末尾计算的，随着更多语句的执行，“窗口”会移动。If offset is greater than or equal to 0, only limit traces are kept in memory. If offset is less than 0, that is not true: instead, -offset traces are kept in memory. Even if limit is smaller than -offset, excluding the last statement, the last statement must still be traced because it will be within the limit after executing one more statement. Since an offset less than 0 is counted from the end, the “window” moves as more statements execute.

使用 optimizer_trace_offset 和 optimizer_trace_limit，这些是在跟踪生成器级别的限制，比在跟踪消费者（SQL）级别使用 SELECT * FROM OPTIMIZER_TRACE LIMIT limit OFFSET offset 设置偏移或限制提供更好的（更高的）速度和（更少的）内存使用，这几乎没有节省任何资源。Using optimizer_trace_offset and optimizer_trace_limit, which are restrictions at the trace producer level, provide better (greater) speed and (less) memory usage than setting offsets or limits at the trace consumer (SQL) level with SELECT * FROM OPTIMIZER_TRACE LIMIT limit OFFSET offset, which saves almost nothing.