准备

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

案例

mysql> EXPLAIN SELECT class.id,class.card,book.bookid,book.card FROM class LEFT JOIN book ON class.card = book.card;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | class | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | NULL                                               |
|  1 | SIMPLE      | book  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
//type出现了ALL，不行

//建立索引，但是我们不知道是给哪个表建立索引，所以试两次咯
//先给book表建立索引
mysql> ALTER TABLE `book` ADD INDEX book_card(`card`);

//再次Explain
mysql> EXPLAIN SELECT class.id,class.card,book.bookid,book.card FROM class LEFT JOIN book ON class.card = book.card;
+----+-------------+-------+------------+------+---------------+-----------+---------+------------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref                    | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-----------+---------+------------------------+------+----------+-------------+
|  1 | SIMPLE      | class | NULL       | ALL  | NULL          | NULL      | NULL    | NULL                   |   20 |   100.00 | NULL        |
|  1 | SIMPLE      | book  | NULL       | ref  | book_card     | book_card | 4       | sqloptimize.class.card |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+-----------+---------+------------------------+------+----------+-------------+

//删除book表的索引，添加在class表上
mysql> DROP INDEX `book_card` ON book;

mysql> ALTER TABLE `class` ADD INDEX class_card(`card`);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

//再次Explain
mysql> EXPLAIN SELECT class.id,class.card,book.bookid,book.card FROM class LEFT JOIN book ON class.card = book.card;
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | class | NULL       | index | NULL          | class_card | 4       | NULL |   20 |   100.00 | Using index                                        |
|  1 | SIMPLE      | book  | NULL       | ALL   | NULL          | NULL       | NULL    | NULL |   20 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+----------------------------------------------------+
/**
对比两次优化，可以发现建立在右表上效果更好。
这是由左连接特性决定的。LEFT JOIN条件用于确定如何从右表搜索行，左边一定都有，所以右边是我们的关键点，一定要建立索引。
*/

mysql> EXPLAIN SELECT * FROM class RIGHT JOIN book ON class.card = book.card;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | book  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | NULL                                               |
|  1 | SIMPLE      | class | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+

//建立索引在class表上(左表)
mysql> ALTER TABLE class ADD INDEX class_card(`card`);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT * FROM class RIGHT JOIN book ON class.card = book.card;
+----+-------------+-------+------------+------+---------------+------------+---------+-----------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key        | key_len | ref                   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------------+---------+-----------------------+------+----------+-------------+
|  1 | SIMPLE      | book  | NULL       | ALL  | NULL          | NULL       | NULL    | NULL                  |   20 |   100.00 | NULL        |
|  1 | SIMPLE      | class | NULL       | ref  | class_card    | class_card | 4       | sqloptimize.book.card |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------------+---------+-----------------------+------+----------+-------------+

//建立索引在book表上(右表)
mysql> ALTER TABLE book ADD INDEX book_card(`card`);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT * FROM class RIGHT JOIN book ON class.card = book.card;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | book  | NULL       | index | NULL          | book_card | 4       | NULL |   20 |   100.00 | Using index                                        |
|  1 | SIMPLE      | class | NULL       | ALL   | NULL          | NULL      | NULL    | NULL |   20 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------------------------+

结论

版权属于：带翅膀的猫

本文链接：https://www.chengpengper.cn/archives/94/

转载时须注明出处及本声明