explain详解与最佳实践

explain使用与详解

简介

explain是MySQL中的一个关键字，用于获取select语句的执行计划。当你对一个select语句使用explain关键字时，MySQL会返回关于如何执行该查询的详细信息，而不是实际执行查询并返回结果。这对于优化查询性能非常有用，因为它可以帮助你理解MySQL如何处理你的查询，并法吸可能存在的性能瓶颈。

tips：本文只会介绍常用的类型，至于其他具体用法可到MySQL官网explain介绍查看

mysql5.7默认会对派生表进行合并优化，通过derived_merge来控制，默认为on，表示开启。

开启：set session optimizer_switch='derived_merge=on';
关闭：set session optimizer_switch='derived_merge=off';

如何使用

部门表：
CREATE TABLE `t_department` (
  `id` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `t_department` VALUES ('1', '部门1');
INSERT INTO `t_department` VALUES ('2', '部门2');
INSERT INTO `t_department` VALUES ('3', '部门3');

用户表：
CREATE TABLE `t_user` (
  `id` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `t_user` VALUES ('1', '张三', '2024-03-16 20:57:12');
INSERT INTO `t_user` VALUES ('2', '李四', '2024-03-16 20:57:24');
INSERT INTO `t_user` VALUES ('3', '王五', '2024-03-16 20:57:30');

用户部门关联表：
CREATE TABLE `t_user_dept` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `dept_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_dept_id` (`user_id`,`dept_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `t_user_dept` VALUES ('1', '1', '1');
INSERT INTO `t_user_dept` VALUES ('2', '2', '2');
INSERT INTO `t_user_dept` VALUES ('3', '3', '3');

在select语句前加入explain关键字即可：

explain select * from t_user where id=1

explain列信息

id

id列就是select的序列号，有几个查询就有几个id，id的顺序是按select出现的顺序增长的。越大执行优先级越高，id相关则从上往下执行，id为null最后执行

select_type

select_type表示查询的类型，由简单到复杂

SIMPLE：简单查询，查询不包含子查询和union。这是最常见的类型

PRIMARY：查询包含任务复杂的子部分（如子查询或union）时，最外层的查询被标记PRIMARY

UBQUERY：当select或where列表中包含子查询时，子查询的select可能是SUBQUERY。与PRIMARY例子相同

DERIVED：在 from列表中包含的子查询被标记为 DERIVED（衍生）。MySQL 会递归执行这些子查询，并把结果放在临时表里

UNION：在union操作中，内层的select（即