Shulou(Shulou.com)06/01 Report--

Preface

In MySQL, we can get information about how MySQL executes SELECT statements through the EXPLAIN command, including the order in which tables are joined and joined during the execution of SELECT statements.

Each column of the result of the EXPLAIN command is described below:

.select _ type: indicates the type of SELECT. Common values are:

Type description SIMPLE simple table without table join or subquery PRIMARY main query, that is, the first of the second or subsequent query statement subquery in the outer query UNIONUNION

.table: the table (table alias) that outputs the result set

.type: indicates how MySQL finds the required rows in the table, or access type. The common types of access are as follows: from top to bottom, the performance changes from poor to best:

ALL full table scan index index full scan range index range scan ref non-unique index scan eq_ref unique index scan const,system single table has at most one matching row NULL does not scan tables or indexes

1. Type=ALL, full table scan, MySQL traverses the whole table to find matching rows

Generally speaking, there are no where conditions or where conditions that do not use indexes.

EXPLAIN SELECT * FROM customer WHERE active=0

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

Generally speaking, the query statement in which all the fields of the query have an index.

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='2006-02-13'

4. Type=ref, scanning with a prefix of a non-unique index or a unique index to return rows of records that match a single value

Store_id field has a normal index (non-unique index)

EXPLAIN SELECT * FROM customer WHERE store_id=10

The ref type also often appears in join operations:

Customer, payment table associated query, associated fields customer.customer_id (primary key), payment.customer_id (non-unique index). When a table association query, there must be a table for full table scan, this table must be the table with the least number of rows recorded in several tables, and then find matching rows in other related tables through non-unique indexes, so as to achieve the minimum number of rows scanned when the table is associated.

Because the customer table has the least number of record rows in the customer and payment tables, the customer table scans the whole 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

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

Eq_ref generally occurs when multi-table joins use primary key or unique index as the association condition.

The film and film_text table association queries are basically the same as the previous one, except that the association condition has 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, so the query is very fast, so the values of other columns of this matching row can be treated by the optimizer as a constant in the current query.

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

Query based on the primary key primary key:

EXPLAIN SELECT * FROM customer WHERE customer_id = 10

Queries based on unique index unique index:

EXPLAIN SELECT * FROM customer WHERE email = 'MARY.SMITH@sakilacustomer.org'

7. Type=NULL,MySQL can get the results directly without accessing the table or index

.indexes _ keys: indicates the index that the query may use

.key: the index actually used

.key _ len: use the length of the index field

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

.rows: number of scan lines

.filtered: the percentage of the number of records left to satisfy the query after the data returned by the storage engine is filtered in the server layer.

.Extra: a description and description of the execution, containing additional information that is not suitable for display in other columns but is important to the execution plan

The main ones are as follows:

Using Index indicates index coverage, does not return table query Using Where indicates return table query Using Index Condition indicates ICP optimization Using Flesort indicates that MySQL requires additional sorting operations, which cannot be sorted through the index order

What is ICP?

MySQL5.6 introduces the features of Index Condition Pushdown (ICP) to further optimize the query. Pushdown indicates that operations are devolved, and in some cases conditional filtering operations are devolved to the storage engine.

EXPLAIN SELECT * FROM rental WHERE rental_date='2005-05-25' AND customer_id > = 300AND customer_id=300 AND customer_id=300 AND customer_id

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.