Mysql执行计划与索引使用最佳实践

实践用全部建表语句

CREATE TABLE IF NOT EXISTS `actor` (
  `id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 正在导出表  test.actor 的数据：~3 rows (大约)
/*!40000 ALTER TABLE `actor` DISABLE KEYS */;
INSERT INTO `actor` (`id`, `name`, `update_time`) VALUES
	(1, 'a', '2022-03-13 16:59:55'),
	(2, 'b', '2022-03-13 17:00:13'),
	(3, 'c', '2022-03-13 17:00:21');
/*!40000 ALTER TABLE `actor` ENABLE KEYS */;

-- 导出  表 test.employees 结构
CREATE TABLE IF NOT EXISTS `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

-- 正在导出表  test.employees 的数据：~2 rows (大约)
/*!40000 ALTER TABLE `employees` DISABLE KEYS */;
INSERT INTO `employees` (`id`, `name`, `age`, `position`, `hire_time`) VALUES
	(4, 'LiLei', 22, 'manager', '2022-03-14 10:49:49'),
	(5, 'HanMeimei', 23, 'dev', '2022-03-14 10:49:49'),
	(6, 'Lucy', 23, 'dev', '2022-03-14 10:49:49');
/*!40000 ALTER TABLE `employees` ENABLE KEYS */;

-- 导出  表 test.film 结构
CREATE TABLE IF NOT EXISTS `film` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- 正在导出表  test.film 的数据：~3 rows (大约)
/*!40000 ALTER TABLE `film` DISABLE KEYS */;
INSERT INTO `film` (`id`, `name`) VALUES
	(3, 'film0'),
	(1, 'film1'),
	(2, 'film2');
/*!40000 ALTER TABLE `film` ENABLE KEYS */;

-- 导出  表 test.film_actor 结构
CREATE TABLE IF NOT EXISTS `film_actor` (
  `id` int(11) NOT NULL,
  `film_id` int(11) NOT NULL,
  `actor_id` int(11) NOT NULL,
  `remark` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_film_actor_id` (`film_id`,`actor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 正在导出表  test.film_actor 的数据：~3 rows (大约)
/*!40000 ALTER TABLE `film_actor` DISABLE KEYS */;
INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`, `remark`) VALUES
	(1, 1, 1, NULL),
	(2, 1, 2, NULL),
	(3, 2, 1, NULL);