Mysql进阶--索引三表优化

单表和量表的优化已经完成，这里再进行三表的优化，前面单表和两表优化有不懂的请参看：

Mysql进阶-索引单表优化

Mysql进阶-索引两表优化

1.首先删除class的card索引，再查看两张表的索引情况，保证没有索引，为三表索引测试做准备，

drop index X on class;

2.再建一张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;

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)));

3.首先三表连查，查询条件为class.card = book.card和book.card = phone.card，执行语句，

SELECT * FROM class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.card;

4.还是先对Sql进行一下性能分析查看，拉满，三张表全部全表检索，这还是数据量小，如果百万数据，，，

分析其原因：

• type 为All ，rows 为表数据总行数，说明 class、 book 和 phone 表都进行了全表检索
• Extra 中 Using join buffer ，表明连接过程中使用了 join 缓冲区

Explain SELECT * FROM class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.card;

5.建立索引，因为class表为驱动表，所以给book表和phone表分别建立card字段的索引，

ALTER TABLE book ADD INDEX Y (card);
ALTER TABLE phone ADD INDEX Z (card);

6.再来性能分析，可以看到，book表和phone表的type都变为了ref，而rows也变为了21+1+1，Extra也没有了Using join buffer，ref可以看到两个索引字段，优化效果良好，因此索引最好设置在需要经常查询的字段中，

总结，Join语句的优化：

将 left join 看作是两层嵌套 for 循环

1. 尽可能减少Join语句中的NestedLoop的循环总次数；“永远用小结果集驱动大的结果集”（在大结果集中建立索引，在小结果集中遍历全表）；
2. 优先优化NestedLoop的内层循环；(相当于外层 for 循环的次数少，内层 for 循环的次数多)
3. 保证Join语句中被驱动表上Join条件字段已经被索引；
4. 当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝惜JoinBuffer的设置；

综上所述，使用小表驱动大表，在大表中建立了索引