MySQL高级知识(五)——索引分析

扫码关注公众号:Java 技术驿站

发送:vip
将链接复制到本浏览器,永久解锁本站全部文章

【公众号:Java 技术驿站】 【加作者微信交流技术,拉技术群】

前言:前面已经学习了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.首先执行查询。

20191027300113\_1.png

#2.通过explain进行分析。

20191027300113\_2.png

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

#3.右表创建索引。

20191027300113\_3.png

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

结论:left join(左连接)情况下,应该在右表(tb_desc)创建索引。

2.right join

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

20191027300113\_4.png

分析:

与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日,晚。


相关内容

MySQL高级知识系列目录


来源:https://www.cnblogs.com/developer_chan

赞(0) 打赏
版权归原创作者所有,任何形式的转载请联系博主:daming_90:Java 技术驿站 » MySQL高级知识(五)——索引分析

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏