建表SQL

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) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO class(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO class(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO class(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO class(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO class(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO class(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO class(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO class(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO class(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO class(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO class(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO class(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO class(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO class(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO class(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO class(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO class(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO class(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO class(card) VALUE(FLOOR(1 + RAND() * 20));

INSERT INTO book(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO book(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO book(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO book(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO book(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO book(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO book(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO book(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO book(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO book(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO book(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO book(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO book(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO book(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO book(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO book(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO book(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO book(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO book(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO book(card) VALUE(FLOOR(1 + RAND() * 20));

LEFT JOIN

Paste_Image.png Paste_Image.png Paste_Image.png

给左边表（class）加索引出现如下：

Paste_Image.png
可以发现rows检索的行数没变。而且是index索引，索引全部。

给由边表（book）加索引出现如下：

Paste_Image.png
可发现只检索1行，而且是ref，效率极高。
所以加到右表（book）最为合适。

RIGHT JOIN

3.png

发现结果正好相反。

得出结论 ：若为左连接，则把索引加到第二张表上的连接字段。若为右连接，则把索引加到第一张表的连接字段。

若有兴趣，欢迎来加入群，【Java初学者学习交流群】：458430385，此群有Java开发人员、UI设计人员和前端工程师。有问必答，共同探讨学习，一起进步！
欢迎关注我的微信公众号【Java码农社区】，会定时推送各种干货：

qrcode_for_gh_577b64e73701_258.jpg