目录

双表建表

双表查询语句分析

三表建表

 查询语句分析

JOIN语句优化总结（简洁版）

双表建表

建表语句：

CREATE TABLE IF NOT EXISTS `class`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
CREATE TABLE IF NOT EXISTS `book`(
`bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);

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

这是两张简单的表book和class

book表一览：

 class表一览：

双表查询语句分析

由book和class表可见有card字段可相关联，因此可以做一个连接查询，我们对book和class联合查询进行分析：

explain select * from class t1 left join book t2 on t1.card = t2.card

可以由结果得知，type类型为ALL，双表都走了全表扫描。

要优化全表扫描，首先我们需要建立索引，card是两表的连接字段，应为其建立索引，那么问题来了，该为哪个表的card建立索引呢？

我们可以都试试看，先为class表的card字段建立索引，然后为book表的card字段建立索引。

CREATE INDEX inx_class_card ON class(card);

可以看到优化后type类型为index了。

然后删掉 class表的card字段的索引，为book表的card字段建立索引。

DROp INDEX inx_class_card ON class
CREATE INDEX inx_book_card ON book(card);

type类型由ALL变成了ref，它的效果比index好，得到了更好的优化。

为什么给book表的card字段建立索引的效果比class表的card字段效果好？

这是由左连接的特性决定的。LEFT JOIN 条件用于确定如何从右表搜索行（使用LEFT JOIN  左表有值，右表不一定有），所以右边是我们的关键点，一定需要建立索引。

话又说回来，数据库索引如果是固定的（假设是DBA建立好的，不能随意更改），也就是说，虽然card字段在book表上建立比在class表上建立索引有更好的优化，但索引就是简历在class表的card字段上的。那该怎么办？

索引在左表class上，把查询语句换成RIGHT JOIN 即可（RIGHT JOIN 条件用于确定如何从左表搜索行，右边一定都有，所以左表是我们的关键点，要为其建立索引）

删除掉原表索引，重新为class表card字段建立索引后，改用右连接explain测试：

DROP INDEX inx_book_card ON book
CREATE INDEX inx_class_card ON class(card);
explain select * from class t1 RIGHT JOIN  book t2 on t1.card = t2.card

 查询结果：可以看到type类型为ref

三表建表

新建表phone

CREATE TABLE IF NOT EXISTS `phone`(
`phoneid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
)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)));

 phone表一览，可见card字段与双表一致。

 查询语句分析

先删掉之前双表上的card字段的索引，目前三表没有索引。

DROp INDEX inx_book_card ON book
DROP INDEX inx_class_card ON class

EXPLAIN
SELECT
	*
FROM
	class t1
LEFT JOIN book t2 ON t1.card = t2.card
LEFT JOIN phone t3 on t2.card = t3.card

执行结果type都为ALL类型

开始优化，既然card字段是连接字段，应为其建立索引，考虑到用到LEFT JOIN ，因此为右表建立card字段的索引。

CREATE INDEX inx_book_card ON book(card);
CREATE INDEX inx_phone_card ON phone(card);

再次查看执行计划，可以看到后两行的type类型为ref，且总rows优化很好，效果不错。

 

JOIN语句优化总结（简洁版）

多表查询中，连接字段作为索引，根据连接类型的特性，把索引建立在不一定有值的那一边是更为优化的选择，比如左连接，把索引建立在右表上，右连接，把索引建立在左表上。

尽可能减少JOIN语句中NestedLoop的循环总次数：永远用小的结果集驱动大的结果集（比如class表（类目表）相对于book（书籍表），class是小表，将class作为主表来驱动大表，假设class有一千条数据，而book表有上百万数据，用小表驱动大表即可减少系统IO次数，因为大表数据多，IO频率高）。

优先优化NestedLoop的内层循环（最先执行的表如果快了，那么才能最快轮到后执行的表）

保证Join语句中被驱动表上Join条件字段已经被索引（本博文举的例子已经很好说明）

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