数据准备
CREATE TABLE IF NOT EXISTS `class` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`card` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS `book` (
`bookid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`card` int(10) unsigned NOT NULL,
PRIMARY KEY (`bookid`)
);
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)));
两表关联
左连接优化
1、一个左连接查询
explain select * from class left join book on class.card = book.card;
image.png
两个select的type都是ALL
2、在右表book表的card字段上建立索引
ALTER TABLE `book` ADD INDEX y ( `card`);
3、查看索引情况
SHOW INDEX FROM book
image.png
4、再次查看执行计划
explain select * from class left join book on class.card = book.card;
image.png
往左连接的右表上加索引效果非常理想!
5、删除旧索引,在class表上加上新索引。
DROP INDEX y ON book;
ALTER TABLE `class` ADD INDEX x ( `card`);
查询索引情况
SHOW INDEX FROM book
image.png
6、再次查看执行计划
explain select * from class left join book on class.card = book.card;
image.png
在left join的左表class添加索引,效果并不理想!
右连接优化
1、接着上面的操作,class表里添加了索引。
SHOW INDEX FROM class
image.png
查看执行计划
explain select * from class right join book on class.card = book.card;
image.png
2、将class表和table表调换位置,再次查看执行计划
explain select * from book right join class on class.card = book.card;
image.png
三表关联
1、在上面的表结构和基础上,继续创建一个phone表
CREATE TABLE IF NOT EXISTS `phone` (
`phoneid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`card` int(10) unsigned NOT NULL,
PRIMARY KEY (`phoneid`)
) engine = innodb;
2、book、phone 两表添加索引
ALTER TABLE `book` ADD INDEX y ( `card`);
ALTER TABLE `phone` ADD INDEX z ( `card`);
3、查看执行计划
explain select * from class left join book on class.card=book.card left j
oin phone on book.card = phone.card;
image.png
后 2 行的 type 都是 ref 且总 rows 优化很好,效果不错。因此索引最好设置在需要经常查询的字段中
join的优化:
1、小表驱动大表;left join的小表放到左边;因此这里使用class驱动book再是phone ;
2、尽可能减少join语句中的NestedLoop的循环总次数;
3、优先优化NestedLoop的内层循环;
4、保证join语句中“被驱动表”上join条件字段已经被索引(key字段非NULL);
5、当无法保证“被驱动表”的join条件字段被索引且内存资源充足的前提下,不要太吝惜JoinBuffer的设置;修改my.conf文件
结论
多表join添加索引原则:左连接加右表,右连接加左表
不要添加多余的索引
image.png