准备
//还有两个表的数据请看【双表优化】文章
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 |
+----+-------------+-------+------------+------+---------------+------------+---------+------------------------+------+----------+-------------+
- 尽可能减少JOIN语句中NestedLoop的循环总次数:"永远有小结果集驱动大结果集"
- 优先优化NestedLoop的内层循环
- 保证JOIN语句中被驱动表上JOIN条件字段已经被索引
- 当无法保证被驱动表的JOIN条件字段被索引且内存资源充足的前提下,不要在吝惜JoinBuffer的设置
版权属于:带翅膀的猫
本文链接:https://www.chengpengper.cn/archives/95/
转载时须注明出处及本声明