Mysqlexplaintype connection type example _ MySQL

For obtaining the MySQL execution plan, we can view it through the explain method. the explain method looks simple and actually contains a lot of content, especially the type column in the output result. Understanding these different types plays an important role in SQL optimization. This article only describes how to obtain the explain MySQL execution plan. we can view the explain method. the explain method seems simple, in fact, it contains a lot of content, especially the type columns in the output results. Understanding these different types plays an important role in SQL optimization. This article only describes the type column in The explain output result and provides its demonstration.

I. value of the type column in The EXPLAIN statement

Type: The connection type system table has only one row of const table and only one row can match. it is generally used for primary key or unique index comparison. eq_ref reads only one row from the table each time it merges with the previous table, this is the best except system and const, and features =, in addition, all the parts of the index are involved in the join operation, and the index is an index ref with a primary key or a non-null unique key. if only a few rows are matched at a time, it is better, use = or <=>. it can be left-covered index or non-primary key or non-unique key fulltext full-text search ref_or_null. similar to ref, if NULL index_merge is included, index merging is optimized (including intersection, union, and union between intersections), but not cross-table and full-text indexes. This is complicated. the current understanding is to merge the range index scanning of a single table (if the cost estimation is better than the normal range) unique_subquery in the in subquery, is value in (select ...) replace the subquery in the format of "select unique_key_column. PS: therefore, it is inefficient to use subqueries in an in clause! Index_subquery is the same as above, but replaces index a with the range of the range constant value in subqueries such as "select non_unique_key_column. when the query is indexed, that is, all data can be obtained from the Index tree (Using Index in Extra); B. searches for full table scans of data rows from the Index in the order of indexes (without Using Index); c. if both Using Index and Using Where appear in Extra, the Index is used to find the key value. d. if it appears separately, the read index is used instead of the Read row, but it is not used to search for all full table scans.

II. Examples of connection types

1. all -- environment description (root @ localhost) [sakila]> show variables like 'version '; + --------------- + -------- + | Variable_name | Value | + --------------- + -------- + | version | 5.6.26 | + --------------- + -------- + MySQL returns data rows through full table traversal, equivalent to full table scan (root @ localhost) [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 | 1000 | NULL | + ---- + ------------- + ------- + ------ + ------------- + ------ + --------- + ------ + ------- + 2. indexMySQL uses full index scanning to return data rows, equivalent to full index scan (root @ local) of Oracle Host) [sakila]> explain select title from film \ G ***************************** 1. row ************************** id: 1 select_type: SIMPLE table: film type: indexpossible_keys: NULL key: idx_title key_len: 767 ref: NULL rows: 1000 Extra: Using index1 row in set (0.00 sec) 3. range index range scanning, index scanning starts at a certain point and returns rows that match the value range. common queries such as between, <,> are equivalent to Oracle's index range scan (root @ localhost) [sakila]> explain select * from Payment where customer_id> 300 and customer_id <400 \ G ***************************** 1. row ************************** id: 1 select_type: SIMPLE table: payment type: rangepossible_keys: idx_fk_customer_id key: idx_fk_customer_id key_len: 2 ref: NULL rows: 2637 Extra: Using where1 row in set (0.00 sec) (root @ localhost) [sakila]> explain select * from payment where customer_id in (200,300,400) \ G ********* * ****************** 1. row ************************** id: 1 select_type: SIMPLE table: payment type: rangepossible_keys: idx_fk_customer_id key: idx_fk_customer_id key_len: 2 ref: NULL rows: 86 Extra: Using index condition1 row in set (0.00 sec) 4. ref non-unique index scan or, returns all rows that match a single value. Common search with a non-unique index or a unique index prefix (root @ localhost) [sakila]> explain select * from payment where customer_id = 305 \ G ************************* ** 1. row ************************** id: 1 select_type: SIMPLE table: payment type: refpossible_keys: idx_fk_customer_id key: idx_fk_customer_id key_len: 2 ref: const rows: 25 Extra: 1 row in set (0.00 sec) idx_fk_customer_id is the foreign key index on the table payment, there are multiple non-unique values, as shown in the following query (root @ localho St) [sakila]> select customer_id, count (*) from payment group by customer_id-> limit 2; + ------------- + ---------- + | customer_id | count (*) | + ------------- + ---------- + | 1 | 32 | 2 | 27 | + ------------- + ---------- + -- The following is an example of using ref for non-unique prefix indexes (root @ localhost) [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 (root @ localhost) [sakila]> select first_name, count (*) from customer group by first_name-> having count (*)> 1 limit 2; + ------------ + ---------- + | first_name | count (*) | + ------------ + ---------- + | JAMIE | 2 | JESSIE | 2 | + ------------ + ---------- + 2 rows in set (0.00 sec) (root @ localhost) [sakila]> explain select first_name from customer where first_name = 'jessi' \ G *********************** * *** 1. row * * *********************** Id: 1 select_type: SIMPLE table: customer type: refpossible_keys: idx_fisrt_last_name key: idx_fisrt_last_name key_len: 137 ref: const rows: 2 Extra: Using where; Using index1 row in set (0.00 sec) (root @ localhost) [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 example shows that join is a ref (Root @ localhost) [sakila]> explain select B. *,. * from payment a inner join-> customer B on. customer_id = B. customer_id \ G **************************** 1. row ************************** id: 1 select_type: SIMPLE table: B type: ALLpossible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 599 Extra: NULL **************************** 2. row ************************** id: 1 select_type: SIMPLE table: A type: refpossible_keys: idx_fk_customer_id key: idx_fk_customer_id key_len: 2 ref: sakila. b. customer_id rows: 13 Extra: NULL2 rows in set (0.01 sec) 5. eq_ref is similar to ref. The difference is that the index used is a unique index. for each index key value, the table only matches one record. Primary key scan or unique index scan. (Root @ localhost) [sakila]> explain select * from film a join film_text B-> on. film_id = B. film_id; + ---- + ------------- + ------- + -------- + ------------- + --------- + hour + ------ + ------------- + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | + ---- + ------------- + ------- + -------- + --------------- + --------- + -------------------- + ------ + -- ----------- + | 1 | SIMPLE | B | ALL | PRIMARY | NULL | 1000 | NULL | 1 | SIMPLE | a | eq_ref | PRIMARY | 2 | sakila. b. film_id | 1 | Using where | + ---- + ------------- + ------- + -------- + --------------- + --------- + ------------------ + ------ + ------------- + (root @ localhost) [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 | 2 | const | 1 | NULL | + ---- + ------------- + ------- + --------------- + --------- + ------- + ------ + ------- + 6. const and system: when MySQL optimizes a certain part of the query, other column values of the matched row can be converted into a constant for processing. If you place the primary key or unique index in the where List, MySQL can convert the query to a constant (root @ localhost) [sakila]> create table t1 (id int, ename varchar (20) unique); Query OK, 0 rows affected (0.05 sec) (root @ localhost) [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 (root @ localhost) [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 |  | System | NULL | 1 | NULL | 2 | DERIVED | t1 | const | ename | 23 | const | 1 | NULL | + ---- + ------------- + ------------ + -------- + --------------- + ------- + --------- + ------- + ------ + ------- + 2 rows in set (0.00 sec) 7. type = NULLMySQL can directly obtain the result (root @ localhost) [sakila]> explain select sysdate () without accessing the table or index (); + ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ---------------- + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | + ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ------------------ + | 1 | SIMPLE | NULL | NULL | No tables used | + ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ---------------- + 1 row in set (0.00 sec)