EXPLAIN Statement provides information about MySQL Information about how to execute a statement .EXPLAIN And SELECT,DELETE,INSERT,REPLACE and UPDATE Statements are used together .
EXPLAIN For SELECT Each table used in the statement returns a row of information . It follows MySQL The order in which statements are read as they are processed lists the tables in the output . MySQL Use the nested loop connection method to resolve all connections . This means MySQL Read a row from the first table , And then in the second table , Find the third row in the table that matches , And so on . After processing all the tables ,MySQL Output selected columns , And trace back through the table list , Until you find a watch , There are more matching lines . Read the next row from this table , Then go on to the next table .
Let's move on to the next few key columns ：
- type ： Connection type
- possible_keys ： Optional index
- key ： The index used in actual execution
- ref ：ref Columns show which columns or constants are compared to the previous key Column to select rows from the table
- rows ：rows Column means MySQL The number of rows that must be checked to execute the query
Connection type , From the best to the worst , In turn, is ：
There is only one row in the table . This is const join A special case of type .
There is at most one table that matches , Read at the beginning of the query . Because there's only one line , So the values of the columns in this row can be treated as constants by the rest of the optimizer .const The watch is very fast , Because they're only read once .
When you use PRIMARY KEY or UNIQUE When all parts of the index are compared with constant values , Will use const.
for example , The table below tbl_name Can be regarded as const surface ：
SELECT * FROM tbl_name WHERE primary_key=1;SELECT * FROM tbl_name WHERE primary_key_part1=1 AND primary_key_part2=2;
For each row in the previous table , Read a row from this table . except system and const Type , This is the best possible join type . When all parts of an index are joined and the index is PRIMARY KEY or UNIQUE NOT NULL When indexing , Use it .
eq_ref It can be used to use = The index column that the operator compares . The comparison value can be a constant , You can also use the expression of the column in the table read before this table .
for example , In the following example MySQL have access to eq_ref Connect to handle ref_table：
SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;
For each combination of rows in the previous table , All rows with matching index values will be read from the table . If the join only uses key The leftmost prefix of , Or if key No PRIMARY KEY or UNIQUE Indexes （ In other words , If the join cannot be based on key Value to select a single row ）, Use ref. If used key Just a few lines , Then this is a good join type .
ref Can be used to use = or <=> Operator to compare the index column .
for example , In the following example ,MySQL It can be used ref Connect to handle ref_table：
SELECT * FROM ref_table WHERE key_column=expr;SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;
Use FULLTEXT Index execution line
This connection type is similar to ref, however MySQL It will search extra to include NULL Row of values . This join type optimization is most commonly used to resolve subqueries .
for example , In the following example ,MYSQL have access to ref_or_null To deal with ref_table：
SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;
This connection type indicates the use of index merge optimization . In this case , Output line of key Column contains the index list used , and key_len Contains the longest index used key Partial list .
This type will eq_ref Replace with some of the following forms IN Subquery ：
value IN (SELECT primary_key FROM single_table WHERE some_expr)
And unique_subquery Similar , It replaces IN Subquery , But it is applicable to the non unique index in the following form of subquery ：
value IN (SELECT key_column FROM single_table WHERE some_expr)
Retrieve only rows within a given range , And use the index to select rows . Output line of key Which column is used to indicate .key_len Contains the longest used key part . For this type ,ref Listed as NULL.
Use =,<>,>,> =,<,<=,IS NULL,<=>,BETWEEN,LIKE or IN() The operator will key When comparing columns with constants , have access to range：
SELECT * FROM tbl_name WHERE key_column = 10;SELECT * FROM tbl_name WHERE key_column BETWEEN 10 and 20;SELECT * FROM tbl_name WHERE key_column IN (10,20,30);SELECT * FROM tbl_name WHERE key_part1 = 10 AND key_part2 IN (10,20,30);
index Connection type and all It's the same , The difference lies in index Index tree during connection type scanning . Usually , Only in the following two situations ：
- If the index is the overlay index of the query , And can be used to satisfy all the data required in the table , Scan only the index tree . In this case ,“Extra” Columns show “Using index”. Index only scanning is usually better than ALL fast , Because the size of the index is usually smaller than the table data .
- Perform a full table scan using read data from the index to query columns in index order .“Uses index” Not in Extra In the column .
Perform a full table scan for each combination of rows in the previous table . If the table is not marked as const The first table of , It's usually not good , And in all other cases, it's usually very bad . Usually , It can be avoided by adding an index ALL, These indexes allow rows to be retrieved from the table based on constant or column values from earlier tables .
About Extra Column output , Just a few common ones ：
MySQL An extra operation has to be done , To find out how to retrieve rows in sort order . Sorting is done by traversing all the rows according to the join type and storing them with WHERE Order of all rows matched by clause key And indicators pointing to the line . And then key Sort , And search the rows in sort order .
Retrieve column information from a table using only the information in the index tree , Instead of performing additional queries to read the actual rows . When a query uses only columns that belong to a single index , You can use this policy .
To parse the query ,MySQL You need to create a temporary table to store the results . Usually , If the query contains GROUP BY and ORDER BY Clause , This will happen .
WHERE Clause is used to restrict which rows match the next table or are passed to the client . Unless you're going to get or check all the rows from the table , Otherwise, if the extra value is not used where, And the table connection type is all or index, Then there may be an error in the query .
In some cases ,MySQL An index may be used to satisfy ORDER BY Clause , So as to avoid executing filesort The additional sort involved in the operation .
Suppose it's in (key_part1, key_part2) There's an index on , The following query can be parsed using the index ORDER BY part . Whether the optimizer really does this , Depends on if you have to read outside the index , Whether reading an index is more efficient than a table scan .
SELECT * FROM t1 ORDER BY key_part1, key_part2;
The above sentence , Query using SELECT *, This may be more than key_part1 and key_part2 More columns . In this case , It may be more expensive to sort a query with an index that contains the entire table and scan the results . If so , The optimizer is less likely to use indexes . If SELECT * Select only index columns , Use indexes and avoid sorting .
In the following query ,key_part1 Is a constant , So all rows accessed through the index press key_part2 In order , And if WHERE Clause is selective enough to make index range scanning cheaper than table scanning , It's in （key_part1,key_part2） The index on can avoid sorting ：
SELECT * FROM t1 WHERE key_part1 = constant ORDER BY key_part2;