Mysql 5.6优化器在小表连接中不使用索引

Question

我们有两个表 - 第一个是比较大的（联系人表）250k行，第二个是小的（用户表，小于10行）。在mysql 5.6版本上，我得到了以下的解释结果：

EXPLAIN SELECT  
  o0_.id AS id_0,  
  o8_.first_name, 
  o8_.last_name 
FROM  
  contact o0_  
  LEFT JOIN user o8_ ON o0_.user_owner_id = o8_.id  
LIMIT  
  25 OFFSET 100

+----+-------------+-------+-------+---------------+----------------------+---------+------+--------+----------------------------------------------------+
| id | select_type | table | type  | possible_keys | key                  | key_len | ref  | rows   | Extra                                              |
+----+-------------+-------+-------+---------------+----------------------+---------+------+--------+----------------------------------------------------+
|  1 | SIMPLE      | o0_   | index | NULL          | IDX_403263ED9EB185F9 | 5       | NULL | 253030 | Using index                                        |
|  1 | SIMPLE      | o8_   | ALL   | PRIMARY       | NULL                 | NULL    | NULL |      5 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+-------+---------------+----------------------+---------+------+--------+----------------------------------------------------+

结果集中有2行 (用时0.00秒)

当我使用 force index 进行连接时：

EXPLAIN SELECT  
  o0_.id AS id_0,  
  o8_.first_name, 
  o8_.last_name 
FROM  
  contact o0_  
  LEFT JOIN user o8_ force index for join(`PRIMARY`) ON o0_.user_owner_id = o8_.id  
LIMIT  
  25 OFFSET 100

或者在用户表(user table)的select子句中出现的字段（first_name，last_name）上添加索引：

alter table user add index(first_name, last_name);

解释以下结果发生的变化：

    +----+-------------+-------+--------+---------------+----------------------+---------+-------------------------+--------+-------------+
| id | select_type | table | type   | possible_keys | key                  | key_len | ref                     | rows   | Extra       |
+----+-------------+-------+--------+---------------+----------------------+---------+-------------------------+--------+-------------+
|  1 | SIMPLE      | o0_   | index  | NULL          | IDX_403263ED9EB185F9 | 5       | NULL                    | 253030 | Using index |
|  1 | SIMPLE      | o8_   | eq_ref | PRIMARY       | PRIMARY              | 4       | o0_.user_owner_id |      1 | NULL        |
+----+-------------+-------+--------+---------------+----------------------+---------+-------------------------+--------+-------------+
    2 rows in set (0,00 sec)

在mysql 5.5版本中，如果没有额外的索引，我得到了相同的解释结果。

    +----+-------------+-------+--------+---------------+----------------------+---------+-------------------------+--------+-------------+
| id | select_type | table | type   | possible_keys | key                  | key_len | ref                     | rows   | Extra       |
+----+-------------+-------+--------+---------------+----------------------+---------+-------------------------+--------+-------------+
|  1 | SIMPLE      | o0_   | index  | NULL          | IDX_403263ED9EB185F9 | 5       | NULL                    | 255706 | Using index |
|  1 | SIMPLE      | o8_   | eq_ref | PRIMARY       | PRIMARY              | 4       | o0_.user_owner_id |      1 |             |
+----+-------------+-------+--------+---------------+----------------------+---------+-------------------------+--------+-------------+
2 rows in set (0.00 sec)

为什么在mysql 5.6版本中需要强制使用PRIMARY索引或添加额外的索引？当连接小表时，其他选择也会出现相同的行为。

- Max

Answer 1

如果您的表只有很少的行，那么执行全表扫描可能比使用索引查找记录再返回表更快。如果用户表中除了查询中的三个字段之外还有其他字段，则可以考虑添加覆盖索引，但实际上，我认为这些都不会对查询速度产生显著影响。

- Shadow

---

这个问题是全表扫描连接对性能的影响非常大：使用索引为0.04秒，而全表扫描需要3.61秒。 - Max

然后保留索引提示。 - Shadow