【mysql】explain执行计划之select_type列

一、说明

• 1.表示select的类型，查询语句执行的查询操作的类型
• 2.常见的取值有：simple、primary、union、subquery、dependent subquery
• 3.simple：简单表，不使用union或者子查询
• 4.primary：主查询，外层的查询
• 5.union：union中的第二个或者后面的查询语句
• 6.subquery：select、where之后包含了子查询，在select语句中出现的子查询语句，结果不依赖于外部查询（不在from语句中）
• 7.dependent subquery：指在select语句中出现的查询语句，结果依赖于外部查询
• 8.derived：派生表，在from子句的查询语句，表示从外部数据源中推导出来，不是从select语句中的其他列中选择出来的
• 9.union：分union与union all两种，若第二个select出现在union之后，则被标记为union；如果union被from子句的子查询包含，则第一个select会被标记为derived；union会针对相同的结果集进行去重，union all不会进行去重处理
• 10.dependent union：当union作为子查询时，第一个union为dependent subquery，第二个union为dependent union

二、示例

2.1 simple：简单表，不使用union或者子查询

1.单表的select_type，不使用union和子查询
explain select * from users;

2.表连接查询的select_type，不使用union和子查询
explain select * from users inner join orders where users.id = orders.user_id;

2.2 primary：主查询，外层的查询

explain select id from users union select id from orders;

2.3 subquery：select、where之后包含了子查询，在select语句中出现的子查询语句，结果不依赖于外部查询（不在from语句中）

explain select orders.*,(select product_name from products where id = 10001) from orders;

2.4 dependent subquery：指在select语句中出现的查询语句，结果依赖于外部查询

explain select orders.*,(select product_name from products where id = orders.product_id) from orders;

2.5 derived：派生表

-- 关闭对衍生表合并优化
set session optimizer_switch='derived_merge=off';
-- 查看optimizer_switch参数
show variables like '%optimizer_switch%';
-- 
explain select * from (select user_id from orders where id = 10001) as temp;
-- 还原表合并优化
set session optimizer_switch='derived_merge=on';

2.6 union

1.union result：union关键字会将数据结果进行去重，会使用一个临时表，临时表的记录会被标记为union result

explain select * from (select id from products where product_price = 8000 union select id from orders where user_id = 20001 union select id from users where user_name = '张三' ) as temp;

2.7 union all

explain select * from (select id from products where product_price = 8000 union all select id from orders where user_id = 20001 union all select id from users where user_name = '张三' ) as temp;

2.8 dependent union

explain select * from orders where id in (select id from products where product_price = 8000 union all select id from orders where user_id = 20001
union all select id from users where user_name = '张三');

三、sql脚本

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for orders
-- ----------------------------
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders`  (
  `id` int(0) NOT NULL COMMENT '主键id',
  `price` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '订单总额',
  `user_id` int(0) DEFAULT NULL COMMENT '用户id',
  `product_id` int(0) DEFAULT NULL COMMENT '产品id',
  `number` int(0) DEFAULT NULL COMMENT '产品数量',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of orders
-- ----------------------------
INSERT INTO `orders` VALUES (10001, '80000', 20001, 10001, 10);

-- ----------------------------
-- Table structure for products
-- ----------------------------
DROP TABLE IF EXISTS `products`;
CREATE TABLE `products`  (
  `id` int(0) NOT NULL COMMENT '主键id',
  `product_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '商品名称',
  `product_price` decimal(10, 2) DEFAULT NULL COMMENT '商品价格',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of products
-- ----------------------------
INSERT INTO `products` VALUES (10001, '苹果手机', 8000.00);

-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users`  (
  `id` int(0) NOT NULL COMMENT '主键id',
  `user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '用户名称',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of users
-- ----------------------------
INSERT INTO `users` VALUES (20001, '张三');

SET FOREIGN_KEY_CHECKS = 1;