The content of the TRACE column is a super large JSON data, directly expand and then analyze one by one, it is estimated that everyone's brains hurt. We don't care about the value of this field for the time being.Īmong them, the most information and the most important is the second column TRACE, which is also the focus of our subsequent analysis. Only in some special cases will it be `1`. `INSUFFICIENT_PRIVILEGES`: Indicates whether you have no permission to view the optimization process. This field shows the number of ignored text bytes. `MISSING_BYTES_BEYOND_MAX_MEM_SIZE`: Because the optimization process may output a lot, if it exceeds a certain limit, the extra text will not be displayed. `TRACE`: JSON format text representing the optimization process. `QUERY`: represents our query statement. The OPTIMIZER_TRACE table has 4 columns, as shown below: # When you stop viewing the optimization process of the statement, turn off the optimizer trace function SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE Open the optimizer trace function (it is off by default): You can use the following method to turn on this function, and then execute the SQL statement you need to analyze, and then find the relevant information about the SQL statement execution optimization from the OPTIMIZER_TRACE of INFORMATION_SCHEMA.
Mysql optimizer trace how to#
let's take a look at how to use Optimizer Trace. This is also one of the important reasons why the MySQL optimizer sometimes makes incorrect optimizations.ġ. Since the cost of labeling is based on statistical data, there will always be samples that cannot accurately reflect the overall situation. Therefore, the optimizer can calculate the total cost for each execution plan based on all operations, and then select the least costly execution plan from among many execution plans for final execution. The base unit or minimum of these costs is the cost of reading random data pages from the disk, and the costs of other operations are multiples of it. The optimizer will mark a cost for each operation. The role of the optimizer is shown in the figure below.
Mysql optimizer trace full#
It will decide whether to use a full table scan or an index to scan, and it will also determine the order of table joins. The optimizer mainly makes judgments based on the statistical data obtained from the storage engine and the metadata information in the data dictionary. The cost of each execution plan roughly reflects the resources required by the plan query, the main factor is the number of rows that will be accessed when calculating the query. MySQL will use a cost-based optimizer to select the execution plan. This article will explain in detail all the relevant information displayed by Optimizer Trace, and will supplement some specific use cases.īefore understanding Optimizer Trace, let's learn how MySQL chooses many execution plans. Although EXPLAIN shows the selected plan, Optimizer Trace can show why the plan was chosen: you will be able to see alternative plans, estimated costs, and decisions made.
If you want to have a deeper understanding of why a query plan was chosen, optimizer tracking is very useful. To this end, MySQL provides the Optimizer Trace function, which allows us to have a more detailed understanding of all the analysis, optimization and selection processes performed by the SQL statement.
It cannot show why some other execution plans are not selected, such as indicating that there is an index, but why the index is not used in the query. But it can only show the execution plan of the SQL statement. In the previous article (), we explained the detailed use of Explain command.
0 kommentar(er)
