准备
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/
转载时须注明出处及本声明