How to analyze SQL execution plan in MySQL through EXPLAIN

.table: table of the output result set (table alias)

.type: indicates how MySQL finds the required row in the table, or the access type. Common access types are as follows, from top to bottom, performance from worst to best:

1. type=ALL, full table scan, MySQL traverses the entire table to find matching rows

Generally, there is no where condition or the where condition does not use the index query statement

EXPLAIN SELECT * FROM customer WHERE active=0; 

2. type=index, index full scan, MySQL traverses the entire index to query matching rows and does not scan the table

Generally, the query fields are indexed.

EXPLAIN SELECT store_id FROM customer;

3. type=range, index range scan, commonly used for <, <=, >, >=, between and other operations

EXPLAIN SELECT * FROM customer WHERE customer_id>=10 AND customer_id<=20; 

Note that the fields being compared in this case need to be indexed. If there is no index, MySQL will perform a full table scan. For example, in the following case, the create_date field is not indexed:

EXPLAIN SELECT * FROM customer WHERE create_date>='2006-02-13'; 

4. type=ref, use a non-unique index or a prefix scan of a unique index to return rows that match a single value

The store_id field has a normal index (non-unique index)

EXPLAIN SELECT * FROM customer WHERE store_id=10; 

Ref types also often appear in join operations:

Query the customer and payment tables, with the associated fields customer.customer_id (primary key) and payment.customer_id (non-unique index). When querying table associations, one table must be fully scanned. This table must be the table with the least number of record rows among the tables. Then, the matching rows in other associated tables are found through non-unique indexes to achieve the minimum number of scanned rows when the tables are associated.

Because the customer table has the least number of rows in the customer and payment tables, the customer table is fully scanned, and the payment table searches for matching rows through a non-unique index.

EXPLAIN SELECT * FROM customer customer INNER JOIN payment payment ON customer.customer_id = payment.customer_id; 

5. type=eq_ref, similar to ref, except that the index used is a unique index. For each index key value, there is only one matching record in the table.

eq_ref usually appears when multiple tables are joined and the primary key or unique index is used as the join condition.

The association query of the film and film_text tables is basically the same as that mentioned in the previous item, except that the association condition is changed from a non-unique index to a primary key.

EXPLAIN SELECT * FROM film film INNER JOIN film_text film_text ON film.film_id = film_text.film_id; 

6. type=const/system: there is at most one matching row in a single table, and the query is very fast, so the values ​​of 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 primary key or unique index

Query based on the primary key:

EXPLAIN SELECT * FROM customer WHERE customer_id =10; 

Query based on unique index:

EXPLAIN SELECT * FROM customer WHERE email = '[email protected]'; 

7. type=NULL, MySQL can get the result directly without accessing the table or index.

.possible_keys: indicates the index that may be used for the query

.key: The actual index used

.key_len: The length of the index field used

.ref: Which column or constant to use together with key to select rows from the table.

.rows: The number of scanned rows

.filtered: The percentage of the number of records that satisfy the query after the data returned by the storage engine is filtered at the server level.

.Extra: Description and description of the execution, including additional information that is not suitable for display in other columns but is very important for the execution plan

The three most important ones are:

What is ICP?

MySQL 5.6 introduced the Index Condition Pushdown (ICP) feature to further optimize queries. Pushdown means that operations are decentralized, and in some cases, conditional filtering operations are decentralized 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 composite index idx_rental_date to filter out the records that meet the condition rental_date='2005-05-25' , then returns to the table to obtain the records based on the composite index idx_rental_date, and finally filters out the final query results based on the conditions customer_id>=300 AND customer_id<=400 (completed at the service layer).

After version 5.6:

MySQL uses ICP to further optimize queries. During retrieval, the conditions customer_id>=300 AND customer_id<=400 are also pushed to the storage engine layer to complete filtering, which can reduce unnecessary IO access. If Extra is Using index condition , it means that ICP optimization is used.

refer to

MySQL in Simple Terms

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.