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

前言：前面已经学习了explain(执行计划)的相关知识，这里利用explain对索引进行优化分析。

---

0.准备

首先创建三张表：tb_emp(职工表)、tb_dept(部门表)和tb_desc(描述表)

1）tb_emp表。

DROP TABLE IF EXISTS `tb_emp`;

CREATE TABLE `tb_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 `tb_emp`(username,deptid) VALUES ('Tom', '1');

INSERT INTO `tb_emp`(username,deptid) VALUES ('Jack', '1');

INSERT INTO `tb_emp`(username,deptid) VALUES ('Mary', '2');

INSERT INTO `tb_emp`(username,deptid) VALUES ('Rose', '3');

2）tb_dept表。

DROP TABLE IF EXISTS `tb_dept`;

CREATE TABLE `tb_dept` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(20) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------

INSERT INTO `tb_dept`(name) VALUES ('综合部');

INSERT INTO `tb_dept`(name) VALUES ('研发');

INSERT INTO `tb_dept`(name) VALUES ('测试');

INSERT INTO `tb_dept`(name) VALUES ('总裁');

3）tb_desc表。

DROP TABLE IF EXISTS `tb_desc`;

CREATE TABLE `tb_desc` (

`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 `tb_desc`(empid,deptid) VALUES (1, 1);

INSERT INTO `tb_desc`(empid,deptid) VALUES (2, 1);

INSERT INTO `tb_desc`(empid,deptid) VALUES (3, 2);

INSERT INTO `tb_desc`(empid,deptid) VALUES (4, 3);

注：这里强行将员工表与部门表不直接关联，通过第三张表（描述表）进行关联，主要为了进行join的分析。

1.left join

#1.首先执行查询。

 

 

#2.通过explain进行分析。

 

 

分析：从explain执行结果可以看出对两表都是用了全表扫描（ALL），并且在tb_desc表中还使用了join连接缓存，需要进行优化。但是如何优化？是在左表建立索引还是右表建立索引呢？因为左连接左表是全有，所以应该在右表建立索引。

#3.右表创建索引。

 

 

通过explain执行可以看到，在创建索引后，获得了比较不错的结果。（type=ref,Extra=Using index）。

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

2.right join

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

 

 

分析：

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

#1.在left join下，首先执行tb_emp（左表），type=ALL，因为左连接情况下左表全有，因此我们在tb_desc（右表）创建索引，得到比较理想的效果。

#2.在right join下（我们交换了tb_emp和tb_desc的位置），执行顺序：tb_emp（右表）→ tb_desc（左表）。右表type=ALL，因为右连接情况下右表全有，因此在左表（tb_desc，我们交换了位置）创建索引，效果肯定和left join一样。

总结

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

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

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

---