explain之type_explain type range usingwhere-CSDN博客

本文链接：https://blog.csdn.net/csdnmaker/article/details/127712479

type说明了什么

type有以下几个字段

ALL

INDEX

RANGE

REF

EQ_REF

CONST,SYSTEM

NULL

自上而下，性能从最差到最好

一、all

全表扫描获得得结果

mysql> show create table rental\G;
*************************** 1. row ***************************
Table: rental
Create Table: CREATE TABLE `rental` (
`rental_id` int(11) NOT NULL AUTO_INCREMENT,
`rental_date` datetime NOT NULL,
`inventory_id` mediumint(8) unsigned NOT NULL,
`customer_id` smallint(5) unsigned NOT NULL,
`return_date` datetime DEFAULT NULL,
`staff_id` tinyint(3) unsigned NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`rental_id`),
UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`),
KEY `idx_fk_inventory_id` (`inventory_id`),
KEY `idx_fk_customer_id` (`customer_id`),

KEY `idx_fk_staff_id` (`staff_id`),
CONSTRAINT `fk_rental_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE,
CONSTRAINT `fk_rental_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `inventory` (`inventory_id`) ON UPDATE CASCADE,
CONSTRAINT `fk_rental_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql> explain select * from rental\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: rental
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 16005

1 row in set, 1 warning (0.00 sec)

ERROR:
No query specified

二、index

就是搜索得是索引树，不用all那么多

mysql> explain select staff_id from rental\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: rental
partitions: NULL
type: index
possible_keys: NULL
key: idx_fk_staff_id
key_len: 1
ref: NULL
rows: 16005
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)

ERROR:
No query specified

/注意，这里换成其他得列字段可能不会是index得，想想为什么，因为索引-staff_id是在一起得呀

三、range

type = range ，索引范围扫描，常见于<、<=、>、>=、between等操作符（因为customer_id是索引，所以只要查找索引的某个范围即可，通过索引找到具体的数据）

mysql> explain select customer_id from rental where rental_id>10;
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | rental | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 8002 | 100.00 | Using where |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

四、ref

1）使用非唯一性索引customer_id单表查询

mysql> explain select * from payment where customer_id = 350\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: payment

type: ref

possible_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)

mysql> select * from rental where inventory_id=10;
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
| rental_id | rental_date | inventory_id | customer_id | return_date | staff_id | last_update |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
| 4364 | 2005-07-07 19:46:51 | 10 | 145 | 2005-07-08 21:55:51 | 1 | 2006-02-15 21:30:53 |
| 7733 | 2005-07-28 05:04:47 | 10 | 82 | 2005-08-05 05:12:47 | 2 | 2006-02-15 21:30:53 |
| 15218 | 2005-08-22 16:59:05 | 10 | 139 | 2005-08-30 17:01:05 | 1 | 2006-02-15 21:30:53 |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
3 rows in set (0.00 sec)

mysql> explain select * from rental where inventory_id=10;
+----+-------------+--------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | rental | NULL | ref | idx_fk_inventory_id | idx_fk_inventory_id | 3 | const | 3 | 100.00 | NULL |
+----+-------------+--------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------+

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: ALL

possible_keys: PRIMARY

key: NULL

key_len: NULL

ref: NULL

rows: 541

Extra:

*************************** 2. row ***************************

id: 1

select_type: SIMPLE

table: a

type: ref

possible_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)

五、eq_ref

读取本表中和关联表表中的每行组合成的一行。除了 system 和 const 类型之外, 这是最好的联接类型。当连接使用索引的所有部分时, 索引是主键或唯一非 NULL 索引时, >将使用该值。
eq_ref 可用于使用 = 运算符比较的索引列。比较值可以是常量或使用此表之前读取的表中的列的表达式。在下面的示例中, MySQL 可以使用 eq_ref 连接(join)ref_table来处
理:

SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;

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
partitions: NULL
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 1000
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: a
partitions: NULL
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: sakila.b.film_id
rows: 1
filtered: 100.00
Extra: Using where
2 rows in set, 1 warning (0.00 sec)