Detailed analysis of explain command in MySQL

Time:2020-11-28

In many cases, we need to know the performance of a SQL statement, and we will check how the query optimizer executes through the explain command.

How to use it

useEXPLAINVery simple, just add theEXPLAINthat will do

explain select s.sid sid, s.name studentName, s.age age, c.name className from student_class sc, student s, class c;

The results are as follows:
如何使用explain命令

Columns in explain

EXPLAINThe columns that are often used in commands are ID

id

ID is a number, identificationSELECTThe row to which it belongs. If there is no subquery or union query in the statement, then there is a unique select, so each row displays 1 in this column. Otherwise, the inner select statements are generally numbered sequentially, corresponding to their position in the statement.

  • Subquery in select column
    SELECT列中子查询

  • Subquery in from clause
    FROM子句中的子查询

  • Union query
    UNION查询

be carefulUNIONThe results are always placed in an anonymous temporary table, and then MySQL reads the results out of the temporary table. The temporary table does not appear in the original SQL, so itsidColumn isNULL

select_type

select_typeColumns show whether the corresponding row is simple or complexSELECTThe details are as follows:

select_type describe
SIMPLE Simple query without any complex subqueries(FROMExcept subqueries in clause)
PRIMARY Complex query
SUBQUERY Complex queries, contained inSELECTIn the listSELECTMarked asSUBQUERY
DERIVED Complex queries, contained inFROMQuery in Clause
UNION Complex query, inUNIONThe second and subsequent in the querySELECTMarked asUNION
UNION RESULT Complex query, which is used fromUNIONOf the anonymous temporary table retrieval resultsSELECTMarked asUNION RESULT
table

tableThe column shows which table the corresponding row is accessing. In general, it’s pretty clear: it’s that table, or an alias for that table. WhenFROMClause has subqueries orUNIONThe table column becomes much more complex.

  • stayFROMWhen there are subqueries in the clause,tableThe column is < derivedN>In whichNIs subqueryid
  • When there isUNIONWhen,UNION RESULTOftableColumn contains a participantUNIONOfidList, such as < union1,2 > and
type

typeRepresents the access type, which means that MySQL decides how to look up rows in a table. From the following listWorst to best

type describe
ALL Scan the whole table, scan the whole table from the beginning to the end, and the efficiency is the worst
index Like full table scanning, only MySQL scans tables in index order instead of rows. The main advantage is that sorting is avoided
range A range scan is a limited index scan that starts at a point in the index and returns rows that match the range.BETWEENINandORAll belong to this category
ref This is an index access (index lookup) that returns all rows that match a single value. Occurs when a non unique index or a non unique prefix of a unique index is used
eq_ref Using this index lookup, MySQL knows that at most one eligible record is returned. Occurs when a primary key or unique index lookup is used
const、system MySQL uses this type when it can optimize a part of the query and convert it to a constant. If you select the primary key of the secondary row by putting the primary key of a row in the where clause, MySQL can convert the query into a constant
NULL This means that MySQL can decompose the query statements in the optimization phase, and there is no need to access tables or indexes at the execution stage. If the minimum value is selected from an index column, it can be done by looking up the index separately, and there is no need to access the table at execution time
possible_keys

possible_keysIndicates which indexes the query may use, based on the columns accessed by the query and the comparison operators used

key

keyShows which index MySQL decides to use to optimize access to the table. If the index does not appear in thepossible_keysColumn, then MySQL may have selected an overlay index, even if notWHEREclause

key_len

key_lenShows the maximum possible length of MySQL in the index field. When there are multiple index fields, the length is the sum of the length of multiple fields. The length of each field is calculated according to the length in the defined table structure

ref

refShows the columns or constants used by previous tables to find values in the index of the key column record.

rows

rowsRepresents the estimated number of rows to read in order to find the desired row. The smaller the value, the better

filtered

filteredShows a pessimistic estimate of the percentage of records in the table that meet a certain condition. If you putrowsMultiply the column by this percentage to see how many rows MySQL estimates it will associate with a table in the query plan.

Extra

ExtraContains additional information that is not suitable for display in other columns. The details are as follows:

Extra describe
Using index This means that MySQL uses overlay indexes to avoid accessing tables
Using where This means that the MySQL server will filter after the storage engine retrieves the rows, not all of themWHEREThere are both conditional queries
Using temporary This means that MySQL uses a temporary table when sorting query results
Using filesort This means that MySQL will sort the results using an external index instead of reading rows from the table in index order
Range checked for each record (index map: N) This value means that there is no good index, and the new index will be reevaluated on each row of the join. I haven’t encountered it in my work for the time being

If there are mistakes in the description of the above content, I hope you can help to point out, exchange and learn together!