1.建表:
1.1 class 商品类别表:
create table if not exists class(
id int(10) unsigned not null primary key auto_increment,
card int(10) unsigned not null
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
1.12 book 书本表:
create table if not exists book(
bookId int(10) unsigned not null primary key auto_increment,
card int(10) unsigned not null
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
1.13 插入数据:
随机生成card;
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 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));
2. 案例:
class 左连接 book:
explain select * from class left join book on class.card =book.card;
出现下面结果:
!结果:type为ALL
所以大胆假设,小心求证!
分别 添加 class(card)和 book(card)为索引试试!
建立索引不会的去看: 关于索引理解
两表的有难度也可以先看:单表
2.1 试试 book*(card)为索引
alter table book add index Y(card);
- 再次分析过程:
explain select * from class left join book on class.card =book.card;
!显然比不建立索引好了许多,右表的type 从 ALL—> ref
2.2 试试class(card)为索引
drop index Y on book; #先删除上一个索引,为了对比明显
alter table class add index Y(card); #建立class(card)索引
- 再次分析过程:
explain select * from class left join book on class.card =book.card;
!显然比不建立索引好了许多,左表的type 从 ALL—> index,但是行数rows却没有变化,没有吧索引建立在右表效果好
不清楚左右连接的看看:join连接查询
3.结论:
!从left join 的特性来看 ,左表是全查的,所以效果不大,对于left join 右表建立索引是关键!right join反之
drop index Y on class; #效果没有上一个明显,删了
alter table book add index Y(card); #建立book(card)索引
后边有三表的索引优化:三表优化