First look at the typical usage of the MySQL tracking optimizer:Open: SET optimizer_trace= "Enabled=on";query Optimizer information: SELECT * from INFORMATION_SCHEMA. Optimizer_trace;off: SET optimizer_trace= "Enabled=off";It is turned off by default and must be turned on when you want to use it. Take a look at the parameters: Enabled: Turn tracker on or off One_line: If on will save the trace in josn storage, but reading is more laborious, in addition to saving space is not good, but it is recommended to use this method. Take a look at the parameters of the optimizer, or use mysqld--verbose--help view:--optimizer-trace=name control optimized tracking--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 are one of {on, off, default}- -optimizer-trace-limit=# Displays the maximum number of optimization trackers--optimizer-trace-max-mem-size=# the maximum size allowed for storage-optimized footprints--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 STR Ucture ' s key (if it has one) is near the closing Bracke and this is the information_schema of what was said earlier. The Optimizer_trace table is the corresponding set GLOBAL optimizer_trace= "One_line=on"; Open the Tracking optimizer and then do a single operation to see the specific Tracking optimizer information, the following statement to query: SELECT * From INFORMATION_SCHEMA. Optimizer_trace\g; Look at the structure of this table: Look at the fieldsInfo: Query: Inquiry statement trace: Trace information, which is a JSON-stored missing_bytes_beyond_max_mem_size:insufficient_privileges tracking optimizer that can track a lot of information, Select Insert,replace (its value or selection); Update/delete and multi-table variants; all explain prefixes before; SET (unless it manipulates optimizer_trace system variables); Declare/case/if/return (stored program language element); If one of these statements is prepared and executed in a separate step, preparation and execution are tracked separately in general, a new trace overwrites the previous trace, especially for executed statements, which are generated only in the latest trackers, which are not generated by the old tracker. This is the overriding principle. So we need to purify the tracking optimizer. Take a look at the following statement: SET Optimizer_trace_offset=<offset>, optimizer_trace_limit=<limit> through the above statement to purify. The default values for Optimizer_trace_offset and Optimizer_trace_limit are 1 and 1, respectively. What does this parameter setting mean: 1: All trace information is cleared when the user exits 2: if the offset The same query that is greater than 0 returns the record information to the first query,offset is less than 0, and the record is recorded. For example, we will set the following information: Offset=-1 and limit=1 The last query information will be recorded Offset=-2 and Limit=1 will record the next to the last query information offset=-5 and limit=5 will record the last five query information offset=0 and limit=5 will only record five times the information offset≥0, in memory will only record the LIMIT tracking information set optimizer_trace_offset=-5, optimizer_trace_limit=5; multiple statements execute n times n greater than 5 in query: SELECT * from INFORMATION_SCHEMA. Optimizer_trace; You can see that only the last five messages are recorded. The official website gives a suggestedQuery statement: SELECT * from optimizer_trace limit limit offset offset; and we can also use the following statement to monitor how much memory is used: Show variables like ' optimizer_t Race_max_mem_size '; and optimizer_trace table missing_bytes_beyond_max_mem_size This column also records how much memory is missing from the current statement, and we pass show variables Like ' optimizer_trace_max_mem_size '; the memory usage information used to find is often a little bit smaller, real memory usage is often high. The insufficient_privileges of the Optimizer_trace table is used to view the permissions, and when some complex statements are made, but when the user viewing the trace lacks permissions, the column values are often displayed as 1. This means that there is no authority in fact. And remember that if a tracker's event is recorded, it will be automatically recorded to --debug. File. We know that the tracker's words are stored in josn, but the josn format is hard to read, so MySQL has the following parameters: End_markers_in_json: [email protected] [Information_schema]>show variables like ' end_markers_in_json '; +---------------------+-------+| variable_name | Value |+---------------------+-------+| End_markers_in_json | OFF |+---------------------+-------+1 row in Set (0.01 sec) Set the following setting @ @end_markers_in_json =on; It is much easier to open the parameters and then query them (this parameter is 5.7 special): Or we can use explain Format=json to achieve the same result. So the question is, what are the features of the optimizer tracker, which avoids tracking the same statement multiple times so that it does not cause the crazy growth of trace files. Trace Optimizer has the following characteristics: Lazy query: In this case a join statement has n tables, at most will produce only nFactorial Execution plan dynamic range tracking: Only the records in the checked range are output, and the records in the range are rerun only once, but records that have not been run will regenerate a schedule. Subquery: Each word query will only run once these functions we can set ourselves: >show variables like ' optimizer_trace_features '; we can look at the tracking information, May be a little messy: Join-preparation Prepare join-optimization Optimization object Join-execution last execution and call the scope of optimization, cost evaluation, why choose the access path in another, Why did you choose another sorting method that shows the reason. Distance shows everything is going to be optimized far, but we plan to show more information in the future. Because of the chaos, I do not know that you can see no, experience the eyesight of the time appeared. Of course, you can change the format in the previous way to see perhaps more optimistic.we'd better keep track of the records and export them to a file so it looks better.SELECT TRACE from INFORMATION_SCHEMA. Optimizer_trace into outfile '/var/lib/mysql-files/dump.sql '; If you want to see all of the query record tracking information for everyone, Be sure to remember to open the following parameters:--maximum-optimizer-trace-max-mem-size=0--optimizer-trace-max-mem-size=0
MySQL Tracking optimizer small test
