专栏首页JavaEEMySQL连接查询索引优化

MySQL连接查询索引优化

一、单表索引优化

  • 建表:

建一张表,SQL如下:

create table `tb_article`(
  `id` int not null primary key auto_increment comment '主键',
  `author_id` int not null comment '作者id',
  `category_id` int not null comment '文章类别id',
  `views` int not null comment '阅读量',
  `comments` int not null comment '评论量',
  `title` varchar(200) not null comment '文章标题',
  `content` text not null comment '文章内容'
) comment '文章表';
 insert into tb_article(author_id,category_id, views, comments, title, content) values(1,1,1,1,1,1);
 insert into tb_article(author_id,category_id, views, comments, title, content) values(2,2,2,2,2,2);
 insert into tb_article(author_id,category_id, views, comments, title, content) values(1,1,3,3,3,3,);
  • 需求:

查询出 类别id为1 且 评论量大于1的情况下,阅读量最多的那篇文章的作者id。

SQL写法如下:

select id, author_id  
from tb_article 
where category_id = 1 and comments > 1 
order by views desc 
limit 1;

用explain分析一下,发现这条SQL问题很大:

执行计划

首先没有使用索引,type是all,然后用了文件内排序,using filesort。这两个都是严重影响性能的,那么接下来就建索引。

之前说过,where后面的字段,以及order by后面的字段,最好都要用索引,所以建立如下索引:create index idx_ccv on tb_article(category_id, comments, views);

即用这三个字段建立了一个复合索引。接下来再看上面那条查询SQL的执行计划。

建索引后的执行计划

见鬼了,怎么还有using filesort呢?我排序字段不是建了索引了吗?假如,把comment 大于1改成等于1,看看什么情况:

comment等于1

发现using filesort神奇地消失了。这是因为,comment大于1是一个范围,而comment等于1是常量,范围后面的索引是会失效的,即使用comment大于1的时候,order by后面根本没用到索引,因为失效了。

那说明我们建的这个索引不太合适,干掉它:drop index idx_ccv on tb_article;

既然comment大于1会导致后面的索引失效,那如果绕开它,只对category_id和views建索引会怎样呢?即create index idx_cv on tb_article(category_id, views);

索引优化后

可以看到,用到了索引,也没有文件内排序了。

  • 结论:如果范围查询的字段跟其他字段一起建立了复合索引,那么范围查询字段后面字段的索引会失效。解决办法可以绕过该字段。

二、两表索引优化

上面是单表,这里来看看连接查询的情况。

  • 建表:
create table `tb_novel`(
  `id` int not null primary key auto_increment comment '主键',
  `title` varchar(100) not null comment '小说名'
) comment '小说';

create table `tb_character`(
  `id` int not null primary key auto_increment comment '主键',
  `name` varchar(100) not null comment '人物名',
  `novel_id` int not null comment '归属于的小说id'
) comment '人物';

create table `tb_kongfu`(
  `id` int not null primary key auto_increment comment '主键',
  `kongfu_name` varchar(100) not null comment '功夫的名字',
  `novel_id` int not null comment '小说的id'
)comment '功夫';

insert into tb_novel(title)values('天龙八部');
insert into tb_novel(title)values('射雕英雄传');
insert into tb_novel(title)values('神雕侠侣');
insert into tb_novel(title)values('倚天屠龙记');

insert into tb_character(name, novel_id) values('乔峰',1);
insert into tb_character(name, novel_id) values('扫地僧',1);
insert into tb_character(name, novel_id) values('洪七公',2);
insert into tb_character(name, novel_id) values('郭靖',2);
insert into tb_character(name, novel_id) values('金轮法王',3);
insert into tb_character(name, novel_id) values('小龙女',3);
insert into tb_character(name, novel_id) values('赵敏',4);
insert into tb_character(name, novel_id) values('灭绝老尼',4);

insert into tb_kongfu(kongfu_name, novel_id) values('北冥神功', 1);
insert into tb_kongfu(kongfu_name, novel_id) values('六脉神剑', 1);
insert into tb_kongfu(kongfu_name, novel_id) values('落英神剑掌', 2);
insert into tb_kongfu(kongfu_name, novel_id) values('北斗七星阵', 2);
insert into tb_kongfu(kongfu_name, novel_id) values('黯然销魂掌', 3);
insert into tb_kongfu(kongfu_name, novel_id) values('龙翔般若功', 3);
insert into tb_kongfu(kongfu_name, novel_id) values('乾坤大挪移', 4);
insert into tb_kongfu(kongfu_name, novel_id) values('九阴白骨爪', 4);
  • 需求:

查询出属于同一部小说的人物名和功夫的名字。

SQL写法如下:

select c.name,f.kongfu_name from tb_character c left join tb_kongfu f on c.novel_id = f.novel_id;

差不多就这个意思,反正就是两表连接,但是不用主键去关联。

来看一下这条sql的执行计划:

执行计划

可以看到,type都是all,因为我们并没有建索引。我们是用novel_id连接的,那么,我是在tb_character表的novel_id上建索引还是在tb_kongfu表的novel_id上建索引呢?

首先在tb_character表的novel_id上建索引,create index idx_novel_id on tb_character(novel_id);,然后再查看执行计划。

执行计划

可以看到,都是all,并没有走索引。

那么我们把tb_character的索引删除,drop index idx_novel_id on tb_character;,加在右表tb_kongfu上试试,create index idx_novel_id on tb_kongfu(novel_id);,再看执行计划:

执行计划

可以看到,这次走索引了,首先左表是驱动表,左连接就是左边都要查出来,所以左边还是all,但是右边是ref了。

我们不妨把tb_kongfu表的索引删除,再把tb_character表的索引加上去,然后将left join改成right join,再看执行计划:

执行计划

可以看到,也走了索引。

  • 结论:左连接的时候索引应该加在右表,右连接应该加在左表。

三、三表索引优化

三表和两表其实没什么差别,比如:

select * from A left join B on A.key = B.key left join C on A.key = C.key;

这里都是left join,且有三表,那么首先应该在B表的key上加索引,A和B连接的结果看成是一个临时表,再和C连接,因此C表的key也应该加上索引。

四、exists和in

连接查询的时候,永远要用小表驱动大表。比如下面的语句:

select * from A where id in (select id from B)

这条语句是先执行select id from B的,然后用它去驱动A表的,当B表的数据少于A表时,用in的效率是更高的。

再看这条语句:

select * from A where exists (select 1 from B where B.id = A.id)

这条语句呢是先执行select * from A,然后用查出来的结果集去驱动B表的,当A表的数据更少时,这样写的效率是比用in更高的。

五、优化结论

  • 连接查询,永远要用小表驱动大表,即用数据少的表作为驱动表。比如A表数据很少,B表很多,要左连接的话,那么应该是 A left join B。
  • 优先优化嵌套循环(nested loop)的内层循环。
  • 保证join语句中被驱动表上的join条件字段加了索引。
  • 无法保证被驱动表的join条件字段加索引且内存充足的情况下,可以加大joinBuffer的设置。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MySQL连接查询&索引介绍

    但是MySQL执行的时候,并不是按顺序执行的,MySQL执行sql语句是从from开始执行的,上面这条语句的执行顺序是:

    贪挽懒月
  • MySQL索引和查询优化

    基数是数据列所包含的不同值的数量,例如,某个数据列包含值 1、3、7、4、7、3,那么它的基数就是 4。

    网络技术联盟站
  • MySQL--索引及优化查询

    通过不断的缩小要查询的数据的范围来筛选出最终想要的结果,同时将随机的事件变成顺序事件。

    lpe234
  • MySQL-性能优化-索引和查询优化

    用户1263954
  • MySQL 索引及查询优化总结

    本文主要讨论 MySQL 索引原理及常用的 sql 查询优化。

    谢庆玲
  • 数据库查询优化——Mysql索引

    工作一年了,也是第一次使用Mysql的索引。添加了索引之后的速度的提升,让我惊叹不已。隔壁的老员工看到我的大惊小怪,平淡地回了一句“那肯定啊”。

    全栈程序员站长
  • mysql:索引原理与慢查询优化

    一 索引的原理 1. 索引原理 索引的目的在于提高查询效率,与我们查阅图书所用的目录是一个道理:先定位到章,然后定位到该章下的一个小节,然后找到页数。相似的例子...

    用户1214487
  • MySQL索引原理及慢查询优化

    MySQL凭借着出色的性能、低廉的成本、丰富的资源,已经成为绝大多数互联网公司的首选关系型数据库。

    Java架构师必看
  • MySQL索引原理以及查询优化

    一般的应用系统,读写比例在10:1左右,而且插入操作和一般的更新操作很少出现性能问题,在生产环境中,我们遇到最多的,也是最容易出问题的,还是一些复杂的查询操作,...

    yaphetsfang

扫码关注云+社区

领取腾讯云代金券