MySQL Explain type connection types example

For MySQL execution plan acquisition, we can see through the explain way, the explain way seems simple, actually contains a lot of content, especially the type column in the output result. Understanding these different types is important for our SQL optimization, this article describes only the type column in the Explian output, and gives its presentation.

For a full description of the Explian output, refer to: MySQL EXPLAIN SQL Output Information description

One, the value of the Type column in the EXPLAIN statement

type: Connection type system table has only one rowConstTable has a maximum of one row of matches, and when used for primary key or unique index comparisons, Eq_ref reads only one row at a time with the previous table merge row, except for system,ConstThe best one, characterized by the use of =, and all parts of the index are involved inJoinAnd index is the index of the primary key or non-null unique key if you match only a few rows at a time, it is a good one, using = or <=>, can be left overlay index or non-primary key or non-unique key fulltext full-text Search Ref_or_                    Null is similar to ref, but including null Index_merge indicates that an index merge optimization (including intersection, Union, and union between intersections) has occurred, but does not include cross-table and full-text indexes. This is more complicated, and the current understanding is to merge the range index scan of a single table (if the cost estimate is better than the normal range) unique_subqueryinchIn a subquery, it is the valueinch(Select...) The shape is like "SelectUnique_key_column "is replaced by a subquery. PS: So not necessarilyinchThe use of subqueries in clauses is inefficient! Index_subquery Ibid, but the shape of the "SelectNon_unique_key_column to replace the range of the range constant value with the subqueryIndexA. When the query is indexed, that is, all data can be fetched from the index tree (extraUsing IndexB. Full table scan of data rows from the index in index order (noneUsing Index); c. If the extraUsing IndexAndUsing WhereAt the same time, it is the use of the index to find the meaning of the key; d. If it appears separately, it is a read index instead of a read row, but not a lookup of all full table scans

Two, connection type part example

1. All-- Environment description ([email protected]) [sakila]> show variables like' Version ';+---------------+--------+| variable_name | Value |+---------------+--------+| version | 5.6.26 |+---------------+--------+MySQL takes a full table traversal to return a data row, equivalent to Oracle's complete table scan ([email protected]) [sakila]> explain select count (description) from Film+----+-------------+-------+------+---------------+------+---------+------+------+-------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------ +-------+| 1 | Simple | Film | All | NULL | NULL | NULL | NULL | 1000 | NULL |+----+-------------+-------+------+---------------+------+---------+------+------+------- +2. Indexmysql take the index full scan to return the data row, equivalent to the Oracle's complete index scan ([email protected]) [sakila]> explain select title from film \g*************************** 1. Row ***************************id:1Select_type:simpleTable:filmType:indexPossible_keys:null key:idx_Titlekey_len:767Ref:nullrows:1000extra:using Index1 row in Set (0.00 sec) 3, range Index range scan, the scan of the index starts at a point, returns the row that matches the value of the domain, common to between, <, > and so on the query is equivalent to the Oracle's Index range scan (email Protected]) [sakila]> explain select * from payment where customer_id>300 and Customer_Id<400\g*************************** 1. Row ***************************id:1Select_type:simpletable:paymentType:rangePossible_keys:idx_Fk_customer_Idkey:idx_fk_customer_idKey_len:2Ref:nullrows:2637extra:using where1 row in Set (0.00 sec) ([e-mail protected]) [sakila]> explain select * from payment where customer_id in (200,300,400) \g*************************** 1. Row *************************** id:1 select_Type:simpletable:paymentType:rangePossible_keys:idx_Fk_customer_Idkey:idx_fk_customer_idKey_len:2Ref:nullrows:86extra:using Index Condition1 row in Set (0.00 sec) 4, ref non-unique index scan or, return all rows that match a single value. A lookup that is common to non-unique prefixes that use a non-unique index that is a unique index ([email protected]) [sakila]> explain select * from payment where customer_id=305\g*************************** 1. Row *************************** id:1 select_Type:simpletable:paymentType:refPossible_keys:idx_Fk_customer_Idkey:idx_fk_customer_idKey_len:2Ref:constrows:25Extra:1 row in Set (0.00 sec) idx_fk_Customer_id is the foreign key index on the table payment, and there are multiple non-unique values, such as the following query ([email protected]) [sakila]> Select Customer_Id,count (*) Payment GROUP by customer_id, limit 2;+-------------+----------+| customer_id | COUNT (*) |+-------------+----------+|       1 | 32 ||       2 | |+-------------+----------+-- The following is an example of a non-unique prefix index using ref ([email protected]) [sakila]> CREATE INDEX IDX_fisrt_Last_name on customer (First_Name,last_name); Query OK, 599 rows Affected (0.09 sec) records:599 duplicates:0 warnings:0([email protected]) [sakila]> Select First_name,count (*) from the customer group by First_NameHave count (*) >1 limit 2;+------------+----------+| first_name | count (*) |+------------+----------+|        JAMIE | 2 ||        JESSIE | 2 |+------------+----------+2 rows in Set (0.00 sec) ([email protected]) [sakila]> explain select first_name from customer where First_Name=' JESSIE '\g*************************** 1. Row ***************************id:1Select_type:simpleTable:customerType:refPossible_keys:idx_Fisrt_last_NameKey:idx_fisrt_last_namekey_len:137Ref:constRows:2extra:using where; Using Index1 row in Set (0.00 sec) ([email protected]) [sakila]> ALTER TABLE customer DROP INDEX idx_fisrt_Last_name; Query OK, 599 rows Affected (0.03 sec) records:599 duplicates:0 warnings:0--The following illustration shows an example of a join that is ref ([email protected]) [sakila]> explain select B.*,a.*From payment a INNER join- customer B on A.customer_id=b.customer_id\g*************************** 1. Row ***************************id:1Select_type:simpletable:bType:allPossible_keys:primary key:null Key_Len:nullRef:nullrows:599Extra:null*************************** 2. Row ***************************id:1Select_type:simpletable:aType:refPossible_keys:idx_Fk_customer_Idkey:idx_fk_customer_idKey_len:2ref:sakila.b.customer_idrows:13Extra:null2 rows in Set (0.01 sec) 5, EQ_ref is similar to ref, where the difference is that the index used is a unique index, and only one record in the table matches each index key value. Mostly seen in primary key scan or index unique scan. ([email protected]) [sakila]> explain select * from film a join Film_Text bOn a.film_id=b.film_id;+----+-------------+-------+--------+---------------+---------+---------+------ ------------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+--------+---------------+---------+---------+------------------+------+------ -------+| 1 | Simple | B | All | PRIMARY | NULL | NULL | NULL | 1000 | NULL || 1 | Simple | A | Eq_ref | PRIMARY | PRIMARY | 2 |    sakila.b.film_id | 1 | Using where |+----+-------------+-------+--------+---------------+---------+---------+------------------+------+ -------------+([email protected]) [sakila]> explain select title from film where film_id=5;+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+| ID | Select_Type | Table | Type | Possible_keys | key | Key_Len | Ref | Rows | Extra |+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+| 1 | Simple | Film | Const | PRIMARY | PRIMARY | 2 |    Const | 1 | NULL |+----+-------------+-------+-------+---------------+---------+---------+-------+------+------- +6, const, System: When MySQL is optimized for a portion of the query, the other column values of this matching row can be converted to a constant to handle. If the primary key or unique index is placed in the where list, MySQL can convert the query to a constant ([email protected]) [sakila]> CREATE TABLE t1 (ID int,ename varchar (20) Unique); Query OK, 0 rows affected (0.05 sec) ([email protected]) [sakila]> insert INTO T1 values (1,' Robin '), (2,' Jack '), (3,' Henry '); Query OK, 3 Rows Affected (0.00 sec) Records:3 duplicates:0 warnings:0 ([email protected]) [sakila]> explain select * FROM (SELECT * from T1 where ename=' Robin ') x;+----+-------------+------------+--------+---------------+-------+---------+-------+------+-------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+------------+--------+---------------+-------+---------+-------+------ +-------+| 1 | PRIMARY | <derived2> | System | NULL | NULL | NULL |    NULL | 1 | NULL || 2 | DERIVED | T1 | Const | ename | ename | 23 |    Const | 1 | NULL |+----+-------------+------------+--------+---------------+-------+---------+-------+------+------- +2 rows in Set (0.00 sec) 7, Type=nullmysql without access to the table or index can be directly obtained results ([email protected]) [sakila]> explain select Sysdate ();+----+-------------+-------+------+---------------+------+---------+------+------+----------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------ +----------------+| 1 | Simple | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |+----+-------------+-------+------+---------------+------+---------+------+------+--------------- -+1 row in Set (0.00 sec)

Copyright NOTICE: This article for Bo Master original article, welcome to spread, spread please be sure to indicate the source.

MySQL Explain type connection types example