mysql--analysis of SQL execution plans through explain

In MySQL, we can obtain information about how MySQL executes the SELECT statement through the EXPLAIN command, including the order in which the table joins and joins during the SELECT statement execution.

Each column of the results of the EXPLAIN command is described separately below:

• Select_type: Represents the type of select, and the common values are:

  type	Description
  Simple	Simple table, not using table joins or subqueries
  PRIMARY	Main query, which is the outer query
  UNION	The second or subsequent query statement in a union
  Subquery	The first in a subquery

• table: Tables for output result sets (table aliases)

• type: Indicates how MySQL finds the desired row in the table, or is called an access type. Common types of access are as follows, from top to bottom, performance from poor to best:

  index range scan TD style= "Text-align:center;" >

  all	full table scan
  index	index Full scan
  range
  ref	non-unique index scan
  Eq_ref	unique index scan
  Const,system	single table with up to one matching row
  NULL	do not scan tables or indexes

  2. Type=all, full table scan, MySQL traversal full table to find matching rows

     Typically there is no where condition or a where condition does not use the index of the query statement

     EXPLAIN SELECT * FROM customer WHERE active=0;

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

     Query statements that are typically indexed for fields that are queried

     EXPLAIN SELECT store_id FROM customer;

  6. Type=range, index range scanning, commonly used in <, <=, >, >=, between and other operations

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

     Note that in this case the field being compared is required to be indexed, and if there is no index, MySQL will perform a full table scan, as in the following case, the Create_date field is not indexed:

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

  8. Type=ref, which uses a non-unique index or a unique index for a prefix scan, returns a record line that matches a single value

     store_id field exists normal index (non-unique index)

     EXPLAIN SELECT * from Customer WHERE store_id=10;

     ref types also often appear in join operations:

     customer , Payment Table Association query, The associated field customer.customer_id (primary key), payment.customer_id (not a unique index). Table associated query must have a table for a full table scan, this table must be a few tables record the lowest number of rows of the table, and then through a non-unique index to look for matching rows in other related tables, so as to reach the table associated with the minimum number of rows scanned.

     Because the Customer table has the fewest number of record rows in the customer , payment two tables, The Customer table is scanned for a full table, and the Payment table looks for matching rows through non-unique indexes.

     EXPLAIN SELECT * from customer customer INNER JOIN Payment payment on customer.customer_id = Payment.customer _id;

  10. Type=eq_ref, similar to ref, differs in that the index used is a unique index, and only one record in the table matches for each index key value

      Eq_ref generally occurs when a multi-table connection uses primary key or unique index as the association condition.

      film, Film_text Table Association queries are basically consistent with the previous one, 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;

  12. Type=const/system, there is a maximum of one matching row in a single table, and the query is very fast, so the value of the other columns of the matching row can be handled by the optimizer as a constant in the current query.

      Const/system appears in queries based on primary key primary key or unique index

      Query based on PRIMARY key primary key:

      EXPLAIN SELECT * FROM customer WHERE customer_id =10;

      A query based on unique index of a uniquely indexed:

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

      ?

  14. Type=null,mysql can get results directly without accessing tables or indexes

• Possible_keys: Indicates the index that the query might use

• key: The actual index used

• Key_len: Using the length of an indexed field

• ref: Use which column or constant to select rows from the table together with the key.

• rows: Number of scanned rows

• Filtered: The 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 level

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

  There are three main types:

  Using Index	represents an index overlay and does not return a table query
  Using Where	Indicates that a back table query was made
  Using Index Condition	Represents an ICP optimization
  Using Flesort	Indicates that MySQL requires an extra sort operation and cannot be sorted by index order

What is an ICP?

MySQL5.6 introduces the features of Index Condition pushdown (ICP) to further refine the query. Pushdown means that the operation is decentralized, and in some cases conditional filtering operations are delegated to the storage engine.

EXPLAIN SELECT * FROM rental WHERE rental_date=‘2005-05-25‘ AND customer_id>=300 AND customer_id<=400;

Before the 5.6 release:

The optimizer first uses the composite index idx_rental_date to filter out the qualifying rental_date=‘2005-05-25‘ records, and then gets the records based on the composite index idx_rental_date back to the table, ultimately customer_id>=300 AND customer_id<=400 filtering out the final query results (done at the service level) based on the criteria.

After the 5.6 release:

MySQL uses the ICP to further refine the query and, when retrieving, pushes the condition customer_id>=300 AND customer_id<=400 to the storage engine layer to complete the filtering, which reduces unnecessary IO access. Extra Using index condition uses ICP optimization as a representation.

Reference

"In layman's MySQL"

The thing about the code.
Public Number:

Source: http://songwenjie.cnblogs.com/
Statement: This article for Bo Master Learning sentiment Summary, the level is limited, if improper, welcome correction. If you think it's good, just click on the "recommend" button below, thanks for your support. Reprint and quote please specify the source.

mysql--analysis of SQL execution plans through explain