標籤：mysql
explain執行計畫中type欄位分為以下幾種：ALL INDEX RANGE REF EQ_REF CONST,SYSTEM NULL從左至右，效能從最差到最好
type = ALL，全表掃描，MYSQL掃描全表來找到匹配的行（因為film表中rating不是索引）mysql> explain extended select * from film where rating > 9\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: film type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1024 filtered: 100.00 Extra: Using where1 row in set, 1 warning (0.00 sec)
type = index，索引全掃描，MYSQL遍曆整個索引來尋找匹配的行。（雖然where條件中沒有用到索引，但是要取出的列title是索引包含的列，所以只要全表掃描索引即可，直接使用索引樹尋找資料）mysql> 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: 1024 Extra: Using index1 row in set (0.00 sec)
type = range ，索引範圍掃描，常見於<、<=、>、>=、between等操作符（因為customer_id是索引，所以只要尋找索引的某個範圍即可，通過索引找到具體的資料）mysql> explain select * from payment where customer_id > 300 and 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 Extra: Using where1 row in set (0.01 sec)
type = ref ，使用非唯一性索引或者唯一索引的首碼掃描，返回匹配某個單獨值的記錄行。
（1）使用非唯一性索引customer_id單表查詢mysql> 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 row in set (0.00 sec)
（2）使用非唯一性索引聯表查詢（由於customer_id在a表中不是主鍵，是普通索引（非唯一），所以是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 table: b type: ALLpossible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 541 Extra:*************************** 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: 14 Extra:2 rows in set (0.00 sec)
type = eq_ref，相對於ref來說就是使用的是唯一索引，對於每個索引鍵值，只有唯一的一條匹配記錄（在聯表查詢中使用primary key或者unique key作為關聯條件）（在film和film_text中film_id都是主鍵，即都是唯一索引）mysql> explain select * from film a ,film_text b where a.film_id = b.film_id\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: b type: ALLpossible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 1000 Extra:*************************** 2. row *************************** id: 1 select_type: SIMPLE 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，單表中最多隻有一條匹配行，查詢起來非常迅速，所以這個匹配行中的其他列中的值可以被最佳化器在當前查詢中當做常量來處理。例如根據主鍵或者唯一索引進行的查詢。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)
注釋：如果上表中film表中只有一行資料，那麼type就是system。
type = NULL，MYSQL不用訪問表或者索引就直接能到結果。mysql> explain select 1 from dual where 1\G （dual是一個虛擬表，可以直接忽略）*************************** 1. row *************************** id: 1 select_type: SIMPLE table: NULL type: NULLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL Extra: No tables used1 row in set (0.00 sec)
mysql> select 1+1 from dual;+-----+| 1+1 |+-----+| 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` where ((`sakila`.`b`.`customer_id` = `sakila`.`a`.`customer_id`) and (`sakila`.`a`.`email` = ‘[email protected]‘))
1 row in set (0.00 sec)
著作權聲明：本文為博主原創文章，未經博主允許不得轉載。
mysql explain type
