mysql 三个表索引优化案例

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 | type | possible_keys | key  | key_len | ref  | rows | Extra                                              |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
|  1 | SIMPLE      | class | ALL  | NULL          | NULL | NULL    | NULL |   20 | NULL                                               |
|  1 | SIMPLE      | book  | ALL  | NULL          | NULL | NULL    | NULL |   20 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | phone | ALL  | NULL          | NULL | NULL    | NULL |   20 | Using where; Using join buffer (Block Nested Loop) |

+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+

#依照两表优化原则建立索引

CREATE INDEX Y ON book(card);
CREATE INDEX Z ON phone(card);

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 | type | possible_keys | key  | key_len | ref             | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-----------------+------+-------------+
|  1 | SIMPLE      | class | ALL  | NULL          | NULL | NULL    | NULL            |   20 | NULL        |
|  1 | SIMPLE      | book  | ref  | Y             | Y    | 4       | mydb.class.card |    1 | Using index |
|  1 | SIMPLE      | phone | ref  | Z             | Z    | 4       | mydb.book.card  |    1 | Using index |
+----+-------------+-------+------+---------------+------+---------+-----------------+------+-------------+

#总结

join语句的优化

1）尽量减少join语句中NestedLoop的循环总次数；“永远用小的结果集驱动大的结果集”。

2）优先优化NestedLoop的内层循环；

3）保证join语句中被驱动表上join条件字段已经被索引；

4）当无法保证被驱动表的join条件字段被索引且内存资源充足的情况下，不要太吝惜join buffer的设置。