双索引 mysql_mysql-双表索引优化

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