5.3 索引三表优化案例

1、案例

#删除之前建立的索引
drop index X on class;

CREATE TABLE IF NOT EXISTS `phone`(
	`phoneid` int(10) UNSIGNED NOT NULL auto_increment,
	`card` int(10) UNSIGNED NOT NULL,
	PRIMARY KEY(`phoneid`)
);

INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));

#explain 分析sql
EXPLAIN SELECT * from class LEFT JOIN book on class.card = book.card LEFT JOIN phone on class.card = phone.card;

结论：都是All ,需要优化。

2、优化

#建立索引
ALTER TABLE `phone` ADD INDEX Z(`card`);
ALTER TABLE `book` ADD INDEX Y(`card`);
#再次分析sql
EXPLAIN SELECT * from class LEFT JOIN book on class.card = book.card LEFT JOIN phone on class.card = phone.card;

【结论】：

join语句的优化
尽可能减少join语句中的NestedLoop的循环总次数；“ 永远用小的结果集驱动大的结果集 ”。
优先优化NestedLoop的内层循环。
保证join语句中被驱动表上join条件字段已经被索引；

当无法保证被驱动的join条件字段被索引且内存资源充足的前提下，不要吝啬 JoinBuffer 的设置。

关注我的公众号，精彩内容不能错过