MySQL explain execution plan – details

Time:2021-11-3

What’s the use of explain

WhenExplainAndSQLWhen used with statements,MySQLInformation about SQL execution from the optimizer is displayed. in other words,MySQLExplains how it will handle the statement, including how to join the table and what order to join the table.

  • Loading order of tables
  • sqlQuery type
  • Which indexes may be used and which indexes are actually used
  • Reference relationship between tables
  • How many rows in a table are queried by the optimizer
    …..

What information does explain have

ExplainThe execution plan contains the following field information:idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra12 fields.

Explain implementation plan

1、 ID

id:: indicates the order in which the select clause or operation table is executed in the query,idThe higher the value, the higher the priority and the earlier the executionidThere are roughly three situations:

1、ididentical

See three recordsidThey are the same. It can be understood that the three tables are a group with the same priority. The execution order is from top to bottom, and the specific order is determined by the optimizer.

2、idDifferent

If ourSQLIf there are subqueries in, thenidThe serial number of the is incremented,idThe higher the value, the higher the priority, and the earlier it is executed. When three tables are nested in turn, the innermost sub query is foundidMax, execute first.

3. The above two exist at the same time

Put the topSQLModify it slightly, add a sub query, and findidThe above two exist at the same time. identicalidIt is divided into one group, so there are three groups. The same group is executed from top to bottom, and different groupsidThe higher the value, the higher the priority and execute first.

2、 Select_ type

select_type: indicatesselectQuery types are mainly used to distinguish various complex queries, such as:General queryJoint querySubqueryWait.

1、SIMPLE

SIMPLEOr: represents the simplest select query statement, that is, the query does not contain subqueriesunionIntersection, union, difference set and other operations.

2、PRIMARY

PRIMARY: when a query statement contains any complex subparts, the outermost query is marked asPRIMARY

3、SUBQUERY

SUBQUERY: whenselectorwhereThe list contains a subquery marked as:SUBQUERY

4、DERIVED

DERIVED: indicates included infromClause, in ourfromSubqueries contained in the list are marked asderived

5、UNION

UNION: ifunionThere’s another one in the backselectStatement, it is marked asunion; ifunionIncluded infromClause, the outer layerselectWill be marked asderived

6、UNION RESULT

UNION RESULTFrom representative:unionRead data from the temporary table, andtableColumnar<union1,4>Represents the first and fourthselectThe results are analyzedunionOperation.

3、 Table

The table name of the query is not necessarily a real table. If there is an alias, the alias may be displayed, or it may be a temporary table, such as the one aboveDERIVED<union1,4>Wait.

4、 Partitions

The partition information matched during query. For non partition tables, the value isNULL, when the partition table is queried,partitionsDisplays the partitions hit by the partition table.

5、 Type

typeWhat type is used in the query, and it:SQLOptimization is a very important index. The following performance is in order from good to bad:system>const>eq_ref>ref>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL

1、system

system: when the table has only one row of records (system table), the amount of data is small, and disk IO is often not required, which is very fast.

2、const

const: indicates a hit when queryingprimary keyPrimary key oruniqueThe unique index, or the connected part, is a constant(const)Value. This kind of scanning is very efficient, the amount of returned data is small, and the speed is very fast.

3、eq_ref

eq_ref: hit primary key when queryingprimary keyperhapsunique keyIndexes,typenamelyeq_ref

4、ref

ref: different fromeq_refrefIndicates that if a non unique index is used, many qualified rows will be found.

5、ref_or_null

ref_or_nullThis connection type is similar to ref except that:MySQLAdditional search will includeNULLRow of values.

6、index_merge

index_merge: the index merge optimization method is used, and the query uses more than two indexes.

7、unique_subquery

unique_subquery: replace the followingINSubquery, which returns a collection that is not repeated.

value IN (SELECT primary_key FROM single_table WHERE some_expr)
8、index_subquery

index_subquery: different fromunique_subquery, for non unique indexes, can return duplicate values.

value IN (SELECT key_column FROM single_table WHERE some_expr)
9、range

range: use the index to select rows and retrieve only rows within a given range. Simply put, it is to retrieve data in a given range for an indexed field. staywhereUsed in statementsbettween...and<><=inEqual condition querytypeAllrange

Only the fields with indexes set can be searchedtypeactually isrange
10、index

indexIndexAndALLIn fact, they all read the whole table. The difference isindexIs to traverse the index tree to read, andALLIs read from the hard disk.

11、ALL

ALL: the whole table will be traversed to find matching rows, with the worst performance.

6、 Possible_ keys

possible_keys: indicates inMySQLWhich indexes can let us find the desired records in the table. Once there is an index on a field involved in the query, the index will be listed,However, this index is not necessarily the index used in the final query of data。 Please refer to the example above for details.

7、 Key

key: different frompossible_keys, key is the index actually used in the query. If no index is used, it is displayed asNULL。 Please refer to the example above for details.

Whentypebyindex_mergeMultiple indexes may be displayed when.

8、 Key_ len

key_len: indicates the index length (bytes) used in the query. In principle, the shorter the length, the better.

  • For a single column index, the entire index length needs to be included;
  • For multi column indexes, not all columns can be used. You need to calculate the columns actually used in the query.

be careful:key_lenCalculate onlywhereThe index length used in the condition. Even if the index is used in sorting and grouping, it will not be calculatedkey_lenYes.

9、 Ref

ref: common are:constfuncnull, field name.

  • When using constant equivalence queries, theconst
  • When associated query, the corresponding associated table will be displayedAssociated field
  • If query criteria are usedexpressionfunction, or the condition column has an internal implicit conversion, which may be displayed asfunc
  • Other situationsnull

10、 Rows

rows: estimate the number of rows to read in order to find the records we need based on the table statistics and index usage.

This is an assessmentSQLPerformance is an important data,mysqlThe number of lines to be scanned is displayed intuitivelySQLThe performance is good or bad. GenerallyrowsThe smaller the value, the better.

11、 Filtered

filteredThis is a percentage value, the percentage of qualified records in the table. Simply put, this field represents the proportion of the number of records that meet the conditions after the data returned by the storage engine is filtered.

stayMySQL.5.7Version previously wanted to be displayedfilteredNeed to useexplain extendedCommand.MySQL.5.7After, defaultexplainDirect displaypartitionsandfilteredInformation about.

12、 Extra

Extra: information not suitable for display in other columns,ExplainA lot of additional information inExtraField display.

1、Using index

Using index: we are in the correspondingselectThe overlay index is used in the operation. Generally speaking, the query column is overwritten by the index. When the overlay index is used, the query speed will be very fast,SQlIdeal state in optimization.

What is overlay index?

OneSQLThe data we need to query (one or several fields) can be returned through the index instead of the secondary index. After finding the primary key, we can query the whole row of data through the primary key(select *)。

be carefulTo use the overlay index, we:selectOnly the required fields are fetched, and cannot be deletedselect *And the field is indexed.

2、Using where

Using whereNo available index was found during query, and then:whereConditional filtering obtains the required data, but it should be noted that not all bandswhereStatements are displayedUsing where

3、Using temporary

Using temporary: indicates that the query results need to be stored in a temporary table, which is generally used in sorting or grouping queries.

4、Using filesort

Using filesort: indicates a sort operation that cannot be completed with an index, that is:ORDER BYThere is no index in the field of. Generally, such SQL needs to be optimized.

IfORDER BYIf the field has an index, the overlay index will be used, which is much faster than the execution speed.

5、Using join buffer

Using join buffer: when we join table queries, if the table join conditions do not use indexes, we need a join buffer to store intermediate results.

6、Impossible where

Impossible where: it’s not quite right for us to usewhereStatement, resulting in no qualified rows.

7、No tables used

No tables used: none in our query statementFROMClause, orFROM DUALClause.

ExtraThere are a lot of information in the column. I won’t list them one by one here. SeeMySQLOfficial documents:https://dev.mysql.com/doc/ref…

MySQL explain execution plan - details

summary

Key columns:

possible_ Keys: the name of the index that may be available. The index name here is the index nickname specified when creating the index; If the index does not have a nickname, the name of the first column in the index is displayed by default (in this case, it is “firstname”). The meaning of the default index name is often not obvious.

Key: it displays the name of the index actually used by mysql. If it is empty (or null), MySQL does not use the index.

key_ Len: the length of the used part of the index, in bytes

Ref: lists whether to filter through a constant (const) or a field of a table (if it is a join) (through key)
of

Rows: the number of records MySQL thinks it must scan before finding the correct result. Obviously, the ideal number here is 1.

Reprint:www.kancloud.cn/martist/be_new_fri…

This work adoptsCC agreement, reprint must indicate the author and the link to this article