为什么解释说竞赛表使用 ALL 类型，尽管竞赛表有 chid 作为主键？

Question

为什么解释说竞赛表使用 ALL 类型，尽管竞赛表有 chid 作为主键？

---

mysql> explain SELECT contests.chid, contests.diff_level from contests, contest_users where contests.chid=contest_users.chid;          


+----+-------------+-----------------+------+---------------------+---------------------+---------+-------------------------------+-------+-------------+
| id | select_type | table           | type | possible_keys       | key                 | key_len | ref                           | rows  | Extra       |
+----+-------------+-----------------+------+---------------------+---------------------+---------+-------------------------------+-------+-------------+
|  1 | SIMPLE      | contests        | ALL  | PRIMARY             | NULL                | NULL    | NULL                          | 37660 |             | 
|  1 | SIMPLE      | contest_users   | ref  | contest_users_idx   | contest_users_idx   | 4       | fbtable.contests.chid         |     2 | Using index | 
+----+-------------+-----------------+------+---------------------+---------------------+---------+-------------------------------+-------+-------------+
2 rows in set (0.00 sec)

---

mysql> desc contests;
  +------------+------------------+------+-----+----------+----------------+
  | Field      | Type             | Null | Key | Default  | Extra          |
  +------------+------------------+------+-----+----------+----------------+
  | chid       | int(10) unsigned | NO   | PRI | NULL     | auto_increment | 
  | puzzle     | char(81)         | YES  |     | NULL     |                | 
  | solution   | char(81)         | YES  |     | NULL     |                | 
  | isComplete | tinyint(1)       | YES  |     | 0        |                | 
  | diff_level | char(7)          | YES  |     | NULL     |                | 
  | time       | time             | YES  |     | 00:00:00 |                | 
  +------------+------------------+------+-----+----------+----------------+
  6 rows in set (0.00 sec)

---

mysql> desc contest_users;
  +------------+----------------------+------+-----+---------+-------+
  | Field      | Type                 | Null | Key | Default | Extra |
  +------------+----------------------+------+-----+---------+-------+
  | chid       | int(10) unsigned     | NO   | MUL | NULL    |       | 
  | uid        | bigint(20) unsigned  | YES  |     | NULL    |       | 
  | gamestate  | char(81)             | YES  |     | NULL    |       | 
  | score      | int(10) unsigned     | YES  |     | 0       |       | 
  | no_correct | smallint(5) unsigned | YES  |     | 0       |       | 
  | no_wrong   | smallint(5) unsigned | YES  |     | 0       |       | 
  | time       | time                 | YES  |     | NULL    |       | 
  | isComplete | tinyint(1)           | YES  |     | 0       |       | 
  +------------+----------------------+------+-----+---------+-------+
  8 rows in set (0.00 sec)

Answer 1

我的猜测是，因为行数很少。

当您有数千行时，查询优化器可能会做出不同的决定。

Answer 2

我（最近）的建议是在竞赛表上添加一个键，它必须包含用于连接（chid）的列以及您想要从该查询中获得的列。

这将导致mysql实际上使用索引来获取数据而不是扫描整个表（解释结果中的type =“index”，extra =“use index”）。

由于 chid 位于结果集中并且也处于连接条件中，因此在您的情况下它将如下所示：

alter table contests add key `chid_diffLevel` (chid, diff_level);

我希望它对您有用。我已经在我的一个数据库上尝试过了，我知道它对我有用。