MySQL高级---B站学习总结---索引优化两表案例-CSDN博客

本文链接：https://blog.csdn.net/qq_39314972/article/details/107010186

MySQL高级—B站学习总结—索引优化两表案例

案例：

创建表

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

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

下面开始explain分析
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;

结论： type 有All
添加索引优化
ALTER TABLE book ADD INDEX Y ( card);
第2次explain的EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;

可以看到第二行的 type 变为了 ref,rows 也变成了优化比较明显。
删除上个索引，尝试使用创建的class表的索引
DROP INDEX Y ON book;
ALTER TABLE class ADD INDEX Y ( card);
执行EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
结果是如下图

结论：所以说由左连接特性决定的。LEFT JOIN(左连接) 条件用于确定如何从右表搜索行,左边一定都有,所以右边是我们的关键点,一定需要建立索引。所以说RIGHT JOIN(右连接)同理，条件用于确定如何从左表搜索行,右边一定都有,所以左边是我们的关键点,一定需要建立索引。