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:左表建)。
by Shawn Chen,2018.6.23日,晚。