1. 样例数据:
version:mysql 8.0.19
create table if not exists `class`(
`id` INT(10) unsigned NOT NULL auto_increment,
`card` INT(10) unsigned NOT NULL,
primary key(`id`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
create table if not exists `book`(
`bookid` INT(10) unsigned NOT NULL auto_increment,
`card` INT(10) unsigned NOT NULL,
primary key(`bookid`)
)ENGINE=INNODB AUTO_INCREMENT=1 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)));
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 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)));
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)));
除了主键并没有任何索引,所以
explain select * from book left join class on book.card = class.card;
初始索引状态.png
type为all,需要优化。
1. 测试优化方案一
在左边的book创建card索引,看看如何
create index idx_card on book(card);
show index from book;
book-索引.png
explain select * from book left join class on book.card = class.card;
此时
检测.png
book的索引ALL提升为index,但是rows两行,需要检索的行数,仍然是21-24,没有得到提升。
2. 测试优化方案二
去除book表上的索引,添加在class的card列
drop index idx_card on book;
create index idx_card on class(card);
explain select * from book left join class on book.card = class.card;
class-card索引.png
当id都为1时,相等,则执行顺序为由上而下,rows,需检索行数分别为21和2,类型由ALL转为ref,有了不小的提升。
这是由于左连接的特性
左连接.png
a作为主表,a的记录一定全部返回,能提升的部分,由b部分决定,一定要对b表的检索方式进行优化,故此b表需要建立索引。
永远用小的结果集驱动大的结果集,小表放左边,大表放右边。
当无法保证b表join条件字段被索引且内存资源充足的前提下,不要太吝啬joinBuffer的设置。