准备

//还有两个表的数据请看【双表优化】文章

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

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

案例

mysql> EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.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) |
|  1 | SIMPLE      | phone | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+

//将三个表的左连接拆分开来
//①、class LEFT JOIN book, book建立索引
//②、[...] LEFT JOIN phone, phone建立索引
mysql> ALTER TABLE book ADD INDEX book_card(`card`);
mysql> ALTER TABLE phone ADD INDEX phone_card(`card`);

//再次Explain
mysql> EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.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 |
|  1 | SIMPLE      | phone | NULL       | ref  | phone_card    | phone_card | 4       | sqloptimize.book.card  |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------------+---------+------------------------+------+----------+-------------+

版权属于：带翅膀的猫

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

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