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
EXPLAINVery simple, just add the
EXPLAINthat 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:
Columns in explain
EXPLAINThe columns that are often used in commands are ID
ID is a number, identification
SELECTThe 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
Subquery in from clause
UNIONThe 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 its
select_typeColumns show whether the corresponding row is simple or complex
SELECTThe details are as follows:
||Simple query without any complex subqueries（
||Complex queries, contained in
||Complex queries, contained in
||Complex query, in
||Complex query, which is used from
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. When
FROMClause has subqueries or
UNIONThe table column becomes much more complex.
FROMWhen there are subqueries in the clause,
tableThe column is < derivedN>In whichNIs subquery
- When there is
tableColumn contains a participant
idList, such as < union1,2 > and
typeRepresents the access type, which means that MySQL decides how to look up rows in a table. From the following listWorst to best：
||Scan the whole table, scan the whole table from the beginning to the end, and the efficiency is the worst|
||Like full table scanning, only MySQL scans tables in index order instead of rows. The main advantage is that sorting is avoided|
||A range scan is a limited index scan that starts at a point in the index and returns rows that match the range.
||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|
||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|
||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|
||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_keysIndicates which indexes the query may use, based on the columns accessed by the query and the comparison operators used
keyShows which index MySQL decides to use to optimize access to the table. If the index does not appear in the
possible_keysColumn, then MySQL may have selected an overlay index, even if not
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
refShows the columns or constants used by previous tables to find values in the index of the key column record.
rowsRepresents the estimated number of rows to read in order to find the desired row. The smaller the value, the better
filteredShows a pessimistic estimate of the percentage of records in the table that meet a certain condition. If you put
rowsMultiply the column by this percentage to see how many rows MySQL estimates it will associate with a table in the query plan.
ExtraContains additional information that is not suitable for display in other columns. The details are as follows:
||This means that MySQL uses overlay indexes to avoid accessing tables|
||This means that the MySQL server will filter after the storage engine retrieves the rows, not all of them
||This means that MySQL uses a temporary table when sorting query results|
||This means that MySQL will sort the results using an external index instead of reading rows from the table in index order|
||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!