1.两表优化数据

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for class
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` int(10) UNSIGNED NOT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 21 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of class
-- ----------------------------
INSERT INTO `class` VALUES (1, 18);
INSERT INTO `class` VALUES (2, 19);
INSERT INTO `class` VALUES (3, 17);
INSERT INTO `class` VALUES (4, 8);
INSERT INTO `class` VALUES (5, 9);
INSERT INTO `class` VALUES (6, 19);
INSERT INTO `class` VALUES (7, 9);
INSERT INTO `class` VALUES (8, 5);
INSERT INTO `class` VALUES (9, 1);
INSERT INTO `class` VALUES (10, 7);
INSERT INTO `class` VALUES (11, 14);
INSERT INTO `class` VALUES (12, 8);
INSERT INTO `class` VALUES (13, 18);
INSERT INTO `class` VALUES (14, 4);
INSERT INTO `class` VALUES (15, 4);
INSERT INTO `class` VALUES (16, 11);
INSERT INTO `class` VALUES (17, 19);
INSERT INTO `class` VALUES (18, 4);
INSERT INTO `class` VALUES (19, 3);
INSERT INTO `class` VALUES (20, 20);

SET FOREIGN_KEY_CHECKS = 1;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for book
-- ----------------------------
DROP TABLE IF EXISTS `book`;
CREATE TABLE `book` (
`book_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` int(10) UNSIGNED NOT NULL,
PRIMARY KEY (`book_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 21 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of book
-- ----------------------------
INSERT INTO `book` VALUES (1, 14);
INSERT INTO `book` VALUES (2, 6);
INSERT INTO `book` VALUES (3, 10);
INSERT INTO `book` VALUES (4, 11);
INSERT INTO `book` VALUES (5, 3);
INSERT INTO `book` VALUES (6, 4);
INSERT INTO `book` VALUES (7, 11);
INSERT INTO `book` VALUES (8, 20);
INSERT INTO `book` VALUES (9, 8);
INSERT INTO `book` VALUES (10, 19);
INSERT INTO `book` VALUES (11, 10);
INSERT INTO `book` VALUES (12, 12);
INSERT INTO `book` VALUES (13, 11);
INSERT INTO `book` VALUES (14, 18);
INSERT INTO `book` VALUES (15, 17);
INSERT INTO `book` VALUES (16, 8);
INSERT INTO `book` VALUES (17, 10);
INSERT INTO `book` VALUES (18, 5);
INSERT INTO `book` VALUES (19, 15);
INSERT INTO `book` VALUES (20, 19);

SET FOREIGN_KEY_CHECKS = 1;

============================================================================运行上面的下面的都行==========================================

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

);

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

CREATE TABLE IF NOT EXISTS `book`(
`book_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY(`book_id`)
)

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

 2.分析

查询
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card=book.card

结论：  type都有ALL

问题：那么索引要加在哪里呢？是calss表中的card还是book表的card呢？

目前不知道（大胆假设小心求证）
现在book表中添加索引
CREATE INDEX index_card ON book(card)
查看索引
SHOW INDEX FROM book

查询
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card=book.card

结论：可以看到第二行type变为ref，rows也发生了明显的优化。

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

右连接同样就需要关心左边了！！！！

删除右表索引
DROP INDEX index_card ON book
在左表上加索引
CREATE INDEX index_card ON class(card)
查看索引
SHOW INDEX FROM class

查询
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card=book.card

结论：左右连接相反加