MySQL高级知识（六）——索引分析

此博客的内容主要来源于尚硅谷的视频中，在此记录，以备以后自己查看。

准备：

首先创建三张表：emp（职工表）、dept（部门表）、conn（描述表）。

1. emp表

DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `deptid` int(11) NOT NULL,
   PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
INSERT INTO `emp`(username,deptid) VALUES ('Tom', '1');
INSERT INTO `emp`(username,deptid) VALUES ('Jack', '1');
INSERT INTO `emp`(username,deptid) VALUES ('Mary', '2');
INSERT INTO `emp`(username,deptid) VALUES ('Rose', '3');

2. dept表

DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
INSERT INTO `dept`(name) VALUES ('综合部');
INSERT INTO `dept`(name) VALUES ('研发');
INSERT INTO `dept`(name) VALUES ('测试');
INSERT INTO `dept`(name) VALUES ('总裁');

3. conn表

DROP TABLE IF EXISTS `conn`;
CREATE TABLE `conn` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `empid` int(11) DEFAULT NULL,
  `deptid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
INSERT INTO `conn`(empid,deptid) VALUES (1, 1);
INSERT INTO `conn`(empid,deptid) VALUES (2, 1);
INSERT INTO `conn`(empid,deptid) VALUES (3, 2);
INSERT INTO `conn`(empid,deptid) VALUES (4, 3);

1. left join

• 首先执行查询
  
• 通过explain进行分析。
  
  分析：从explain执行的结果中可以看出对两个表都是使用了全表扫描（ALL），并且再conn表中还使用了join连接缓存，需要进行优化。

优化思考：该如何优化？是在左表建立索引还是右表建立索引？因为左连接的话左表全有，所以应该在右表建立索引。

• 右表创建索引
  
  通过explain执行可以看出，在创建索引后，获得了比较不错的效果。（type=ref，extra=Using index）

• 结论：left join（左连接）情况下，应该在右表（conn）创建索引。

2. right join

通过上面left join的例子，我们直接交换两个表的位置，并将left join 改变成right join。

与left join进行对比，可以得到如下结论：

1. 在left join下，首先执行emp，type=ALL，因为左连接情况下左表全有，因此我们在conn（右表创建索引）。
2. 在right join下，（我们交换emp和conn的位置），执行顺序：emp（右表）-> conn（左表）。右表type=ALL，因为右连接情况下右表全有，因此在左表（conn）创建索引，效果和left join一样。

3. 总结

• left join（左连接）：右表创建索引。

• right join（右连接）：左表创建索引。

• 简记：左右外连接，索引相反建（left：右表建，right：左表建）。