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这一行。
- 如果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)来访问某个索引值。