In MySQL, we can use the EXPLAIN command to obtain information about how MySQL executes SELECT statements, including how tables are joined during SELECT statement execution and the order in which they are joined.
The following describes each column of the EXPLAIN command result:
-
Select_type: indicates the SELECT type. Common values are as follows:
type instructions SIMPLE Simple tables, without table joins or subqueries PRIMARY The primary query, that is, the outer query UNION The second or subsequent query statement in UNION SUBQUERY The first one in the subquery -
Table: the table that outputs the result set (table alias)
-
Type: indicates how MySQL finds the desired rows in the table, or the access type. The common access types are as follows, from top to bottom, with poor to best performance:
ALL A full table scan index Index full scan range Index range scan ref Non-unique index scan eq_ref Unique index scan const,system A single table has at most one matching row NULL Do not scan tables or indexes -
MySQL > select * from table where type=ALL
A query that does not have a WHERE condition or where condition does not use an index
EXPLAIN SELECT * FROM customer WHERE active=0; -
Type =index, index full scan, MySQL traverses the entire index to search for matching rows, does not scan the table
Generally is the query of the field has an index query statement
EXPLAIN SELECT store_id FROM customer; -
Type =range: indicates index range scanning. It is used for <, <=, >, >=, and between operations
EXPLAIN SELECT * FROM customer WHERE customer_id>=10 AND customer_id<=20;MySQL > select * from table_name where create_date = 1; MySQL > select * from table_name where create_date = 1;
EXPLAIN SELECT * FROM customer WHERE create_date>='2006-02-13' ; -
Type =ref, using a prefix scan with a non-unique index or unique index, returns a row of records that matches a single value
The store_id field has a common index (not unique index)
EXPLAIN SELECT * FROM customer WHERE store_id=10;The ref type is also commonly used in join operations:
Customer and Payment table associated query with the associated fields Customer_id (primary key) and Payment. customer_id (non-unique index). In table association query, one table must be scanned for the entire table. The table must have the least number of rows among the tables. Then, non-unique indexes are used to search for matching rows in other associated tables to minimize the number of rows scanned during table association.
The customer table has the least number of rows in the customer and Payment tables, so the Customer table performs a full table scan, and the Payment table searches for matching rows through non-unique indexes.
EXPLAIN SELECT * FROM customer customer INNER JOIN payment payment ON customer.customer_id = payment.customer_id; -
Type =eq_ref, similar to ref, except that the index used is unique. For each index key value, only one record in the table matches
Eq_ref usually occurs when multiple tables are joined using the primary key or unique index as the association condition.
Film, FILM_TEXT, filM_text, film, filM_text, film, film_text, film, filM_text, film, filM_text, film, filM_text
EXPLAIN SELECT * FROM film film INNER JOIN film_text film_text ON film.film_id = film_text.film_id; -
Type =const/system. There is at most one matching row in a single table, which can be queried very quickly, so the values of the other columns of this matching row can be treated as constants by the optimizer in the current query
Const /system appears in queries based on the primary key or unique index
Select * from primary key;
EXPLAIN SELECT * FROM customer WHERE customer_id =10;Select * from unique index;
EXPLAIN SELECT * FROM customer WHERE email ='[email protected]';
-
Type =NULL, MySQL does not need to access the table or index, directly to obtain the result
-
-
Possible_keys: indicates the possible index in the query
-
Key: indicates the actual index
-
Key_len: Uses the length of the index field
-
Ref: Which column or constant is used with the key to select rows from the table.
-
“Rows” : indicates the number of scanned rows
-
Filtered: Percentage of the number of records that are left to satisfy the query after the data returned by the storage engine is filtered at the server layer
-
Extra: A description and description of the performance, containing additional information that is important to the execution plan but not suitable for display in other columns
The most important ones are as follows:
Using Index Indicates that the index overwrites the table Using Where Indicates that a table-back query is performed Using Index Condition ICP optimization is performed Using Flesort Indicates that MySQL requires additional sorting operations and cannot use index order to achieve sorting effect
What is ICP?
MySQL5.6 further optimizes queries by introducing **Index Condition Pushdown (ICP) **. Pushdown indicates that the operation is pushed down, and in some cases the conditional filtering operation is pushed down to the storage engine.
EXPLAIN SELECT * FROM rental WHERE rental_date='2005-05-25' AND customer_id>=300 AND customer_id<=400;
Prior to version 5.6:
The optimizer first uses the compound index IDx_rental_date to filter out the records that match rental_date=’2005-05-25′ and then returns to the table to fetch the records based on the compound index IDx_rental_date. The final query result is filtered out based on conditions customer_id>=300 AND customer_id<=400 (at the service layer).
After 5.6:
Customer_id >=300 AND customer_id<=400 are filtered to the storage engine layer to reduce unnecessary I/O access. Extra Using index condition means ICP optimization is used.
reference
MySQL in Simple Form
—–END—–
If you like this article, please scan the picture below and watch more exciting content
