5.3 索引三表优化案例

最新推荐文章于 2025-05-21 16:32:50 发布

weixin_30799995

最新推荐文章于 2025-05-21 16:32:50 发布

阅读量76

点赞数

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/huanchupkblog/p/7453184.html

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