索引优化——多表优化

use runoob

DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT  ,
  `card` varchar(32) DEFAULT NULL  ,
  
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `book`;
CREATE TABLE `book` (
  `bookid` bigint(20) NOT NULL AUTO_INCREMENT  ,
  `card` varchar(32) DEFAULT NULL  ,
  
  PRIMARY KEY (`bookid`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8;

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

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


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

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

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

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


select * from  class;

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

select * from book;


select * from book inner join class on book.card=class.card;


 explain select * from class left join book on class.card=book.card;

alter table  book add index y(card);


 explain select * from class left join book on class.card=book.card;

drop index y on book;

/*左连接加上右表*/
alter table  class add index y(card);
explain select * from class left join book on class.card=book.card;
drop index y on class;

show index from class;
show index from book;
/*-------------------------------*/

explain select * from class right join book on class.card=book.card;
alter table  book add index y(card);
drop index y on book;

explain select * from class right join book on class.card=book.card;
alter table  class add index y(card);
drop index y on class

三表连接：使用了join bubuffer 。

use runoob

DROP TABLE IF EXISTS `phone`;
CREATE TABLE `phone` (
  `phoneid` bigint(20) NOT NULL AUTO_INCREMENT  ,
  `card` varchar(32) DEFAULT NULL  ,
  
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8;


show index from class;
show index from book;


explain select * 
from class left join book
on class.card=book.card
left join phone 
on book.card=phone.card;


alter table  phone add index z(card);
alter table  book add index z(card);