The Type field in the explain execution plan is divided into the following types:All INDEX RANGE REF eq_ref const,system NULLFrom left to right, performance from worst to best
Type = all, full table scan, MySQL scan full table to find matching rows (because rating is not an index in the film table) mysql> explain extended select * from film where rating > 9\g*** 1. Row *************************** id:1 select_type:simple table:film Type:ALLpossible_keys:NU LL key:null key_len:null ref:null rows:1024 filtered:100.00 extra:using WH Ere1 row in Set, 1 Warning (0.00 sec)
Type = index, index full scan, MySQL traverses the entire index to find matching rows.(although the index is not used in the Where condition, the column you want to remove is the title of the column that the index contains, so you can use the index tree to find the data directly if you scan the index in the full table)Mysql> Explain SelecttitleFrom film\g*************************** 1. Row *************************** id:1 select_type:simple table:film Type:indexpossible_keys: NULL key:idx_title key_len:767 ref:null rows:1024 extra:using index1 row in Set (0.00 sec)
Type = range, index range scan, commonly used in <, <=, >, >=, between, etc. (becausecustomer_id is an index, so just look for a range of indexes to find specific data by index) mysql> Explain select * from payment where customer_id > customer_id < 350\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:1294 Ext Ra:using Where1 Row in Set (0.01 sec)
Type = ref, which uses a non-unique index or a prefix scan of a unique index to return a row of records that matches a single value.
(1) Use of non-unique indexescustomer_id Single-table queryMysql> Explain select * from payment where customer_id = 350\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:23 extra:1 R ow in Set (0.00 sec)
(2) Querying with non-unique indexed tables(since customer_id is not a primary key in table A, it is a normal index (not unique), so it is ref)Mysql> Explain select b.*, a.* from payment A, customer B where a.customer_id = b.customer_id\g************************ 1. Row *************************** id:1 select_type:simple &nbs P table:b type:ALLpossible_keys:PRIMARY KEY:NU ll key_len:null ref:null rows:54 1 extra:*************************** 2. Row *************************** id:1 select_type:simple &nbs P table:a type:refpossible_keys:idx_fk_customer_id &NB Sp key:idx_fk_customer_id key_len:2 ref:sakila.b.customer_id & nbsp rows:14 EXTRa:2 rows in Set (0.00 sec)
Type = Eq_ref, which is a unique index relative to ref, with only one matching record for each index key value(use primary key or unique key as an association condition in a query of the table)(in film and Film_text, film_id are primary keys, which are unique indexes)Mysql> explain select * from film A, Film_text b where a.film_id = b.film_id\g*************************** 1. Row *************************** id:1 select_type:simple &nbs P table:b type:ALLpossible_keys:PRIMARY KEY:NU ll key_len:null ref:null &NBSP;ROWS:10 00 extra:*************************** 2. Row *************************** id:1 select_type:simple &nbs P table:a type:eq_refpossible_keys:PRIMARY key: primary key_len:2 ref:sakila.b.film_id rows:1 extra:using where2 rows in Set (0.00 sec)
Type = Const/system, with a maximum of one matching row in a single table, is very quick to query.so the values in the other columns in this matching row can be handled by the optimizer as constants in the current query. For example, a query based on a primary key or a unique index. Mysql> explain select * from film where film_id = 1\g*************************** 1. Row *************************** id:1 select_type:simple table:film Type:constpossible_keys: PRIMARY key:primary key_len:2 ref:const rows:1 extra:1 row in Set (0.02 sec)
Note: If there is only one row of data in the film table in the table above, the type is System.
Type = Null,mysql directly to the result without accessing the table or index. Mysql> explain select 1 from dual where 1\g(dual is a virtual table that can be ignored directly)1. Row *************************** id:1 select_type:simple table:null type:nullpossible_keys:n ULL key:null key_len:null ref:null rows:null extra:no tables used1 row in set ( 0.00 sec)
mysql> Select from dual;+-----+|+-----+| 2 |+-----+1 row in Set (0.05 sec)
Explain extendedmysql> explain extended select sum (amount) from customer A, payment b where 1 = 1 and a.customer_id = b . customer_id and email = '[email protected]' \g
1. Row ***************************
Id:1
Select_type:simple
Table:a
Type:all
Possible_keys:primary
Key:null
Key_len:null
Ref:null
rows:541
filtered:100.00
Extra:using where
2. Row ***************************
Id:1
Select_type:simple
Table:b
Type:ref
possible_keys:idx_fk_customer_id
key:idx_fk_customer_id
Key_len:2
ref:sakila.a.customer_id
Rows:14
filtered:100.00
Extra:
2 rows in Set, 1 Warning (0.00 sec)
Mysql> Show Warnings\g
1. Row ***************************
Level:note
code:1003
Message:select sum (' Sakila '. ' B '. ' Amount ') as ' sum (amount) ' From ' Sakila '. ' Customer ' a ' join ' Sakila '. ' Payment ' ' B ' wher E (' Sakila '. ' B '. ' customer_id ' = ' sakila '. ' A '. ' customer_id ') and (' Sakila '. ' A '. ' Email ' = '[email protected]‘))
1 row in Set (0.00 sec)
Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.
MySQL explain type
