MySQL之Explain详解

Explain含义

Explain是SQL分析工具中非常重要的一个功能，它可以模拟优化器执行查询语句，帮助我们理解查询是如何执行的。分析查询执行计划可以帮助我们发现查询瓶颈，优化查询性能。

Explain作用

• 表的读取顺序
• SQL执行时查询操作类型
• 可以使用哪些索引
• 实际使用哪些索引
• 每张表有多少行记录被扫描
• SQL语句性能优化

Explain语句返回列的各列含义：

Explain返回列详解

数据准备

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    password VARCHAR(100) NOT NULL
)ENGINE = INNODB DEFAULT CHARSET = utf8;
    
CREATE TABLE products (
     id INT PRIMARY KEY AUTO_INCREMENT, 
     name VARCHAR(50) NOT NULL, 
     price FLOAT NOT NULL
) ENGINE = INNODB DEFAULT CHARSET = utf8;
    
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    order_date DATETIME NOT NULL,
    total_price FLOAT NOT NULL,
    product_id INT NOT NULL,
    FOREIGN KEY ( user_id ) REFERENCES users ( id ),
FOREIGN KEY ( product_id ) REFERENCES products ( id )) ENGINE = INNODB DEFAULT CHARSET = utf8;
    
--给用户表users添加联合索引(name,email)
ALTER TABLE users ADD INDEX index_name_email ( name, email );

INSERT INTO users ( NAME, email, PASSWORD )
VALUES
    ( '张三', 'zhangsan@example.com', 'password123' ),
    ( '李四', 'lisi@example.com', 'password123' ),
    ( '王五', 'wangwu@example.com', 'password123' ),
    ( '赵六', 'zhaoliu@example.com', 'password123' ),
    ( '钱七', 'qianqi@example.com', 'password123' );
    
INSERT INTO products (name,price)
VALUES ('产品 1',10.00),
('产品 2',15.00),
('产品 3',20.00),
('产品 4',12.00),
('产品 5',18.00);    

INSERT INTO orders ( user_id, order_date, total_price, product_id )
VALUES
    ( 1, '2023-08-18 10:00:00', 100.00, 1 ),
    ( 2, '2023-08-18 11:00:00', 50.00, 2 ),
    ( 3, '2023-08-18 12:00:00', 20.00, 3 ),
    ( 4, '2023-08-18 13:00:00', 15.00, 4 ),
    ( 5, '2023-08-18 14:00:00', 25.00, 5 );

id列

注：每个select都有一个对应的id号，并且从1开始自增的。

• 如果id序号相同，从上往下执行。

explain
SELECT
    users.NAME,
    orders.total_price,
    products.price 
FROM
    users
    INNER JOIN orders ON users.id = orders.user_id
    INNER JOIN products ON orders.product_id = products.id;

• 如果id序号不同，序号大先执行。

EXPLAIN SELECT
    * 
FROM
    orders 
WHERE
    product_id = ( SELECT id FROM products WHERE products.price = 10 );

• 如果两种都存在，先执行序号大，再同级从上往下执行。

set session optimizer_switch = 'derived_merge=off'; #关闭mysql5.7对衍生表合并优化

explain
select orders.* from (SELECT id from products) as temp inner join orders on temp.id = orders.product_id;    

set session optimizer_switch = 'derived_merge=on'; #还原配置

• 如果显示NULL，最后执行。表示结果集，并不需要使用它来进行查询。

explain
select id from users
union
select id from products;

优化器会针对子查询进行一定的优化重写SQL

例如以下SQL，因为有两个select，执行计划按正常情况应该有(users表对应)1、(orders表对应)2两个id。但是我们看到的执行计划结果是两个id都为1。

explain select * from users where id in (select user_id from orders where id = 1);

原因就是因为MySQL优化器会针对子查询进行一定的优化重写SQL。我们可以通过 "SHOW WARNINGS;" 进行查看优化器优化后的执行SQL。

explain select * from users where id in (select user_id from orders where id = 1);
SHOW WARNINGS;

 /* select#1 */

SELECT
'1' AS `id`,
'张三' AS `name`,
'zhangsan@example.com' AS `email`,
'password123' AS `password` 
FROM
    `db01`.`orders`
    JOIN `db01`.`users` 
WHERE
    1

优化器优化后的执行SQL由子查询(两个select)变成了表关联查询(一个select)，所以执行计划的结果是两个id都为1。

select_type列

• 含义：表示查询语句执行的查询操作类型
• simple：简单select，不包含union与子查询。

•  primary：复杂查询中最外层查询，比如使用union或union all时，id为1的记录select_type通常为primary。
•  subquery：指在select语句中出现的子查询语句，结果不依赖于外部查询(不在from语句中)。
•  dependent subquery：指在 select 语句中出现的查询语句，结果依赖于外部查询。
•  derived：派生表，在FROM字句的查询语句，表示从外部数据源中推导出来的，而不是从select语句中的其他列中选择出来的。
•  union：分union与union all两种，若第二个select出现在union之后，则被标记为union；如果union被from字句的子查询包含，那么第一个select会被标记为derived；union会针对相同的结果集进行去重，union all不会进行去重处理。
  • 如果union换为union all将不会进行去重，执行计划就没有select_type为UNION RESULT这一行。

   

• dependdent union：当union作为子查询时，其中第一个union为dependent subquery，第二个union为dependent union。 
•  union result：如果两个查询中有相同的列，则会对这些列进行重复删除，只保留一个表中的列。

table列

• 查询所涉及到的表名，如果有多个表，将显示多行记录。

partitions列

• 表分区情况

type列

• 含义：查询所使用的访问类型。

效率从高到底分别为：system > const > eq ref > ref > fulltext > ref or null > range > index >ALL,一般来说保证range级别，最好能达到ref级别。

• system：const类型的一种特殊场景，查询的表只有一行记录的情况，并且该表使用的存储引擎的统计数据是精确的。
•  const：基于主键或唯一索引查看一行，当mysql对查询某部分进行优化，并转换为一个常量时，使用这些类型访问转换为常量查询，效率高。
•  eq_ref：基于主键或唯一索引连接两个表，对于每个索引键值，只有一条匹配记录，被驱动表的类型为"eq_ref"；
•  ref：基于非唯一索引连接两个表或通过二级索引列与常量进行等值匹配，可能存在多条匹配记录。
• range：使用非唯一索引扫描部分索引，比如使用索引获取某些范围区间的记录。
• index：扫描整个索引就能拿到结果，一般是二级索引，这种查询一般为使用覆盖索引(需优化，缩小数据范围)
•  all：扫描整个表进行匹配，即扫描聚簇索引树（需优化，添加索引优化）
• NULL：MySQL在优化过程中分解语句就可以获取到结果，执行时甚至不用访问表或索引。 

possible_keys列

• 含义：表示在查询中可能使用到某个索引和多个索引；如果没有选择索引，显示NULL。

key列

• 含义：表示在查询中实际使用的索引，如果没有使用索引，显示NULL。

key_len列 

• 含义：表示优化器决定使用某个索引执行查询时，该索引记录的最大长度(主要使用在联合索引)；
• 计算规则：

ref列

•  含义：表示将哪个字段或常量和key列所使用的字段进行比较。
• 常量
• 字段
• 函数

rows列

• 含义：全表扫描时表示需要扫描表的行数估计值，值越小越好。
• 注：不是结果集中的行数
• 全表扫描（优化器发现一共5条数据通过全表扫描要比不走索引效率高，所以type类型为ALL，rows为5，最终结果是一条数据，filtered百分比就为20）
•  索引扫描（因为走了索引只扫描了2条数据，所以type类型为range，rows为2，filtered百分比为100）

filtered列

• 含义：表示符合查询条件的数据百分比。可以使用rows*filtered/100计算出与explain前一个表进行连接的行数。

Extra列

• 含义：SQL执行查询的一些额外信息。
• Using Index：使用非主键索引树就可以查询所需要的数据。一般是覆盖索引，即查询列包含在辅助索引树叶子节点中，不需要回表查询。
•  Using Where：不通过索引查询所需要的数据。
•  Using index condition：表示查询列不被索引覆盖，where条件中是一个索引范围查找，过滤完索引后回表找到所有符合条件的数据行。
•  Using temporary：表示使用临时表来处理查询。
•  Using filesort：当查询中包含 order by 操作而无法利用索引完成的排序操作，数据较少时从内存排序，如果数据较多需要从磁盘中排序。需优化成索引排序。
•  Select tables optimized away：使用某些聚合函数（min，max）来访问某个索引值。