mysql 两个表索引优化案例

1、EXPLAIN SELECT * FROM class LEFT JOIN book on class.card = book.card;

mysql>  EXPLAIN SELECT * FROM class LEFT JOIN book on class.card = book.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) |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+

#添加索引（左联接加到右表）

CREATE INDEX Y ON book(card);

mysql> EXPLAIN SELECT * FROM class LEFT JOIN book on class.card = book.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 |
+----+-------------+-------+------+---------------+------+---------+-----------------+------+-------------+

#删除上一个所以，增加到左表中

DROP INDEX Y ON book;

CREATE INDEX Y ON class(card);

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

#得出结论：type一个是ref，一个是index，ref好于index

这是由左连接的特性决定的，LEFT JOIN 条件用于确定如何从右表搜索行，左表一定都有，所以右表是我们关注的重点，一定需要建立索引。

即：左连接右表要加索引。右连接原理相同。