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

In this article, we will take a look at the usage and result analysis of explain in MySQL usage. There is a certain reference value, friends in need can refer to, hope to help you.

1. Brief introduction to EXPLAIN

Use the EXPLAIN keyword to simulate the optimizer's execution of SQL queries to know how MySQL handles your SQL statements. Analyze the performance bottlenecks of your query or table structure.

➤ through EXPLAIN, we can analyze the following results:

Table read order data read operation type of operation which indexes can be used which indexes are actually used references between tables how many rows in each table are queried by the optimizer

➤ is used as follows:

EXPLAIN + SQL statement

EXPLAIN SELECT * FROM T1

Information contained in the execution plan

two。 The meaning of each field of the execution plan is 2.1 id

The sequence number of a select query that contains a set of numbers indicating the order in which the select clause or action table is executed in the query

There are 3 cases in the result of id.

The id is the same and the execution order is from top to bottom

[summary] the order of loading tables is shown in the table column in the figure above: T1 T3 T2

Id is different. If it is a subquery, the sequence number of id will be incremented. The higher the id value, the higher the priority, the first to be executed.

Id is the same and different, but it exists at the same time.

As shown in the figure above, when id is 1, table shows, in this case, the derived table that points to the table with id 2, that is, the T3 table. 2.2 select_type

The common and commonly used values are as follows:

Respectively used to represent the types of queries, mainly used to distinguish between ordinary queries, federated queries, subqueries and other complex queries.

SIMPLE simple select query, which does not include subqueries or UNION

If the PRIMARY query contains any complex subsections, the outermost query is marked as PRIMARY

SUBQUERY contains subqueries in the SELECT or WHERE list

The subqueries that DERIVED contains in the FROM list are marked as DERIVED (derivation), and MySQL recursively executes these subqueries to put the results in a temporary table

UNION if the second SELECT appears after UNION, it is marked as UNION: if UNION is included in the subquery of the FROM clause, the outer SELECT is marked as: DERIVED

UNION RESULT gets the SELECT of the result from the UNION table

2.3 table

Refers to the currently executed table

2.4 type

Type shows which types are used by the query, and type contains several types as shown in the following figure:

From the best to the worst:

System > const > eq_ref > ref > range > index > all

In general, you need to ensure that the query reaches at least the range level, preferably ref.

The system table has only one row of records (equal to the system table), which is a special column of const type, which usually does not appear. This can also be ignored. Const means that it can be found once through the index, and const is used to compare primary key or unique indexes. Because it matches only one row of data, it's fast. If the primary key is placed in the where list, MySQL can convert the query to a constant.

First of all, a subquery is carried out to get a D1 temporary table of the result, and the subquery condition is that id = 1 is a constant, so type with const,id of 1 is equivalent to querying only one record, so type is system. Eq_ref unique index scan, for each index key, only one record in the table matches it. Common in primary key or unique index scan ref non-unique index scan, returns all rows that match a single value, which is essentially an index access, it returns all rows that match a single value, however, it may find multiple rows that meet the criteria, so it should be a mixture of lookup and scanning.

Range only retrieves a given range of rows, using an index to select rows, and the key column shows which index is used, which usually appears in your where statement, between,

, in, etc., this range scan index is better than a full table scan, because it only needs to start at one point of the index and end at another point, without scanning all the indexes.

Index Full Index Scan,Index differs from All in that the index type only traverses the index tree. This is usually faster than ALL because index files are usually smaller than data files. (that is, although both all and Index read the whole table, index is read from the index and all is read from the hard disk.)

Id is the primary key, so there is a primary key index all Full Table Scan will traverse the entire table to find matching rows

2.5 possible_keys and key

Possible_keys displays one or more indexes that may be applied to this table. If an index exists on the fields involved in the query, the index is listed, but not necessarily used by the query.

Key

The index actually used, if NULL, is not used. (possible reasons include lack of index establishment or index failure)

If an overlay index is used in the query (the field to be queried after select is exactly the same as the index field created), the index appears only in the key list

2.6 key_len

Represents the number of bytes used in the index, which can be used to calculate the length of the index used in the query, and the shorter the length, the better without losing accuracy. The value displayed by key_len is the maximum possible length of the index field, not the actual used length, that is, the key_len is calculated based on the table definition, not retrieved within the table.

2.7 ref

The column that shows the index is used, preferably a constant, if possible. Which columns or constants are used to find values on indexed columns.

2.8 rows

Based on the table statistics and index selection, roughly estimate the number of rows that need to be read to find the required records, that is, the less you use, the better

2.9 Extra

Contains additional information that is not explicit but important in other columns

2.9.1 Using filesort (narrowly missed)

Indicates that mysql sorts the data using an external index instead of reading it in the order in which it is indexed within the table. The sort operation that cannot be done with indexes in MySQL is called "file sorting".

2.9.2 Using temporary (ten dead without life)

Using temporary tables to save intermediate results, MySQL uses temporary tables when sorting query results. It is common in sorting order by and grouping query group by.

2.9.3 Using index (get rich)

Indicates that the overlay index (Covering Index) is used in the corresponding select operation to avoid accessing the data rows of the table, which is efficient. If using where appears at the same time, the index is used to perform the lookup of index key values; if there is no using where at the same time, the index is used to read data rather than perform lookup actions.

2.9.4 Using where

Indicates the use of where filtering

2.9.5 Using join buffer

Indicates that the connection cache is used, for example, when querying, the number of multi-table join is very high, so increase the join buffer of the buffer in the configuration file.

2.9.6 impossible where

The value of the where clause is always false and cannot be used to get any tuples

SELECT * FROM t_user WHERE id ='1' and id ='2 '2.9.7 select tables optimized away

In the case of no GROUPBY clause, optimizing the MIN/MAX operation based on the index or optimizing the COUNT (*) operation for the MyISAM storage engine does not have to wait until the execution phase, and the optimization is completed at the stage of query execution plan generation.

2.9.8 distinct

Optimize the distinct operation to stop looking for the same value after finding the first matching tuple

3. Case analysis

The execution order 1:select_type is UNION, indicating that the fourth select is the second select in UNION, and the first execution [select name,id from T2] execution order 2:id is 3, which is part of the third select in the whole query. Because the query is contained in from, the subquery select_type in the sequential 3:select list executed for DERIVED [select id,name from T1 where other_column=''] is subquery, and the sequential 4:id column 1 is executed for the second select [select id from T3] in the whole query. The primary that represents the first select,select_type column in UNION indicates that the query is an outer query, and the table column is marked to indicate that the query result comes from a derived table. Where 3 in derived3 represents that the query is derived from the third select query, select with id 3. [select d1.name... ]

Execution order 5: represents the stage of reading rows from the temporary table of UNION, and the

Indicates that UNION operations are performed with the results of the first and fourth select. [two result union operations]

The above is a brief introduction to the usage and result analysis of explain in the usage of MySQL. Of course, the differences in the detailed use of the above have to be understood by everyone. If you want to know more, welcome to follow the industry information channel!

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.