explain分析
1. EXPLAIN 是什么？
MySQL 提供的一个关键字，用于分析查询语句的执行计划。通过执行 EXPLAIN 命令，可以获取查询执行的详细信息，包括使用的索引、表的访问顺序、连接类型和估计的行数等。分析 EXPLAIN 结果可以帮助我们了解查询的性能瓶颈，优化查询语句和索引设计。
下面是分析 EXPLAIN 结果的一般步骤：
-
查询类型（Type）：查询类型表示 MySQL 在执行查询时使用的访问方法。常见的查询类型包括 SELECT_TYPE、INSERT、UPDATE、DELETE 等。较好的查询类型是 SIMPLE，它表示查询是简单的表扫描或索引扫描。
-
表的访问顺序（Table）：EXPLAIN 结果中的表访问顺序表示查询中涉及的表的访问顺序。如果查询涉及多个表，可以根据表的访问顺序来判断查询的连接顺序。
-
使用的索引（Key）：EXPLAIN 结果中的索引表示查询在执行过程中使用的索引。如果查询使用了索引，可以判断索引的选择是否合理。
-
连接类型（Join Type）：如果查询涉及多个表的连接，EXPLAIN 结果中的连接类型表示连接的方式，如 INNER JOIN、LEFT JOIN、RIGHT JOIN 等。连接类型对查询性能有重要影响，需要确保选择合适的连接类型。
-
估计的行数（Rows）：EXPLAIN 结果中的估计行数表示查询返回的估计行数。这个值可以帮助我们判断查询的效率，较小的估计行数通常意味着查询性能较好。
-
额外信息（Extra）：EXPLAIN 结果中的额外信息提供了查询执行过程中的一些额外细节。例如，Using where 表示 MySQL 在读取数据后需要进行额外的过滤操作，Using index 表示查询可以通过索引覆盖来避免访问实际数据行。
通过分析 EXPLAIN 结果，我们可以识别潜在的性能问题，如全表扫描、不合适的索引使用、连接类型选择不当等。根据分析结果，可以考虑优化查询语句、调整索引设计或重构查询逻辑，以提高查询性能和效率。。
2. EXPLAIN type类型说明[通俗易懂]
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: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1024
filtered: 100.00
Extra: Using where
1 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: index
possible_keys: NULL
key: idx_title
key_len: 767
ref: NULL
rows: 1024
Extra: Using index
1 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: range
possible_keys: idx_fk_customer_id
key: idx_fk_customer_id
key_len: 2
ref: NULL
rows: 1294
Extra: Using where
1 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: 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)
（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)
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: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: a
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: sakila.b.film_id
rows: 1
Extra: Using where
2 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: const
possible_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: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: No tables used
1 row in set (0.00 sec)
mysql> select 1+1 from dual;
+—–+
| 1+1 |
+—–+
| 2 |
+—–+
1 row in set (0.05 sec)
explain extended
mysql> explain extended select sum(amount) from customer a ,payment b where 1 = 1 and a.customer_id = b.customer_id and email = ‘JANE.BENNETT@sakilacustomer.org’\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 = ‘JANE.BENNETT@sakilacustomer.org’))
1 row in set (0.00 sec)
