索引3三表优化

背景：

索引2双表优化_zew1040994588的博客-优快云博客

过程：

CREATE TABLE IF NOT EXISTS `phone`(
`phoneid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
)ENGINE = INNODB;

insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));

select * from phone;
show index from class;
alter table class drop index y; 
-- 这个会出现using buffer[mysql用了连接缓存]
explain select * from class inner join book on class.card=book.card
inner join phone on book.card = phone.card;
-- 工作中left join和 right join居多--我们以此来建立索引[测试用left join]
explain select * from class left join book on class.card=book.card
left join phone on book.card = phone.card;
alter table book add index z(card);
alter table phone add index y(card);

三表优化其实本质上是两表优化

join语句的优化
尽可能减少join语句中的NestedLoop的循环总次数:"永远用小结果集驱动大的结果集"
1-永远记得小表驱动大表！
比如存放书籍类的表来驱动存放书籍的表
2-优先NestedLoop[嵌套]的内层循环
先保证内层能够快速执行
3-保证join语句中被驱动表上Join条件字段已经被索引
4-当无法保证被驱动表的join条件字段被索引且
内存资源充足的前提下,不要太吝惜JoinBuffer的设置！