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的设置。