【mysql】explain执行计划之id列

一、说明

• 1.select查询的序号
• 2.查询中执行select子句或者操作表的顺序
• 3.id相同，执行顺序从上到下
• 4.id不相同，id值越大越先执行
• 5.既有id相同也有id不同的情况，先执行序号大的，再同级从上往下执行
• 6.id列显示为null的最后执行。表示结果集，不需要使用它来进行查询
• 7.优化器会针对子查询进行一定的优化重写sql

二、示例

2.1 id相同，执行顺序从上到下

-- id相同，执行顺序从上到下
explain select * from users inner join orders on users.id = orders.user_id inner join products on orders.product_id = products.id

2.2 id不相同，id值越大越先执行

-- id不同，序号大的先执行
explain select * from orders where orders.product_id = (select id from products where products.product_name = '苹果手机');

2.3 既有id相同也有id不同的情况，先执行序号大的，再同级从上往下执行

-- 关闭表合并优化
set session optimizer_switch='derived_merge=off';
-- 查看optimizer_switch参数
show variables like '%optimizer_switch%';
-- 
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';

2.4 id列显示为null的最后执行。表示结果集，不需要使用它来进行查询

-- null表示结果集，不需要使用它来进行查询
explain select id from users union select id from products

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

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

使用show warnings;查看优化后的sql
/* select#1 */ select '20001' AS `id`,'张三' AS `user_name` from `explain`.`orders` join `explain`.`users` where true

三、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;