Shulou(Shulou.com)06/02 Report--

How to use the artifact Optimizer Trace in the execution of MySQL statements? in view of this problem, this article introduces the corresponding analysis and solutions in detail, hoping to help more partners who want to solve this problem to find a more simple and easy way.

It is obvious that there is an index, but why the index is not used in the query. To this end, MySQL provides the Optimizer Trace function, which allows us to get a more detailed understanding of all the analysis, optimization, and selection processes executed by SQL statements.

Optimizer tracing is useful if you want to learn more about why you chose a query plan. Although EXPLAIN displays the selected plan, Optimizer Trace can show why the plan was selected: you will be able to see alternative plans, estimated costs, and decisions made. This article will explain all the relevant information presented by Optimizer Trace in detail, supplemented by some specific use cases.

Cost-based execution plan

Before we look at Optimizer Trace, let's take a look at how MySQL chooses many execution plans.

MySQL uses a cost-based optimizer to select execution plans. The cost of each execution plan roughly reflects the resources required for the plan query, and the main factor is the number of rows to be accessed when calculating the query. The optimizer mainly makes a judgment based on the statistical data obtained from the storage engine and the metadata information in the data dictionary. It determines whether to use a full table scan or an index scan, and it also determines the order of the table join. The role of the optimizer is shown in the following figure.

The optimizer marks each operation with a cost, the base unit or minimum of which is the cost of reading random data pages from disk, and the cost of other operations is multiples of it. So the optimizer can calculate the total cost for each execution plan according to all the operations of each execution plan, and then select the one with the lowest cost from many execution plans for final execution.

Since the marking cost is based on statistics, there will always be samples that can not correctly reflect the overall situation, which is one of the important reasons why the MySQL optimizer sometimes makes wrong optimizations.

Basic use of Optimizer Trace

First, let's take a look at how to use Optimizer Trace. By default, this function is turned off, and you can turn it on in the following way, then execute the SQL statement you need to analyze, and then find the relevant information about the optimization of the SQL statement in the OPTIMIZER_TRACE of INFORMATION_SCHEMA.

# 1. Turn on the optimizer trace function (it is off by default): SET optimizer_trace= "enabled=on"; SELECT.; # enter your own query statement SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;# when you stop viewing the optimization process of the statement, turn off the optimizer trace function to turn off SET optimizer_trace= "enabled=off"

The OPTIMIZER_TRACE table has four columns, as follows:

QUERY: represents our query statement.

TRACE: text in JSON format that represents the optimization process.

MISSING_BYTES_BEYOND_MAX_MEM_SIZE: because the optimization process may output a lot, if a certain limit is exceeded, the extra text will not be displayed. This field shows the number of bytes of text that have been ignored.

INSUFFICIENT_PRIVILEGES: indicates whether you do not have permission to view the optimization process. The default value is 0, and it will be 1 only in some special cases. We do not care about the value of this field for the time being.

Among them, the most important and most important information is the second column TRACE, which is also the focus of our follow-up analysis.

Basic format of TRACE column

The content of the TRACE column is a super-large JSON data, which can be expanded directly and then parsed one by one, you can see that everyone has a headache.

So, let's first take a look at the skeleton of this big JSON. It has three pieces of content, and it also represents the three stages of SQL statement processing, namely, the preparation phase, the optimization phase and the execution phase.

Next, we introduce a case in detail, in which we introduce the specific fields and meanings involved.

Why does the query scan the whole table instead of the index?

First of all, there are many cases in which the SQL statement query does not use an index, and we only discuss here because the cost-based optimizer believes that the cost of the full table query execution plan is lower than that of walking the index execution plan.

As shown in the following scenario, it is clear that there is an index on the val column and there are some differences in the existing values of val, so why not use the index for query?

We use Optimizer Trace to find the range_analysis-related data in its join_optimization as above, which shows how the index is selected during the where clause scope query

As you can see from the figure above, MySQL compares the cost of a full table scan with that of using val as an index, and finally finds that although a full table scan requires more rows to be scanned, the cost is lower. Therefore, the implementation scheme of full table scan is chosen.

Why is that? Obviously, 4 rows can be saved by using val index. This actually involves the principle of using indexes to query data rows in InnoDB.

When the Innodb engine cannot use index override when querying records (that is, more data and index values need to be queried, for example, in this example, I want to check name, and the index column is val), you need to go back to the table to obtain the required fields of the record, that is, find out the primary key through the index, then check the data rows, and retrieve the corresponding columns, which is bound to cost more.

Therefore, when the amount of data in the return table is relatively large, it often occurs that Mysql estimates the query cost of the return table operation so that the index is not used.

Generally speaking, when the SQL statement queries more than 1/5 of the records in the table and cannot use the overlay index, the cost of returning the index to the table is too high and the full table scan is chosen. And this ratio increases slightly with the increase of the byte size of a single row of records.

The relevant data in range_analysis can also be used to analyze how multiple index columns are used in where clauses and how to select the index to be used in execution.

This is the answer to the question about how to use the artifact Optimizer Trace in the execution of the MySQL statement. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.